READ UNCOMMITTED
隔离级别下,不加锁,直接读取记录的最新版本,可能发生脏读、不可重复读和幻读问题。READ COMMITTED
隔离级别下,不加锁,在每次执行普通的SELECT语句时都会生成一个ReadView,这样解决了脏读问题,但没有解决不可重复读和幻读问题REPEATABLE READ
隔离级别下,不加锁,只在第一次执行普通的SELECT语句时生成一个ReadView,这样把脏读、不可重复读和幻读问题都解决了。其实并不能完全解决幻读问题, 这里可以参考另一篇博客[mysql事务隔离级别与加锁分析]autocommit=0
时,也就是禁用自动提交时,普通的SELECT语句会被转为SELECT … LOCK IN SHARE MODE这样的语句,也就是在读取记录前需要先获得记录的S锁,具体的加锁情况和REPEATABLE READ隔离级别下一样autocommit=1
时,也就是启用自动提交时,普通的SELECT语句并不加锁,只是利用MVCC来生成一个ReadView去读取记录。 为啥不加锁呢?因为启用自动提交意味着一个事务中只包含一条语句,一条语句也就没有啥不可重复读、幻读这样的问题了。RU/RC 情况下加锁情况基本一致, 在加锁情况下脏读
和不可重复读
在任何一个隔离级别下都不会发生(因为读-写操作需要排队进行)
SELECT ... LOCK IN SHARE MODE
为记录 + XLock
-- number 是主键(聚簇索引)
SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;
这条语句只需要访问 number 为8的记录,只给这个记录加普通的S锁
使用SELECT ... FOR UPDATE
来为记录 + XLock
SELECT * FROM hero WHERE number = 8 FOR UPDATE;
这种情况也只访问 number 为 8 的记录,只给这个记录加普通的X锁
使用UPDATE ...
来为记录加锁,分两个情况,比方说:
如果没有对二级索引进行更新,跟Select .. for update
一样, 只对聚簇索引 + XLock
如果对二级索引进行了更新, 不但对聚簇索引 + XLock, 还需要给对应二级索引 + XLock
-- country 列上无索引, 只锁定聚簇索引记录
UPDATE hero SET country = '汉' WHERE number = 8;
-- name 列上是二级索引, 还需要锁定对应二级索引
UPDATE hero SET name = 'cao曹操' WHERE number = 8;
DELETE ...
来为记录加锁, 与 update 一样SELECT ... LOCK IN SHARE MODE
来为记录加锁,满足条件每条记录 + SLock, 比方说:
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;
SELECT ... FOR UPDATE
语句。与上面过程类似,不过加的是X锁UPDATE ...
来为记录加锁。这里还是分是否有更新二级索引的情况,如果不更新就只往符合条件的聚簇索引加锁DELETE ...来为记录
加锁, 与UPDATE一样SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
, 与上面类似, 不过换成 XLockUPDATE ...
, 与上面类似, 不过如果被更新的列中有别的二级索引列话, 对应二级索引列也 + XLockDELETE ...
, 与 update 一样SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
与上面类似, 不过 +XLockUPDATE ...
, 与 for update 类似, 如果有其他二级索引列被更新, 对应二级索引也 +XLockDELETE ...
与 update 类似SELECT ... LOCK IN SHARE MODE
, 比如说
SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;
SELECT ... FOR UPDATE
进行加锁的情况与上边类似,只不过加的是+ XLockDELETE/UPDATE
在遍历聚簇索引中的记录,都会为该聚簇索引记录加上X型正经记录锁,然后:REPEATABLE READ
隔离级别与READ UNCOMMITTED
和READ COMMITTED
这两个隔离级别相比,最主要的就是要解决幻读问题,幻读问题的解决还得靠gap锁(间隙锁)。
SELECT ... LOCK IN SHARE MODE
来为记录加锁,若命中记录,由于主键具有唯一性,这种情况只需要为满足条件的记录+ SLock 就好了,并不会产生幻读,不可重复读的情况
特别注意的是在查询主键值不存在的情况,比如:
-- 数据库没有 number=7 这条记录
SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;
由于number值为7的记录不存在,为了禁止幻读现象(也就是避免在同一事务中下一次执行相同语句时得到的结果集中包含number值为7的记录),在当前事务提交前我们需要预防别的事务插入number值为7的新记录,所以需要在number值为7的下一条记录记录上加一个gap锁(假设是number=8是下一条记录,number=3是上一条记录,就是在(3,8)之间加上间隙锁)
READ UNCOMMITTED/READ COMMITTED
是一样的使用SELECT ... LOCK IN SHARE MODE
语句来为记录加锁,比方:
SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;
因为要解决幻读问题,所以需要禁止别的事务插入number值符合number >= 8的记录,又因为主键本身就是唯一的,所以我们不用担心在number值为8的前边有新记录插入,只需要保证不要让新记录插入到number值为8的后边就好了,所以:
使用SELECT ... FOR UPDATE
语句,与上面基本一样,只不过需要将上边提到的S型 next-key Lock 替换成X型 next-key Lock
使用UPDATE ...
来为记录加锁,也要区分是否更新二级索引的场景:
-- country 没有索引
UPDATE hero SET country = '汉' WHERE number >= 8;
-- name 是二级索引
UPDATE hero SET name = 'cao曹操' WHERE number >= 8;
SELECT..FOR UPDATE
一样SELECT..FOR UPDATE
一样,然后再给对应的二级索引加上普通X锁DELETE ...
来为记录加锁的情况和UPDATE一致使用SELECT ... LOCK IN SHARE MODE
语句来为记录加锁,比方说:
-- 这里给 name 列是唯一二级索引
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
SELECT ... FOR UPDATE
语句来为记录加锁,这里和上面过程一样,不过这里加的是 XLockUPDATE ...
来为记录加锁,这里与SELECT .. FOR UPDATE
类似,不过如果更新的列还有别的二级索引,对应的二级索引也要加锁DELETE ...
与上面 update 一样SELECT ... LOCK IN SHARE MODE
语句来为记录加锁SELECT ... FOR UPDATE
和上面基本类似,不过加的是X型 next-key Lock 和 XLockUPDATE ...
的情况, 这里其实跟上面SELECT .. for UPDATE
基本一样,只不过如果更新了其他的二级索引,需要对其他的二级索引对应加锁。DELETE
和 上面 update情况一样SELECT ... LOCK IN SHARE MODE
语句,比如:
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
与唯一二级索引类似
这里需要说明一下,再REPEATABLE READ
隔离级别下,如果是全表扫描的方式,表里面的所有记录都会被加上next-key
锁,直到事务提交才释放。
这样会极大的影响该表的并发事务处理能力,如果遇到这个情况,还是尽量对表建立合适的索引
另外可以通过设置innodb_locks_unsafe_for_binlog
来提前释放锁
INSERT语句一般情况下不加锁,不过当前事务在插入一条记录前需要先定位到该记录在B+树中的位置,如果该位置的下一条记录已经被加了gap锁(next-key锁也包含gap锁),那么当前事务会在该记录上加上一种类型为插入意向锁的锁,并且事务进入等待状态。 INSERT 在执行成功后会给记录+X Lock
这里有一些 insert 语句的特殊情况
在插入一条新记录时,首先要做的事情其实是定位到这条新记录应该插入到B+树的哪个位置。如果定位位置时发现了有已存在记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列相同,那么此时此时是会报错.在生成报错信息前,其实还需要做一件非常重要的事情 —— 对聚簇索引中那条记录加S锁。不过具体的行锁类型在不同隔离级别下是不一样的:
外键的情况也是特殊处理,再插入子表的时候,也要对相应关联的父表做一些加锁的操作
前提条件:
这样就其中有一个将返回死锁信息
过程: