Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL deadlock analysis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The RC isolation level is rarely out of GAP. I already know.

Inheriting and splitting will result in LOCK_GAP, which is written by the code.

The purge thread may trigger

Splitting and merging of pages may trigger

Internal rollback may trigger

Uniqueness check will result in LOCK_ Ordinal [next _ key_lock]

1. Construct deadlock

RC RR level is universal

Deadlock table structure and data

Drop table testunj1; create table testunj1 (id1 int primary key,id2 int unique key,name varchar (20)); insert into testunj1 values (1 select * from testunj1), (10, 10), (20), 20 (20)) +-+ | id1 | id2 | name | +-+ | 1 | 1 | gaopeng | | 10 | 10 | gaopeng | | 20 | 20 | gaopeng | +-+ 3 rows in set (0.01sec)

Deadlock construction process

T1T2T3beginscape insert into testunj1 values (17, 17); insert into testunj1 values (15, 15, 15)

Begin; insert into testunj1 values (14, 14, 15, 15); jam

Begin; insert into testunj1 values (16pc17); blocked rollback; successfully deadlock

Deadlock record

-LATEST DETECTED DEADLOCK----2017-08-29 05:03:47 0x7f2fdc6f0700 lock struct * (1) TRANSACTION:TRANSACTION 7261233, ACTIVE 12 sec insertingmysql 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 updateinsert into testunj1 values '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`.`tesimj1` trx id 7261233 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 4; hex 80000014; asc; 1: len 4; hex 80000014; asc * (2) TRANSACTION:TRANSACTION 7261234, ACTIVE 5 sec insertingmysql 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 updateinsert into testunj1 values. (2) HOLDS THE LOCK (S): RECORD LOCKS space id 797 page no 4 n bits 72 index id2 of table `test`.`test`j1` trx id 7261234 lock mode S locks gap before recRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2 compact format; Info bits 00: 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`.`tekeshij1` trx id 7261234 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000014; asc; 1: len 4; hex 80000014; asc * WE ROLL BACK TRANSACTION (2) II. Analysis

This deadlock actually involves the inheritance and splitting of locks. We analyze the following two cases of blocking and locking steps, mainly to figure out how gap lock came into being.

Set global innodb_lock_wait_timeout=200000; set global innodb_show_verbose_locks=1; set global transaction_isolation = 1; re-log in to the session establishment table and insert data as follows: drop table testunj1; create table testunj1 (id1 int primary key,id2 int unique key,name varchar (20)); insert into testunj1 values (1meme 1)), (10) (10)), (20) (20); if there is a debug environment gdb breakpoint: lock_rec_set_nth_bit

The steps are as follows:

T1T2 stage 1

