MYSQL死锁分析案例二(高并发增删改同一条记录)
1、建表
CREATE TABLE `t1` ( `id` int NOT NULL, `name` varchar(200) DEFAULT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx111` (`name`), KEY `idx_age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2、数据
mysql> select * from t1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | c | 300 | +----+------+------+ 3 rows in set (0.00 sec)
3、复现
| session1 | session2 | session3 | session4 | |
|
时间1 |
begin; insert into t1 select 4,'e',5; |
|
|
|
| 时间2 |
begin; update t1 set age=1000 where name='e'; --锁等待状态 |
|||
| 时间3 |
begin; delete from t1 where age=5; --锁等待状态 |
|||
| 时间4 | 查询锁等待信息 | |||
| 时间5 | commit; | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | Query OK, 1 row affected (6.21 sec) |
4、在时间4查询锁等待信息
mysql> select *from data_lock_waits; +--------+-------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | ENGINE | REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN | +--------+-------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | INNODB | 5795977440:419:6:4:5671150960 | 139150 | 62 | 66 | 5671150960 | 5795975856:419:6:4:5671141744 | 139144 | 62 | 65 | 5671141744 | | INNODB | 5795976648:419:5:5:5671146352 | 139149 | 61 | 64 | 5671146352 | 5795975856:419:5:5:5671141400 | 139144 | 61 | 63 | 5671141400 | +--------+-------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+-------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> mysql> mysql> select * from performance_schema.data_locks; +--------+-------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | INNODB | 5795977440:1590:5668664920 | 139150 | 62 | 65 | ceshi | t1 | NULL | NULL | NULL | 5668664920 | TABLE | IX | GRANTED | NULL | | INNODB | 5795977440:419:6:4:5671150960 | 139150 | 62 | 66 | ceshi | t1 | NULL | NULL | idx_age | 5671150960 | RECORD | X,REC_NOT_GAP | WAITING | 5, 4 | | INNODB | 5795976648:1590:5668663896 | 139149 | 61 | 63 | ceshi | t1 | NULL | NULL | NULL | 5668663896 | TABLE | IX | GRANTED | NULL | | INNODB | 5795976648:419:5:5:5671146352 | 139149 | 61 | 64 | ceshi | t1 | NULL | NULL | idx111 | 5671146352 | RECORD | X,REC_NOT_GAP | WAITING | 'e', 4 | | INNODB | 5795975856:1590:5668662872 | 139144 | 60 | 94 | ceshi | t1 | NULL | NULL | NULL | 5668662872 | TABLE | IX | GRANTED | NULL | | INNODB | 5795975856:419:5:5:5671141400 | 139144 | 61 | 63 | ceshi | t1 | NULL | NULL | idx111 | 5671141400 | RECORD | X,REC_NOT_GAP | GRANTED | 'e', 4 | | INNODB | 5795975856:419:6:4:5671141744 | 139144 | 62 | 65 | ceshi | t1 | NULL | NULL | idx_age | 5671141744 | RECORD | X,REC_NOT_GAP | GRANTED | 5, 4 | +--------+-------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ 7 rows in set (0.00 sec) mysql> select * from information_schema.innodb_trx; +--------+-----------+---------------------+-------------------------------+---------------------+------------+---------------------+---------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight | +--------+-----------+---------------------+-------------------------------+---------------------+------------+---------------------+---------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+ | 139150 | LOCK WAIT | 2024-08-06 17:42:03 | 5795977440:419:6:4:5671150960 | 2024-08-06 17:43:24 | 2 | 26 | delete from t1 where age=5 | starting index read | 1 | 1 | 2 | 1128 | 2 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 0 | 0 | 0 | 1 | | 139149 | LOCK WAIT | 2024-08-06 17:41:52 | 5795976648:419:5:5:5671146352 | 2024-08-06 17:43:23 | 2 | 25 | update t1 set age=1000 where name='e' | starting index read | 1 | 1 | 2 | 1128 | 2 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 0 | 0 | 0 | 1 | | 139144 | RUNNING | 2024-08-06 17:41:42 | NULL | NULL | 4 | 24 | NULL | NULL | 0 | 1 | 3 | 1128 | 2 | 1 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL | +--------+-----------+---------------------+-------------------------------+---------------------+------------+---------------------+---------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+ 3 rows in set (0.00 sec)
mysql> select* from x$innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2024-08-06 17:45:07
wait_age: 00:00:16
wait_age_secs: 16
locked_table: `ceshi`.`t1`
locked_table_schema: ceshi
locked_table_name: t1
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: idx111
locked_type: RECORD
waiting_trx_id: 139149
waiting_trx_started: 2024-08-06 17:41:52
waiting_trx_age: 00:03:31
waiting_trx_rows_locked: 3
waiting_trx_rows_modified: 0
waiting_pid: 25
waiting_query: update t1 set age=1000 where name='e'
waiting_lock_id: 5795976648:419:5:5:5671146696
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 139144
blocking_pid: 24
blocking_query: NULL
blocking_lock_id: 5795975856:419:5:5:5671141400
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2024-08-06 17:41:42
blocking_trx_age: 00:03:41
blocking_trx_rows_locked: 2
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 24
sql_kill_blocking_connection: KILL 24
*************************** 2. row ***************************
wait_started: 2024-08-06 17:45:08
wait_age: 00:00:15
wait_age_secs: 15
locked_table: `ceshi`.`t1`
locked_table_schema: ceshi
locked_table_name: t1
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: idx_age
locked_type: RECORD
waiting_trx_id: 139150
waiting_trx_started: 2024-08-06 17:42:03
waiting_trx_age: 00:03:20
waiting_trx_rows_locked: 3
waiting_trx_rows_modified: 0
waiting_pid: 26
waiting_query: delete from t1 where age=5
waiting_lock_id: 5795977440:419:6:4:5671151304
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 139144
blocking_pid: 24
blocking_query: NULL
blocking_lock_id: 5795975856:419:6:4:5671141744
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2024-08-06 17:41:42
blocking_trx_age: 00:03:41
blocking_trx_rows_locked: 2
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 24
sql_kill_blocking_connection: KILL 24
2 rows in set (0.00 sec)
mysql>
