%#"͕։ൃνʔϜʹΑ ͘ฉ͖͔ΕΔ͜ͱબ .Z42-
$BTVBM
5BMLT
JO
'VLVPLB
7PM
5BLBZVLJ
)POEB !WJEBJTVLJ


ࣗݾ঺հ w 5BLBZVLJ
)POEB
!WJEBJTVLJ
w ౎಺ͷ๭ࣾͰ%#"΍ͬͯ·͢
w .Z/"ձͰ-5ਓһ͕଍Γͳ͍ͱͷ࿩Λฉ͖ɺ੎͍ͰࢀՃ͠·͠ ͨ
w -5͸ॳΊͯͰ͕͢ποίϛ͸ͲΜͲΜ͓ئ͍͠·͢ ?@?ʢެ։ ͢Δ࣌಺ॹͰ௚͠·͢ʣ 

ࠓ೔ͷ͓୊ wҰ೥લ͘Β͍ʹࠓͷձࣾʹ དྷͯΑ͘ฉ͔Εͨࣄͱରॲ ͨ͠ࣄΛͭΒͭΒͱ࿩͠· ͢ɻ 

σουϩοΫ͕ͨ͘͞Μൃੜ͢Δ ΜͰ͚͢Ͳɾɾ 

αϯϓϧςʔϒϧ $3&"5&
5"#-&
AVTFS@UBCMFA



A64&3@*%A
CJHJOU 
/05
/6--
"650@*/$3&.&/5



A130+&$5@$0%&A
CJHJOU 
/05
/6--



A$6450.&3@*%A
CJHJOU 
/05
/6--



A$0..&/5A
WBSDIBS 
$0--"5&
VUGNC@CJO


13*."3:
,&:
A64&3@*%A


6/*26&
,&:
A6,@1&0+&$5@$0%&@$6450.&3@*%A
A130+&$5@$0%&A A$6450.&3@*%A

&/(*/&*OOP%#
%&'"6-5
$)"34&5VUGNC
$0--"5&VUGNC@CJO ˡ͕͜͜6/*26&ɹ,&: 

αϯϓϧσʔλ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF    IPHF    IPHF    IPHF 

͜͜ʹσʔλΛೖΕ͍ͨ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF    IPHF    GVHB    IPHF    IPHF 

࢓༷ 
#&(*/

61%"5&
VTFS@UBCMF
4&5
$0..&/5

GVHB
8)&3&

130+&$5@$0%&


"/%
$6450.&3@*%


2VFSZ
0,

SPXT
B⒎FDUFE

TFD
3PXT
NBUDIFE


$IBOHFE


8BSOJOHT

6QEBUFͯ݅ͩͬͨ͠৔߹͸*OTFSU

*/4&35
*/50
VTFS@UBCMF
130+&$5@$0%& $6450.&3@*% $0..&/5
7"-6&4
  GVHB 

$0..*5 

6QEBUF͕ۭৼΔͱΪϟοϓϩοΫΛऔΔ 59

#&(*/

61%"5&
VTFS@UBCMF
4&5
$0..&/5

GVHB
8)&3&

130+&$5@$0%&


"/%
$6450.&3@*%


2VFSZ
0,

SPXT
B⒎FDUFE

TFD
3PXT
NBUDIFE


$IBOHFE


8BSOJOHT

mysql> SELECT * FROM performance_schema.data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2098:1064 ENGINE_TRANSACTION_ID: 2098 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: honda OBJECT_NAME: user_table PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140578507493192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2098:3:5:4 ENGINE_TRANSACTION_ID: 2098 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: honda OBJECT_NAME: user_table PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: UK_PEOJECT_CODE_CUSTOMER_ID OBJECT_INSTANCE_BEGIN: 140578507490152 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 100, 100020 2 rows in set (0.00 sec) -0$,@.0%&
9 ("1
-0$,@%"5"
  

ผͷΫΤϦ͕ಉ͡6QEBUFΛ౤͛Δͱ
ΪϟοϓϩοΫΛ̎ॏͰऔΔ 59

#&(*/

61%"5&
VTFS@UBCMF
4&5
$0..&/5

GVHB
8)&3&

130+&$5@$0%&


"/%
$6450.&3@*%


2VFSZ
0,

SPXT
B⒎FDUFE

TFD
3PXT
NBUDIFE


