mysql死锁和等待锁

一.msyql死锁

• 死锁是相互争用资源过程中，都等待彼此释放资源，产生的现象。

死锁场景模拟

• 当session1持有共享锁S，并请求排他锁X，同时session2持有排他锁，并请求共享锁S的时候会产生死锁。

1）模拟session1持有共享锁，session2持有排他锁

## 在session1中执行
start transaction;
Query OK, 0 rows affected (0.00 sec)

select * from aa where userid = 18 lock in share mode;
+----+--------+
| id | userid |
+----+--------+
|  7 |     18 |
+----+--------+
1 row in set (0.00 sec)

## 在session1未提交之前，在session2执行删除，请求排他锁
start transaction;
Query OK, 0 rows affected (0.00 sec)

## 执行删除的时候，delete会一直阻塞，直到超过innodb_lock_wait_timeout，返回错误信息ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
delete from aa where id=7;

## 为了实验观察，调整innodb_lock_wait_timeout
show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 3600  |
+--------------------------+-------+
1 row in set (0.00 sec)

2）观察执行线程执行情况

## session2一直在执行
select * from information_schema.processlist order by state desc;
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE     | INFO                                                             |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
|  4 | root | localhost | test | Query   |   49 | updating  | delete from aa where id=7                                        |
|  5 | root | localhost | NULL | Query   |    0 | executing | select * from information_schema.processlist order by state desc |
|  3 | root | localhost | test | Sleep   |  331 |           | NULL                                                             |
|  7 | root | localhost | NULL | Sleep   |  991 |           | NULL                                                             |
+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
4 rows in set (0.00 sec)

3）模拟session1再持有排他锁

## 在session1中再执行
delete from aa where id=7;
Query OK, 1 row affected (0.00 sec)

## session1执行成功的同时session2的delete操作返回死锁错误信息
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

4）检查死锁情况
show engine innodb status\G;

