环境说明 zabbix的表history_str的数据总行数为680：root@zabbix 10:24:26>select count(*) from history_str;
+----------+
| count(*) |
+----------+
| 680 |
+----------+
1 row in set (0.00 sec)
通过这个表模拟会话1执行一个事务很快就完成了但是未提交，会话2对该表进行update操作就会被阻塞。
会话1 root@zabbix 10:24:26>begin;
root@zabbix 10:24:26>select * from history_str for update;
会话1上对该表开启一个事务，并执行一个操作，对表的每一行都加X锁。也就是说相当于对该表加了表锁。
会话2 这时另一个会话对该表的某一条记录进行更新操作，就遇到了报错：root@zabbix 10:26:43>update history_str set value='testupdate' where itemid=23327;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
由于表数据很少，所以会话1中的操作很快已完成，所以在processlist中是看不到任何正在执行的sql：root@zabbix 10:31:30>show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------+------------------+
| 1 | root | localhost | zabbix | Sleep | 80 | | NULL |
| 2 | root | localhost | zabbix | Query | 0 | init | show processlist |
| 3 | root | localhost | NULL | Sleep | 217 | | NULL |
+----+------+-----------+--------+---------+------+-------+------------------+
3 rows in set (0.00 sec)
会话3 查看是否存在正在被锁定的事务线程：root@(none) 10:27:21>select r.*,p.HOST,p.COMMAND,p.state,p.time from information_schema.innodb_trx r,information_schema.processlist p where p.id = trx_mysql_thread_id \G
*************************** 1. row ***************************
trx_id: 19828757
trx_state: RUNNING #这个状态说明事务还在运行中
trx_started: 2017-07-07 10:24:26
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 1
trx_query: NULL #由于已经执行完毕，这里看不到具体的SQL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 5
trx_lock_memory_bytes: 1184
trx_rows_locked: 684
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
HOST: localhost
COMMAND: Sleep #这个状态说明sql已执行完毕
state:
time: 210
1 row in set (0.01 sec)
可以看到有一个事务id为19828757，为了确定下update操作是否被他阻塞，再次执行会话2的update语句然后马上到会话3查询：root@(none) 10:27:56>select r.*,p.HOST,p.COMMAND,p.state,p.time from information_schema.innodb_trx r,information_schema.processlist p where p.id = trx_mysql_thread_id \G
*************************** 1. row ***************************
trx_id: 19828759
trx_state: LOCK WAIT
trx_started: 2017-07-07 10:28:13
trx_requested_lock_id: 19828759:79:6:2
trx_wait_started: 2017-07-07 10:28:13
trx_weight: 2
trx_mysql_thread_id: 2
trx_query: update history_str set value='testupdate' where itemid=23327
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
HOST: localhost
COMMAND: Query
state: updating
time: 2
*************************** 2. row ***************************
trx_id: 19828757
trx_state: RUNNING
trx_started: 2017-07-07 10:24:26
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 1
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 5
trx_lock_memory_bytes: 1184
trx_rows_locked: 684
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
HOST: localhost
COMMAND: Sleep
state:
time: 229
2 rows in set (0.00 sec)
可以看到我们的update果然是被会话1中未提交的事务（trx_id:19828757）阻塞了。
解决办法 应急解决：Kill掉会话1