$IBOHFE


8BSOJOHT

mysql> SELECT * FROM performance_schema.data_locks\G; +--------+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ | INNODB | 2255:1070 | 2255 | 49 | 51 | user_table | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 2255:9:5:4 | 2255 | 49 | 51 | user_table | UK_PEOJECT_CODE_CUSTOMER_ID | RECORD | X,GAP | GRANTED | 100, 100020 | | INNODB | 2254:1070 | 2254 | 48 | 140 | user_table | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 2254:9:5:4 | 2254 | 48 | 140 | user_table | UK_PEOJECT_CODE_CUSTOMER_ID | RECORD | X,GAP | GRANTED | 100, 100020 | +————+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ ˞ΧϥϜҰ෦ུ


σʔλ͕ແ͍ͷͰ*OTFSU͕ͦΕͧΕ࣮ߦ͞Ε
%FBE-PDLൃੜ 1)Tx1:ͰInsert mysql> INSERT INTO user_table (PROJECT_CODE,CUSTOMER_ID,COMMENT) VALUES (100,100015,'fuga'); Tx2ͷϩοΫ଴ͪ 2)Tx2:ͰInsert mysql> INSERT INTO user_table (PROJECT_CODE,CUSTOMER_ID,COMMENT) VALUES (100,100015,'fuga'); Tx1ͷϩοΫ଴ͪͰσουϩοΫ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 3)Tx2͸ϩʔϧόοΫ͞ΕTx1ͷINSERT͸࣮ߦ͞ΕΔɻ Query OK, 1 row affected (17.22 sec) 

ਤղ 130+&$5@$0%& $6450.&3@*%     ˣ ˣ     ᶃVQEBUF ᶄVQEBUF 5Y 5Y ᶅJOTFSU ᶆJOTFSU ᶇ
☠%FBE-PDL 

ରԠ wجຊతʹ%FBE-PDL͸ϩδοΫଆͰ௚ͯ͠΋ Β͏͔͠ͳ͍ͷͰɺ࠶ݱͤͯ͞ΞϓϦέʔγϣ ϯΤϯδχΞʹ͓౉͢͠Δ༁Ͱ͕͢ɺ.Z42- ͷϩοΫ͸தʑ૝ఆͮ͠Β͍ॴͰൃੜ͠·͢ Ͷɻ
w˞͖͞΄ͲͷϩοΫ͸෼཭Ϩϕϧ

3FBE
$PNJUUFEͰ͸ൃੜ͠·ͤΜɻ


ΧϥϜ௥Ճ͍ͨ͠ΜͰ͚͢Ͳɾɾ 

ΦϯϥΠϯͰग़དྷ·͔͢ʁ w .Z42-
͔Β0/-*/&
"-5&3
5"#-& "-(03*5).*/1-"$& ͕͋ΔͷͰΦϯϥΠϯͰ࣮ߦ ग़དྷ·͢ɻ

w ͨͩ͠%%-࣮ߦͷ࢝ΊͱऴΘΓͰ.FUBEBUB-PDLΛऔΓ·͢ɻ ςʔϒϧ࠶ߏஙத΋ෛՙߴΊɻ
w ͳͷͰෛՙͷ௿͍࣌ؒଳʹ࣮ߦ͢Δͷ͕޷·͍͠ɻ 

ͲΕ͘Β͍͔͔Γ·͔͢ʁ w ͜Ε͕ͳ͔ͳ͔೉͍͠ɻ
w ςʔϒϧͷσʔλαΠζʹԠ͕ͯ࣌ؒ͡มΘΔͷͰɺຊ൪ͱ ಉ౳ͷσʔλͰςετ
w ͔ͭຊ൪ͱಉ౳ͷෛՙͰςετʢಉΦϒδΣΫτͰߋ৽ɾࢀ র͕͋Δͱ͔͔Δ͕࣌ؒେ෼มΘΔʣ
w ຊ൪ͱಉ͡ෛՙΛֻ͚Δͬͯ݁ߏΊΜͲ͍ɾɾ
w ͱ͍͏༁Ͱ೔࿨ͬͯϝϯςೖΕͪΌ͏࣌΋··͋Γ·͢ɻ 