MySQL thread id 2, OS thread handle 140247796619008, query id 141 localhost root updating
delete from aa where id=7
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `test`.`aa` trx id 3336 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `test`.`aa` trx id 3336 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000000000b04; asc       ;;
 2: len 7; hex a4000001180128; asc       (;;
 3: len 4; hex 80000012; asc     ;;

5）查看历史执行情况
select * from performance_schema.events_statements_history;

二.mysql等待锁

• mysql8.0已经废弃掉innodb_lock_waits表
• state状态是Waiting for table metadata lock，事务A等待事务B释放资源

## session1中执行
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

select * from aa where id >6;
+----+--------+
| id | userid |
+----+--------+
|  7 |     18 |
+----+--------+
1 row in set (0.00 sec)

## session2中执行修改表，会一直等待session1中事务提交
alter table aa add column addr varchar(100) not null default '';

## session3中查看线程信息，可以看到状态是Waiting
select * from information_schema.processlist order by state desc;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                                                             |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+
|  3 | root | localhost | test | Query   |  236 | Waiting for table metadata lock | alter table aa add column addr varchar(100) not null default ''  |
|  4 | root | localhost | NULL | Query   |    0 | executing                       | select * from information_schema.processlist order by state desc |
|  2 | root | localhost | test | Sleep   |  130 |                                 | NULL                                                             |
|  5 | root | localhost | NULL | Sleep   | 4597 |                                 | NULL                                                             |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------+

三.锁相关的表结构解析

• mysql8.0已经废弃掉innodb_lock_waits和inndb_locks表，取而代之的是performance_schema库中新增的data_lock_waits,data_locks。

1）innodb_trx表

字段	说明
trx_id	事务id,只读事务和非锁事务不会创建id的
trx_state	事务状态，running / lock wait / rolling back /committng
trx_started	事务开始时间
trx_requested_lock_id	事务当前正在等待锁的标识
trx_wait_started	事务开始等待的时间
trx_weight	事务权重，代表修改的行数和被事务锁住的行数
trx_mysql_thread_id	事务线程id
trx_query	事务正在执行的sql语句
trx_operation_state	事务当前操作状态
trx_tables_in_use	当前事务执行sql中使用的表的个数
trx_tables_locked	当前执行sql的行锁数量
trx_lock_structs	事务保留的锁数量
trx_lock_memory_bytes	事务锁住的内存大小
trx_rows_locked	事务锁住的记录数
trx_rows_modified	事务更改的行数
trx_concurrency_tickets	当前事务在被清掉之前可以多少工做，由 innodb_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	是否为了自适应hash索引立即释放查询所，或者通过调用mysql函数保留它

2）innodb_lock_waits

字段	说明
requesting_trx_id	请求事务的id
requested_lock_id	事务锁等待的锁定的id,可以关联innodb_locks获取更多信息
blocking_trx_id	阻塞事务的id
blocking_lock_id	某一事务的锁的id,该事务阻塞的了另一事务的运行

3）innodb_locks

字段	说明
lock_id	锁id
lock_trx_id	持有锁的事务id
lock_mode	锁模式
lock_type	锁的类型。record:行锁；table:表锁
lock_table	被锁定的或者包含的锁定记录的表的名称
lcok_index	索引名称，所类型为行锁才会显示，否则为null
lock_space	锁定行的表空间id,表锁为null
lock_page	锁定行的页号，表锁为null
lock_rec	锁定行数，表锁为null
lock_data	锁定行主键，表锁为null

4）data_lock_waits表

字段	说明
engine	请求锁定的存储引擎
requesting_engine_lock_id	存储引擎请求的锁的id
requesting_engine_transaction_id	请求锁定的事务的存储引擎内部id
requesting_thread_id	请求锁定的会话的线程id
requesting_event_id	在请求锁定的会话中导致锁定的performance schema事件
requesting_object_instance_begin	请求的锁在内存中的地址
blocking_engine_lock_id	阻止锁的id,关联data_locks可以获取锁更多信息
blocking_engine_transaction_id	拥有阻止锁的事务的存储引擎内部标识
blocking_thread_id	拥有阻止锁的会话的线程id
blocking_event_id	在持有该锁的会话中导致阻塞锁的performance schema事件
blocking_object_instance_begin	阻塞锁在内存中的地址

5）data_locks表

字段	说明
engine	存储引擎
engine_lock_id	存储引擎内部的锁id
engine_transaction_id	等同innodb_tr中的trx_id
thread_id	持有锁的线程id
event_id	事件id
object_schema	数据库名
object_name	表名
partition_name	分区名
subpartition_name	子分区名
index_name	索引名
object_instance_begin	锁的内存空间起始地址
lock_type	锁类型
lock_mode	锁模式
lock_status	锁状态，granted / waiting
lock_data	锁的数据，当lock_type类型为record时才会有值

6）锁信息定位

## mysql5.7
## 显示谁阻塞和谁等待，以及等待多久的的查询
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,
b.trx_id as blocking_trx_id,b.trx_mysql_thread_id as blocking_thread,substring(p.host,1,instr(p.host,':')-1) as blocking_host,
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;

## mysql5.7
## 线程在一个事务中空闲而正在遭受大量的锁操作，可以通过下面sql查询多少查询被哪些线程阻塞
select concat('thread ',b.trx_mysql_thread_id,' from ',p.host) as who_blocks,
if(p.command = "Sleep",p.time,0) as idle_in_trx,
max(timestampdiff(second,r.trx_wait_started,now())) as max_wait_time,
count(*) as num_waites
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
left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id
group by who_blocks 
order by num_waiters;

## mysql8.0
select b.trx_query,c.trx_query,d.object_name,object_name,index_name
from performance_schema.data_lock_waits a
left join information_schema.innodb_trx b on a.requesting_engine_transaction_id = b.trx_id
left join information_schema.innodb_trx c on a.blocking_engine_transaction_id = c.trx_id
left join performance_schema.data_locks d on a.requesting_engine_lock_id =d.engine_lock_id;