mysql with nolock放在where mysql deadlock found

并发时常见的死锁及解决方法

死锁是并发系统中常见的问题，同样也会出现在数据库MySQL的并发读写请求场景中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现“死锁”。常见的报错信息为 Deadlock found when trying to get lock...。

举例来说 A 事务持有 X1 锁 ，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。

如上图，是右侧的四辆汽车资源请求产生了回路现象，即死循环，导致了死锁。

从死锁的定义来看，MySQL 出现死锁的几个要素为：

1. 两个或者两个以上事务
2. 每个事务都已经持有锁并且申请新的锁
3. 锁资源同时只能被同一个事务持有或者不兼容
4. 事务之间因为持有锁和申请锁导致彼此循环等待

所谓死锁: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁。所以解决死锁主要还是针对于最常用的InnoDB。 InnoDB 引擎实现了标准的行级别锁：共享锁( S lock ) 和排他锁 ( X lock )。

关于进程死锁产生的四个条件：互斥、请求和保持、不可剥夺、环路等待。见：进程间通信、死锁、信号量、PV原语

1. 不同事务可以同时对同一行记录加 S(共享锁或者读锁) 锁。
2. 如果一个事务对某一行记录加 X(写锁或者排他锁) 锁，其他事务就不能加 S 锁或者 X 锁，从而导致锁等待。

如果事务 T1 持有行 r 的 S 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理:

1. T2 请求 S 锁立即被允许，结果 T1 T2 都持有 r 行的 S 锁
2. T2 请求 X 锁不能被立即允许

如果 T1 持有 r 的 X 锁，那么 T2 请求 r 的 X、S 锁都不能被立即允许，T2 必须等待 T1 释放 X 锁才可以，因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示：

• 间隙锁( gap lock )

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值，如果对 4 加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:

1. 如果索引列是唯一索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。
2. 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。

• 临界锁(next-key lock)

next-key lock 实际上就是 行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],

(10,11],

(11,13],

(13,20],

(20,正无穷)

在 RR (可重复读)隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读问题产生。

• 意向锁( Intention lock )

InnoDB 为了支持多粒度的加锁，允许行锁和表锁同时存在。为了支持在不同粒度上的加锁操作，InnoDB 支持了额外的一种锁方式，称之为意向锁( Intention Lock )。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。意向锁分为两种:

1. 意向共享锁( IS )：事务有意向对表中的某些行加共享锁
2. 意向排他锁( IX )：事务有意向对表中的某些行加排他锁

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。表级意向锁与行级锁的兼容性如下所示:

• 阅读死锁日志

在进行具体案例分析之前，咱们先了解下如何去读懂死锁日志，尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。在进行具体案例分析之前，咱们先了解下如何去读懂死锁日志，尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。

表结构和数据如下:

create table student (
	'id' int NOT NULL AUTO INCREMENT.
	'stuno' int DEFAULT NULL,
	'score' int DEFAULT NULL,
	 primary key ('id'),
	 key 'idx_stuno' ('stuno')
) ENGINE=InnoDB AUTO INCREMENT=5 DEFAULT CHARSET=ulf&mb4:
# 插入
insert into student(stuno,score) values (2,80),(5,98),(6,77):

测试用例如下:

通过执行show engine innodb status 可以查看到最近一次死锁的日志。

日志分析如下：

1. (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

事务号为2322，活跃 6秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:

mysql tables in use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁，对于 DML 语句为 LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT 表示正在等待锁，2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中 2 locks 表示 IX(意向排他锁) 锁和lock_mode X (Next-key lock)

1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )

delete from student where stuno=5 表示事务1正在执行的 sql，比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的，通常显示当前正在等待锁的 sql。

(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS 表示记录锁， 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁，本案例中其实是 Next-Key Lock 。

事务2的 log 和上面分析类似:

2. ***** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X | LOCK_gap，不过我们从日志里面看不到事务2执行的 delete from student where stuno=5;

这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

3. (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X +LOCK_REC_gap )

案例一：先 update 再 insert 的并发死锁问题

表结构如下，无数据:

测试用例如下:

死锁分析:
可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

案例二：

需求：将投资的钱拆成几份随机分配给借款人。

起初业务程序思路是这样的：投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。

抽象出来就是一个session通过for循环会有几条如下的语句：

select * from xxx where id='随机id' for update

基本来说，程序开启后不一会就死锁。这可以是说最经典的死锁情形了。

例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2。B用户金额随机分为2份，分给借款人2，1。

由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

select * from xxx where id in (xx,xx,xx) for update

在 in 里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁。

# 以下会话id为主键 
session 1:

select * from t3 where id in (8,9) for update;

session 2:

select * from t3 where id in (10,8,5) for update;
锁等待中……
其实这个时候id=10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在id=8的这里。
不信请看

session 3:
select * from t3 where id=5 for update;
锁等待中....

session 4:
select * from t3 where id=10 for update;

在其它session中id=5是加不了锁的，但是id=10是可以加上锁的。

案例三：

在开发中，经常会做这类的判断需求：根据字段值查询(有索引)，如果不存在，则插入；否则更新。

# 以id为主键为列，目前还没有id=22的行

session 1:
select * from t3 where id=22 for update;

session 2:
select * from t3 where id=23  for update;

session 1:
insert into t3 values(22,'ac','a',now());
锁等待中……

session 2:
insert into t3 values(23,'bc','b',now());

当对存在的 行 进行锁的时候(主键)，mysql就只有行锁。

当对未存在的 行 进行锁的时候(即使条件为主键)，mysql是会锁住一段范围(有gap锁)

锁住的范围为：
(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)

如：如果表中目前有已有的id为(11 ， 12)
那么就锁住(12，无穷大)
如果表中目前已有的id为(11 ， 30)
那么就锁住(11，30)

对于这种死锁的解决办法是：

insert into t3(xx,xx) on duplicate key update xx=‘XX’;

用mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁。

案例四：

session 1:
select * from t3 where id=9 for update;

session 2:
select * from t3 where id<20 for update;
锁等待中...

session 1:
insert into t3 values(7,'ae','a',now());

这个跟案例一其它是差不多的情况，只是session1不按常理出牌了，

Session2在等待Session1的id=9的锁，session2又持了1到8的锁(注意9到19的范围并没有被session2锁住)，最后，session1在插入新行时又得等待session2,故死锁发生了。

这种一般是在业务需求中基本不会出现，因为你锁住了id=9，却又想插入id=7的行，这就有点跳了，当然肯定也有解决的方法，那就是重理业务需求，避免这样的写法。

• 如何尽可能的避免死锁

1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里(运行了 start transaction 或设置了autocommit 等于0),那么就会锁定所查找到的记录。
6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。