MySQL的事务和锁（转）

最新推荐文章于 2022-04-03 14:43:47 发布

agoodchild

最新推荐文章于 2022-04-03 14:43:47 发布

阅读量203

点赞数 1

分类专栏：数据库

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

转自：https://www.cnblogs.com/maying3010/p/8804941.html

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, +∞)的间隔锁，其统称为防插入锁。