MySQL行锁堵塞案例

背景

客户执行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_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 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.
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: mysql
1 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)

session 2

root@localhost : test 06:07:11> delete from test111 where a >3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction