MySQL锁机制

发布时间:2023年12月18日

?MySQL的锁机制用于管理事务对共享资源的并发访问,实现事务的隔离级别。

MySQL的锁比较多,下面我们按照四个维度来介绍相关的锁。

图 MySQL 锁的分类

1 加锁机制

悲观锁

操作数据时,认为其他线程也会对该数据进行更改。于是在获取数据时会先加锁,其他线程会被阻塞直到拿到锁。(先加锁后访问)

乐观锁

操作数据时,认为其他线程不会对数据进行操作。不对数据进行上锁,但是在提交更新时会判断其他线程是否在同时更新或已更新,如果是,则继续等待更新或抛出异常。

表 悲观锁与乐观锁

1.1 悲观锁

悲观锁的实现原理为:

1)在对记录进行修改前,先尝试为该记录加上排他锁;

2)如果加锁失败,则等待或抛出异常(用户决定);

3)如果加锁成功,则可对记录进行修改,事务完成才会解锁。

4)期间如果有其他事务对该记录做加锁操作,则需要等待当前事务解锁。

优点:

1)更新失败的概率比较低。

缺点:

  1. 依赖数据库;
  2. 效率比较低;

1.1.1 悲观锁的使用

需求描述:模拟商场系统下单过程,先判断购买数量是否大于库存,是则可以购买,用户完成订单信息填写及付款后,下单成功,库存数量更新,等于现库存减去购买数量。

建立个存储过程来实现上述操作,下面是没有使用悲观锁的代码:

DROP PROCEDURE IF EXISTS buyIPhone;
CREATE PROCEDURE buyIPhone(IN p_count INT)
BEGIN
DECLARE d_stock INT DEFAULT 0;
START TRANSACTION;
SELECT stock INTO d_stock FROM goods WHERE id = 1; -- 初始库存 stock = 10
SELECT SLEEP(10); -- 提交订单等耗时操作
IF d_stock >= p_count THEN
? UPDATE goods SET stock = stock - p_count WHERE id = 1; -- 下单成功,商品减去库存
ELSE?
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT="库存不足";
END IF;
COMMIT;
END;

然后在两个线程中同时调用:CALL buyIPhone(6);

最后结果是,两个都调用成功,但是库存变成了-2。

然后,我们使用悲观锁来改写上面的代码。

图 使用悲观锁后的代码

然后在两个线程中同时调用:CALL buyIPhone(6);

最后的结果是,有一个调用成功,另一个报错:库存不足。商品的库存数量为4。

在上面的代码中,我们使用了“SELECT...FOR UPDATE”语句,这个语句的作用是申请排他锁。注意:在申请排他锁时,如果其他线程有对该结果集中的任何数据使用排他锁或共享锁,那么这个线程会被阻塞。

另外,InnoDB默认是行级锁,但是如果没有指定主键(或索引),那么会把整张表都锁住。

1.2 乐观锁

乐观锁实际上是一种无锁机制。CAS是一种乐观锁机制,全称compare and swap。乐观锁通过比较操作值与预期值(操作原值)是否相等来确定是否执行交换操作。如果相等,则执行,否则不执行。CAS避免了使用传统锁带来的性能开销和死锁问题,提高了程序的并发性能。

优点:

  1. 并未真正加锁,效率高。

缺点:

1)如果粒度掌握不好,更新失败的概率会比较高。

图 CAS示意图

下面我们将实现一个需求来复现CAS的ABA问题、自旋及问题优化方案。

需求描述:银行有张表记录了用户名、用于余额信息,用户可以存取钱,现在要求,任何情况下,账号余额不能小于0。

1.2.1 ABA问题

在CAS中,线程1修改某个值时,该值旧值=A,然后继续其他的业务操作。此时线程2将该值修改为B并提交了事务,然后线程3将该值修改为A,这时线程1在提交事务修改前,先判断该值的旧值是否等于现值,发现相等,于是提交修改。

ABA问题就好比:1)某公司会计挪用了公司50w,过了几个月后,再把50w补回去,虽然最后结果没什么变化,但是该会计这样操作已涉嫌犯罪。2)你老婆去老王家出轨了,然后再回到家中。那么你会不会原谅你老婆呢?

ABA 问题的解决一般使用版本号机制来解决。

1.2.2版本号机制

在数据表中加一个表示版本的int类型字段(或时间戳字段),每次提交修改时,版本的值会加1。

