1 mysql中的锁类型:
1) 表锁
表共享锁(S
):表级别的读锁,表共享锁之间是兼容的。
表排他锁(X
): 表级别的写锁,表排他锁和任何锁(包括表排他锁)都不兼容(不包括意向锁)。
意向排他锁(IX
): 获取行排他锁之前必须获取的意向排他锁,这个锁是用来快速指示当前表中是否存在行排他锁,而不用去表中遍历判断每行数据是否有行锁。
意向共享锁(IS
): 获取行共享锁之前必须获取得意向共享锁,这个锁是用来快速指示当表中前是否存在行共享锁,而不用在表中遍历每行数据判断当前行是否有行排他锁。
2) 行锁
行锁(R
)主要是针对唯一索引(包括主键),行锁也分行共享锁(S
)、行排他锁(X
)。
3) 间隙锁
间隙锁(GAP
):指锁定一个范围区间,主要用来接解决幻读问题。
插入意向锁(INSERT_INTENTION
):它不是意向锁,意向锁是表锁,它是一种特殊的间隙锁,在数据插入的时候需要先获取插入意向锁。
4) 临键锁
临键锁(NEXT-KEY
) : 它可以看成一种组合锁,相当于行锁+间隙锁
。普通的索引列(非唯一索引)就是加临键锁。间隙锁是用来锁住一个区间的,防止这个区间内插入其他数据,普通索引是可以重复的,需要锁住自身,所以还需要行锁,而唯一索引本身是有唯一性的,不能插入重复数据,只需要锁住间隙就可以了,不需要锁住自己本身。
2 具体验证
现在有一个表stu,其中的age字段有索引,现在针对age字段做锁实验。stu表中目前有如下这些数据,( 注意:下面sql实验的隔离级别都是’可重复读’,且开启了自动提交事务,每次写数据后都要恢复成下面的初始化数据, )。
CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age_index` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
select * from stu;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | NULL | 10 |
| 3 | 23 | 24 |
| 6 | NULL | 32 |
| 7 | 23 | 45 |
+----+------+------+
1) 间隙锁with间隙锁
间隙锁和间隙锁之间是兼容的,即可以同时同一个区间加锁,不会有锁竞争。
先在会话1中开启一个事务,更新age=24
的记录,此时会获取到[10,32)
这个区间的插入间隙锁,此时先不提交事务,然后在新会话2中尝试更新[10,32)
区间的一条数据age=25
的记录,此时会话2中sql脚本执行正常(尽管此数据不存在)、没有被阻塞。
// 会话1
COMMIT;
update stu set `name` ='hi' WHERE age=24
//会话2
update stu set `name` ='hi' WHERE age=25
2) 插入意向锁with插入意向锁
插入意向锁和插入意向锁(也是一种间隙锁)之间是兼容的,只要不是唯一索引(包括主键)冲突,可以直接插入,不会有锁竞争。
先在会话1中开启一个事务,插入一条age=15
的记录,此时会获取到[10,24)
这个区间的插入意向锁,此时先不提交事务,然后在新会话2中尝试插入[10,24)
区间的一条数据age=16
的记录,此时会话2中数据插入成功、没有被阻塞。
//会话1
begin;
INSERT stu (`name`,age) values ('he',15);
//会话2
INSERT stu (`name`,age) values ('he',16);
3) 间隙锁with插入意向锁
间隙锁和插入意向锁之间有锁竞争,但只有在先有间隙锁再申请插入意向锁时才会不兼容,有锁竞争;反之在先有插入意向锁再申请间隙锁是不会有锁竞争的,是兼容的。
(1)先在会话1中开启一个事务,删除记录age=25
的记录(尽管此数据不存在),此时会获取到[32,45)
这个区间的间隙锁,此时先不提交事务,然后在新会话2中尝试插入[32,45)
区间的一条数据age=36
的记录,此时会话2中数据一直被阻塞,直到会话1的事务提交。这充分证明了先有间隙锁再申请插入意向锁,锁不兼容
.
//会话1
BEGIN;
DELETE from stu WHERE age=35;
//会话2
INSERT stu (`name`,age) values ('he',36); //被阻塞
(2) 其他很多技术贴都说间隙锁的锁区间是左开右闭,但我实验的结果是左闭右开。
先在会话1中开启一个事务,删除记录age=25
的记录(尽管此数据不存在),此时会获取到[32,45)
这个区间的间隙锁,此时先不提交事务,然后在新会话2中尝试插入[32,45)
区间的一条数据age=32
的记录,此时插入失败、被阻塞,然而在新会话3尝试插入另一条数据age=45
的记录则成功插入、没被阻塞。按照那些技术贴的说法,应该age=45
的数据插入失败被阻塞,age=32
的数据插入成功,但事实却不是如此。
//会话1
BEGIN;
DELETE from stu WHERE age=35;
//会话2
INSERT stu (`name`,age) values ('he',32); //被阻塞
//会话3
INSERT stu (`name`,age) values ('he',45); //成功插入
(3)先在会话1中开启一个事务,插入一条记录age=27
的记录,此时会获取到[25,32)
这个区间的插入意向锁,此时先不提交事务,然后在新会话2中尝试在[25,32)
区间更新的一条数据age=29
的记录(尽管此数据不存在),此时会话2中sql脚本正常返回、没有被阻塞。这充分证明了先有插入意向锁再申请间隙锁,锁兼容
.
//会话1
BEGIN;
INSERT stu (`name`,age) values ('he',32);
//会话2
UPDATE stu set `name`='haha' WHERE age=29;//正常返回、不阻塞
跟undo log相关的支持,undo log 分TRX_UNDO_INSERT 和TRX_UNDO_UPDATE 。TRX_UNDO_INSERT在事务提交后,可以直接删除,因为他只在自己的事务中可见,而TRX_UNDO_UPDATE在事务提交后,还不能直接删除,它可能会被mvvc使用到。
update 和delete
update 时如果更新条件是二级索引(包括唯一索引),先获取这个二级索引的锁,再获取对应主键的行锁。
delete时需要获取对应行上的所有索引列的的锁
3 mvvc
网上对mvvc的解释太复杂了,我这里简单说下的行为结果。
mvvc中读提交
、可重复读
这两种隔离级别中有不同的行为。
读提交
:每次的读都是当前读,即每次都读当前最新的已提交数据。
可重复
:可重复读是快照读,第一次读和当前读一样,读此时的最新的已提交数据,不同点在于之后的第2~n
次读。第一次之后的第2~n
次读出的数据不会再变化,这时读出的数据是第一次读的快照,它和第一次读的数据始终是一样的,不再关心第一次读之后的已提交数据。每次读数是一样的,这就是名副其实的可重复读
。
在可重复读隔离级别下, 普通的select
语句是快照读,而update
、delete
、insert
(如果把update delete insert判断是否能写数据的过程看成读的话) 、select (for update)/(lock on share mode)
都是当前读。