Lock wait timeout分析
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)
最新文章
- 3-HOP: A High-Compression Indexing Scheme for Reachability Query
- 解决Pyqt打包后运行报错：应用程序无法启动 因为程序的并行配置不正确
- 错误：Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp;的解决
- Mathematica修改默认字体
- GridView实现一个图片加多个文本框
- 转！！各种数据库的jdbc驱动下载及连接方式
- Codeforces Round #258 (Div. 2)（A，B，C，D）
- wifi详解（三）
- HDU 3259 Wormholes
- JavaScript异步编程 ( 一 )
- AJAX入门---DOM操作HTML
- RoutePrefix和Route 路由前缀
- ACM 第十一届 河南省省赛A题 计划日
- linux安装rabbitmq以及相关的操作命令
- jvm字节占用空间分析
- phpstudy一系列安装问题
- 机器学习基石（台湾大学 林轩田），Lecture 1: The Learning Problem
- 洛谷 P2629 好消息，坏消息
- PAT 1045 快速排序(25)（STL-set+思路+测试点分析）
- 使用EntityManager批量保存数据