CREATE DEFINER=`root`@`localhost` PROCEDURE `depositByVersion`(IN p_amount DECIMAL)
BEGIN
DECLARE d_version_before INT DEFAULT 0;
DECLARE d_version_after INT DEFAULT 0;
DECLARE d_amount DECIMAL DEFAULT 0;
SELECT version,amount INTO d_version_before,d_amount FROM account WHERE id = 1;
SELECT SLEEP(5); -- 其他耗时操作
IF d_amount > -p_amount THEN
SELECT version INTO d_version_after FROM account WHERE id = 1;
IF d_version_before = d_version_after THEN
UPDATE account SET version = version + 1,amount = amount + p_amount WHERE id = 1;
ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额已被修改,请重新操作';
END IF;
ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额不足';
END IF;
END

在实际开发中,我们常使用“自旋”的方式让事务遇到版本号不一致的问题时能得以继续执行。

DROP PROCEDURE IF EXISTS depositBySpin;
CREATE DEFINER=`root`@`localhost` PROCEDURE `depositBySpin`(IN p_amount DECIMAL)
BEGIN
DECLARE d_version_before INT DEFAULT 0;
DECLARE d_version_after INT DEFAULT 0;
DECLARE d_amount DECIMAL DEFAULT 0;
label: LOOP
SELECT version,amount INTO d_version_before,d_amount FROM account WHERE id = 1;
SELECT SLEEP(5); -- 其他耗时操作
IF d_amount > -p_amount THEN
SELECT version INTO d_version_after FROM account WHERE id = 1;
IF d_version_before = d_version_after THEN
UPDATE account SET version = version + 1,amount = amount + p_amount WHERE id = 1;
LEAVE label;
END IF;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额不足';
END IF;
END LOOP label;
END

1.2.3 高并发下乐观锁的问题

在高并发场景下,比如在双十一,1秒钟就可能会有几十万个订单,而这几十个订单都需要同时来修改商品库存,此时将会有大量的修改库存的事务陷入长时间的自旋中,让用户长时间的等待。

此时,我们应该修改事务的粒度,让修改库存这个事务操作的粒度变细些。

2 兼容性

共享锁

多个事务可以同时持有共享锁,用于读取数据行。其他事务也可以获取共享锁,但不能获取排他锁。

排他锁

只能由一个事务持有排他锁,用于修改或删除数据行。其他事务无法同时持有任何类型的锁。

表 共享锁与排他锁

共享锁语法是:LOCK IN SHARE MODE。

排他锁的语法是:FOR UPDATE。

3 颗粒度

全局锁

对整个数据库实例加锁,加锁后数据库处于只读状态。其他的DML语句将会被阻塞。

表锁

对目标表进行加锁。锁定颗粒度大,发生锁冲突的概率最高,并发度最低。

页锁

数据库底层是以页为单位的,一页大小为16KB,一张表可能有很多页。页锁是介于表锁和行锁之间的锁。

行锁

当一个事务要修改或读取某行数据时,会申请该行的记录锁,阻止其他事务对同一行的修改操作。

表锁 按颗粒度划分SQL锁

全局锁一般用于全库的备份。

加全局锁:FLUSH TABLES WITH READ LOCK;

释放锁:UNLOCK TABLES;

3.1 表锁

元数据锁

meta data lock 简称MDL。加锁过程是系统自动控制的。元数据是指表结构,当对一张表进行增删改查时,加MDL读锁,不能修改这张表的结构,当对表结构进行修改时,加MDL写锁。

表共享读锁

不会阻塞其他线程读,但会阻塞线写。LOCK TABLES 表名 READ;

表独占写锁

既会阻塞其他线程读,也会阻塞其他线程写。LOCK TABLES 表名 WRITE。

表 表锁的种类

4模式

记录锁

行锁,对表中的记录加锁。记录锁是锁住索引记录而不是真正的数据记录。属于排他锁。

间隙锁

Gap Lock是行锁的一种,是InnoDB在RR隔离级别下为解决幻读问题引入的锁机制。

临界锁

Next-key 是记录锁和间隙锁的组合。加在某条记录以及这条记录前面间隙上的锁。

意向锁

不与行级锁冲突的表级锁。避免为了判断表是否存在行锁而去全表扫描。

插入意向锁

Insert Intention Lock,是间隙锁的一种,专门针对insert操作。多个事务在同一个索引范围区间插入记录时,如果插入位置不冲突,不会彼此阻塞。

自增锁

