MySQL事务隔离级别,锁(转)

时间:2022-09-17 23:14:05

add by zhj: 本文针对的是MySQL的InnoDB存储引擎,不适用于MySQL的其它存储引擎和其它数据库

 

原文:MySQL数据库事务隔离级别(Transaction Isolation Level)

 

1. 事务隔离级别

数据库隔离级别有四种,应用《高性能mysql》一书中的说明:

MySQL事务隔离级别,锁(转)

MySQL事务隔离级别,锁(转)

MySQL事务隔离级别,锁(转)

 

然后说说修改事务隔离级别的方法:

1.全局修改,修改mysql.ini配置文件,在最后加上

1 #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
2 [mysqld]
3 transaction-isolation = REPEATABLE-READ

这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

2.对当前session修改,在登录mysql客户端后,执行命令(以修改为read committed为例):set session transaction isolation level read committed;

MySQL事务隔离级别,锁(转)

 

要记住mysql有一个autocommit参数,默认是on,他的作用是每一条单独的查询都是一个事务,并且自动开始,自动提交(执行完以后就自动结束了,如果你要适用select for update,而不手动调用 start transaction,这个for update的行锁机制等于没用,因为行锁在自动提交后就释放了),所以事务隔离级别和锁机制即使你不显式调用start transaction,这种机制在单独的一条查询语句中也是适用的,分析锁的运作的时候一定要注意这一点

 

2. 锁

2.1 读锁和写锁

共享锁:Share Lock,又称为S锁,由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写

排他锁:Exclusive Lock,又称为X锁,所有写操作UPDATE/INSERT/DELETE一定会加排他锁,这与事务的隔离级别无关,其它事务不能对加了排他锁的记录再加任何锁。

共享锁与排它锁的相容性(add by zhj):

    1)一个事务内:这些锁都是相容的,已测试验证过。比如事务A给记录加了X锁之后,还可以加S锁,或多次加X锁。具体实现我不太清楚,我猜如果X锁与S

        锁相遇的话,那应该只会加X锁;X锁遇到X锁,那就加一次行了。

    2)不同事务之间:只有S锁与S锁是相容的。X锁与S锁,X锁与X锁是不相容的

相容性 S锁 X锁
S锁 True False
X锁 False False

CRUD语句与锁的对应关系(add by zhj):

    1) SELECT语句,分为下面三种情况 

SELECT语句 锁类型
SELECT  ...  FOR UPDATE X锁
SELECT  ...  LOCK IN SHARE MODE S锁
SELECT  ...  (除了上面两种情况)

不加锁。读取时既不会等待表/行上的锁释放,也不会给表/行加锁

    2)UPDATE/DELETE/INSERT语句

写操作 锁类型
UPDATE/INSERT/DELETE X锁

        加X锁,亲测。UPDATE和DELECT就不用说了,加X锁容易理解。对于INSERT语句,也会加写锁,不过应该是只对主键和unique键加写

锁,比如当在两个事务中,在同一张表中插入主键值相同的一条记录时,先插入记录的那个事务会给记录加上写锁,这样后面那个事务在执行插入语

句时会阻塞。如果前面那个事务成功提交,那后面这个事务解除阻塞,并报类似“ERROR 1062 (23000): Duplicate entry '15' for key 'PRIMARY'”

这样的错误;如果前面那个事务回滚了,那后面这个事务解除阻塞,并成功插入记录。关于INSERT时加写锁这点,在MySQL的repeatable read和

read committed两个隔离级别下都测试过了。

我们以MySQL来说明一下锁的相容性,这个例子适用于MySQL的REPEATED READ和READ COMMITTED隔离级别

     事务1

     start transaction;

     select * from user where userId = 1 for update;

     事务2

  start transaction;

  update user set userAge = 100 where userId = 1; 

  当执行事务1,然后执行事务2时,事务2会被挂起。因为事务1给userId=1的记录加了X锁,当事务2也试图加X锁时,block了。

2.2 悲观锁和乐观锁(add by zhj)

    前面说的排他锁和共享锁,那悲观锁和乐观锁又是从哪个角度对锁进行的分类呢? 我们可以想想版本控制工具Git的工作原理,它允许多人pull,

pull其实就是读,也允许他们在本地修改文件,但当push时,Git会检查本地版本与远程版本是否是同一版本,如果是,那可以push,如果不是,

那就报错。

    乐观并发控制:乐观锁其实并不是锁,它的真名叫"乐观并发控制"。它提供check-and-set (CAS)机制,即在事务内对记录的update/delete是