ظ଴ͷ৽੕
*OTUBOU
"EE
$PMVNO ALTER TABLE t1 ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT; Currently, InnoDB supports INSTANT algorithm for these operations: ▪Change index option ▪Rename table (in ALTER way) ▪SET/DROP DEFAULT ▪MODIFY COLUMN ▪Add/drop virtual columns ▪Add columns(non-generated) – We call this instant ADD COLUMN ▪ ࢀߟ
▪ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ ▪ https://mysqlserverteam.com/the-mysql-8-0-12-maintenance-release-is-generally-available/ ▪ https://mysql.wisborg.dk/2018/07/29/mysql-8-0-12-instant-alter-table/ ʙ 

͕͢͞ʹ.FUBEBUB-PDL଴ͪ͸༗ΔΈ͍ͨͰ͕͢ɾɾɾ
ૣ͘ຊ൪Ͱ࢖͍͍ͨʂʂ TX1: mysql> BEGIN; mysql> SELECT user_id FROM user_table where user_id = 1; +---------+ | user_id | +---------+ | 1 | +————+ 1 row in set (0.00 sec) mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 140577299575536 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5617 | 55 | 20 | | TABLE | honda | user_table | NULL | 140577299549744 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5617 | 55 | 21 | +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ 2 rows in set (0.00 sec) -------------------------------------------------------------------------------------------------------------------- TX2: ALTER TABLE user_table ADD COLUMN dd INT NOT NULL DEFAULT 1000, ALGORITHM=INSTANT; ɻɻɻɻ 

ΞϓϦαʔόʔ૿΍͍ͨ͠Μ Ͱ͚͢Ͳ ίωΫγϣϯ਺૿΍ͯ͠ ΋େৎ෉ ʁ 

