MySQL 死锁专题问题处理

我就是我 2022-06-01 13:52 390阅读 0赞

## 1.Delete删除不存在的数据导致死锁 ##

原文：[Delete&Insert引发的Mysql死锁][Delete_Insert_Mysql]

mysql的锁分为三种(按照锁定的行数划分):  
1.record lock：记录锁，也就是仅仅锁着单独的一行  
2.gap lock：区间锁，仅仅锁住一个区间（注意这里的区间都是开区间，也就 是不包括边界值，至于为什么这么定义？innodb官方定义的）  
3.next-key lock：record lock+gap lock，所以next-key lock也就半开半闭区间，且是下界开，上界闭。(为什么这么定义？innodb官方定义的)

由于此处是在明确指定了no=XX的情况下抛出了死锁异常, 并且no建立的是普通索引, 所以此处mysql使用的应该是next-key lock(查看何种情况下使用何种锁 https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html).

下面来举个手册上的例子看看next-key lock是如何上锁的。假如一个索引的行有10,11,13,20  
那么可能的next-key lock的包括：  
(无穷小, 10\]  
(10,11\]  
(11,13\]  
(13,20\]  
(20, 无穷大)

因此对于delete删除不存在的数据造成了区间锁。

解决方法：

①先检查记录是否存在，否则不删除

②对于同一条记录，建议使用update

③如果锁是gap锁，这种死锁需要将事务的隔离级别设置为read commit。

④对于等待锁的的会话使用innodb\_lock\_wait\_timeout，防止过载。

## 2.锁与事务的隔离级别 ##

原文：[锁、事务与并发控制][Link 1]

原文：[MySQL中的锁（表锁、行锁）][MySQL]

### 事务的隔离级别 ###

![20160522233050544][]

### innodb行锁&表锁 ###

①没有索引字段的情况下不存在行锁，如果加锁，那么排他锁和共享锁都会升级为表锁

②修改表结构时的锁为表锁

③修改大量数据的时候可以锁表

④mysql的innodb存储引擎实务锁虽然是锁行，但它内部是锁索引的，根据where条件和select的值是否只有主键或非主键索引来判断怎么锁，比如只有主键，则锁主键索引，如果只有非主键，则锁非主键索引，如果主键非主键都有，则内部会按照顺序锁

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。  
举个例子:  
假设有个表单products ，里面有id跟name二个栏位，id是主键。  
例1: (明确指定主键，并且有此笔资料，row lock)  
SELECT \* FROM products WHERE id='3' FOR UPDATE;  
SELECT \* FROM products WHERE id='3' and type=1 FOR UPDATE;  
  
例2: (明确指定主键，若查无此笔资料，无lock)  
SELECT \* FROM products WHERE id='-1' FOR UPDATE;  
  
例2: (无主键，table lock)  
SELECT \* FROM products WHERE name='Mouse' FOR UPDATE;  
  
例3: (主键不明确，table lock)  
SELECT \* FROM products WHERE id<>'3' FOR UPDATE;  
  
例4: (主键不明确，table lock)  
SELECT \* FROM products WHERE id LIKE '3' FOR UPDATE;

例5:(主键不存在，gap锁)

SELECT \* FROM products WHERE id='50' FOR UPDATE; //假设50不存在  
  
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。  
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

### 锁超时时间 ###

原文：[Mysql事物锁等待超时][Mysql]

innodb\_lock\_wait\_timeout

问题出现环境：   
1、在同一事务内先后对同一条数据进行插入和更新操作；   
2、多台服务器操作同一数据库；   
3、瞬时出现高并发现象；

1、通过下面语句查找到为提交事务的数据，kill掉此线程即可。

select * from information_schema.innodb_trx

2、增加锁等待时间，即增大下面配置项参数值，单位为秒（s）

innodb_lock_wait_timeout=500

3、优化存储过程,事务避免过长时间的等待。

### MyISAM 表锁 ###

原文：[MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解][MySQL_MyISAM_InnoDB]

①此引擎不支持事务、也不支持行锁。

①表锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）

![Center][]

## 3、常见的死锁解决方式 ##

原文：[Mysql并发时经典常见的死锁原因及解决方法][Mysql 1]

## 4.并发插入导致死锁 ##

原因：uk冲突导致出现gap死锁

解决方式：

方式一：不使用自增主键和索引

方式二：降低事务的隔离级别

方式三：使用业务锁，使得并发串行执行。

对于有些记录要求存在就更新、不存在就插入，可以使用如下方式

c on duplicate key update count=count+1;

也可以使用replace

insert into player_count(player_id,count) value(1,1)

## 5.解除死锁状态 ##

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In\_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1.查看下在锁的事务

SELECT \* FROM INFORMATION\_SCHEMA.INNODB\_TRX;

2.杀死进程id（就是上面命令的trx\_mysql\_thread\_id列）

kill 线程ID

其它关于查看死锁的命令：

1：查看当前的事务  
SELECT \* FROM INFORMATION\_SCHEMA.INNODB\_TRX;

2：查看当前锁定的事务

SELECT \* FROM INFORMATION\_SCHEMA.INNODB\_LOCKS;

3：查看当前等锁的事务  
SELECT \* FROM INFORMATION\_SCHEMA.INNODB\_LOCK\_WAITS;

转载：[MySQL 死锁专题问题处理][MySQL 1]

[Delete_Insert_Mysql]: https://www.jianshu.com/p/11c46dbfd6e7
[Link 1]: http://blog.csdn.net/lemon89/article/details/51477497
[MySQL]: http://www.cnblogs.com/chenqionghe/p/4845693.html
[20160522233050544]: /images/20220601/141cadd11cb24813ae083ac0ede764be.png
[Mysql]: http://blog.csdn.net/wo541075754/article/details/50717842
[MySQL_MyISAM_InnoDB]: http://blog.csdn.net/hsd2012/article/details/51112009
[Center]: /images/20220601/49206a2ef8b448698daac3c2247e2f81.png
[Mysql 1]: http://www.cnblogs.com/zejin2008/p/5262751.html
[MySQL 1]: https://my.oschina.net/ososchina/blog/1615826