不加锁的,直到事务提交时才检查是否有冲突,Git就是用的乐观并发控制。*对乐观并发控制的定义是:“乐观并发控制假设多用户并发的事务

在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,

有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚”。

    乐观并发控制适用场景:与加锁机制相比,乐观并发控制省去了加锁的开销,所以性能更好。不过,如果在事务提交时有冲突,那要事务回滚,这就意味着

该事务的提交之前的执行和回滚是无用功,这是乐观并发控制有开销的地方。*中提到 “乐观并发控制多数用于数据争用不大、冲突较少的环境中,

这种环境中,偶尔回滚事务的成本会低于读取数据时锁定数据的成本,因此可以获得比其他并发控制方法更高的吞吐量”。在Yii2.0的乐观锁与悲观锁 一文

中也提到了类似的观点:“乐观锁(optimistic locking)表现出大胆、务实的态度。使用乐观锁的前提是, 实际应用当中,发生冲突的概率比较低”。但

是在并发写冲突的场景下,貌似也只有乐观并发控制这一种解决办法啊,悲观锁并不能解决更新丢失的问题,参见也谈乐观并发控制 一文。

    乐观锁的实现:在MySQL中,乐观锁要由开发人员自己在数据库表和应用程序中实现。一般来说给修改的字段加个版本号字段,建议用整数,而不要用时

间戳,因为系统产生的时间毕竟是有最小间隔的,在小于这个时间间隔内的修改就没法处理了。可参见也谈乐观并发控制

    悲观锁:其实就是前面我们提到的锁机制,主要指X锁。悲观锁在事务A中,给记录加了S锁或X锁后,其它事务不能再修改该记录,直到事务A提交或

回滚,写锁释放后,其它事务才可以修改该记录。它对应的SQL语句是"SELECT ... FOR UPDATE",悲观锁并不能解决更新丢失问题,MySQL本身并

不能解决更新丢失问题,这样的话,我不知道"SELECT ... FOR UPDATE"到底还有什么用,回后在问答社区问一下其他人吧。

    有人把乐观锁与MVCC看成是一回事,恩,两者其实真差不多,他们都用了版本号,只不过他们的具体作用不相同而已。参见知乎问题

乐观锁和MVCC的区别?中Feng Guangpu的回答:“MVCC是多版本并发控制,同一个数据有多个版本,事务开启时看到是哪个版本就看到这个版本,最

大的好处是读写不冲突,只有写于写是冲突的,这个特性可以很大程度上提升性能。而乐观锁就是另外一个东西,它是一个前提假设,遇到读写冲突需要

回退,在数据库的实现中,一般使用悲观锁,即:操作数据之前先拿到对应的锁”。我感觉MVCC可以实现普通读时不用加锁,但写时还是要加X锁的,MySQL

的InnoDB就是这么干的,即MVCC是与锁机制结合使用的;而乐观并发控制就根本没有锁,直到提交时才检查是否有冲突。区分嘛,就是MVCC要与锁机制结

合使用,而乐观并发控制不与锁一起使用。

2.3 锁粒度

行锁: 对某行记录加上锁

表锁: 对整个表加上锁

这样组合起来就有,行级共享锁,表级共享锁,行级排他锁,表级排他锁

2.4 MySQL锁算法(add by zhj)

这里只说一下InnoDB存储引擎的行锁算法,貌似行锁在InnoDB中使用的应该是最频繁的,参见《MySQL技术内幕:InnoDB存储引擎》6.3节 “锁的算法”,

有三种行锁的算法设计:

Record Lock:单个行记录上的锁。Record Lock总是会去锁住索引对应的行记录。

Gap Lock: 间隙锁,又称范围锁,锁定一个范围,但不包含记录本身。

Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身。

这三种算法都有共享锁和排他锁,在Repeatable Read隔离级别下,Next-Key Lock算法是默认的行锁算法,不过在一些情况下它会使用Record Lock。额,

不知道在 Read Commit隔离级别下,用的是什么行锁算法。关于Gap Lock和Next-Key Lock,以及什么情况下使用Record Lock,这些都非常复杂,有时

间可以看看《MySQL数据库开发、优化与管理维护》第20章 锁问题。Next-Key Lock有时真让人摸不着头脑,举个例子,我在事务A中删除where field=3

的记录,它可能会锁住field=[1,4]区间内的记录,在其它事务中不允许插入这个区间的记录。也就是说我查询条件是等于,但锁住的却不只是满足这些条件的