.Z42-ͷϝϞϦߏ੒ ࡶ JOOPEC@CV⒎FS@QPPM@TJ[F
JOOPEC@MPH@pMF@TJ[F TPSU@CV⒎FS@TJ[F
SFBE@CV⒎FS@TJ[F
SFBE@SOE@CV⒎FS@TJ[F
CJOMPH@DBDIF@TJ[F
UNQ@UBCMF@TJ[F
UISFBE@TUBDL
OFU@CV⒎FS άϩʔόϧόοϑΝ
ηογϣϯόοϑΝ 

࢖༻ϝϞϦαΠζͷݟੵ΋Γ ࡶ άϩʔόϧόοϑΝ



ηογϣϯόοϑΝ
Y
ίωΫγϣϯ਺


Ωϟογϡͱ͔04͕࢖͏෼


ͨͩ͠ ηογϣϯόοϑΝ͸ઃఆ஋ΛϑϧαΠζඞͣ֬อ ͢ΔΘ͚Ͱ͸ͳ͘ɺίωΫγϣϯຖʹϑϧαΠζ֬ อ͢Δ΋ͷɺ࠷খ஋͔Β࢝·ͬͯඞཁʹԠͯ͡࠷େ ஋·Ͱ֬อ͞ΕΔ΋ͷɺඞཁͳঢ়گʹͳͬͨΒϑϧ αΠζ֬อ͢Δ΋ͷɾɾͳͲ৭ʑύλʔϯ͕͋ΔΒ ͍͠ ͋Μ·ΓϚχϡΞϧʹॻ͍ͯͳ͍ʣɻ


KPJO@CV⒎FS@TJ[F w ΠϯσοΫεΛ࢖༻͠ͳ͍+0*/Ͱ࢖༻͞ΕΔɻ
w σϑΥϧτ஋
,# Ҏ߱
w ઃఆ஋͕.#ͩͬͨ
w ࢀߟ
 IUUQTXXXQFSDPOBDPNCMPH IPXJTKPJO@CV⒎FS@TJ[FBMMPDBUFE 

εϨουຖͷ࣮ফඅϝϞϦྔΛܭଌ NZTRM
TFMFDU

GSPN
TZTNFNPSZ@CZ@UISFBE@CZ@DVSSFOU@CZUFT

c
UISFBE@JE
c
VTFS



























c
DVSSFOU@DPVOU@VTFE
c
DVSSFOU@BMMPDBUFE
c
DVSSFOU@BWH@BMMPD
c
DVSSFOU@NBY@BMMPD
c
UPUBM@BMMPDBUFE
c

c








c
IPOEB!YYYYYYYYY













c
















c

.J#









c

,J#








c

.J#









c

.J#





c
c








c
IPOEB!YYYYYYYYY













c

















c

,J#







c

,J#









c

,J#







c

.J#






c
c








c
SETBENJO!MPDBMIPTU













c

















c

,J#








c

CZUFT








c

,J#








c

.J#






c
c








c
TRMTJHOBM@IBOEMFS













c


















c

CZUFT










c

CZUFT










c

CZUFT










c

CZUFT








c
c








c
TRMDPNQSFTT@HUJE@UBCMF








c


















c

CZUFT










c

CZUFT










c

CZUFT










c

CZUFT








c
c









c
TRMNBJO























c


















c

CZUFT










c

CZUFT










c

CZUFT










c

CZUFT








c
c









c
TRMUISFBE@UJNFS@OPUJpFS






c


















c

CZUFT










c

CZUFT










c

CZUFT










c

CZUFT








c
c









c
JOOPECJP@XSJUF@UISFBE









c


















c

CZUFT










c

CZUFT










c

CZUFT










c

CZUFT








c
mysql> select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id = 42 and SUM_NUMBER_OF_BYTES_ALLOC <> 0; +-----------+-------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+-------------------------- +------------------------------+---------------------------+ | THREAD_ID | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | +-----------+-------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+-------------------------- +------------------------------+---------------------------+ | 42 | memory/sql/thd::main_mem_root | 673 | 671 | 8127360 | 8111040 | 0 | 2 | 16 | 0 | 16320 | 326400 | | 42 | memory/sql/THD::sp_cache | 1 | 0 | 8176 | 0 | 0 | 1 | 1 | 0 | 8176 | 8176 | | 42 | memory/sql/sp_head::main_mem_root | 97 | 0 | 1122536 | 0 | 0 | 97 | 97 | 0 | 1122536 | 1122536 | | 42 | memory/sql/sp_head::execute_mem_root | 2447 | 2447 | 19967520 | 19967520 | 0 | 0 | 2 | 0 | 0 | 16320 | | 42 | memory/sql/sp_head::call_mem_root | 797 | 797 | 6503520 | 6503520 | 0 | 0 | 2 | 0 | 0 | 16320 | | 42 | memory/sql/String::value | 951 | 920 | 90544 | 73272 | 0 | 31 | 33 | 0 | 17272 | 32808 | | 42 | memory/sql/THD::db | 809 | 808 | 3251 | 3232 | 0 | 1 | 2 | 0 | 19 | 23 | | 42 | memory/sql/user_var_entry | 7 | 5 | 2155 | 992 | 0 | 2 | 3 | 0 | 1163 | 1675 | | 

ରԠ wશίωΫγϣϯ͕ઃఆ஋Λ'VMMͰ࢖͏ͱɺܭࢉ্ϝ ϞϦ͋;ΕΔͷͰίωΫγϣϯ૿΍ͤͳ͍ɾɾͱ͍ ͏ঢ়گͩͬͨ
wઃఆ஋Λదਖ਼Խ͢Δ εϨουόοϑΝ͸૿΍͗͢͠ ې෺
wࠓ͸͕Μ͕Μαʔόʔ૿΍ͯ͠·͢ 

%#"ͷਓͬͯ
ීஈԿͯ͠ΔΜͰ͔͢ʁ 

৭ʑ΍ͬͯ·͢ wલهͨ͠Α͏ͳ໰͍߹ΘͤରԠ
wςʔϒϧઃܭϨϏϡʔ
wෛՙ؂ࢹ
w42-ύϑΥʔϚϯενϡʔχϯά
wτϥϒϧఆظϝϯςφϯεରԠ
w৽όʔδϣϯݕূ
wσʔλநग़
wσʔλϕʔεΠϯετʔϧ
wόοΫΞοϓઃܭ 

ࠓ·Ͱ͜Μͳମ੍Ͱ࢓ࣄ͖ͯ͠·͕ͨ͠ %#" *OGSB %FW ʙਓ ʙਓ͘Β͍ ͨ͘͞Μ 

Ϋϥ΢υશ੝ͷ͜ͷ࣌͝ੈɾɾ *OGSB%#" %FW Ռͯ͸ %FW*OGSB%#" 

ࠓޙ΁ͷෆ҆ɾɾ %FW *OGSB %#" 0UIFS 

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