本文共 4101 字，大约阅读时间需要 13 分钟。
客户执行delete操作一直显示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 05:58:52> select * from test111;+------+| a |+------+| 1 || 3 || 7 || 10 || 11 |+------+5 rows in set (0.00 sec)root@localhost : test 06:06:53> begin;Query OK, 0 rows affected (0.00 sec)root@localhost : test 06:06:55> insert into test111 values(11);Query OK, 1 row affected (0.00 sec)
root@localhost : (none) 06:07:07> begin;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 06:07:09> use testDatabase changedroot@localhost : test 06:07:11> delete from test111 where a >3;
root@localhost : test 06:07:03> use sysDatabase changedroot@localhost : sys 07:40:44> select * from innodb_lock_waits\G*************************** 1. row *************************** wait_started: 2019-02-18 19:41:29 wait_age: 00:00:18 wait_age_secs: 18 locked_table: `test`.`test111` locked_index: GEN_CLUST_INDEX locked_type: RECORD waiting_trx_id: 605682 waiting_trx_started: 2019-02-18 19:41:29 waiting_trx_age: 00:00:18 waiting_trx_rows_locked: 3 waiting_trx_rows_modified: 2 waiting_pid: 903466 waiting_query: delete from test111 where a >3 waiting_lock_id: 605682:47:3:6 waiting_lock_mode: X blocking_trx_id: 605672 blocking_pid: 903490 blocking_query: NULL blocking_lock_id: 605672:47:3:6 blocking_lock_mode: X blocking_trx_started: 2019-02-18 19:41:20 blocking_trx_age: 00:00:27 blocking_trx_rows_locked: 1 blocking_trx_rows_modified: 1 sql_kill_blocking_query: KILL QUERY 903490sql_kill_blocking_connection: KILL 9034901 row in set, 3 warnings (0.00 sec)Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.root@localhost : sys 07:41:47> select * from sys.session where conn_id=903490\G*************************** 1. row *************************** thd_id: 903532 conn_id: 903490 user: root@localhost db: test command: Sleep state: NULL time: 37 current_statement: NULL statement_latency: NULL progress: NULL lock_latency: 128.00 us rows_examined: 0 rows_sent: 0 rows_affected: 1 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: insert into test111 values(11)last_statement_latency: 406.35 us current_memory: 0 bytes last_wait: NULL last_wait_latency: NULL source: NULL trx_latency: NULL trx_state: NULL trx_autocommit: NULL pid: 21460 program_name: mysql1 row in set (0.11 sec)root@localhost : sys 07:41:57> show processlist;+--------+------+--------------------+------+---------+------+----------+--------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+--------------------+------+---------+------+----------+--------------------------------+| 903410 | root | localhost | sys | Query | 0 | starting | show processlist || 903466 | root | localhost | test | Query | 65 | updating | delete from test111 where a >3 || 903490 | root | localhost | test | Sleep | 74 | | NULL || 903705 | root | 10.244.2.124:44058 | test | Sleep | 1 | | NULL |+--------+------+--------------------+------+---------+------+----------+--------------------------------+4 rows in set (0.00 sec)
root@localhost : test 06:07:11> delete from test111 where a >3;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
转载地址：http://myzeo.baihongyu.com/