记录,还要包括他们周围的记录。这个让人不易理解

3. 事务操作SQL语句(add by zhj)

 开始:start transaction

 提交:commit

 回滚:rollback

下面来说说不同的事务隔离级别的实例效果,例子使用InnoDB,开启两个客户端A,B,在A中修改事务隔离级别,在B中开启事务并修改数据,然后在A中的事务查看B的事务修改效果:

4. 四种隔离级别的实例测试

1.READ-UNCOMMITTED(读取未提交内容)级别

  1)A修改事务级别并开始事务,对user表做一次查询

   MySQL事务隔离级别,锁(转)

 

  2)B更新一条记录

   MySQL事务隔离级别,锁(转)

 

  3)此时B事务还未提交,A在事务内做一次查询,发现查询结果已经改变

   MySQL事务隔离级别,锁(转)

 

  4)B进行事务回滚

   MySQL事务隔离级别,锁(转)

 

  5)A再做一次查询,查询结果又变回去了

   MySQL事务隔离级别,锁(转)

 

  6)A表对user表数据进行修改

   MySQL事务隔离级别,锁(转)

 

  7)B表重新开始事务后,对user表记录进行修改,修改被挂起,直至超时,但是对另一条数据的修改成功,说明A的修改对user表的数据行加行排他锁,由于select不加锁,所以即使这条记录加了排他锁,用select也是可以读取到的

   MySQL事务隔离级别,锁(转)

 

  可以看出READ-UNCOMMITTED隔离级别,当两个事务同时进行时,即使事务没有提交,所做的修改也会对事务内的查询做出影响,这种级别显然很不安全。但是在表对某行进行修改时,会对该行加上行排他锁

 

2. READ-COMMITTED(读取提交内容)

  1)设置A的事务隔离级别,并进入事务做一次查询

   MySQL事务隔离级别,锁(转)

 

  2)B开始事务,并对记录进行修改

   MySQL事务隔离级别,锁(转)

 

  3)A再对user表进行查询,发现记录没有受到影响

   MySQL事务隔离级别,锁(转)

 

  4)B提交事务

   MySQL事务隔离级别,锁(转)

 

  5)A再对user表查询,发现记录被修改

   MySQL事务隔离级别,锁(转)

 

  6)A对user表进行修改

   MySQL事务隔离级别,锁(转)

 

  7)B重新开始事务,并对user表同一条进行修改,发现修改被挂起,直到超时,但对另一条记录修改,却是成功

   MySQL事务隔离级别,锁(转)

   MySQL事务隔离级别,锁(转)

 

  READ-COMMITTED事务隔离级别,只有在事务提交后,才会对另一个事务产生影响

 

3. REPEATABLE-READ(可重读)

  1)A设置事务隔离级别,进入事务后查询一次

   MySQL事务隔离级别,锁(转)

 

  2)B开始事务,并对user表进行修改

   MySQL事务隔离级别,锁(转)

 

  3)A查看user表数据,数据未发生改变

   MySQL事务隔离级别,锁(转)

 

  4)B提交事务

   MySQL事务隔离级别,锁(转)

 

  5)A再进行一次查询,结果还是没有变化

   MySQL事务隔离级别,锁(转)

 

  6)A提交事务后,再查看结果,结果已经更新

   MySQL事务隔离级别,锁(转)

 

  7)A重新开始事务,并对user表进行修改

   MySQL事务隔离级别,锁(转)

   

  8)B表重新开始事务,并对user表进行修改,修改被挂起,直到超时,对另一条记录修改却成功

   MySQL事务隔离级别,锁(转)

   MySQL事务隔离级别,锁(转)

 

  REPEATABLE-READ事务隔离级别,当两个事务同时进行时,其中一个事务修改数据对另一个事务不会造成影响,即使修改的事务已经提交也不会对另一个事务造成影响。

 

 

4.SERIERLIZED(可串行化)

  1)修改A的事务隔离级别,并作一次查询

   MySQL事务隔离级别,锁(转)

 

  2)B对表进行查询,正常得出结果,可知对user表的查询是可以进行的

   MySQL事务隔离级别,锁(转)

 

  3)B开始事务,并对记录做修改,因为A事务未提交,所以B的修改处于等待状态,等待A事务结束,最后超时

   MySQL事务隔离级别,锁(转)

 

  SERIALIZABLE事务隔离级别最严厉,在进行查询时就会对表或行加上共享锁,其他事务对该表将只能进行读操作,而不能进行写操作。