MySQLのロックについて（DDL編）

Transcript

1. .Z42-ͷϩοΫʹ͍ͭͯ ʙ%%-ฤʙ
2. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
   wΦϯϥΠϯ%%-
   wNFUBEBUB
   MPDL
   ʹ͸ؾΛ෇͚Ζ
   w"-5&3ؔ࿈ͷখٕ
   w"NB[PO
   "VSPSB
   ͱ
   7BOJMMB
   .Z42-
   ͷҧ͍
   wਪ঑ͷ%%-ϦϦʔεํ๏
3. ͓͞Β͍ w%%-
   %BUB
   %FpOJUJPO
   -BOHVBHF
   w $3&"5&
   %301
   "-5&3
   536/$"5&
   w%.-
   %BUB
   .BOJQVMBUJPO
   -BOHVBHF
   w 4&-&$5
   */4&35
   61%"5&
   %&-&5&
   w%$-
   %BUB
   $POUSPM
   -BOHVBHF
   w #&(*/
   $0..*5
   30--#"$,
4. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
   wΦϯϥΠϯ%%-
   wNFUBEBUB
   MPDL
   ʹ͸ؾΛ෇͚Ζ
   w"-5&3ؔ࿈ͷখٕ
   w"NB[PO
   "VSPSB
   ͱ
   7BOJMMB
   .Z42-
   ͷҧ͍
   wਪ঑ͷ%%-ϦϦʔεํ๏
5. .Z42-
   "-5&3
   5"#-&ͷॳظ࣮૷ w৽͍͠ςʔϒϧఆٛͷςʔϒϧʹσʔλΛίϐʔ
   w ࣮ߦதɺ؍ଌ͢ΔͱςϯϙϥϦϑΝΠϧ͕ݟ͑Δ
   w ίϐʔ͕ऴΘͬͨΒೖΕସ͑
   w࣮ߦத͸ॻ͖ࠐΈ͕ϒϩοΫ͞ΕΔ
6. վળͷาΈ w .Z42-
   W
   w 'BTU
   *OEFY
   $SFBUJPO
   ͕αϙʔτ
   w .Z42-
   W
   w ΦϯϥΠϯ%%-͕αϙʔτ
   w ߋ৽ϒϩοΫΛճආՄ w .Z42-
   
   w WBSDIBSͷΧϥϜ௕֦ு͕ΦϯϥΠϯରԠ
   w .Z42-
   
   w %%-͕ΞτϛοΫʹ
   w *OTUBOU
   "EE
   $PMVNO
   ͷαϙʔτ
7. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
   wΦϯϥΠϯ%%-
   wNFUBEBUB
   MPDL
   ʹ͸ؾΛ෇͚Ζ
   w"-5&3ؔ࿈ͷখٕ
   w"NB[PO
   "VSPSB
   ͱ
   7BOJMMB
   .Z42-
   ͷҧ͍
   wਪ঑ͷ%%-ϦϦʔεํ๏
8. ΦϯϥΠϯ%%- w ରԠͰ͖Δέʔεͱͦ͏Ͱͳ͍έʔε͕͋Δ
 IUUQTEFWNZTRMDPNEPDSFGNBOKBJOOPECDSFBUFJOEFYPWFSWJFXIUNM
   wରԠ͍ͯ͠Δ৔߹ɺࣗಈతʹΦϯϥΠϯ%%-͕બ୒͞ΕΔ
9. ΦϯϥΠϯ%%- w ΦϯϥΠϯ%%-ɹରԠɾඇରԠ
   w ˓ΠϯσοΫε௥Ճɾ࡟আɺΧϥϜ௥Ճɾ࡟আ
   w º
   WBSDIBSΧϥϜ௕ͷมߋʢWʣ
   w º
   ΧϥϜͷܕͷมߋ
   w -0$,/0/&
   "-(03*5).*/1-"$&
   Λ໌ࣔతʹࢦఆ͠ɺ൑ผՄೳ mysql> ALTER TABLE sbtest.sbtest1 MODIFY COLUMN v1 VARCHAR(110), LOCK=NONE, ALGORITHM=INPLACE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
