MySQL的锁机制

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
锁类型XS
X不兼容不兼容
S不兼容兼容

  • 意向共享锁(IS Lock)
  • 意向排他锁(IX Lock)

意向锁针对的是表锁,当事务去申请记录R的X锁时,会同时去申请表的IX锁,S锁也一样。
即:事务A获得记录R的S锁,同时也获得了表的IS锁,事务B去申请表的IX锁时就会阻塞。

意向锁的目的:当事务去申请表锁时,无需扫描每一行记录有没有加锁,只需判断意向锁即可。

锁类型ISIXSX
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
1111
5555
10101010

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」隔离级别下才能防止「幻读」。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:C马雯娟 返回首页