查看与调试死锁

查看区间锁(间隙锁,临键锁 是innodb 特有施加在索引记录区间的锁)是否关闭，它由innodb_locks_unsafe_for_binlog 参数控制

ON，表示关闭区间锁，此时一致性会被破环

OFF，表示开启区间锁

 

mysql> show global variables like "innodb_locks%";

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_locks_unsafe_for_binlog | OFF |

+--------------------------------+-------+

1 row in set (0.00 sec)

 

 

查看事务是否自动提交

MYSQL 默认把每一个单独的SQL语句作为一个事务，自动提交

可以这么查询事务自动提交的参数

show global variables like "autocommit";

 

可以使用 set session autocommit=0 ，修改事务自动提交这个选项，每个session 要改为手动提交

 

 

mysql> show global variables like "autocommit";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

 

 

mysql> show variables like "autocommit";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

修改session 变量，并不影响global 变量 ，session 变量默认基础global 变量。

 

 

查看 事务的隔离级别

show global variables like "tx_isolation";

 

mysql> show global variables like "tx_isolation";

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.01 sec)

 

可以这么设置 事务的隔离级别

set session transaction isolation level X;

X取

read uncommitted

read committed

repeatable read

serializable 

 

 

 

在新版本的InnoDB Plugin 种，在INFORMATION_SCHEMA架构下添加了 innodb_trx，innodb_locks， innodb_lock_waits，通过着三张表，可以更简单地监控当前的事务并分析可能存在锁的问题，

 

innodb_trx

trx_id innodb存储引擎内部唯一的事务ID

trx_state: 当前事务的状态

trx_started: 事务的开始的时间

trx_requested_lock_id: 等待事务的锁ID。如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state 不是LOCK WAIT, 则该值为NULL

trx_wait_started: 事务等待开始的时间

trx_weight: 事务的权重，反映了一个事务修改和锁住的行数。在innodb存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的进行回滚

trx_mysql_thread_id: mysql 中的线程ID

trx_query: 事务运行的SQL 语句，有时候查询为NULL

 

 

 

desc innodb_locks;

 

lock_id 锁的ID

lock_trx_id 事务ID

lock_mode 锁的模式

lock_type 锁的类型，表锁还是行锁

lock_table 要加锁的表

lock_index 锁的索引

lock_space innodb 存储引擎表空间的ID号

lock_page 被锁住的页的数量。若是表锁，则该值为NULL

lock_rec 被锁住的行的数量，若时表锁，则该值为NULL

lock_data 被锁住的行的主键值。当是表锁时，该值为NULL

 

 

当事务较小时，我们人为地，直观地旧可以进行判断了。但是 当事务量非常大，锁的等待也时常发生时，这个时候 不容易判断，但是通过INNODB_LOCK_WAITS，可以很直观地反映出当前的等待

 

INNODB_LOCK_WAITS

requesting_trx_id ： 申请锁资源的事务ID

requested_lock_id ： 申请的锁的ID

blocking_trx_id ：阻塞的事务ID

blocking_lock_id ：阻塞的锁的ID

这次 我们可以清楚直观地看到哪个事务阻塞了另一个事务。当然这里只给出了事务和锁的ID，

如果 需要根据 INNODB_TRX , INNODB_LOCKS,INNODB_LOCK_WAITS 这三张表直观地看到详细信息

 

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

 

 

 

 

 

 

参考资源

https://mp.weixin.qq.com/s/_36Sy0FldFRNvLRpHxfucQ

<MYSQL技术内幕>