MySQL服务端是允许多个客户端连接的,服务端允许客户端并发的对数据进行CRUD操作,以提升数据库整体的并发访问性能。
但是,为了保证并发访问数据的一致性和完整性,MySQL服务端内部有它特有的锁机制。
MySQL支持插件式的存储引擎,不同的存储引擎内部锁实现也大不相同。
例如:MyISAM只支持表锁,而InnoDB则支持更细粒度的行锁和间隙锁。
1、InnoDB的锁机制
InnoDB相较于其他存储引擎,锁机制较为完善,MVCC多版本并发控制可以最大程度的减少使用锁而带来更好的并发性能。
1.1、锁的类型
- 共享锁(S Lock、读锁)
- 排他锁(X Lock、写锁)
只有共享锁和共享锁才兼容,其他均不兼容。
即:对于一行记录R,事务A获得R的S锁时,事务B可以继续获得R的S锁,但是获得R的X锁则会阻塞。事务A获得R的X锁时,事务B不管获得S锁还是X锁均会阻塞。
加共享锁:
SELECT ... LOCK IN SHARE MODE
加排他锁:
SELECT ... FOR UPDATE
锁类型 | X | S |
---|---|---|
X | 不兼容 | 不兼容 |
S | 不兼容 | 兼容 |
- 意向共享锁(IS Lock)
- 意向排他锁(IX Lock)
意向锁针对的是表锁,当事务去申请记录R的X锁时,会同时去申请表的IX锁,S锁也一样。
即:事务A获得记录R的S锁,同时也获得了表的IS锁,事务B去申请表的IX锁时就会阻塞。
意向锁的目的:当事务去申请表锁时,无需扫描每一行记录有没有加锁,只需判断意向锁即可。
锁类型 | IS | IX | S | X |
---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
1.2、锁的监测
在MySQL自带的数据库information_schema
下有三张表:
- INNODB_TRX(事务情况)
- INNODB_LOCKS(加锁情况)
- INNODB_LOCK_WAITS(锁等待的情况)
通过这三张表可以了解当前MySQL内部锁的一个争用情况。
当新开启一个事务,且事务没有结束之前,INNODB_TRX会记录当前事务的一个状态信息。
当事务A和事务B都去对记录R加X锁时,INNODB_LOCKS会包含两条加锁记录。
由于X锁的排他性,只有一个事务会加锁成功,其中一个事务会阻塞,此时INNODB_LOCK_WAITS会记录一条锁的等待信息。
通过查询这三张表,可以清晰的了解当前MySQL内部的一个加锁情况,以及事务的阻塞情况。
如果项目运行卡住了,且日志显示是MySQL这边发生了阻塞,那么首先应该排查是否事务异常。
1.3、一致性非锁定读
普通的Select查询操作不会加任何锁,即非阻塞,InnoDB通过MVCC多版本并发控制来实现。
即:事务A查询记录R时,如果此时事务B在对记录R进行写操作,事务A不用等待事务B写完锁释放后才能查询到数据,而是InnoDB会去读取记录R的一个快照数据。
“非锁定读”大大的提高了数据库的并发性能,需要注意的是:不同的事务隔离级别下,读到的快照版本不同。
对于记录R,可能存在多个快照版本,在“READ COMMITTED”隔离级别下,读到的总是最新的一份快照。而在“REPEATABLE READ”隔离级别下,读到的总是事务开始时的快照版本。
2、行锁的三种算法
InnoDB存储引擎有三种行锁算法。
现有数据库表t,含4列,结构数据如下:
a(主键) | b(唯一索引) | c(普通索引) | d |
---|---|---|---|
1 | 1 | 1 | 1 |
5 | 5 | 5 | 5 |
10 | 10 | 10 | 10 |
2.1、行锁(Record Lock)
临键锁(Next-Key Lock)是InnoDB默认的加锁算法,但是当使用主键或唯一索引时,InnoDB只会对涉及的数据行加锁,即「临键锁」降级为「行锁」。
如下,由于a为主键,InnoDB只会锁住【1、5、10】数据行,并不会对间隙加锁,效率是最高的。
将列a换成列b也是同样的效果,因为b为唯一索引,也不会加间隙锁。
-- 事务A
begin;
select * from t where a > 0 for update;
-- 事务B
begin;
SELECT * FROM t WHERE a = 2 FOR UPDATE;-- 不会阻塞
SELECT * FROM t WHERE a = 1 FOR UPDATE;-- 会阻塞
2.2、间隙锁(Gap Lock)
当使用范围查询而不是等值查询时,InnoDB会给符合范围的间隙加锁。
-- 事务A
begin;
select * from t where a between 1 and 5 for update;
-- 事务B
begin;
insert into t values (2,2,2,2);-- 会阻塞
2.3、临键锁(Next-Key Lock)
临键锁(Next-Key Lock)是InnoDB默认的加锁算法,结合了Record Lock和Gap Lock,锁定一个范围并且包含记录本身。
对于上表的数据,可能被锁的区间为:
- (负无穷、1]
- (1、5]
- (5、10]
- (10、正无穷]
临键锁针对的是普通索引,主键或唯一索引会降级为Record Lock,并不会锁住范围。
-- 事务A
begin;
select * from t where c = 5 for update;
-- 事务B
begin;
insert into t values (4,4,4,4);-- 会阻塞
insert into t values (6,6,6,6);-- 会阻塞
insert into t values (11,11,11,11);-- 不阻塞
对于上述SQL,由于列C不是主键和唯一索引,因此innodb采用临键锁算法,锁住(1、5]区间,需要特别注意的是:InnoDB还会对下一个键加上Gap Lock,即(5、10)。
所以插入4和6均会阻塞,而11就不会阻塞了。
间隙锁和临键锁的加入,主要是为了解决「幻读」,InnoDB不同于其他数据库,在「REPEATABLE READ」事务隔离级别下就可以防止「幻读」,其他数据库可能需要在「SERIALIZABLE」隔离级别下才能防止「幻读」。