设置事务时,不加上范围,那么仅影响一次事务。
1.SET TRANSACTION ....影响一次事务
2.SET SESSION TRANSACTION ... SESSION,影响范围为当前会话
3.SET GLOBAL TRANSACTION ... GLOBAL影响范围为当前全局
lock_type:行锁和表锁。
lock_mode:读锁和写锁,如下:
- LOCK_IS:读意向锁;
- LOCK_IX:写意向锁;
- LOCK_S:读锁;
- LOCK_X:写锁;
- LOCK_AUTO_INC:自增锁;
行锁的种类:
- LOCK_ORDINARY:也称为 Next-Key Lock,锁一条记录及其之前的间隙,这是 RR 隔离级别用的最多的锁,从名字也能看出来;
- LOCK_GAP:间隙锁,锁两个记录之间的 GAP,防止记录插入;
- LOCK_REC_NOT_GAP:只锁记录;
- LOCK_INSERT_INTENSION:插入意向 GAP 锁,插入记录时使用,是 LOCK_GAP 的一种特例。
读写意向锁:
表锁锁定了整张表,而行锁是锁定表中的某条记录,它们俩锁定的范围有交集,因此表锁和行锁之间是有冲突的。譬如某个表有 10000 条记录,其中有一条记录加了 X 锁,如果这个时候系统需要对该表加表锁,为了判断是否能加这个表锁,系统需要遍历表中的所有 10000 条记录,看看是不是某条记录被加锁,如果有锁,则不允许加表锁,显然这是很低效的一种方法,为了方便检测表锁和行锁的冲突,从而引入了意向锁。
意向锁为表级锁,也可分为读意向锁(IS 锁)和写意向锁(IX 锁)。当事务试图读或写某一条记录时,会先在表上加上意向锁,然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了,只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的,也不和 AUTO_INC 表锁冲突,它只会阻塞表级读锁或表级写锁,另外,意向锁也不会和行锁冲突,行锁只会和行锁冲突。
锁之间是否冲突:
- 意向锁之间互不冲突;
- S 锁只和 S/IS 锁兼容,和其他锁都冲突;
- X 锁和其他所有锁都冲突;
- AI 锁只和意向锁兼容;
Next-Key Locks
只使用唯一索引查询,但是检索条件是范围检索,或者是唯一检索然而检索结果不存在(试图锁住不存在的数据)时,会产生Next-Key Lock。RR
Next-key 锁 是记录锁和间隙锁的组合,它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含
10、11、13 和 20 这几个值,可能的 Next-key 锁如下:
- (-∞, 10]
- (10, 11]
- (11, 13]
- (13, 20]
- (20, +∞)
通常我们都用这种左开右闭区间来表示 Next-key 锁,其中,圆括号表示不包含该记录,方括号表示包含该记录。前面四个都是 Next-key 锁,最后一个为间隙锁。和间隙锁一样,在 RC 隔离级别下没有 Next-key 锁,只有 RR 隔离级别才有。继续拿上面的 SQL 例子来说,如果 id 不是主键,而是二级索引,且不是唯一索引,那么这个 SQL 在 RR 隔离级别下会加什么锁呢?答案就是 Next-key 锁,如下:
- (a, 5]
- (5, b)
其中,a 和 b 是 id = 5 前后两个索引,我们假设 a = 1、b = 10,那么此时如果插入一条 id = 3 的记录将会阻塞住。之所以要把 id = 5 前后的间隙都锁住,仍然是为了解决幻读问题,因为 id 是非唯一索引,所以 id = 5 可能会有多条记录,为了防止再插入一条 id = 5 的记录,必须将下面标记 ^ 的位置都锁住,因为这些位置都可能再插入一条 id = 5 的记录:
1 ^ 5 ^ 5 ^ 5 ^ 10 11 13 15
可以看出来,Next-key 锁确实可以避免幻读,但是带来的副作用是连插入 id = 3 这样的记录也被阻塞了,这根本就不会引起幻读问题的。
关于 Next-key 锁,有一个比较有意思的问题,比如下面这个 orders 表(id 为主键,order_id 为二级非唯一索引):
1 2 3 4 5 6 7 8 9 |
|
事务 A 执行下面的 SQL:
1 2 3 4 5 6 7 8 9 |
|
这个时候不仅 order_id = 5 这条记录会加上 X 记录锁,而且这条记录前后的间隙也会加上锁,加锁位置如下:
1 2 ^ 5 ^ 5 ^ 9
可以看到 (2, 9) 这个区间都被锁住了,这个时候如果插入 order_id = 4 或者 order_id = 8 这样的记录肯定会被阻塞,这没什么问题,那么现在问题来了,如果插入一条记录 order_id = 2 或者 order_id = 9 会被阻塞吗?答案是可能阻塞,也可能不阻塞,这取决于插入记录主键的值,感兴趣的读者可以参考这篇博客。
插入意向锁
插入意向锁 是一种特殊的间隙锁(所以有的地方把它简写成 II GAP),这个锁表示插入的意向,只有在 INSERT 的时候才会有这个锁。注意,这个锁虽然也叫意向锁,但是和上面介绍的表级意向锁是两个完全不同的概念,不要搞混淆了。插入意向锁和插入意向锁之间互不冲突,所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中,id = 1 和 id = 5 之间如果有两个事务要同时分别插入 id = 2 和 id = 3 是没问题的,虽然两个事务都会在 id = 1 和 id = 5 之间加上插入意向锁,但是不会冲突。
插入意向锁只会和间隙锁或 Next-key 锁冲突,正如上面所说,间隙锁唯一的作用就是防止其他事务插入记录造成幻读,那么间隙锁是如何防止幻读的呢?正是由于在执行 INSERT 语句时需要加插入意向锁,而插入意向锁和间隙锁冲突,从而阻止了插入操作的执行。