MySql-两阶段加锁协议

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySql-两阶段加锁协议

前言

此篇博客主要是讲述MySql(仅限innodb)的两阶段加锁(2PL)协议,而非两阶段提交(2PC)协议,区别如下:

2PL,两阶段加锁协议:主要用于单机事务中的一致性与隔离性。
2PC,两阶段提交协议:主要用于分布式事务。

MySql本身针对性能,还有一个MVCC(多版本控制)控制,本文不考虑此种技术,仅仅考虑MySql本身的加锁协议。

什么时候会加锁

在对记录更新操作或者(select for update、lock in share model)时,会对记录加锁(有共享锁、排它锁、意向锁、gap锁、nextkey锁等等),本文为了简单考虑,不考虑锁的种类。

什么是两阶段加锁

在一个事务里面,分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前,如下图所示:
输入图片说明

为什么需要两阶段加锁

引入2PL是为了保证事务的隔离性,即多个事务在并发的情况下等同于串行的执行。 在数学上证明了如下的封锁定理:

如果事务是良构的且是两阶段的,那么任何一个合法的调度都是隔离的。

具体的数学推到过程可以参照<<事务处理:概念与技术>>这本书的7.5.8.2节.
此书乃是关于数据库事务的圣经,无需解释(中文翻译虽然晦涩,也能坚持读下去,强烈推荐)

工程实践中的两阶段加锁-S2PL

在实际情况下,SQL是千变万化、条数不定的,数据库很难在事务中判定什么是加锁阶段,什么是解锁阶段。于是引入了S2PL(Strict-2PL),即:

在事务中只有提交(commit)或者回滚(rollback)时才是解锁阶段,
其余时间为加锁阶段。

如下图所示:
输入图片说明
这样的话,在实际的数据库中就很容易实现了。

两阶段加锁对性能的影响

上面很好的解释了两阶段加锁,现在我们分析下其对性能的影响。考虑下面两种不同的扣减库存的方案:

方案1:
begin;
// 扣减库存
update t_inventory set count=count-5 where id=${id} and count >= 5;
// 锁住用户账户表
select * from t_user_account where user_id=123 for update;
// 插入订单记录
insert into t_trans;
commit;
方案2:
begin;
// 锁住用户账户表
select * from t_user_account where user_id=123 for update;
// 插入订单记录
insert into t_trans;
// 扣减库存
update t_inventory set count=count-5 where id=${id} and count >= 5;
commit;

由于在同一个事务之内,这几条对数据库的操作应该是等价的。但在两阶段加锁下的性能确是有比较大的差距。两者方案的时序如下图所示:
输入图片说明

由于库存往往是最重要的热点,是整个系统的瓶颈。那么如果采用第二种方案的话,
tps应该理论上能够提升3rt/rt=3倍。这还仅仅是业务就只有三条SQL的情况下,
多一条sql就多一次rt,就多一倍的时间。

值得注意的是:

在更新到数据库的那个时间点才算锁成功
提交到数据库的时候才算解锁成功
这两个round_trip的前半段是不会计算在内的

如下图所示:
输入图片说明
当前只考虑网络时延,不考虑数据库和应用本身的时间消耗。

依据S2PL的性能优化

从上面的例子中,可以看出,需要把最热点的记录,
放到事务最后,这样可以显著的提高吞吐量。更进一步:
越热点记录离事务的终点越近(无论是commit还是rollback)
笔者认为,先后顺序如下图:   

输入图片说明

避免死锁

这也是任何SQL加锁不可避免的。上文提到了按照记录Key的热度在事务中倒序排列。
那么写代码的时候任何可能并发的SQL都必须按照这种顺序来处理,不然会造成死锁。如下图所示:
输入图片说明

select for update和update where 谓词计算

我们可以直接将一些简单的判断逻辑写到update的谓词里面,以减少加锁时间,考虑下面两种方案:
方案1:

 begin:
 int count = select count from t_inventory for update;
 if count >= 5:
     update t_inventory set count=count-5 where id =123
     commit 
 else
     rollback

方案2:

 begin:
     int rows = update t_inventory set count=count-5 where id =123 and count >=5
    if rows > 0:
        commit;
    ele 
        rollback;

时延如下图所示:
输入图片说明
可以看到,通过在update中加谓词计算,少了1rt的时间。

由于update在执行过程中对符合谓词条件的记录加的是和select for update一致的排它锁
(具体的锁类型较为复杂,不在这里描述),所以两者效果一样。

总结

MySql采用两阶段加锁协议实现隔离性和一致性,我们只有深入的去理解这种协议,才能更好的对我们的SQL进行优化,增加系统的吞吐量。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
关系型数据库 MySQL 数据库
【MySQL实战笔记】 06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?-01
【4月更文挑战第17天】MySQL的锁分为全局锁、表级锁和行锁。全局锁用于全库备份,可能导致业务暂停或主从延迟。不加锁备份会导致逻辑不一致。推荐使用`FTWRL`而非`readonly=true`因后者可能影响其他逻辑且异常处理不同。表级锁如`lock tables`限制读写并限定操作对象,常用于并发控制。元数据锁(MDL)在访问表时自动加锁,确保读写正确性。
70 31
|
13天前
|
SQL 存储 关系型数据库
【MySQL 数据库】11、学习 MySQL 中的【锁】
【MySQL 数据库】11、学习 MySQL 中的【锁】
89 0
|
13天前
|
存储 关系型数据库 MySQL
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
276 0
|
13天前
|
关系型数据库 MySQL 数据库
MySQL的行级锁锁的到底是什么?
本文简述了InnoDB的行级锁机制,包括记录锁、间隙锁和Next-Key锁。记录锁锁定索引记录,防止其他事务对相同值的行进行操作;间隙锁锁定索引记录间的间隙,防止插入。Next-Key锁是两者的结合,锁定记录及其前后间隙。在可重复读(RR)隔离级别下,加锁策略涉及Next-Key锁,但会因查询条件退化为行锁或间隙锁。MySQL的加锁机制遵循两个原则和两个优化,例如唯一索引等值查询时退化为行锁。RR级别虽能防止幻读,但也可能降低并发并引发死锁,因此有些场景下会选择读已提交(RC)级别。
MySQL的行级锁锁的到底是什么?
|
13天前
|
SQL 关系型数据库 MySQL
Mysql事务隔离级别和锁特性
Mysql事务隔离级别和锁特性
|
13天前
|
存储 算法 关系型数据库
MySQL事务与锁,看这一篇就够了!
MySQL事务与锁,看这一篇就够了!
|
13天前
|
存储 关系型数据库 MySQL
MySQL的锁机制
MySQL的锁机制主要用于管理并发事务对数据的一致性和完整性的访问控制
27 4
|
13天前
|
存储 SQL 关系型数据库
|
13天前
|
关系型数据库 MySQL 数据库
MySQL锁解密:读锁与写锁
【4月更文挑战第20天】
26 1
|
13天前
|
关系型数据库 MySQL 数据库