Mysql事务和锁

Mysql事务

这里主要讲的是Mysql InnoDB引擎相关事务和锁。Mysql事务主要和上诉数据库理论中类似,有所不同的是在事务隔离级别的Repeatable Read(可重复读)、和锁有着不同的实现。

事务隔离级别

Mysql同样有4种事务隔离级别,其中Repeatable Read(可重复读)是Mysql默认隔离级别,其通过MVCC机制实现了不会出现幻读现象。

隔离级别 脏读 不可重复读 幻读
Read Uncommitted 可能 可能 可能
Read Committed 不能 可能 可能
Repeatable Read 不能 不能 不能
Serializable 不能 不能 不能

Mysql可以通过下诉API操作事务隔离级别:

查看系统隔离级别:
select @@global.tx_isolation;
查看当前会话隔离级别
select @@tx_isolation;
设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;
设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Mysql锁

Mysql InnoDB引擎有悲观锁、乐观锁(MVCC),其共同组成Mysql相关锁机制,并且Mysql通过其使用Repeatable Read能够防止幻读的出现。

Mysql悲观锁

Mysql主要有下面4种悲观锁:

  • 共享锁(S锁、多锁): 事务获得元组的共享锁后,其它事务也只能获得该元组的共享锁,而不能获得排它锁;获得共享锁的事务可以对元组进行读操作。
  • 排它锁(X锁,写锁): 事务获得元组的排它锁后,其它事务既不能获得该元组的共享锁,也不能获得排它锁;获得排它锁的事务可以对元组进行写操作。
  • 意向共享锁(IS锁): 事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排它锁(IX锁): 事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

IS、S、IX、X锁的兼容性为:

图片来自网络

Mysql InnoDB引擎默认支持行锁,来尽量缩小锁定元组的粒度,行锁分为三级,粒度从小到大依次是:

  • 记录锁(Record Lock):单行
  • 间隔锁(Gap Lock):一个开区间内的多行
  • 防插入锁(Next-Key Lock):一个前开后闭区间内的多行,实际上是记录锁和间隔锁的结合

Mysql乐观锁(MVCC)

前面所说,乐观锁机制通过在表中添加version字段进行实现。在Mysql InnoDB引擎中,其会在每行数据中额外添加两个隐藏值来实现MVCC,这两个值一个记录这行数据何时被创建,另外一个记录这行数据何时更新(或者被删除)。在实际操作中,每开启一个新事务,事务的版本号就会递增。

Mysql事务隔离级别和其对应锁级别

Read Uncommited

任何操作都不加锁

Read Commited

操作 锁对应级别
select * 无锁
select * for update 排它锁
select * lock in share mode 共享锁
insert 排它锁,行锁中的记录锁
delete 排它锁,行锁中的记录锁
update 排它锁,行锁中的记录锁

Repeated Read

操作 锁对应级别
select * 乐观锁(MVCC机制),也叫快照读(snapshot read)
select * for update 排它锁
select * lock in share mode 共享锁
insert 排它锁,行锁中的记录锁
delete 排它锁,行锁中的防插入锁
update 排它锁,行锁中的防插入锁

Serializable

该隔离级别下,读(select)下会加共享锁,相当于select lock in share mode,写会加排他锁,读写互斥,相当于全部使用悲观锁实现事务隔离级别。

Mysql解决Repeatable Read事务隔离级别的可重复读和幻读问题

Mysql在Repeatable Read事务隔离级别下,通过MVCC机制、update和delete操作的防插入锁解决可重复读和幻读问题。

可重复读问题

这里主要由Mysql的MVCC机制所解决的。mysql会在每行数据中额外添加何时被创建何时被更新两个版本号字段,在RP级别下,每个操作对应得MVCC版本号字段的操作:

  • select: 读取创建版本号<=当前版本号,更新版本号为空或<=当前版本号
  • insert: 保存当前事务版本号为行的创建版本号
  • update: 保存当前事务版本号为行创建版本号,同时保存当前事务版本号到行更新版本号
  • delete: 保存当前事务版本号为行的更新版本号

所以我们由上可知在事务A在select操作时,事务B的update和delete操作并不会影响事务A的select操作。

幻读问题

幻读问题我们主要是由于insert操作导致,所以这里面主要涉及到select时有insert操作和update(delete)时有insert操作,下面主要讲这两种Mysql InnoDB引擎是如何解决的。

select时的insert操作

由上所诉解决可重复读问题,该操作已由Mysql的MVCC机制所解决

update和delete时的insert操作

该操作主要由Mysql InnoDB引擎的防插入锁(Next-Key Lock)解决的。防插入锁的区间是根据索引来确定的。对于没有索引的列会直接锁表。下面是一个例子:

--------------------------------------------------------------------------------------------------
事务A                                              |                       事务B
--------------------------------------------------------------------------------------------------
begin                                              |                       begin
--------------------------------------------------------------------------------------------------
update isolation set name = '-2' where name = '2'; |
--------------------------------------------------------------------------------------------------
                                                   |      insert into isolation(name) values('2');
                                                   |      // 这里会阻塞等待
--------------------------------------------------------------------------------------------------------
commit                                             |                       
--------------------------------------------------------------------------------------------------------

注意:上面事务A的update操作会造成name='2'的行锁和name=(-∞, 2]和[2, +∞)的间隔锁,其统称为防插入锁。

Reference

https://tech.meituan.com/innodb-lock.html
http://chenzhou123520.iteye.com/blog/1860954
https://blog.csdn.net/sadfishsc/article/details/51027734

posted @ 2018-04-12 09:03  默默的看雨下  阅读(6857)  评论(0编辑  收藏  举报