MySQL行锁堵塞案例

灰太狼 2022-02-14 14:41 199阅读 0赞

#### 背景 ####

客户执行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