MySQL锁的调试

2019独角兽企业重金招聘Python工程师标准>>>

MySQL锁的调试

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.26    |
+-----------+
1 row in set (0.01 sec)

创建表，如下，

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

client A，

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Next, client B begins a transaction and attempts to delete the row from the table:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

此时，使用下面的sql可以显示谁阻塞和谁在等待，以及等待多久，

SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    TIMESTAMPDIFF(SECOND,
        r.trx_wait_started,
        CURRENT_TIMESTAMP) AS wait_time,
    r.trx_query AS waiting_query,
    l.lock_table AS waiting_table_lock,
    l.lock_mode as waiting_lock_mode,
    l.lock_index as waiting_lock_index,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    IF(INSTR(p.host, ':') = 0,
        p.host,
        SUBSTRING(p.host,
            1,
            INSTR(p.host, ':') - 1)) AS blocking_host,
    IF(INSTR(p.host, ':') = 0,
        '',
        SUBSTRING(p.host,
            INSTR(p.host, ':') + 1)) AS blocking_port,
    IF(p.command = 'Sleep', p.time, 0) AS idle_in_trx,
    b.trx_query AS blocking_query
FROM
    information_schema.innodb_lock_waits AS w
        INNER JOIN
    information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
        INNER JOIN
    information_schema.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
        INNER JOIN
    information_schema.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
        LEFT JOIN
    information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC\G
*************************** 1. row ***************************
    waiting_trx_id: 34106
    waiting_thread: 4
         wait_time: 1
     waiting_query: DELETE FROM t WHERE i = 1
waiting_table_lock: `test`.`t`
   blocking_trx_id: 34105
   blocking_thread: 1
     blocking_host: localhost
     blocking_port:
       idle_in_trx: 1252
    blocking_query: NULL
1 row in set (0.01 sec)

mysql>

可以很清晰的看到事务34106正在等待，线程4正在等待，等待的时间为1秒。

还可以看到线程1导致了阻塞，其事务为34105，并且空闲了1252秒。

附录：关于锁调试的表结构的说明

mysql> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |
| lock_trx_id | varchar(18)         | NO   |     |         |       |
| lock_mode   | varchar(32)         | NO   |     |         |       |
| lock_type   | varchar(32)         | NO   |     |         |       |
| lock_table  | varchar(1024)       | NO   |     |         |       |
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+

lock_id：锁 ID。

lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。

lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。

lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。

lock_table：被锁定的或者包含锁定记录的表的名称。

lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。

lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。

lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。

lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。

lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

mysql> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |
| requested_lock_id | varchar(81) | NO   |     |         |       |
| blocking_trx_id   | varchar(18) | NO   |     |         |       |
| blocking_lock_id  | varchar(81) | NO   |     |         |       |
+-------------------+-------------+------+-----+---------+-------+

requesting_trx_id：请求事务的 ID。

requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。

blocking_trx_id：阻塞事务的 ID。

blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |
| trx_state                  | varchar(13)         | NO   |     |                     |       |
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |
| trx_wait_started           | datetime            | YES  |     | NULL                |       |
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_is_read_only           | int(1)              | NO   |     | 0                   |       |
| trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+

trx_id：事务ID。

trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。

trx_started：事务开始时间。

trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。

trx_wait_started：事务开始等待的时间。

trx_weight：事务的权重。

trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。

trx_query：事务正在执行的 SQL 语句。

trx_operation_state：事务当前操作状态。

trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。

trx_tables_locked：当前执行 SQL 的行锁数量。

trx_lock_structs：事务保留的锁数量。

trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。

trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。

trx_rows_modified：事务更改的行数。

trx_concurrency_tickets：事务并发票数。

trx_isolation_level：当前事务的隔离级别。

trx_unique_checks：是否打开唯一性检查的标识。

trx_foreign_key_checks：是否打开外键检查的标识。

trx_last_foreign_key_error：最后一次的外键错误信息。

trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。

trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

=================END=================

转载于:https://my.oschina.net/xinxingegeya/blog/530158