MySQL行锁堵塞案例

2019年02月28日原文

背景客户执行delete操作一直显示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 测试环境 centos7.

背景

客户执行delete操作一直显示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

测试环境

centos7.4

MySQL5.7.25

测试步骤

session 1

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)

session 2

root@localhost : (none) 06:07:07> begin;

Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 06:07:09> use test

Database changed

root@localhost : test 06:07:11> delete from test111 where a >3;

session 3

root@localhost : test 06:07:03> use sys

Database changed

root@localhost : sys 07:40:44> select * from innodb_lock_waitsG

*************************** 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 903490

sql_kill_blocking_connection: KILL 903490

1 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.

root@localhost : sys 07:41:47> select * from sys.session where conn_id=903490G

*************************** 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: mysql

1 row in set (0.11 sec)

root@localhost : sys 07:41:57> show processlist;

+--------+------+--------------------+------+---------+------+----------+--------------------------------+

+--------+------+--------------------+------+---------+------+----------+--------------------------------+

| 903705 | root | 10.244.2.124:44058 | test | Sleep | 1 | | NULL |

+--------+------+--------------------+------+---------+------+----------+--------------------------------+

4 rows in set (0.00 sec)

session 2

root@localhost : test 06:07:11> delete from test111 where a >3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction