关于linux运维:详解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 进行优化，减少零碎的吞吐量。