Mysql 死锁问题

1.Mysql中的隔离级别RC与RR

1.1. 数据库事务ACID特性

数据库事务的4个特性:

原子性(Atomic): 事务中的多个操作,不可分割,要么都成功,要么都失败; All or Nothing.

一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后,总金额不变;

隔离性(Isolation): 多个事务之间就像是串行执行一样,不相互影响;

持久性(Durability): 事务提交后被持久化到永久存储.

1.2. 隔离性

其中 隔离性 分为了四种:

READ UNCOMMITTED:可以读取未提交的数据,未提交的数据称为脏数据,所以又称脏读。此时:幻读,不可重复读和脏读均允许;

READ COMMITTED:只能读取已经提交的数据;此时:允许幻读和不可重复读,但不允许脏读,所以RC隔离级别要求解决脏读;

REPEATABLE READ:同一个事务中多次执行同一个select,读取到的数据没有发生改变;此时:允许幻读,但不允许不可重复读和脏读,所以RR隔离级别要求解决不可重复读;

SERIALIZABLE: 幻读,不可重复读和脏读都不允许,所以serializable要求解决幻读;

1.3. 几个概念

脏读:可以读取未提交的数据。RC 要求解决脏读;

不可重复读:同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交);

可重复读:同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现);RR各级级别要求达到可重复读的标准;

幻读:同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题;

这里一定要区分 不可重复读 和 幻读:

不可重复读的重点是修改:

同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除:

同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大:

对于前者, 在RC下只需要锁住满足条件的记录,就可以避免被其它事务修改,也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读;

对于后者, 要锁住满足条件的记录及所有这些记录之间的gap,也就是需要 gap lock。

而ANSI SQL标准没有从隔离程度进行定义,而是定义了事务的隔离级别,同时定义了不同事务隔离级别解决的三大并发问题:

Isolation LevelDirty ReadUnrepeatable ReadPhantom Read
Read UNCOMMITTEDYESYESYES
READ COMMITTEDYESYESYES
READ REPEATABLENONOYES
SERIALIZABLENONONO

1.4. 数据库的默认隔离级别

除了MySQL默认采用RR隔离级别之外,其它几大数据库都是采用RC隔离级别。

但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别,解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。

MySQL的实现:MySQL默认采用RR隔离级别,SQL标准是要求RR解决不可重复读的问题,但是因为MySQL采用了gap lock,所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢?其实MySQL的SERIALIZABLE采用了经典的实现方式,对读和写都加锁。

1.5. MySQL 中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR,但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢?我们该如何选择呢?为什么MySQL将RR作为默认的隔离级别呢?

1.5.1 RC 与 RR 在锁方面的区别

1> 显然 RR 支持 gap lock(next-key lock),而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR;

2> RC 隔离级别,通过 where 条件过滤之后,不符合条件的记录上的行锁,会释放掉(虽然这里破坏了“两阶段加锁原则”);但是RR隔离级别,即使不符合where条件的记录,也不会是否行锁和gap lock;所以从锁方面来看,RC的并发应该要好于RR;

1.5.2 RC 与 RR 在复制方面的区别

1> RC 隔离级别不支持 statement 格式的bin log,因为该格式的复制,会导致主从数据的不一致;只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时,我们最好使用:binlog_format=row

2> MySQL5.6 的早期版本,RC隔离级别是可以设置成使用statement格式的bin log,后期版本则会直接报错;

1.5.3 RC 与 RR 在一致性读方面的区别

简单而且,RC隔离级别时,事务中的每一条select语句会读取到他自己执行时已经提交了的记录,也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时,事务中的一致性读的ReadView是以第一条select语句的运行时,作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

具体可以参加:MySQL 一致性读 深入研究

1.5.4 RC 支持半一致性读,RR不支持

RC隔离级别下的update语句,使用的是半一致性读(semi consistent);而RR隔离级别的update语句使用的是当前读;当前读会发生锁的阻塞。

1> 半一致性读:

简单来说,semi-consistent read是read committed与consistent read两者的结合。一个update语句,如果读到一行已经加锁的记录,此时InnoDB返回记录最近提交的版本,由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新),则MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下,或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

对比RR隔离级别,update语句会使用当前读,如果一行被锁定了,那么此时会被阻塞,发生锁等待。而不会读取最新的提交版本,然后来判断是否符合where条件。

半一致性读的优点:

减少了update语句时行锁的冲突;对于不满足update更新条件的记录,可以提前放锁,减少并发冲突的概率。

2.Mysql中的锁

2.1.Mysql都有什么锁

MySQL有三种锁的级别:表级、行级、页级。

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

算法:

行锁(Record Lock):锁直接加在索引记录上面,锁住的是key。

间隙锁(Gap Lock):锁定索引记录间隙,确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

Next-Key Lock :行锁和间隙锁组合起来就叫Next-Key Lock。

默认情况下,InnoDB工作在可重复读隔离级别下,并且会以Next-Key Lock的方式对数据行进行加锁,这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合,当InnoDB扫描索引记录的时候,会首先对索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。加上间隙锁之后,其他事务就不能在这个间隙修改或者插入记录。

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作,以此防止幻读的发生。

2.1.1 行锁(Record Lock)

行锁锁定的是索引记录,而不是行数据,也就是说锁定的是key。

2.1.2 间隙锁(Gap Lock)

例如:

create table test(id int,v1 int,v2 int,primary key(id),key idx_v1(v1))Engine=InnoDB DEFAULT CHARSET=UTF8;

该表的记录如下:

+—-+——+——+

| id | v1 | v2 |

+—-+——+——+

| 1 | 1 | 0 |

| 2 | 3 | 1 |

| 3 | 4 | 2 |

