文档章节

MySQL锁的调试

秋风醉了
 秋风醉了
发布于 2015/11/13 20:50
字数 1205
阅读 234
收藏 15

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=================

© 著作权归作者所有

秋风醉了
粉丝 252
博文 532
码字总数 405694
作品 0
朝阳
程序员
私信 提问
MySQL MVCC && 事务隔离级别 && 锁

MySQL MVCC && 事务隔离级别 && 锁 InnoDB多版本并发控制——MVCC http://my.oschina.net/xinxingegeya/blog/208821 MySQLS锁X锁read lockwrite lock http://my.oschina.net/xinxingegeya/bl......

秋风醉了
2014/07/30
582
0
MySQL · 特性分析 · 到底是谁执行了FTWL

什么是FTWL FTWRL是FLUSH TABLES WITH READ LOCK的简称(FTWRL),该命令主要用于保证备份一致性备份。为了达到这个目的,它需要关闭所有表对象,因此这个命令的杀伤性很大,执行命令时容易导致...

阿里云RDS-数据库内核组
2017/08/05
0
0
MySQL数据库InnoDB存储引擎源代码调试跟踪分析

导读目录: 1 早期结论... 3 2 测试一:死锁检测... 4 3 测试二:cursor测试... 6 4 测试三:external_lock测试... 6 5 测试四:杂项测试... 6 6 测试五:autocommit测试... 7 7 测试六:unl...

吴问志
2011/12/06
245
0
MySQL • 特性分析 • 到底是谁执行了FTWL

摘要: 什么是FTWL FTWRL是FLUSH TABLES WITH READ LOCK的简称(FTWRL),该命令主要用于保证备份一致性备份。为了达到这个目的,它需要关闭所有表对象,因此这个命令的杀伤性很大,执行命令时...

姬子玉
2017/12/13
0
0
MySQL所有操作hang住了,怎么破?

作者介绍 王松磊,现任职于UCloud,从事MySQL数据库内核研发工作。主要负责UCloud云数据库udb的内核故障排查工作以及数据库新特性的研发工作。 系统环境 CentOS release 6.7 MySQL社区版MyS...

王松磊
2017/05/17
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring Security 自定义登录认证(二)

一、前言 本篇文章将讲述Spring Security自定义登录认证校验用户名、密码,自定义密码加密方式,以及在前后端分离的情况下认证失败或成功处理返回json格式数据 温馨小提示:Spring Security...

郑清
14分钟前
0
0
php yield关键字以及协程的实现

php的yield是在php5.5版本就出来了,而在初级php界却很少有人提起,我就说说个人对php yield的理解 Iterator接口 在php中,除了数组,对象可以被foreach遍历之外,还有另外一种特殊对象,也就是继承...

冻结not
27分钟前
0
0
servlet请求和响应的过程

本文转载于:专业的前端网站➥servlet请求和响应的过程 1.加载 Servlet类被加载到Java虚拟机中,并且实例化。在这个过程中,web容器(例如tomcat)会调用Servlet类的公开无参构造函数,产生一...

前端老手
27分钟前
2
0
golang 1.13 errors 包来了,不用写“err 气功波”代码

引 这篇是对 errors 包 的姿势挖掘 气功波错误代码 从 http.Get()返回的错误 判断 syscall.ECONNREFUSED 错误.以前要对 go 标准库 error 结构有点熟悉,才能写出下面的代码 func CmdErr(err ...

guonaihong
31分钟前
20
0
喜玛拉雅已听书单

时间倒序排 书名 作者 状态 唐砖 孑与2 进行中 死灵之书(克苏鲁神话合集) 阿卜杜拉·阿尔哈萨德 进行中 赡养人类 刘慈欣 完结 赡养上帝 刘慈欣 完结 中国太阳 刘慈欣 完结 中国太阳 刘慈欣...

Alex_Java
32分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部