Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQLのロックについて(DDL編)

 MySQLのロックについて(DDL編)

8847086af047cbf895ab3277b59529fe?s=128

ANDPAD inc

August 18, 2020
Tweet

More Decks by ANDPAD inc

Other Decks in Technology

Transcript

  1. .Z42-ͷϩοΫʹ͍ͭͯ ʙ%%-ฤʙ

  2. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ wΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ 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ΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ w"-5&3ؔ࿈ͷখٕ w"NB[PO"VSPSBͱ7BOJMMB.Z42-ͷҧ͍ wਪ঑ͷ%%-ϦϦʔεํ๏

  5. .Z42-"-5&35"#-&ͷॳظ࣮૷ 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ΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ 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 NFUBEBUBMPDL͸ճආ͢Δ͜ͱ͸Ͱ͖ͳ͍ʢޙड़ʣ w ॻ͖ࠐΈϒϩοΫ͸ൃੜ͠ͳ͍͕ɺϨϓϦ஗Ԇ͸ൃੜ͢Δ
 
 ˞"84"VSPSBͰ͸ϨϓϦ஗Ԇ͸΄΅ൃੜ͠ͳ͍ʢޙड़ʣ w JOOPEC@POMJOF@BMUFS@MPH@NBY@TJ[F EFGBVMU.#

    ΛඞཁʹԠͯ͡ௐ੔ w ΦϯϥΠϯ%%-தʹߋ৽͞Εͨ಺༰Λ֨ೲ
  11. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ wΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ w"-5&3ؔ࿈ͷখٕ w"NB[PO"VSPSBͱ7BOJMMB.Z42-ͷҧ͍ wਪ঑ͷ%%-ϦϦʔεํ๏

  12. "-5&35"#-&ʹඞཁͳϩοΫ w %%-࣮ߦதͷϩοΫʢߋ৽ͷϒϩοΫʣˠɹΦϯϥΠϯ%%-Ͱղܾ w %%-׬ྃ௚લͷϩοΫɹNFUBEBUBMPDL

  13. NFUBEBUBMPDL

  14. NFUBEBUBMPDL

  15. w ௕͍τϥϯβΫγϣϯ͕"-5&3ͷ׬ྃΛϒϩοΫ w .%-଴ͪͷ"-5&3ͷ׬ྃΛɺޙଓͷ42-͕଴ͭ NFUBEBUBMPDL 4&44*0/ 4&44*0/ 4&44*0/ "-5&3 5*.&

    8"*5 .%- 8"*5 .%-
  16. ରࡦ w ௕͍τϥϯβΫγϣϯ͕ྲྀΕͯͳ͍λΠϛϯάΛૂ͏ w MPDL@XBJU@UJNFΛ୹͘ઃఆ͢Δ w 4&54&44*0/MPDL@XBJU@UJNF9 w ա౓ʹ୹͍ͱɺ"-5&3͕ࣦഊ͠΍͘͢ͳΔ w

    ਺ඵఔ౓͕ଥ౰Ͱ͸
  17. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ wΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ w"-5&3ؔ࿈ͷখٕ w"NB[PO"VSPSBͱ7BOJMMB.Z42-ͷҧ͍ wਪ঑ͷ%%-ϦϦʔεํ๏

  18. "-5&35*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 ºܕมߋɺΧϥϜ࡟আ 3PMMJOH4DIFNB6QHSBEF  3FQMJDB  3FQMJDB "QQ  .BTUFS "-5&3  3FQMJDB  3FQMJDB "QQ  .BTUFS "-5&3 ࡁ  .BTUFS  3FQMJDB "QQ  3FQMJDB ࡁ ࡁ
  20. w ΦϯϥΠϯͰ%%-Λ࣮ߦ͢Δπʔϧ w ࢓૊Έ  ৽͍͠ఆٛͷςʔϒϧΛ࡞੒  ϩοΫΛ͔͚ͣʹɺݱߦˠ৽ͰσʔλΛͪΐͬͱͣͭίϐʔ  ݱߦςʔϒϧ΁ͷߋ৽͸


    τϦΨʔͰ৽ςʔϒϧ΁࿈ܞ w ஫ҙ఺ w ॻ͖ࠐΈྔ͕̎ഒʹ૿͑Δ w NFUBEBUBMPDL͸ճආͰ͖ͳ͍ QUPOMJOFTDIFNBDIBOHF
  21. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ wΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ w"-5&3ؔ࿈ͷখٕ w"NB[PO"VSPSBͱ7BOJMMB.Z42-ͷҧ͍ wਪ঑ͷ%%-ϦϦʔεํ๏

  22. "VSPSBͱ7BOJMMB.Z42-ͷҧ͍ w %%-ʹΑΔϨϓϦΧ஗Ԇ͸ൃੜ͠ͳ͍
  w NFUBEBUBMPDL͸ϚελʔͰͷΈߟྀ͢Ε͹ྑ͍ w ͦͷ୅ΘΓɺ"-5&3࣮ߦ։࢝ɾ׬ྃ࣌ʹϨϓϦΧͰΫΤϦ͕୯ൃΤϥʔʹͳΔՄೳੑ͕͋Δ

  23. ໨࣍ w%%-ͷ࢓૊ΈͱվળͷาΈ wΦϯϥΠϯ%%- wNFUBEBUBMPDLʹ͸ؾΛ෇͚Ζ w"-5&3ؔ࿈ͷখٕ w"NB[PO"VSPSBͱ7BOJMMB.Z42-ͷҧ͍ wਪ঑ͷ%%-ϦϦʔεํ๏

  24. %%-࣮ߦӡ༻ϑϩʔ ΦϯϥΠϯ %%-ͷର৅͔ʁ ίʔυͱ ಉ࣌ϦϦʔε͕ඞཁʁ :&4 ਂ໷ϦϦʔε εςʔδϯάͰ ݕূ͠ɺ໰୊ͳ͚Ε͹೔தͰ(0 ҰॠͰ

    ऴΘΔʁ :&4 /0 :&4 /0 NJHSBUJPOͰ͸ MPDL@XBJU@UJNFPVU͕ ઃఆͰ͖ͳ͍ͷͰ खಈͰ΍Δ
  25. ·ͱΊ w NFUBEBUBMPDL͸ΦϯϥΠϯ%%-Ͱ΋ճආͰ͖ͳ͍ͷͰ஫ҙ w "VSPSBͰ͸ϨϓϦ஗Ԇ͕ൃੜ͠ͳ͍ͨΊɺΦϯϥΠϯ%%-ͷ׆༻͕͠΍ ͍͢ w ࠓޙ͸ɺੵۃతʹ೔தʹ%%-Λྲྀ͍͖ͯ͠·͠ΐ͏

  26. 5IBOLT