| 5 | 5 | 3 |

| 7 | 7 | 4 |

| 10 | 9 | 5 |

间隙锁(Gap Lock)一般是针对非唯一索引而言的,test表中的v1(非唯一索引)字段值可以划分的区间为:

(-∞,1)

(1,3)

(3,4)

(4,5)

(5,7)

(7,9)

(9, +∞)

假如要更新v1=7的数据行,那么此时会在索引idx_v1对应的值,也就是v1的值上加间隙锁,锁定的区间是(5,7)和(7,9)。同时找到v1=7的数据行的主键索引和非唯一索引,对key加上锁。

记录的GAP的区间如下:是一个左开右闭的空间(原因是默认主键的有序自增的特性)

2.1.3后码锁(Next-Key Lock)

记录锁和间隙锁的结合,对于InnoDB中,更新非唯一索引对应的记录(在这里来说是更新v1字段的值),会加上Next-Key Lock。如果更新记录为空,就不能加记录锁,只能加间隙锁。

在默认情况下,mysql的事务隔离级别是可重复读,并且innodb_locks_unsafe_for_binlog参数为0,这时默认采用next-key locks。所谓Next-Key Locks,就是Record lock和gap lock的结合,即除了锁住记录本身,还要再锁住索引之间的间隙。

下面我们针对大部分的SQL类型分析是如何加锁的,假设事务隔离级别为可重复读。

select .. from

不加任何类型的锁

select…from lock in share mode

在扫描到的任何索引记录上加共享的(shared)next-key lock,还有主键聚集索引加排它锁

select..from for update

在扫描到的任何索引记录上加排它的next-key lock,还有主键聚集索引加排它锁

update..where delete from..where

在扫描到的任何索引记录上加next-key lock,还有主键聚集索引加排它锁

insert into..

简单的insert会在insert的行对应的索引记录上加一个排它锁,这是一个record lock,并没有gap,所以并不会阻塞其他session在gap间隙里插入记录。不过在insert操作之前,还会加一种锁,官方文档称它为insertion intention gap lock,也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他session就可完成,这样就使得insert操作无须加真正的gap lock。想象一下,如果一个表有一个索引idx_test,表中有记录1和8,那么每个事务都可以在2和7之间插入任何记录,只会对当前插入的记录加record lock,并不会阻塞其他session插入与自己不同的记录,因为他们并没有任何冲突。

假设发生了一个唯一键冲突错误,那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时,如果另外一个session已经获得改行的排它锁,那么将会导致死锁。

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法,锁定的不是单个值,而是一个范围,按照这个方法是会和第一次测试结果一样。但是,当查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。

2.1.4 意向锁(Next-Key Lock)

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。为了解决这个问题,可以在表级上引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念。举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。事务B更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了。
说白了意向锁的主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”

2.1.5 锁选择

1)如果更新条件没有走索引,例如执行”update from t1 set v2=0 where v2=5;” ,此时会进行全表扫描,扫表的时候,要阻止其他任何的更新操作,所以上升为表锁。

2)如果更新条件为索引字段,但是并非唯一索引(包括主键索引),例如执行“update from t1 set v2=0 where v1=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因:

a)首先要保证在符合条件的记录上加上排他锁,会锁定当前非唯一索引和对应的主键索引的值;

b)还要保证锁定的区间不能插入新的数据。

3)如果更新条件为唯一索引,则使用Record Lock(记录锁)。

2.2.什么情况下会造成死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.
表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是:让不同的session加锁有次序

3.Mysql中发生死锁的问题分析(默认RR隔离级别)

默认情况下,InnoDB存储引擎不会回滚超时引发的异常,除死锁外。

3.1 案例一

一个session通过for循环会有几条如下的语句:

Select * from xxx where id=’随机id’ for update

基本来说,程序开启后不一会就死锁。

这可以是说最经典的死锁情形了。

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

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

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

3.2 案例二

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

展开代码
以id为主键为例,目前还没有id=2的行

Session1:

select * from test where id=2 for update;

Empty set (0.00 sec)


session2:

select * from test where id=3 for update;

Empty set (0.00 sec)


Session1:

insert into test values(2,’a’,’b’,now());

锁等待中……


Session2:

insert into test values(3,’b’,’c’,now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
select的where子句没有满足条件的记录,而对于不存在的记录 并且在RR级别下,select加锁类型为gap lock,gap lock之间是兼容的,所以两个事务都能成功执行select;关于gap lock可以参考文章加锁分析。

insert时,其加锁过程为先在插入间隙上获取插入意向锁,插入数据后再获取插入行上的排它锁。又插入意向锁与gap lock和 Next-key lock冲突,即一个事务想要获取插入意向锁,如果有其他事务已经加了gap lock或 Next-key lock,则会阻塞。

场景中两个事务都持有gap lock,然后又申请插入意向锁,此时都被阻塞,循环等待造成死锁。

\GapInsert IntensionRecordNext-Key
Gap兼容兼容兼容冲突
Insert Intension兼容冲突兼容冲突
Record兼容兼容冲突冲突
Next-Key兼容兼容冲突冲突

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

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

锁住的范围详细可见2.1.2

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

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

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

3.3 案例三

展开代码
mysql> select * from test where id=9 for update;

+—-+——–+——+———————+

| id | v1 | v2 | ctime |

+—-+——–+——+———————+

| 9 | a | b | 2018-08-05 11:36:30 |

+—-+——–+——+———————+

1 row in set (0.00 sec)


Session2:

mysql> select * from test where id<20 for update;

锁等待中


Session1:

mysql> insert into test values(7,’ae’,’af’,now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
这个跟案例一其它是差不多的情况,只是session1不按常理出牌了,

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值