##锁等待超时参数（新的连接生效）,这里设置为5000便于测试.
set global innodb_lock_wait_timeout=5000; mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)mysql>
2、死锁状态模拟
session1
session2
begin;select * from test.student where id = 2 for update;
begin;select * from test.student where id = 5 for update;
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5;
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 2;
mysql> select thread_id,sql_text from performance_schema.events_statements_history where thread_id = 84;
+-----------+--------------------------------------------------------------------------+
| thread_id | sql_text |
+-----------+--------------------------------------------------------------------------+
| 84 | select @@version_comment limit 1 |
| 84 | SELECT DATABASE() |
| 84 | NULL |
| 84 | select * from test.student |
| 84 | begin |
| 84 | select * from test.student where id = 2 for update |
| 84 | update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5 |
+-----------+--------------------------------------------------------------------------+
7 rows in set (0.00 sec)mysql>
查看最近一个死锁情况
mysql> show engine innodb status \G
*************************** 1. row ***************************Type: InnoDBName:
Status:
=====================================
2024-06-22 18:06:14 0x85d4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 37 srv_active, 0 srv_shutdown, 279528 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2126
OS WAIT ARRAY INFO: signal count 2078
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-06-22 18:04:22 0x182c
*** (1) TRANSACTION: #开启第一个事务
TRANSACTION 672229, ACTIVE 435 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 40, OS thread handle 18880, query id 896 localhost ::1 root updating# 更新语句
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672229 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000002; asc ;;1: len 6; hex 0000000a41c0; asc A ;;2: len 7; hex 80000000000000; asc ;;3: len 6; hex e5bca0e4b889; asc ;;4: len 8; hex 8000000000000002; asc ;;5: len 8; hex 8000000000000002; asc ;;6: len 8; hex 8000000000000002; asc ;;*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672229 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000005; asc ;;1: len 6; hex 0000000a41c0; asc A ;;2: len 7; hex 80000000000000; asc ;;3: len 8; hex 6b6576696e313131; asc kevin111;;4: len 8; hex 8000000000000005; asc ;;5: len 8; hex 8000000000000005; asc ;;6: len 8; hex 8000000000000005; asc ;;*** (2) TRANSACTION: #开启第二个事务
TRANSACTION 672230, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 41, OS thread handle 37908, query id 916 localhost ::1 root updating# 更新语句
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 2*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672230 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000005; asc ;;1: len 6; hex 0000000a41c0; asc A ;;2: len 7; hex 80000000000000; asc ;;3: len 8; hex 6b6576696e313131; asc kevin111;;4: len 8; hex 8000000000000005; asc ;;5: len 8; hex 8000000000000005; asc ;;6: len 8; hex 8000000000000005; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672230 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000002; asc ;;1: len 6; hex 0000000a41c0; asc A ;;2: len 7; hex 80000000000000; asc ;;3: len 6; hex e5bca0e4b889; asc ;;4: len 8; hex 8000000000000002; asc ;;5: len 8; hex 8000000000000002; asc ;;6: len 8; hex 8000000000000002; asc ;;*** WE ROLL BACK TRANSACTION (2) #第二个事务回滚（此处为什么选择第二个事务？？）mysql>
