ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction分析
1、4个用户连接数据库(A和D是本地回环登陆,B和C是本地登陆)
A用户信息:
mysql> status;
--------------
Connection id: 8
Current database: db1
Current user: root@localhost
Connection: 127.0.0.1 via TCP/IP
B用户信息:
mysql> status;
--------------
Connection id: 3
Current database: db1
Current user: root@localhost
Connection: Localhost via UNIX socket
C用户信息:
mysql> status;
--------------
Connection id: 6
Current database: db1
Current user: root@localhost
Connection: Localhost via UNIX socket
D用户信息:
mysql> status;
--------------
Connection id: 9
Current database: db1
Current user: root@localhost
Connection: 127.0.0.1 via TCP/IP
2、4个用户分别开启事务,对db1里面的t1表进行DML
start transaction;
A用户执行:
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 4 | mvk |
| 6 | marry |
| 5 | fang |
| 5 | fang |
+------+-------+
4 rows in set (0.00 sec)
mysql> insert into t1 values(1,'juhua'),(2,'long');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 4 | mvk |
| 6 | marry |
| 5 | fang |
| 5 | fang |
| 1 | juhua |
| 2 | long |
+------+-------+
6 rows in set (0.01 sec)
B用户执行:
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 4 | mvk |
| 6 | marry |
| 5 | fang |
| 5 | fang |
+------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> update t1 set name='liu' where id=5;
等待50秒,报错
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
在第5个会话层面查看:
mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 426545 | 426545:45:3:10 | 426540 | 426540:45:3:10 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
锁等待是426545,因为426540占着事务没有提交造成的
mysql> select * from information_schema.innodb_locks;
+----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
| 426545:45:3:10 | 426545 | X | RECORD | `db1`.`t1` | GEN_CLUST_INDEX | 45 | 3 | 10 | 0x000000242504 |
| 426540:45:3:10 | 426540 | X | RECORD | `db1`.`t1` | GEN_CLUST_INDEX | 45 | 3 | 10 | 0x000000242504 |
+----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 426546
trx_state: RUNNING
trx_started: 2019-08-07 17:24:36
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 6
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 426545
trx_state: LOCK WAIT
trx_started: 2019-08-07 17:21:36
trx_requested_lock_id: 426545:45:3:10
trx_wait_started: 2019-08-07 17:26:38
trx_weight: 5
trx_mysql_thread_id: 3
trx_query: update t1 set name='liu' where id=5
trx_operation_state: fetching rows
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 6
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 3. row ***************************
trx_id: 426540
trx_state: RUNNING
trx_started: 2019-08-07 17:20:10
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
3 rows in set (0.00 sec)
找到trx_mysql_thread_id对应的id号,show processlist看是否对应,然后commit事务即可。
改变情况:
分别在B、C、D执行DML
B:
mysql> update t1 set name='liu' where id=5;
C:
mysql> delete from t1 where id=4;
D:
mysql> update t1 set id=8 where id=6;
全部发生所等待
mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 426547 | 426547:45:3:4 | 426545 | 426545:45:3:4 |
| 426546 | 426546:45:3:4 | 426547 | 426547:45:3:4 |
| 426546 | 426546:45:3:4 | 426545 | 426545:45:3:4 |
| 426545 | 426545:45:3:10 | 426540 | 426540:45:3:10 |
+-------------------+-------------------+-----------------+------------------+
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 426547
trx_state: LOCK WAIT
trx_started: 2019-08-07 17:35:19
trx_requested_lock_id: 426547:45:3:4
trx_wait_started: 2019-08-07 17:36:48
trx_weight: 4
trx_mysql_thread_id: 9
trx_query: update t1 set id=8 where id=6
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 426546
trx_state: LOCK WAIT
trx_started: 2019-08-07 17:24:36
trx_requested_lock_id: 426546:45:3:4
trx_wait_started: 2019-08-07 17:36:52
trx_weight: 2
trx_mysql_thread_id: 6
trx_query: delete from t1 where id=4
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 3. row ***************************
trx_id: 426545
trx_state: LOCK WAIT
trx_started: 2019-08-07 17:21:36
trx_requested_lock_id: 426545:45:3:10
trx_wait_started: 2019-08-07 17:36:56
trx_weight: 5
trx_mysql_thread_id: 3
trx_query: update t1 set name='liu' where id=5
trx_operation_state: fetching rows
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 7
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 4. row ***************************
trx_id: 426540
trx_state: RUNNING
trx_started: 2019-08-07 17:20:10
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
4 rows in set (0.00 sec)
直接在426540对应的trx_mysql_thread_id: 8会话提交之后,发现B和D锁等待消失,只有C还有锁等待。
查找innodb_lock_waits表看到426545还引起了锁等待。同理commit即可。
mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 426546 | 426546:45:3:4 | 426545 | 426545:45:3:4 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
Lock wait timeout分析
最新推荐文章于 2023-12-19 20:25:33 发布