MySQL - 事务的启动 / 设置 / 锁 / 解锁——入门

sqlican 2019-06-21

废话

本篇的名字简直可以起成《事务操作:从入门到放弃》。

力图解决:在MySQL 5.5 版本及更高版本时,使用事务的完整流程和细节记录,而无需面对互联网上纷繁零散的事务笔记。

实践 - 基础

首先,在你的空数据库上(譬如Test预留数据库),创建一个Test表,有idtext(varchar 50)两个字段。

请开启两个MySQL操作端,分别依次键入:

A端B端
SET AUTOCOMMIT=0SET AUTOCOMMIT=0
SELECT text FROM test WHERE id = 1不输入
UPDATE test SET text='UioSun' WHERE id = 1;UPDATE test SET text='UioYang' WHERE id = 1;

注意你的查询提示栏,你能发现:在A端未提交之前,默认状态下,B端的UPDATE是没有反馈的——被挂起。等待一段时间后,你能收到关于Transaction失败的消息。

这种错误状态被称为死锁,你可以通过解锁相关的内容,来Kill it。

上述是很极端的情况,正常来说,事务是通过自动插入来完成,基本上可以避免死锁情况。

这就是事务的基础演示,最后,通过ROLLBACKCOMMIT,你可以完成事务的结束。

实践 - 锁

在上一部分,你完成了一个事务的基础流程,启动、进行、并最终得到结果(或许是意外结果)。
至少我在上一部分结尾处,脑海中有两个问题:

  1. 我听过事务的锁,它通过锁完成独享目标,并在完成修改后释放它的独享权,但我该如何设置它的级别?

  2. 锁的阻塞时间为多久?我如何检测它?

当然,为了另一种思路的编程玩家,我也将在本节末尾放上当前支持锁的优缺比较。


行级锁,页级锁,表级锁。闻其名知其意,比较少见的是:页级锁,它锁定的是一组相邻数据。

而MySQL的不同引擎,对锁级别支持是不一样的,以最常用的InnoDB为代表,默认采用行级锁,也支持表级锁,但这是有条件的,只有在针对索引SQL操作时,才会使用行级锁,否则这个操作将采取表级锁。

表级锁锁定的数量最多,占据内存最多,但有在做内部处理时中,它的操作速度是相当快的,而且几乎不存在死锁问题,所以在中大型内部处理机制中,表级锁的应用场景大于行级锁。

行级锁又分为共享锁和独占锁(排它锁,翻译差异),允许读取的共享锁是默认锁,而独占锁是不允许读写的完全占有——废话。

共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的读写。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

页级锁(间隙锁)

@gaoyulong 表示:间隙锁被吃啦?

对此我表示抱歉,之前一直了解的都是页级锁(也就是间隙锁),偏偏页级锁在互联网上的信息又不够丰富,所以就没考虑到。

页级锁是个很重要的锁,它会锁定一组数据,但这个锁并不是那么好用(更多的考虑是安全性)。

对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对一个AutoID 100条数据的表做ID为102的查询,要是不使用间隙锁,如果其他事务插入了ID大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另外一方面,是为了满足其恢复和复制的需要。

本段内容源于:间隙锁(Next-Key锁) - xiaobluesky

在此基础上,如果在查询锁表时,对不存在ID进行Insert操作,将导致等待阻塞。

额外的锁

除了DB本身分类外,在框架层面,还有乐观锁与悲观锁之分。注意层面,这种锁属于应用程序设计的锁,而非数据库设计的锁。

以我最熟悉的Yii 2框架为例。简述:

  1. 乐观锁就是一个可对比序列号,但存在高频并发时的对比错位 BUG;

  2. 悲观锁就是一个严谨可对比序列号,并提供解锁功能。事实上,由于悲观锁的使用复杂度(我没看出来),Yii 2并没有提供悲观锁功能。

解锁

说完锁,我们肯定需要一个解锁机制,脑海里忽然蹦出冷段子:一人去买门锁,安好了才发现,这门只能从外面开,进去锁门就出不来了。

很冷吧。没有解锁机制的事务处理系统,是一个只能进,不能出的事务处理系统——死锁尽管会自动解锁,但反馈时间是一个很刚性的设置。

先说这个很刚的设置,如果你想修改它,可以去 my.ini 文件的innodb_lock_wait_timeout这一行,默认为50
s的等待时间。

应用层面的锁可以通过校对序列号来自行解锁,而MySQL层面的锁,可以通过information_schemePROCESSLIST表,来完成解锁——确认无法完成事务。

这里说一下PROCESSLIST表,当一个关闭自动提交的事务已经启动,另一个同类事务也启动,双方冲突后,在这个表内是存在冲突SQL Status,你可以自己去观察。

最后:无论解锁机制多么健全,死锁本身是代码逻辑引起的,不修正/优化代码逻辑,单纯的解锁机制不过是对系统的额外负担。

解决方案很简单:自己写一个简单的Log功能,将所有触发解锁机制的情况,记录在Log里,自行优化。

隔离

配合锁机制的就是隔离机制,它可以尽可能有效的设置:事务间的可见度。

  1. 读取未提交(RU,Read Uncommitted):最低隔离,问题是脏读(未被提交的UPDATE,仍然可被读取)。

  2. 读取提交(RC,Read Committed):语句提交以后即执行了COMMIT以后别的事务就能读到这个改变. 问题:不可重复读(同事务时,前后读取到不一致数据)。

  3. 可重复读(RR,Repeatable Read):在同一个事务里面先后执行同一个查询语句的时候,得到的结果是一样的,问题:幻读(并发事务同时处理同内容,并导致一方内容覆盖了另一方,令对方感觉出现了幻觉)。

  4. 序列化(S,Serializable):在这个级别下,所有的事务的完整性都被保留,意味着所有的事务都可以被序列化的执行,只有当两个事务之间没有任务冲突时,才能并发的执行。

四个级别中,高级隔离不会遇到比自己低级隔离的问题,但隔离级别越高,对并发的损失性越高。

MySQL默认采用RR级别。

题外

提到锁,就想到以前做过的秒杀后端,当时的处理机制很简单,时间戳 + 事务。

时光荏苒,现在回头看,忽然发现有一些改进的地方,一笔带过:秒杀最大数量 缓存对比 → 服务器端 微秒级时间戳 + 事务/悲观锁 插入 + 插入失败 缓存队列及二次插入尝试,这样已经能够解决极大程度的并发问题了。

如果这样都会出现重复插入问题,那按我目前的水准,在应用层面是解决不了了。

相关推荐