10. ΦϯϥΠϯ%%-ͷ஫ҙ఺ w NFUBEBUB
    MPDL
    ͸ճආ͢Δ͜ͱ͸Ͱ͖ͳ͍ʢޙड़ʣ
    w ॻ͖ࠐΈϒϩοΫ͸ൃੜ͠ͳ͍͕ɺϨϓϦ஗Ԇ͸ൃੜ͢Δ
 
 ˞
    "84
    "VSPSB
    Ͱ͸ϨϓϦ஗Ԇ͸΄΅ൃੜ͠ͳ͍ʢޙड़ʣ
    w JOOPEC@POMJOF@BMUFS@MPH@NBY@TJ[F
    EFGBVMU
    .#
    ΛඞཁʹԠͯ͡ௐ੔
    w ΦϯϥΠϯ%%-தʹߋ৽͞Εͨ಺༰Λ֨ೲ
11. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
    wΦϯϥΠϯ%%-
    wNFUBEBUB
    MPDL
    ʹ͸ؾΛ෇͚Ζ
    w"-5&3ؔ࿈ͷখٕ
    w"NB[PO
    "VSPSB
    ͱ
    7BOJMMB
    .Z42-
    ͷҧ͍
    wਪ঑ͷ%%-ϦϦʔεํ๏
12. "-5&3
    5"#-&ʹඞཁͳϩοΫ w %%-
    ࣮ߦதͷϩοΫʢߋ৽ͷϒϩοΫʣ
    ˠɹΦϯϥΠϯ%%-Ͱղܾ
    w %%-
    ׬ྃ௚લͷϩοΫɹNFUBEBUB
    MPDL
13. NFUBEBUB
    MPDL
14. NFUBEBUB
    MPDL
15. w ௕͍τϥϯβΫγϣϯ͕"-5&3ͷ׬ྃΛϒϩοΫ
    w .%-଴ͪͷ"-5&3ͷ׬ྃΛɺޙଓͷ42-͕଴ͭ NFUBEBUB
    MPDL 4&44*0/ 4&44*0/ 4&44*0/ "-5&3 5*.& 8"*5
    .%- 8"*5
    .%-
16. ରࡦ w ௕͍τϥϯβΫγϣϯ͕ྲྀΕͯͳ͍λΠϛϯάΛૂ͏
    w MPDL@XBJU@UJNF
    Λ
    ୹͘ઃఆ͢Δ
    w 4&5
    4&44*0/
    MPDL@XBJU@UJNF9
    w ա౓ʹ୹͍ͱɺ"-5&3͕ࣦഊ͠΍͘͢ͳΔ
    w ਺ඵఔ౓͕ଥ౰Ͱ͸
17. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
    wΦϯϥΠϯ%%-
    wNFUBEBUB
    MPDL
    ʹ͸ؾΛ෇͚Ζ
    w"-5&3ؔ࿈ͷখٕ
    w"NB[PO
    "VSPSB
    ͱ
    7BOJMMB
    .Z42-
    ͷҧ͍
    wਪ঑ͷ%%-ϦϦʔεํ๏
18. "-5&3
    5*14 w ෳ਺ͷมߋ͸̍ͭͷ"-5&3
    จͰ࣮ߦͰ͖Δ
    w ͨͩ͠ɺΦϯϥΠϯ%%-ඇରԠͷ΋ͷΛࠞͥΔͱɺϩοΫΛऔΔ mysql> ALTER TABLE sbtest.sbtest1 
 MODIFY COLUMN v1 VARCHAR(110), DROP COLUMN x2;
19. w ΦϯϥΠϯͰ%%-Λ࣮ߦ͢Δӡ༻ख๏
    w ྲྀΕ
     ϨϓϦΧҰ୆ΛαʔϏε͔Β֎͢
     TRM@MPH@CJO
    ্ͨ͠Ͱ
    "-5&3
    ࣮ࢪ
     ϨϓϦΧΛαʔϏεʹ໭͢
     શͯͷϨϓϦΧʹ্هΛॱ൪ʹ࣮ࢪ
     "-5&3ࡁϨϓϦΧͱϚελʔΛೖΕସ͑Δ
     ࠷ޙʹɺچϚελʔʹରͯ͠"-5&3࣮ߦ
    w ஫ҙ఺
    w "84
    "VSPSB
    Ͱ͸ΠϯελϯεؒϨϓϦέʔγϣϯΛར༻͢Δඞཁ͕͋Δ
    w ϨϓϦΧΛϚελʔʹॠ࣌ʹঢ֨Ͱ͖Δ࢓૊Έ͕ඞཁ
    w "-5&3࣮ࢪલޙͰϨϓϦέʔγϣϯͷޓ׵ੑΛอͭมߋʹݶΒΕΔ
    w ˓
    จࣈྻ௕ͷ֦ுɺςʔϒϧ຤ඌ΁ͷΧϥϜ௥Ճ
    w º
    ܕมߋɺΧϥϜ࡟আ 3PMMJOH
    4DIFNB
    6QHSBEF  3FQMJDB  3FQMJDB "QQ  .BTUFS "-5&3  3FQMJDB  3FQMJDB "QQ  .BTUFS "-5&3 ࡁ
     .BTUFS  3FQMJDB "QQ  3FQMJDB ࡁ
    ࡁ
    
20. w ΦϯϥΠϯͰ%%-Λ࣮ߦ͢Δπʔϧ
    w ࢓૊Έ
     ৽͍͠ఆٛͷςʔϒϧΛ࡞੒
     ϩοΫΛ͔͚ͣʹɺݱߦˠ৽ͰσʔλΛͪΐͬͱͣͭίϐʔ
     ݱߦςʔϒϧ΁ͷߋ৽͸
 τϦΨʔͰ৽ςʔϒϧ΁࿈ܞ
    w ஫ҙ఺
    w ॻ͖ࠐΈྔ͕̎ഒʹ૿͑Δ
    w NFUBEBUB
    MPDL
    ͸ճආͰ͖ͳ͍ QUPOMJOFTDIFNBDIBOHF
21. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
    wΦϯϥΠϯ%%-
    wNFUBEBUB
    MPDL
    ʹ͸ؾΛ෇͚Ζ
    w"-5&3ؔ࿈ͷখٕ
    w"NB[PO
    "VSPSB
    ͱ
    7BOJMMB
    .Z42-
    ͷҧ͍
    wਪ঑ͷ%%-ϦϦʔεํ๏
22. "VSPSB
    ͱ
    7BOJMMB
    .Z42-
    ͷҧ͍ w %%-ʹΑΔϨϓϦΧ஗Ԇ͸ൃੜ͠ͳ͍

    w NFUBEBUB
    MPDL
    ͸ϚελʔͰͷΈߟྀ͢Ε͹ྑ͍
    w ͦͷ୅ΘΓɺ"-5&3࣮ߦ։࢝ɾ׬ྃ࣌ʹϨϓϦΧͰΫΤϦ͕୯ൃΤϥʔʹͳΔՄೳੑ͕͋Δ
23. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ
    wΦϯϥΠϯ%%-
    wNFUBEBUB
    MPDL
    ʹ͸ؾΛ෇͚Ζ
    w"-5&3ؔ࿈ͷখٕ
    w"NB[PO
    "VSPSB
    ͱ
    7BOJMMB
    .Z42-
    ͷҧ͍
    wਪ঑ͷ%%-ϦϦʔεํ๏
24. %%-࣮ߦӡ༻ϑϩʔ ΦϯϥΠϯ %%-ͷର৅͔ʁ ίʔυͱ
    ಉ࣌ϦϦʔε͕ඞཁʁ :&4 ਂ໷ϦϦʔε εςʔδϯάͰ
    ݕূ͠ɺ໰୊ͳ͚Ε͹೔தͰ(0 ҰॠͰ
    ऴΘΔʁ :&4 /0 :&4 /0 NJHSBUJPOͰ͸
    MPDL@XBJU@UJNFPVU͕
    ઃఆͰ͖ͳ͍ͷͰ
    खಈͰ΍Δ
25. ·ͱΊ w NFUBEBUB
    MPDL
    ͸ΦϯϥΠϯ%%-Ͱ΋ճආͰ͖ͳ͍ͷͰ஫ҙ
    w "VSPSB
    Ͱ͸ϨϓϦ஗Ԇ͕ൃੜ͠ͳ͍ͨΊɺΦϯϥΠϯ%%-ͷ׆༻͕͠΍ ͍͢
    w ࠓޙ͸ɺੵۃతʹ೔தʹ%%-Λྲྀ͍͖ͯ͠·͠ΐ͏
26. 5IBOLT