BEGIN;insert into testunj1 values (17pyrrine 17mlgapeng')

BEGIN;insert into testunj1 values (16pd17); clogging stage 2

ROLLBACK

We only use two things to analyze the process, in fact, the process knows the reason.

-Phase 1 T1 does not commit T2 blockage

Prelude

The insertion of T1 does not have any locks, because if the next record does not have a lock, it is an implicit lock. Analysis from T2

Insert into testunj1 values (16pd17); blockage begins.

The first step T2 executes the insert into testunj1 values (16, 17, 7, 7, 7, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3 and 4). Step 1

T2 helps convert the T1 hermit lock to LOCK_X, which is converted here through the function lock_rec_convert_impl_to_expl.

The stack frames are as follows:

(gdb) bt#0 lock_rec_set_nth_bit (lock=0x3054068, iTun5) 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 to continue Or q to quit---#28 0x00007ffff79c3aa1 in start_thread () from / lib64/libpthread.so.0#29 0x00007ffff6516bcd in clone () from / lib64/libc.so.6

Step 2 insert into testunj1 values (16, 17, 5, 7, 7, 7, 7, 7, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 16, 17, 16, 17, 16, 16, 16, 17, 16, 16, 17, 16, 16, 17, 16, 17, 16, 16, 17, 16, 17, 16, 16, 17, 16, 17, 16, 17, 16, 17, 16, 17, 16, 17, 16, 17, 16, 17, 16, 17, 16, 14, 16, 17, 16, 14, 16, 17,

Need to do a uniqueness check does not pass the LOCK_ order [next _ key_lock] wait, the only check will involve the primary key and the unique key, if the primary key check passes, the data will be inserted, and then check the secondary unique index. If the unique index conflicts, the data inserted by the primary key needs to be rolled back. This is because each index is individually inserted by calling the row_ins_index_entry_step upper-level function.

The stack frames are as follows:

(gdb) bt#0 lock_rec_set_nth_bit (lock=0x30580e8, iTun5) 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 to continue Or q to quit---#28 0x00007ffff79c3aa1 in start_thread () from / lib64/libpthread.so.0#29 0x00007ffff6516bcd in clone () from / lib64/libc.so.6

After these two steps are completed, we can go to the existence of LOCK_S | LOCK_ order [next _ key_lock]

-TRANSACTION 19508, ACTIVE 143sec insertingmysql 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 684localhost root updateinsert into testunj1 values. 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`.`test`j1` trx id 19508 lock mode S (LOCK_S) locks gap and rec (LOCK_ LOCK_ [next _ key_lock]) waiting (LOCK_WAIT) Record lock, heap no 5 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 4; hex 80000011; asc; 1: len 4; hex 80000011; asc -TRANSACTION 19507, ACTIVE 1488sec2 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 rootTABLE LOCK table `test`.`test`j1`trx id 19507 lock mode IXRECORD LOCKS space id 91 page no 4 n bits 72 index id2 of table, addr is 0x3056b48 `test`.`tekesj1`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 00: len 4; hex 80000011 Asc;; 1: len 4; hex 80000011; asc;-stage 2 do ROLLBACK

Step 3 T1 helps T2 to inherit locks

From the pointer 0x7ffff10ca5f0 of things, we can see that it is T2. If there are multiple things that LOCK_GAP is compatible so they can all be inherited and completed, LOCK_GAP exists only for LOCK_INTENTION, just to prevent misreading.

If you do GDB, you can see the inherited locks here:

(gdb) p lock- > type_mode$1 = 546

The lock is inherited from heap 4, that is, record 20 (heir_heap_no=4, heap_no=5).

Here, LOCK_S | LOCK_GAP is inherited from heap_no 4, that is, record 20.

The stack frames are as follows:

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, iTunes 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, iTun4) 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 split on his own.

Heir_heap_no=5 (heap_no=4) can be seen here splitting the type_mode=546 of record 20 into record 17, that is, 512+2+32=LOCK_GAP+LOCK_S+LOCK_REC.

The stack frames are as follows:

[Switching to Thread 0x7ffff0d5f700 (LWP 9548)] Breakpoint 2, lock_rec_set_nth_bit (lock=0x3058230, iTunes 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, iTunes 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)

Finally, the following lock mode is formed, because the record 11BI 11 no longer exists, so

Addr is 0x30580e8 `test`.`test`j1` trx id 19999 lock mode S (LOCK_S) locks gap and rec (LOCK_ order [next _ key_lock])

There won't be any records.

-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 startingshow engine innodb statusTABLE LOCK table `test`.`test`j1`trx id 19999 lock mode IXRECORD LOCKS space id 93 page no 4 n bits 72 index id2 of table, addr is 0x30580e8 `test`.`teyogj1` 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`.`test`j1` 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 00: 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 00: len 4; hex 80000011; asc; 1: len 4; hex 80000010; asc

You can see that lock mode S (LOCK_S) locks gap before rec (LOCK_GAP) has come out.

III. Breakpoints and some addenda

Lock_rec_has_to_wait detects if you need to wait

RecLock::add_to_waitq adds LOCK_T structure to rec hash (wait)

RecLock::lock_add adds LOCK_T structure to rec hash

Lock_rec_set_nth_bit sets LOCK_T bitmap

Row_ins_scan_sec_index_for_duplicate second-level unique index uniqueness check locking function

Lock_rec_other_has_conflicting determines whether the conflict-> lock_rec_has_to_wait detection needs to wait

Lock_rec_inherit_to_gap LOCK inherit function

Lock_rec_inherit_to_gap_if_gap_lock LOCK splitting function

Lock_rec_convert_impl_to_expl implicit lock conversion function, which is more complex

1. Even if it is the same block, different things (even different lock modes of the same thing) need to create a new LOCK_T structure to represent a lock, which is based on space id/page no. Its memory structure BITMAP will be in the form of bitmap, each bit represents whether a row of data is locked (0 or 1) 2, innodb lock types are only LOCK_REC and LOCK_TABLE and row lock and table lock, but there can be a variety of mode combinations. 3. Rec hash is constructed through space id and page no so that the same block is put into a linked list, and there may be conflicts on this linked list, so every time you get it, you must check the page no and space id reference lock_rec_add_to_queue-> lock_rec_get_first_on_page function. 4. Each time a lock_t structure is added to the rec hash, it is also called waiting queue. Joining the queue means joining the queue about the space id and page no in the rec hash. Of course, the location of the lock in the page can only be found by confirming the bitmap. 5, different things for the same row of data locked usually do not share a lock_t, they are jointly connected to the rec hash linked list below 6, to determine whether a row is locked needs to constantly loop the entire linked list using heap no positioning to bitmap to determine. Refer to lock_rec_other_has_conflicting-> lock_rec_get_first. 7. For some records marked as del flag without purge, locks will be added in some cases, but judgment will be skipped. Refer to the row_ins_scan_sec_index_for_duplicate-> row_ins_dupl_error_with_rec function.

Row_ins_scan_sec_index_for_duplicate fragment:

If (cmp = 0 & &! index- > allow_duplicates) {/ / records are equal and are unique indexes. You also need to determine whether the unique fields can be matched. At the same time, make sure that records that are not del flag if (row_ins_dupl_error_with_rec (rec, entry, index, offsets)) {/ / the records skipping del flag are not marked as duplicates. The IF logic will not stop and will continue to the line 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 fragment:

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 < nasty uniquei +) {if (dfield_is_null (dtuple_get_nth_field (entry, I)) {return (FALSE);} return (! rec_get_deleted_flag (rec, rec_offs_comp (offsets) / / if the records of the same but del flag are also returned to false IV, the unanalyzed deadlock

This system does not have ROLLBACK, but INSERT ON DUPLICATE statements, ordinary statements report errors when doing uniqueness checks for INSERT ON DUPLICATE/REPLACE statements, but these two statements will return errors to the upper layer, and then deal with them accordingly.

INSERT ON DUPLICATE is the UPDATE interface that is called

REPLACE is the interface of the called DELETE and then INSERT

Therefore, there are two places where the impression of blocking insertion occurs in the two types of statements, either at the time of the first INSERT, or on the second change caused by the only key conflict. The LOCK_X on the uniqueness test of INSERT ON DUPLICATE/REPLACE is not LOCK_S as follows:

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); / / this data needs to be added here with NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl possible conversion} else {err = row_ins_set_shared_rec_lock (lock_type, block, rec, index, offsets, thr) / / you need to add NTEX KEY LOCK LOCK_ORDINARY lock_rec_convert_impl_to_expl to this data here, which may convert implicit locks}.

Therefore, the two statements of INSERT ON DUPLICATE/REPLACE need to be locked in many places and the process is more complex, so it is more troublesome to analyze.

Deadlocks are as follows:

5.7.22

RC isolation level

Row data is about 2000W.

INSERT ON DUPLICATE statement

LOCK_GAP exists

Trigger this deadlock at an interval of one key

No application initiating ROLLBACK

The unique secondary index is not reasonable, imam.

If any big brother analyzes it, please let me know.

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

Deadlock record

-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`, `fans_ id`, `fans_ uid`, `last_ msg`, `buyer_ type`, `last_receptionist_ id`) VALUES (41372282, '41372282 mm packs 6562662125 customerserviceworthy,' 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 211PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 8; hex 0000000002774a7a; asc wJz;; 1: len 30; hex 343133372383283277636861745f3635343836323823423637573; asc 41372282 wechatinitiate 6548622824 discussion; (total 42 bytes); 2: len 8; hex 0000000836ffd3; asc 6 * (2) TRANSACTION: TRANSACTION 33473425184, ACTIVE 0 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 14 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 mm pinch 6563932378 customerservice`,' mmp_6563932378', 153932378 customerservice` '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 211PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 8; hex 00000002774a7a; asc wJz;; 1: len 30; hex 343133372383283277636861745f3635343836323823423637573; asc 4137228222222222222222222222222222282total discussion; (total 42 bytes); 2: len 8; hex 0000000836ffd3; 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 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 8hex 0000000002774a7a; Asc wJz;; 1: len 30; hex 34313337323832236861745f363534363238323423637573; asc 4137228222222222282 total 42 bytes; 2: len 8; hex 0000000836ffd3; asc 6; * * WE ROLL BACK TRANSACTION (1) "MySQL deadlock Analysis" is here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.