Lock wait timeout分析

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

1、4个用户连接数据库（A和D是本地回环登陆，B和C是本地登陆）

A用户信息：
    mysql> status;
    --------------
    Connection id:        8
    Current database:    db1
    Current user:        root@localhost
    Connection:            127.0.0.1 via TCP/IP

B用户信息：
    mysql> status;
    --------------
    Connection id:        3
    Current database:    db1
    Current user:        root@localhost
    Connection:            Localhost via UNIX socket

C用户信息：
    mysql> status;
    --------------
    Connection id:        6
    Current database:    db1
    Current user:        root@localhost
    Connection:            Localhost via UNIX socket
      
D用户信息：
    mysql> status;
    --------------
    Connection id:        9
    Current database:    db1
    Current user:        root@localhost
    Connection:            127.0.0.1 via TCP/IP

2、4个用户分别开启事务，对db1里面的t1表进行DML
    start transaction;

A用户执行：
    mysql> select * from t1;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    4 | mvk   |
    |    6 | marry |
    |    5 | fang  |
    |    5 | fang  |
    +------+-------+
    4 rows in set (0.00 sec)

    mysql> insert into t1 values(1,'juhua'),(2,'long');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> select * from t1;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    4 | mvk   |
    |    6 | marry |
    |    5 | fang  |
    |    5 | fang  |
    |    1 | juhua |
    |    2 | long  |
    +------+-------+
    6 rows in set (0.01 sec)

B用户执行：
    mysql> select * from t1;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    4 | mvk   |
    |    6 | marry |
    |    5 | fang  |
    |    5 | fang  |
    +------+-------+
    4 rows in set (0.00 sec)

    mysql>
    mysql> update t1 set name='liu' where id=5;
    等待50秒，报错
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
在第5个会话层面查看：
    mysql> select * from information_schema.innodb_lock_waits;
    +-------------------+-------------------+-----------------+------------------+
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
    +-------------------+-------------------+-----------------+------------------+
    | 426545            | 426545:45:3:10    | 426540          | 426540:45:3:10   |
    +-------------------+-------------------+-----------------+------------------+
    1 row in set, 1 warning (0.00 sec)
    锁等待是426545，因为426540占着事务没有提交造成的

    mysql> select * from information_schema.innodb_locks;
    +----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
    | lock_id        | lock_trx_id | lock_mode | lock_type | lock_table | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
    +----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
    | 426545:45:3:10 | 426545      | X         | RECORD    | `db1`.`t1` | GEN_CLUST_INDEX |         45 |         3 |       10 | 0x000000242504 |
    | 426540:45:3:10 | 426540      | X         | RECORD    | `db1`.`t1` | GEN_CLUST_INDEX |         45 |         3 |       10 | 0x000000242504 |
    +----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+
    
    mysql> select * from information_schema.innodb_trx\G
    *************************** 1. row ***************************
                        trx_id: 426546
                     trx_state: RUNNING
                   trx_started: 2019-08-07 17:24:36
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 1
           trx_mysql_thread_id: 6
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 1
         trx_lock_memory_bytes: 1136
               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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 2. row ***************************
                        trx_id: 426545
                     trx_state: LOCK WAIT
                   trx_started: 2019-08-07 17:21:36
         trx_requested_lock_id: 426545:45:3:10
              trx_wait_started: 2019-08-07 17:26:38
                    trx_weight: 5
           trx_mysql_thread_id: 3
                     trx_query: update t1 set name='liu' where id=5
           trx_operation_state: fetching rows
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 3
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 6
             trx_rows_modified: 2
       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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 3. row ***************************
                        trx_id: 426540
                     trx_state: RUNNING
                   trx_started: 2019-08-07 17:20:10
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 4
           trx_mysql_thread_id: 8
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 1
             trx_rows_modified: 2
       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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    3 rows in set (0.00 sec)
    找到trx_mysql_thread_id对应的id号，show processlist看是否对应，然后commit事务即可。

改变情况：
    分别在B、C、D执行DML
    B：
    mysql> update t1 set name='liu' where id=5;
    C:
    mysql> delete from t1 where id=4;
    D：
    mysql> update t1 set id=8 where id=6;
    全部发生所等待
    
    mysql> select * from information_schema.innodb_lock_waits;
    +-------------------+-------------------+-----------------+------------------+
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
    +-------------------+-------------------+-----------------+------------------+
    | 426547            | 426547:45:3:4     | 426545          | 426545:45:3:4    |
    | 426546            | 426546:45:3:4     | 426547          | 426547:45:3:4    |
    | 426546            | 426546:45:3:4     | 426545          | 426545:45:3:4    |
    | 426545            | 426545:45:3:10    | 426540          | 426540:45:3:10   |
    +-------------------+-------------------+-----------------+------------------+
    
    mysql> select * from information_schema.innodb_trx\G
    *************************** 1. row ***************************
                        trx_id: 426547
                     trx_state: LOCK WAIT
                   trx_started: 2019-08-07 17:35:19
         trx_requested_lock_id: 426547:45:3:4
              trx_wait_started: 2019-08-07 17:36:48
                    trx_weight: 4
           trx_mysql_thread_id: 9
                     trx_query: update t1 set id=8 where id=6
           trx_operation_state: starting index read
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 1
             trx_rows_modified: 2
       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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 2. row ***************************
                        trx_id: 426546
                     trx_state: LOCK WAIT
                   trx_started: 2019-08-07 17:24:36
         trx_requested_lock_id: 426546:45:3:4
              trx_wait_started: 2019-08-07 17:36:52
                    trx_weight: 2
           trx_mysql_thread_id: 6
                     trx_query: delete from t1 where id=4
           trx_operation_state: starting index read
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 2
             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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 3. row ***************************
                        trx_id: 426545
                     trx_state: LOCK WAIT
                   trx_started: 2019-08-07 17:21:36
         trx_requested_lock_id: 426545:45:3:10
              trx_wait_started: 2019-08-07 17:36:56
                    trx_weight: 5
           trx_mysql_thread_id: 3
                     trx_query: update t1 set name='liu' where id=5
           trx_operation_state: fetching rows
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 3
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 7
             trx_rows_modified: 2
       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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 4. row ***************************
                        trx_id: 426540
                     trx_state: RUNNING
                   trx_started: 2019-08-07 17:20:10
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 4
           trx_mysql_thread_id: 8
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 1
             trx_rows_modified: 2
       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: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    4 rows in set (0.00 sec)
    直接在426540对应的trx_mysql_thread_id: 8会话提交之后，发现B和D锁等待消失，只有C还有锁等待。
    查找innodb_lock_waits表看到426545还引起了锁等待。同理commit即可。
   
    mysql> select * from information_schema.innodb_lock_waits;
    +-------------------+-------------------+-----------------+------------------+
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
    +-------------------+-------------------+-----------------+------------------+
    | 426546            | 426546:45:3:4     | 426545          | 426545:45:3:4    |
    +-------------------+-------------------+-----------------+------------------+
    1 row in set, 1 warning (0.00 sec)