实现自增约束,当一个事务要插入数据并获取下一个自增值时,它首先会获取个自增锁,一旦事务获得自增锁,它就可以安全地执行插入操作,并确保每次插入都会得到唯一且连续的自增值,其他事务在等待自增锁被释放前,无法获取下一个自增值。

表 按模式划分SQL锁

4.1 记录锁、间隙锁与临界锁

这些锁都是基于索引实现的。

记录锁:精准加在某一行上。

间隙锁:加载不存在的空闲空间,可以是两个索引记录之间,也可以是第一个索引记录之前,或者最后一个索引之后的无限空间。

临界锁:记录锁与间隙锁的组合。

图 演示的数据记录表

针对主键索引(id字段),可加锁的范围分别是:

记录锁:1,2,5,18

间隙锁:(负无穷,1)、(2,5)、(5,18)、(18,正无穷)

临界锁:(负无穷,1]、[2,5)、[5,18)、[18,正无穷)

4.2 意向锁

没加意向锁时:线程1对某表的某条记录加行锁;线程2对该表加表锁前,需检查当前表是否有对应行锁,如果没有则添加表锁,否则阻塞。会从第一行检索到最后一行,效率极低。

有意向锁:线程1对某表的某条记录加行锁,同时对该表加上意向锁。其他线程在对这张表加锁时,会根据该表所加的意向锁来判断是否可以添加表锁。而不用逐行判断行锁了。

共享意向锁

将在一个范围内共享锁定,阻止其他事务获取排他所。多个事务可以同时持有共享意向锁。

排他意向锁

将在一个范围内请求排他锁,阻止其他事务获取共享锁和排他锁。只能由一个事务持有排他意向锁。

表 意向锁的种类

事务1:申请id=2或5的共享意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 2 OR id = 5 LOCK IN SHARE MODE; -- 申请共享锁
SELECT SLEEP(5);
COMMIT;

事务2:申请id=2或5的排他意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 2 OR id = 5 FOR UPDATE; -- 申请排他锁
SELECT SLEEP(5);
COMMIT;

事务3: 申请id=1 的排他意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 1; -- 申请排他锁
SELECT SLEEP(5);
COMMIT;
  1. 执行事务1,事务2。结果是事务2要等事务1提交后,才能获取排他锁。
  2. 执行事务1,事务2,事务3。结果是事务3获取排他锁不会受事务1及事务2的影响。

5 MVCC

MVCC(Mutil-Version Concurrency Control)全称多版本并发访问。是一种并发环境下进行数据安全控制的方法,其本质上是一种乐观锁。

MVCC的核心是Undo Log及Read View。

5.1 Undo Log

对记录做了变更操作(INSERT、UPDATE、DELETE)时就会产生一条Undo记录。作用是保护事务在发生异常或手动回滚时可以回滚到历史版本数据,能让你读取某个时间点保存的数据。

InnoDB引擎中,一个聚簇索引(主键索引)的记录之中,一定会有两个隐藏字段trx_id 和 roll_pointer。

trx_id:记录修改这条记录的事务id。

roll_pointer:记录该条记录上一个版本的地址。

图 Undo Log示意图

5.2 Read View

一致性视图,是在读操作前创建的。RC级别下每次读操作前都生成一个Read View。而RR下是在第一次读操作前生成一个Read View。主要有四个字段:1)creator_trx_id,创建当前Read View所对应的事务ID;2)m_ids: 所有当前未提交的事务(活跃事务)id;3)min_trx_id:m_ids 里最小的事务id值;4)max_trx_id:InnoDB需要分配给下一个事务的事务ID值(事务ID是累计递增的)。

在访问某条记录时,按照下面规则来判断该记录在版本链中的某个版本(取trx_id字段)是否可见:

  1. trx_id < min_trx_id, 表明生成该版本的事务在生成ReadView前已提交,所以可读。
  2. trx_id > max_trx_id, 表明该版本事务在生成ReadView后才生成,所以不可读。
  3. min_trx_id <= trx_id <= max_trx_id,分两种情况:

1)trx_id 在m_ids 中

trx_id = creator_trx_id,表明该版本是当前事务产生的,所以可读;

trx_id != creator_trx_id,表明该版本还是活跃事务但不是当前事务,所以不可读。

2)trx_id 不在m_ids 中,所以该版本事务已提交,所以可读。

文章来源:https://blog.csdn.net/qq_25308331/article/details/135070086
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。