• MySQL锁与死锁分析

    1.环境

    MySQL 5.7.26

    数据

    表 a 中的字段 b_id 外键关联 表 b 中的 id

    1. create table a
    2. (
    3.     id   int auto_increment
    4.         primary key,
    5.     name varchar(64) null,
    6.     age  int         null,
    7.     b_id int         null,
    8.     constraint a_b_id_fk
    9.         foreign key (b_id) references b (id)
    10. );
    11. [
    12.   {
    13.     "id": 2,
    14.     "name": "hsh",
    15.     "age": 24,
    16.     "b_id": 3
    17.   }
    18. ]
    19. create table b
    20. (
    21.     id   int auto_increment
    22.         primary key,
    23.     name varchar(64) null,
    24.     time bigint      null
    25. );
    26. [
    27.   {
    28.     "id": 3,
    29.     "name": "h",
    30.     "time": 330
    31.   }
    32. ]

    2.事务执行流程

    通过主、外键更新数据来产生排它锁，阻塞和死锁。

    步骤

    事务1 id=56389

    事务2 id=56390

    1

    update a

    set name='hsh_1'

    where id = 2;

    2

    update a

    set age=19

    where b_id = 3;

    3

    update a

    set name='hsh_2'

    where b_id = 3;

    3.阻塞

    当执行完第2步时，事务2阻塞，此时事务与锁的状态为：

    1. select *
    2. from information_schema.INNODB_TRX trx
    3.          left join information_schema.INNODB_LOCKS locks on trx.trx_id = locks.lock_trx_id;
    4. [
    5.     {
    6.       "trx_id": "56389",
    7.       "trx_state": "RUNNING",
    8.       "trx_started": "2022-08-20 16:07:43",
    9.       "trx_requested_lock_id": null,
    10.       "trx_wait_started": null,
    11.       "trx_weight": 3,
    12.       "trx_mysql_thread_id": 12,
    13.       "trx_query": null,
    14.       "trx_operation_state": null,
    15.       "trx_tables_in_use": 0,
    16.       "trx_tables_locked": 1,
    17.       "trx_lock_structs": 2,
    18.       "trx_lock_memory_bytes": 1136,
    19.       "trx_rows_locked": 1,
    20.       "trx_rows_modified": 1,
    21.       "trx_concurrency_tickets": 0,
    22.       "trx_isolation_level": "REPEATABLE READ",
    23.       "trx_unique_checks": 1,
    24.       "trx_foreign_key_checks": 1,
    25.       "trx_last_foreign_key_error": null,
    26.       "trx_adaptive_hash_latched": 0,
    27.       "trx_adaptive_hash_timeout": 0,
    28.       "trx_is_read_only": 0,
    29.       "trx_autocommit_non_locking": 0,
    30.       "lock_id": "56389:227:3:9",
    31.       "lock_trx_id": "56389",
    32.       "lock_mode": "X",
    33.       "lock_type": "RECORD",
    34.       "lock_table": "`dps`.`a`",
    35.       "lock_index": "PRIMARY",
    36.       "lock_space": 227,
    37.       "lock_page": 3,
    38.       "lock_rec": 9,
    39.       "lock_data": "2"
    40.     },
    41.     {
    42.       "trx_id": "56390",
    43.       "trx_state": "LOCK WAIT",
    44.       "trx_started": "2022-08-20 16:07:55",
    45.       "trx_requested_lock_id": "56390:227:3:9",
    46.       "trx_wait_started": "2022-08-20 16:07:55",
    47.       "trx_weight": 3,
    48.       "trx_mysql_thread_id": 13,
    49.       "trx_query": "/* ApplicationName=DataGrip 2022.2 */ update a set age=19 where b_id=3",
    50.       "trx_operation_state": "starting index read",
    51.       "trx_tables_in_use": 1,
    52.       "trx_tables_locked": 1,
    53.       "trx_lock_structs": 3,
    54.       "trx_lock_memory_bytes": 1136,
    55.       "trx_rows_locked": 2,
    56.       "trx_rows_modified": 0,
    57.       "trx_concurrency_tickets": 0,
    58.       "trx_isolation_level": "REPEATABLE READ",
    59.       "trx_unique_checks": 1,
    60.       "trx_foreign_key_checks": 1,
    61.       "trx_last_foreign_key_error": null,
    62.       "trx_adaptive_hash_latched": 0,
    63.       "trx_adaptive_hash_timeout": 0,
    64.       "trx_is_read_only": 0,
    65.       "trx_autocommit_non_locking": 0,
    66.       "lock_id": "56390:227:3:9",
    67.       "lock_trx_id": "56390",
    68.       "lock_mode": "X",
    69.       "lock_type": "RECORD",
    70.       "lock_table": "`dps`.`a`",
    71.       "lock_index": "PRIMARY",
    72.       "lock_space": 227,
    73.       "lock_page": 3,
    74.       "lock_rec": 9,
    75.       "lock_data": "2"
    76.     }
    77.   ]
    1. select * from information_schema.INNODB_LOCK_WAITS;
    2.   [
    3.     {
    4.       "requesting_trx_id": "56390",
    5.       "requested_lock_id": "56390:227:3:9",
    6.       "blocking_trx_id": "56389",
    7.       "blocking_lock_id": "56389:227:3:9"
    8.     }
    9.   ]

    分析：

    可以看出事务2当前处于锁等待状态，也就是阻塞状态。事务2当前正在请求的锁id为 56390:227:3:9。

    事务1对应的锁ID为56389:227:3:9，这两个其实是同一把锁，都是添加在 表空间为227页号为3行记录是9的行上，是加在主键索引上。

    4.死锁

    再执行步骤3，产生死锁，事务2被kill掉。

    [40001][1213] Deadlock found when trying to get lock; try restarting transaction。

    死锁日志会在innodb status中记录。

    innodb状态

    show engine innodb status;

    此语句会展示innodb监控信息，包含事务，文件IO，死锁，线程，内存和缓冲池等信息。

    1. INNODB MONITOR OUTPUT
    2. Per second averages calculated from the last 20 seconds
    3. BACKGROUND THREAD
    4. SEMAPHORES
    5. LATEST DETECTED DEADLOCK
    6. TRANSACTIONS
    7. FILE I/O
    8. INSERT BUFFER AND ADAPTIVE HASH INDEX
    9. LOG
    10. BUFFER POOL AND MEMORY
    11. ROW OPERATIONS

    下面只展示死锁相关信息：

    1. LATEST DETECTED DEADLOCK
    2. ------------------------
    3. 2022-08-20 16:08:43 0x17e0
    4. *** (1) TRANSACTION:
    5. TRANSACTION 56390, ACTIVE 48 sec starting index read
    6. mysql tables in use 1, locked 1
    7. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
    8. MySQL thread id 13, OS thread handle 3788, query id 1277 BOGON 192.168.0.101 root updating
    9. /* ApplicationName=DataGrip 2022.2 */ update a set age=19 where b_id=3
    10. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    11. RECORD LOCKS space id 227 page no 3 n bits 80 index PRIMARY of table `dps`.`a` trx id 56390 lock_mode X locks rec but not gap waiting
    12. Record lock, heap no 9 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    13.  0: len 4; hex 80000002; asc     ;;
    14.  1: len 6; hex 00000000dc45; asc      E;;
    15.  2: len 7; hex 300000017018b8; asc 0   p  ;;
    16.  3: len 5; hex 6873685f31; asc hsh_1;;
    17.  4: len 4; hex 80000018; asc     ;;
    18.  5: len 4; hex 80000003; asc     ;;
    19. *** (2) TRANSACTION:
    20. TRANSACTION 56389, ACTIVE 60 sec starting index read, thread declared inside InnoDB 5000
    21. mysql tables in use 1, locked 1
    22. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    23. MySQL thread id 12, OS thread handle 6112, query id 1321 BOGON 192.168.0.101 root updating
    24. /* ApplicationName=DataGrip 2022.2 */ update a set name='hsh_2' where b_id=3
    25. *** (2) HOLDS THE LOCK(S):
    26. RECORD LOCKS space id 227 page no 3 n bits 80 index PRIMARY of table `dps`.`a` trx id 56389 lock_mode X locks rec but not gap
    27. Record lock, heap no 9 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    28.  0: len 4; hex 80000002; asc     ;;
    29.  1: len 6; hex 00000000dc45; asc      E;;
    30.  2: len 7; hex 300000017018b8; asc 0   p  ;;
    31.  3: len 5; hex 6873685f31; asc hsh_1;;
    32.  4: len 4; hex 80000018; asc     ;;
    33.  5: len 4; hex 80000003; asc     ;;
    34. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    35. RECORD LOCKS space id 227 page no 4 n bits 72 index a_b_id_fk of table `dps`.`a` trx id 56389 lock_mode X waiting
    36. Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    37.  0: len 4; hex 80000003; asc     ;;
    38.  1: len 4; hex 80000002; asc     ;;
    39. *** WE ROLL BACK TRANSACTION (1)

    死锁分析：

    当事务2执行更新时，时通过外键更新的，所以不仅会请求 227:3:9 这把锁，同时也会在 227:4:6 这个外键索引上加锁。

    当事务1再通过外键更新时，就请求 227:4:6 这把外键锁，此时事务2持有这把锁，由于事务1持有事务2请求的锁，

    所以事务2无法释放持有的外键锁，死锁就会产生。这是MySQL检测到了死锁，就会kill掉事务2。

    5.外键锁示例

    按如下语句执行事务：

    步骤

    事务1 id=56395

    事务2 id=56396

    1

    update a

    set name='hsh_2'

    where b_id = 3;

    2

    update a

    set age=19

    where b_id = 3;

    1. [
    2.     {
    3.       "trx_id": "56395",
    4.       "trx_state": "RUNNING",
    5.       "trx_started": "2022-08-20 17:05:16",
    6.       "trx_requested_lock_id": null,
    7.       "trx_wait_started": null,
    8.       "trx_weight": 4,
    9.       "trx_mysql_thread_id": 12,
    10.       "trx_query": null,
    11.       "trx_operation_state": null,
    12.       "trx_tables_in_use": 0,
    13.       "trx_tables_locked": 1,
    14.       "trx_lock_structs": 3,
    15.       "trx_lock_memory_bytes": 1136,
    16.       "trx_rows_locked": 3,
    17.       "trx_rows_modified": 1,
    18.       "trx_concurrency_tickets": 0,
    19.       "trx_isolation_level": "REPEATABLE READ",
    20.       "trx_unique_checks": 1,
    21.       "trx_foreign_key_checks": 1,
    22.       "trx_last_foreign_key_error": null,
    23.       "trx_adaptive_hash_latched": 0,
    24.       "trx_adaptive_hash_timeout": 0,
    25.       "trx_is_read_only": 0,
    26.       "trx_autocommit_non_locking": 0,
    27.       "lock_id": "56395:227:4:6",
    28.       "lock_trx_id": "56395",
    29.       "lock_mode": "X",
    30.       "lock_type": "RECORD",
    31.       "lock_table": "`dps`.`a`",
    32.       "lock_index": "a_b_id_fk",
    33.       "lock_space": 227,
    34.       "lock_page": 4,
    35.       "lock_rec": 6,
    36.       "lock_data": "3, 2"
    37.     },
    38.     {
    39.       "trx_id": "56396",
    40.       "trx_state": "LOCK WAIT",
    41.       "trx_started": "2022-08-20 17:05:33",
    42.       "trx_requested_lock_id": "56396:227:4:6",
    43.       "trx_wait_started": "2022-08-20 17:05:33",
    44.       "trx_weight": 2,
    45.       "trx_mysql_thread_id": 13,
    46.       "trx_query": "/* ApplicationName=DataGrip 2022.2 */ update a\nset age=19\nwhere b_id = 3",
    47.       "trx_operation_state": "starting index read",
    48.       "trx_tables_in_use": 1,
    49.       "trx_tables_locked": 1,
    50.       "trx_lock_structs": 2,
    51.       "trx_lock_memory_bytes": 1136,
    52.       "trx_rows_locked": 1,
    53.       "trx_rows_modified": 0,
    54.       "trx_concurrency_tickets": 0,
    55.       "trx_isolation_level": "REPEATABLE READ",
    56.       "trx_unique_checks": 1,
    57.       "trx_foreign_key_checks": 1,
    58.       "trx_last_foreign_key_error": null,
    59.       "trx_adaptive_hash_latched": 0,
    60.       "trx_adaptive_hash_timeout": 0,
    61.       "trx_is_read_only": 0,
    62.       "trx_autocommit_non_locking": 0,
    63.       "lock_id": "56396:227:4:6",
    64.       "lock_trx_id": "56396",
    65.       "lock_mode": "X",
    66.       "lock_type": "RECORD",
    67.       "lock_table": "`dps`.`a`",
    68.       "lock_index": "a_b_id_fk",
    69.       "lock_space": 227,
    70.       "lock_page": 4,
    71.       "lock_rec": 6,
    72.       "lock_data": "3, 2"
    73.     }
    74.   ]

    从"lock_index": "a_b_id_fk" 可以看出此时的锁是外键锁，

    锁的记录为 227:4:6 上的外键锁。

    事务与锁 表中的具体字段说明，参见

    information_schema.INNODB_TRX表详解_CaptHua的博客-CSDN博客_information_schema.innodb_trx

    INNODB_LOCKS与INNODB_LOCK_WAITS表_CaptHua的博客-CSDN博客

  • 相关阅读:
    强化学习从基础到进阶–案例与实践[11]：AlphaStar论文解读、监督学习、强化学习、模仿学习、多智能体学习、消融实验
    【Java成王之路】EE初阶第十五篇:(网络原理) 5
    Leetcode刷题112. 路径总和
    PTA-L2-004 这是二叉搜索树吗？
    个人搭建frp服务及客户端连接实现网路穿透
    延宕执行,妙用无穷,Go lang1.18入门精炼教程,由白丁入鸿儒,Golang中defer关键字延迟调用机制使用EP17
    【服务器数据恢复】LINUX误删除、误格式化的数据恢复
    存储器相关的术语总结
    python学习—第一步—聪明方法学python
    蓝桥杯入门即劝退（八）回文数
  • 原文地址：https://blog.csdn.net/CaptHua/article/details/126442113