溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“MySQL死鎖分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
RC 隔離級別很少出GAP我已經知道的
繼承和分裂會出LOCK_GAP這是代碼寫死的
purge線程可能觸發
頁的分裂融合可能觸發
內部回滾可能觸發
唯一性檢查會出LOCK_ORDINARY[next_key_lock]
RC RR級別通用
死鎖表結構和數據
drop table testunj1 ; create table testunj1 (id1 int primary key,id2 int unique key,name varchar(20)); insert into testunj1 values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng'); mysql> select * from testunj1; +-----+------+---------+| id1 | id2 | name |+-----+------+---------+| 1 | 1 | gaopeng || 10 | 10 | gaopeng || 20 | 20 | gaopeng |+-----+------+---------+3 rows in set (0.01 sec)
死鎖構造流程
| T1 | T2 | T3 |
|---|---|---|
| begin;insert into testunj1 values(17,17,'gaopeng'); insert into testunj1 values(15,15,'gaopeng'); | ||
| begin; insert into testunj1 values(14,15,'gaopeng');堵塞 | ||
| begin; insert into testunj1 values(16,17,'gaopeng');堵塞 | ||
| rollback; | 成功 | 死鎖 |
死鎖記錄
------------------------ LATEST DETECTED DEADLOCK ------------------------2017-08-29 05:03:47 0x7f2fdc6f0700*** (1) TRANSACTION: TRANSACTION 7261233, ACTIVE 12 sec inserting mysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 3, OS thread handle 139843538720512, query id 583 localhost root update insert into testunj1 values(14,15,'gaopeng') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261233 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; *** (2) TRANSACTION: TRANSACTION 7261234, ACTIVE 5 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 139843538454272, query id 585 localhost root update insert into testunj1 values(16,17,'gaopeng') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261234 lock mode S locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`testunj1` trx id 7261234 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; *** WE ROLL BACK TRANSACTION (2)
這個死鎖實際上涉及到鎖的繼承和分裂我們分析如下兩個事物堵塞的案例和加鎖步驟，主要弄明白gap lock怎么來的。
set global innodb_lock_wait_timeout=200000; set global innodb_show_verbose_locks=1; set global transaction_isolation =1; 重新登陸會話建立表和插入數據如下： drop table testunj1 ;create table testunj1 (id1 int primary key,id2 int unique key,name varchar(20));insert into testunj1 values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng'); 如果有debug環境gdb斷點： lock_rec_set_nth_bit
步驟如下：
| T1 | T2 |
|---|---|
| 階段1 | |
| BEGIN;insert into testunj1 values(17,17,'gaopeng'); | |
| BEGIN;insert into testunj1 values(16,17,'gaopeng'); 堵塞 | |
| 階段2 | |
| ROLLBACK; |
我們只用2個事物來分析流程，實際上流程知道了原因也就知道了。
前奏
T1的插入不上任何鎖，因為插入如果下一條記錄沒有鎖，因此是隱含鎖。分析從T2
insert into testunj1 values(16,17,'gaopeng'); 堵塞開始
第一步 T2執行 insert into testunj1 values(16,17,'gaopeng'); 步驟1
T2幫助T1隱士鎖轉換 上LOCK_X，這里通過函數lock_rec_convert_impl_to_expl 進行轉換。
棧幀如下：
(gdb) bt#0 lock_rec_set_nth_bit (lock=0x3054068, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10c95a0, index=0x7fffa89e3410, mode=1059, rec_id=..., size=9) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d59d20, trx=0x7ffff10c95a0, owns_trx_mutex=false, add_to_hash=true, prdt=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#3 0x0000000001a40152 in lock_rec_add_to_queue (type_mode=1059, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, trx=0x7ffff10c95a0, caller_owns_trx_mutex=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853#4 0x0000000001a49cee in lock_rec_convert_impl_to_expl_for_trx (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, trx=0x7ffff10c95a0, heap_no=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6180#5 0x0000000001a4a124 in lock_rec_convert_impl_to_expl (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6242#6 0x0000000001a4a9f6 in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, mode=LOCK_S, gap_mode=0, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6446#7 0x0000000001aeff23 in row_ins_set_shared_rec_lock (type=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483#8 0x0000000001af108d in row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, s_latch=false, mtr=0x7ffff0d5aec0, offsets_heap=0x7fff9c02ef08) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115#9 0x0000000001af3440 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c02ef08, heap=0x7fff9c00e918, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034#10 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#11 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#12 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#13 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#14 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#15 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#16 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#17 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#18 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#19 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#20 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#21 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#22 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#23 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#24 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#25 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#26 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#27 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190---Type <return> to continue, or q <return> to quit---#28 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0#29 0x00007ffff6516bcd in clone () from /lib64/libc.so.6
第二步 insert into testunj1 values(16,17,'gaopeng'); 步驟2
需要做唯一性檢查不通過上LOCK_ORDINARY[next_key_lock]等待，唯一檢查會涉及到主鍵和唯一鍵，如果主鍵檢查通過則會插入數據，然后檢查二級唯一索引，如果唯一索引沖突，則主鍵插入的數據需要回滾。這里是因為每個索引是單獨調用row_ins_index_entry_step上層函數進行單獨插入的。
棧幀如下：
(gdb) bt#0 lock_rec_set_nth_bit (lock=0x30580e8, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10ca5f0, index=0x7fffa89e3410, mode=258, rec_id=..., size=9) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d5a1b0, trx=0x7ffff10ca5f0, owns_trx_mutex=true, add_to_hash=true, prdt=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#3 0x0000000001a3fd2b in RecLock::add_to_waitq (this=0x7ffff0d5a1b0, wait_for=0x3054068, prdt=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1731#4 0x0000000001a4091f in lock_rec_lock_slow (impl=0, mode=2, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2004#5 0x0000000001a40c94 in lock_rec_lock (impl=false, mode=2, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2082#6 0x0000000001a4ab0a in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, mode=LOCK_S, gap_mode=0, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:6457#7 0x0000000001aeff23 in row_ins_set_shared_rec_lock (type=0, block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200", index=0x7fffa89e3410, offsets=0x7fff9c02ef90, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:1483#8 0x0000000001af108d in row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, s_latch=false, mtr=0x7ffff0d5aec0, offsets_heap=0x7fff9c02ef08) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:2115#9 0x0000000001af3440 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c02ef08, heap=0x7fff9c00e918, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3034#10 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#11 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#12 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#13 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#14 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#15 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#16 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#17 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#18 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#19 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#20 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#21 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#22 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#23 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#24 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#25 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#26 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#27 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190---Type <return> to continue, or q <return> to quit---#28 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0#29 0x00007ffff6516bcd in clone () from /lib64/libc.so.6
這兩步完成后，我們可以到LOCK_S|LOCK_ORDINARY[next_key_lock]的存在
---TRANSACTION 19508, ACTIVE 143 sec inserting mysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 140737233942272, query id 684 localhost root update insert into testunj1 values(16,17,'gaopeng') ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 91 page no 4 n bits 72 index id2 of table ,addr is 0x3054068 `test`.`testunj1` trx id 19508 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock]) waiting(LOCK_WAIT) Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000011; asc ;; 1: len 4; hex 80000011; asc ;; ---TRANSACTION 19507, ACTIVE 148 sec2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1MySQL thread id 3, OS thread handle 140737234208512, query id 682 localhost root TABLE LOCK table `test`.`testunj1` trx id 19507 lock mode IX RECORD LOCKS space id 91 page no 4 n bits 72 index id2 of table ,addr is 0x3056b48 `test`.`testunj1` trx id 19507 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000011; asc ;; 1: len 4; hex 80000011; asc ;;
第三步 T1幫助T2做鎖繼承
從事物的指針0x7ffff10ca5f0可以看出是T2，如果有多個事物LOCK_GAP是兼容所以都可以繼承完成，LOCK_GAP的存在只是為了LOCK_INTENTION，就是為了防止幻讀。
如果做了GDB可以看到這里繼承的鎖：
(gdb) p lock->type_mode$1 = 546
546 = 512+2+32= LOCK_GAP+LOCK_S+LOCK_REC 這個鎖繼承給了 heap 4 也就是記錄 20(heir_heap_no=4, heap_no=5)
這里將LOCK_S|LOCK_GAP 繼承到heap_no 4 上也就是 記錄記錄20上。
棧幀如下：
2018-10-11T08:15:44.292686Z 4 [Note] InnoDB: Trx(19999) is blocked!!!!! [Switching to Thread 0x7ffff0da0700 (LWP 9278)] Breakpoint 2, lock_rec_set_nth_bit (lock=0x3058230, i=4) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:9191 ut_ad(lock); (gdb) bt#0 lock_rec_set_nth_bit (lock=0x3058230, i=4) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#1 0x0000000001a3f0cf in RecLock::lock_alloc (trx=0x7ffff10ca5f0, index=0x7fffa89e3410, mode=546, rec_id=..., size=9) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1484#2 0x0000000001a3f435 in RecLock::create (this=0x7ffff0d9ada0, trx=0x7ffff10ca5f0, owns_trx_mutex=false, add_to_hash=true, prdt=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1537#3 0x0000000001a40152 in lock_rec_add_to_queue (type_mode=546, block=0x7fffea699ba0, heap_no=4, index=0x7fffa89e3410, trx=0x7ffff10ca5f0, caller_owns_trx_mutex=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1853#4 0x0000000001a4299b in lock_rec_inherit_to_gap (heir_block=0x7fffea699ba0, block=0x7fffea699ba0, heir_heap_no=4, heap_no=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2787#5 0x0000000001a4475e in lock_update_delete (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200") at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3692#6 0x0000000001c26418 in btr_cur_optimistic_delete_func (cursor=0x7ffff0d9b7c0, flags=0, mtr=0x7ffff0d9b2b0) at /root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:5200#7 0x0000000001d54fe7 in row_undo_ins_remove_sec_low (mode=16386, index=0x7fffa89e3410, entry=0x7fffa89cde10, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:260#8 0x0000000001d55101 in row_undo_ins_remove_sec (index=0x7fffa89e3410, entry=0x7fffa89cde10, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:295#9 0x0000000001d555a8 in row_undo_ins_remove_sec_rec (node=0x7fffa89a25c0, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:429#10 0x0000000001d55810 in row_undo_ins (node=0x7fffa89a25c0, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0uins.cc:483#11 0x0000000001b69c80 in row_undo (node=0x7fffa89a25c0, thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:324#12 0x0000000001b69dcd in row_undo_step (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0undo.cc:370#13 0x0000000001abfea4 in que_thr_step (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1061#14 0x0000000001ac00ae in que_run_threads_low (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1125#15 0x0000000001ac0254 in que_run_threads (thr=0x7fffa89a23e8) at /root/softm/percona-server-5.7.22-22/storage/innobase/que/que0que.cc:1165#16 0x0000000001bcf4dc in trx_rollback_to_savepoint_low (trx=0x7ffff10c95a0, savept=0x0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:118#17 0x0000000001bcf714 in trx_rollback_for_mysql_low (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:180#18 0x0000000001bcf9b2 in trx_rollback_low (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:212#19 0x0000000001bcfceb in trx_rollback_for_mysql (trx=0x7ffff10c95a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/trx/trx0roll.cc:289#20 0x00000000019b1c6c in innobase_rollback (hton=0x2edf1f0, thd=0x7fffa8012940, rollback_trx=true) at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:5126#21 0x0000000000f6db24 in ha_rollback_low (thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/handler.cc:2007#22 0x00000000018671d9 in MYSQL_BIN_LOG::rollback (this=0x2e39a40, thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/binlog.cc:2447#23 0x0000000000f6ddba in ha_rollback_trans (thd=0x7fffa8012940, all=true) at /root/softm/percona-server-5.7.22-22/sql/handler.cc:2094#24 0x00000000016ca4d5 in trans_rollback (thd=0x7fffa8012940) at /root/softm/percona-server-5.7.22-22/sql/transaction.cc:356#25 0x00000000015bc90a in mysql_execute_command (thd=0x7fffa8012940, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:4556#26 0x00000000015c030e in mysql_parse (thd=0x7fffa8012940, parser_state=0x7ffff0d9f600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#27 0x00000000015b3ea2 in dispatch_command (thd=0x7fffa8012940, com_data=0x7ffff0d9fd70, command=COM_QUERY) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#28 0x00000000015b2c2f in do_command (thd=0x7fffa8012940) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#29 0x00000000016fb8a8 in handle_connection (arg=0x3c52dd0) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#30 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#31 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0#32 0x00007ffff6516bcd in clone () from /lib64/libc.so.6
T2自己做分裂了
分裂(heir_heap_no=5, heap_no=4) 可以看到這里將 記錄20的type_mode=546分裂給記錄17 也就是512+2+32=LOCK_GAP+LOCK_S+LOCK_REC。
棧幀如下：
[Switching to Thread 0x7ffff0d5f700 (LWP 9548)] Breakpoint 2, lock_rec_set_nth_bit (lock=0x3058230, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:9191 ut_ad(lock); (gdb) bt#0 lock_rec_set_nth_bit (lock=0x3058230, i=5) at /root/softm/percona-server-5.7.22-22/storage/innobase/include/lock0priv.ic:91#1 0x0000000001a400fa in lock_rec_add_to_queue (type_mode=546, block=0x7fffea699ba0, heap_no=5, index=0x7fffa89e3410, trx=0x7ffff10ca5f0, caller_owns_trx_mutex=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:1845#2 0x0000000001a42acf in lock_rec_inherit_to_gap_if_gap_lock (block=0x7fffea699ba0, heir_heap_no=5, heap_no=4) at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:2829#3 0x0000000001a44643 in lock_update_insert (block=0x7fffea699ba0, rec=0x7fffeae680a8 "\200") at /root/softm/percona-server-5.7.22-22/storage/innobase/lock/lock0lock.cc:3659#4 0x0000000001c219b4 in btr_cur_optimistic_insert (flags=0, cursor=0x7ffff0d5b6f0, offsets=0x7ffff0d5b7c8, heap=0x7ffff0d5a6e0, entry=0x7fff9c01aa70, rec=0x7ffff0d5b7c0, big_rec=0x7ffff0d5b7b8, n_ext=0, thr=0x7fff9c035c18, mtr=0x7ffff0d5aec0) at /root/softm/percona-server-5.7.22-22/storage/innobase/btr/btr0cur.cc:3346#5 0x0000000001af3a0d in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7fffa89e3410, offsets_heap=0x7fff9c00e918, heap=0x7fff9c02ef08, entry=0x7fff9c01aa70, trx_id=0, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3166#6 0x0000000001af451d in row_ins_sec_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18, dup_chk_only=false) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3421#7 0x0000000001af46d1 in row_ins_index_entry (index=0x7fffa89e3410, entry=0x7fff9c01aa70, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3470#8 0x0000000001af4bf1 in row_ins_index_entry_step (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3618#9 0x0000000001af4f67 in row_ins (node=0x7fff9c035978, thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3760#10 0x0000000001af5564 in row_ins_step (thr=0x7fff9c035c18) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0ins.cc:3945#11 0x0000000001b14775 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2283#12 0x0000000001b14c7d in row_insert_for_mysql (mysql_rec=0x7fff9c034b10 "\374\020", prebuilt=0x7fff9c0353a0) at /root/softm/percona-server-5.7.22-22/storage/innobase/row/row0mysql.cc:2406#13 0x00000000019b87e5 in ha_innobase::write_row (this=0x7fff9c0345d0, record=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/storage/innobase/handler/ha_innodb.cc:8344#14 0x0000000000f7d74d in handler::ha_write_row (this=0x7fff9c0345d0, buf=0x7fff9c034b10 "\374\020") at /root/softm/percona-server-5.7.22-22/sql/handler.cc:8466#15 0x00000000017ed7e9 in write_record (thd=0x7fff9c000b70, table=0x7fff9c033bd0, info=0x7ffff0d5ca00, update=0x7ffff0d5c980) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:1881#16 0x00000000017ea893 in Sql_cmd_insert::mysql_insert (this=0x7fff9c006e90, thd=0x7fff9c000b70, table_list=0x7fff9c0068f8) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:773#17 0x00000000017f141d in Sql_cmd_insert::execute (this=0x7fff9c006e90, thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_insert.cc:3121#18 0x00000000015b9a83 in mysql_execute_command (thd=0x7fff9c000b70, first_level=true) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:3746#19 0x00000000015c030e in mysql_parse (thd=0x7fff9c000b70, parser_state=0x7ffff0d5e600) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:5901#20 0x00000000015b3ea2 in dispatch_command (thd=0x7fff9c000b70, com_data=0x7ffff0d5ed70, command=COM_QUERY) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1490#21 0x00000000015b2c2f in do_command (thd=0x7fff9c000b70) at /root/softm/percona-server-5.7.22-22/sql/sql_parse.cc:1021#22 0x00000000016fb8a8 in handle_connection (arg=0x38e2880) at /root/softm/percona-server-5.7.22-22/sql/conn_handler/connection_handler_per_thread.cc:312#23 0x00000000019320be in pfs_spawn_thread (arg=0x3c64160) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#24 0x00007ffff79c3aa1 in start_thread () from /lib64/libpthread.so.0#25 0x00007ffff6516bcd in clone () from /lib64/libc.so.6(gdb)
最終形成如下的鎖模式，因為記錄 11,11已經不存在了因此
addr is 0x30580e8 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock])
下不會有任何記錄
---TRANSACTION 19999, ACTIVE 972 sec3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 140737233942272, query id 687 localhost root starting show engine innodb status TABLE LOCK table `test`.`testunj1` trx id 19999 lock mode IX RECORD LOCKS space id 93 page no 4 n bits 72 index id2 of table ,addr is 0x30580e8 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock]) RECORD LOCKS space id 93 page no 4 n bits 72 index id2 of table ,addr is 0x3058230 `test`.`testunj1` trx id 19999 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP) Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000011; asc ;; 1: len 4; hex 80000010; asc ;;
可以看到 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP) 已經出來了。
lock_rec_has_to_wait 檢測是否需要等待
RecLock::add_to_waitq 將LOCK_T結構加入到rec hash中(等待)
RecLock::lock_add 將LOCK_T結構加入到rec hash中
lock_rec_set_nth_bit 設置LOCK_T位圖
row_ins_scan_sec_index_for_duplicate 二級唯一索引唯一性檢查加鎖函數
lock_rec_other_has_conflicting 判斷沖突->lock_rec_has_to_wait 檢測是否需要等待
lock_rec_inherit_to_gap LOCK繼承函數
lock_rec_inherit_to_gap_if_gap_lock LOCK分裂函數
lock_rec_convert_impl_to_expl 隱含鎖轉換函數，比較復雜
row_ins_scan_sec_index_for_duplicate 片段：
if (cmp == 0 && !index->allow_duplicates) { //記錄相等并且是唯一索引 ，還需要判斷唯一的字段是否能夠對上，同時要確認不是del flag的記錄
if (row_ins_dupl_error_with_rec(rec, entry,
index, offsets)) { //這里會跳過 del flag的記錄 不標記為 重復，IF邏輯不會停止，會繼續到一行
err = DB_DUPLICATE_KEY;
thr_get_trx(thr)->error_info = index;
...... goto end_scan;
}
} else {
ut_a(cmp < 0 || index->allow_duplicates); goto end_scan;
}row_ins_dupl_error_with_rec 片段：
if (matched_fields < n_unique) { //需要相同的字段 否則判斷為FLASE
return(FALSE);
} /* In a unique secondary index we allow equal key values if they
contain SQL NULLs */
if (!dict_index_is_clust(index) && !index->nulls_equal) { for (i = 0; i < n_unique; i++) { if (dfield_is_null(dtuple_get_nth_field(entry, i))) { return(FALSE);
}
}
} return(!rec_get_deleted_flag(rec, rec_offs_comp(offsets))); //如果相同 但是是del flag的記錄則同樣放回FALSE本系統沒有ROLLBACK，但是 INSERT ON DUPLICATE 語句，對于 INSERT ON DUPLICATE/REPLACE語句都是做唯一性檢查的時候普通語句進行報錯，但是這兩個語句會返回錯誤給上層，接著做相應的處理
INSERT ON DUPLICATE 是調用的UPDATE接口
REPLACE是調用的DELETE 然后 INSERT的接口
因此兩類語句出現堵插入印象的地方就有兩處要么是初次INSERT的時候，要么是唯一鍵沖突造成的二次更改上。而對于 INSERT ON DUPLICATE/REPLACE做唯一性檢測的時候上的LOCK_X并非LOCK_S如下：
if (flags & BTR_NO_LOCKING_FLAG) { /* Set no locks when applying log
in online table rebuild. */
} else if (allow_duplicates) { /* If the SQL-query will update or replace
duplicate key we will take X-lock for
duplicates ( REPLACE, LOAD DATAFILE REPLACE,
INSERT ON DUPLICATE KEY UPDATE). */
err = row_ins_set_exclusive_rec_lock(
lock_type, block, rec, index, offsets, thr); //此處需要對這條數據加NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl 可能轉換
} else {
err = row_ins_set_shared_rec_lock(
lock_type, block, rec, index, offsets, thr);//此處需要對這條數據加NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl 可能轉換隱含鎖
}因此 INSERT ON DUPLICATE/REPLACE兩個語句需要加鎖的地方比較多并且流程比較復雜，分析比較麻煩。
死鎖如下：
5.7.22
RC隔離級別
行數據2000W左右
INSERT ON DUPLICATE語句
LOCK_GAP存在
間隔一鍵時間觸發本死鎖
無應用發起ROLLBACK
唯一二級索引不太合理鍵值教長
如果哪位大哥分析出來請賜教
CREATE TABLE `testgp` ( `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'id', `kdt_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' , `conversation_id` VARCHAR(100) NOT NULL , `fans_uid` VARCHAR(50) NOT NULL COMMENT 'fansUId', `last_msg` BIGINT NOT NULL , `buyer_type` VARCHAR(32) NOT NULL , `last_receptionist_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' , `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`), UNIQUE KEY `uniq_key` (`kdt_id`,`conversation_id`), KEY `idx_kdt_lastmsg` (`kdt_id`,`last_msg`) ) ENGINE=InnoDB CHARSET=utf8mb4 ;
死鎖記錄
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-10-12 12:26:22 0x7fd70f7ff700 *** (1) TRANSACTION: TRANSACTION 33473425185, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 1465312, OS thread handle 140558885697280, query id 1628083907 10.255.201.50 courier update INSERT INTO testgp ( `kdt_id`, `conversation_id`, `fans_uid`, `last_msg`, `buyer_type`, `last_receptionist_id` ) VALUES ( 41372282, '41372282#mmp_6562662125#customerservice', 'mmp_6562662125', 1539318382643, 'mmp', 0 ) ON DUPLICATE KEY UPDATE last_msg = 1539318382643, buyer_type='mmp',last_receptionist_id= 0, updated_at = CURRENT_TIMESTAMP() *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425185 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000002774a7a; asc wJz;; 1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes); 2: len 8; hex 000000000836ffd3; asc 6 ;; *** (2) TRANSACTION: TRANSACTION 33473425184, ACTIVE 0 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 1460265, OS thread handle 140561654740736, query id 1628083905 10.255.201.50 courier update INSERT INTO testgp ( `kdt_id`, `conversation_id`, `fans_uid`, `last_msg`, `buyer_type`, `last_receptionist_id` ) VALUES ( 41372282, '41372282#mmp_6563932378#customerservice', 'mmp_6563932378', 1539318382633, 'mmp', 0 ) ON DUPLICATE KEY UPDATE last_msg = 1539318382633, buyer_type='mmp',last_receptionist_id= 0, updated_at = CURRENT_TIMESTAMP() *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425184 lock_mode X locks gap before rec Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000002774a7a; asc wJz;; 1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes); 2: len 8; hex 000000000836ffd3; asc 6 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1234 page no 468974 n bits 320 index uniq_key of table `courier`.`testgp` trx id 33473425184 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 211 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000002774a7a; asc wJz;; 1: len 30; hex 3431333732323832237765636861745f3635343836323238323423637573; asc 41372282#wechat_6548622824#cus; (total 42 bytes); 2: len 8; hex 000000000836ffd3; asc 6 ;; *** WE ROLL BACK TRANSACTION (1)
“MySQL死鎖分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
