AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL：一个死锁分析 (未分析出来的死锁)

2018-10-16 1762
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 最近一个朋友给了我一个死锁 没分析出来搞了好几天，但是把以前出现的一个死锁理了一下流程。这里大概记录一下，并且给出朋友的案例。 RC 隔离级别很少出GAP我已经知道的 继承和分裂会出LOCK_GAP这是代码写死的purge线程可能触发页的分裂融合可能触发内部回滚可能触发 唯一性检查会出LOCK_O.

最近一个朋友给了我一个死锁 没分析出来搞了好几天，但是把以前出现的一个死锁理了一下流程。这里大概记录一下，并且给出朋友的案例。

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 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL 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 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL 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个事物来分析流程，实际上流程知道了原因也就知道了。

- 阶段1 T1不提交T2堵塞
  • 前奏
    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 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL 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 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL 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     ;;
- 阶段2 做ROLLBACK
  • 第三步 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:91
91              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:91
91              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 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL 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 隐含锁转换函数，比较复杂
1、即便是同一个block，不同事物(即便是同一个事物的不同锁模式)也需要新建一个LOCK_T结构，来表示一个锁，其以space id/page no为基础。其内存结构BITMAP会以位图的形式每一位代表一行数据是否上锁(0 or 1)
2、innodb锁类型只有LOCK_REC和LOCK_TABLE两种及行锁和表锁，但是可以有多种模式组合。
3、rec hash 通过space id 和 page no 构造 那么同一块的 都放到一个链表中，同时这个链表上的可能还有冲突而来的，所以每次获取的时候必然查看page no和space id 参考lock_rec_add_to_queue -> lock_rec_get_first_on_page函数。
4、每次增加一个lock_t 结构都会加入到rec hash中,这也是所谓的等待队列，加入队列就是指加入rec hash中关于本space id和page no的队列，当然最后还需要 bitmap的确认才能在page中找到这个锁的位置。
5、不同的事物对于同一行数据的上锁通常不共享一个lock_t，他们共同连接到rec hash的链表下面
6、判断某行是否上锁 需要不断循环整个链表使用heap no定位到bitmap 来进行判断。参考lock_rec_other_has_conflicting ->lock_rec_get_first。
7、对于某些标记为del flag还没有purge的记录，在某些情况下会加锁，但是会跳过判断，参考row_ins_scan_sec_index_for_duplicate ->row_ins_dupl_error_with_rec 函数。

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)

作者微信：gp_22389860

文章标签：
云数据库 RDS MySQL 版
图数据库
SQL
关系型数据库
MySQL
索引
NoSQL
关键词：
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版死锁
云数据库 RDS MySQL 版死锁分析
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
重庆八怪
目录
相关文章
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
MySQL死锁及源码分析！
MySQL死锁及源码分析！
程序员飞鱼
45 0
MySQL死锁及源码分析！
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析：MySQL唯一索引并发插入导致死锁！
案例剖析：MySQL唯一索引并发插入导致死锁！
程序员飞鱼
171 0
案例剖析：MySQL唯一索引并发插入导致死锁！
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析，MySQL共享锁引发的死锁问题！
案例剖析，MySQL共享锁引发的死锁问题！
程序员飞鱼
48 0
大数据文摘
|
22小时前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解：分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持，提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析，常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`，常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
大数据文摘
25 11
蓝易云
|
7天前
|
存储 关系型数据库 MySQL
从新手到高手：彻底掌握MySQL表死锁
通过本文的介绍，希望你能深入理解MySQL表死锁的概念、原因、检测方法及解决方案，并在实际开发中灵活应用这些知识，提升系统的稳定性和性能。
蓝易云
59 9
qhhf_地道酬勤
|
1月前
|
SQL 算法 关系型数据库
面试：什么是死锁，如何避免或解决死锁；MySQL中的死锁现象，MySQL死锁如何解决
面试：什么是死锁，死锁产生的四个必要条件，如何避免或解决死锁；数据库锁，锁分类，控制事务；MySQL中的死锁现象，MySQL死锁如何解决
qhhf_地道酬勤
83 1
绿水长流*z
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1684 14
mzlogin
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
mzlogin
136 3
小王老师呀
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
47 4
糜终
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
161 4

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    mysql安装过程
  • 4
    Mysql数据库初始化后更改数据存储目录
  • 5
    MySQL学习笔记_2_MySQL创建数据表（上）
  • 6
    MySQL服务器 IO 100%的案例分析
  • 7
    MySQL和Oracle的添加字段的处理差别
  • 8
    [MySQL]命令行工具和基本操作
  • 9
    PostgreSQL:系统表pg_attribute膨胀解决方案
  • 10
    mysql中的时间函数---运维常用
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    76
  • 2
    Linux对MySQL语法校验
    89
  • 3
    【MySQL系列笔记】分库分表
    206
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    161
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    214
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    419
  • 7
    golang使用gorm操作mysql3，数据查询
    97
  • 8
    golang使用gorm操作mysql2
    61
  • 9
    golang使用gorm操作mysql1
    112
  • 10
    Hbase与MySQL对比，区别是什么？
    303

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 函数计算X RDS PostgreSQL，基于LLM大语言模型构建AI知识库

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。