05、MySQL进阶：锁等待及死锁初探

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860686407.png)

------------

### **版本信息**

```python
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set

mysql> 
```

------------

### **MySQL 行锁分析**

mysql> 
```

**变量说明：**

- Innodb_row_lock_current_waits 当前正在等待锁定的数量 单位毫秒
- Innodb_row_lock_time 从系统启动到现在锁定总时间长度 单位毫秒
- Innodb_row_lock_time_avg 每次等待所花平均时间 单位毫秒
- Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花时间 单位毫秒
- Innodb_row_lock_waits 系统启动后到现在总共等待的次数

重点关注 ： Innodb_row_lock_time_avg 、Innodb_row_lock_waits 、Innodb_row_lock_time

------------

### **MySQL死锁演示**

**事务隔离级别， 默认 可重复读**

mysql>

```
【操作步骤】

| session1 | session2 |
| --- | --- |
| begin 模拟开启事务 |  |
| select * from art_info where id=1 for update; |  |
|  | begin 模拟开启事务 |
|  | select * from account where id=2 for update; |
| select * from art_info where id=2 for update; ---->一直等待 |  |
|  | select * from art_info where id=1 for update; —> Deadlock found when trying to get lock; try restarting transaction |

**大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁**

------------

### **排查过程**

【模拟锁等待 】

| session1 | session2 |
| --- | --- |
| begin 模拟开启事务 |  |
| select * from art_info where id=1 for update; |  |
|  | begin 模拟开启事务 |
|  | select * from account where id=2 for update; |
| select * from account where id=2 for update;	 |  |

```python
mysql> select * from art_info where id =2 for update ;    
1205 - Lock wait timeout exceeded; try restarting transaction

```

```python
-- 查看事务 
select  * from information_schema.INNODB_TRX;
-- 查看锁 
select * from information_schema.INNODB_LOCKS;
-- 查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;
-- 锁释放  information_schema.INNODB_TRX  查询 trx_mysql_thread_id 然后去 kill 对应的value 
kill  trx_mysql_thread_id
```

来吧，用上面的SQL查吧

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860692175.png)

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860697739.png)

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860703418.png)

### **查看近期死锁日志信息**

------------

```python
show engine innodb status \G;
```

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860709089.png)

------------

### **查询锁等待命令及kill 锁**
```python
-- 查看事务 
select  * from information_schema.INNODB_TRX;
-- 查看锁 
select * from information_schema.INNODB_LOCKS;
-- 查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;
-- 锁释放  information_schema.INNODB_TRX  查询 trx_mysql_thread_id 然后去 kill 对应的value 
kill  trx_mysql_thread_id
```

![](https://cloud.cxykk.com/images/2024/2/2/1558/1706860714757.png)

**锁等待有自己的超时时间，超过后一般都会自动释放**

```python
mysql> select * from art_info where id =2 for update ;    
1205 - Lock wait timeout exceeded; try restarting transaction
```

------------

### **优化建议**

- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少检索条件范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离