资讯专栏INFORMATION COLUMN

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

Gu_Yan / 2845人阅读

摘要:行级锁,页级锁,表级锁。闻其名知其意,比较少见的是页级锁,它锁定的是一组相邻数据。排他锁允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的读写。意向排他锁事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的锁。

废话

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

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

实践 - 基础

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

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

A端 B端
SET AUTOCOMMIT=0 SET 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,你可以完成事务的结束。

实践 - 锁

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

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

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

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


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

而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框架为例。简述:

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

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

解锁

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

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

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

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

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

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

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

隔离

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

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

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

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

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

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

MySQL默认采用RR级别。

题外

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

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

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

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/48749.html

相关文章

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

    摘要:行级锁,页级锁,表级锁。闻其名知其意,比较少见的是页级锁,它锁定的是一组相邻数据。排他锁允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的读写。意向排他锁事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的锁。 废话 本篇的名字简直可以起成《事务操作:从入门到放弃》。 力图解决:在MySQL 5.5 版本及更高版本时,使用事务的完整流程和细节记录,而无需面对...

    iOS122 评论0 收藏0
  • mysql机制浅析(1)

    摘要:当多个客户端并发访问同一个数据的时候,为了保证数据的一致性,数据库管理系统会自动的为该数据加锁解锁,这种被称为隐式锁。 mysql最初是希望设计出一种独立于各种存储引擎的锁定机制,mysql存储引擎的设计者是建立在任何表在同一时刻只允许单个线程对其进行访问(包括读)这样的假设的基础之上的!很明显,现在的mysql并不是这个样子的,因为mysql如今已经发展成为了一款多用户、多线程的my...

    jiekechoo 评论0 收藏0
  • MySQL 游标、变量、事务

    摘要:当当前连接断开后,其设置的所有会话变量均失效。局部变量作用域仅限于该语句块内,在该语句块执行完毕后,局部变量就消失了。行级锁开销大,加锁慢会出现死锁锁定粒度小,发生锁冲突的概率最低,并发度也最高。 变量分类 会话变量 SET @var1 = 1; 作用域:仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。 局部变量 DECLARE a INT DEFAUL...

    Near_Li 评论0 收藏0
  • 浅析MySQL二段

    摘要:何时解锁在一个事务中,只有在或者时,才是解锁阶段。另外,当更新操作到达数据库的那个点,才算加锁成功。所以,更新操作的前半个和操作的后半个都不计算在整个锁库存的时间内。死锁加锁都或多或少会遇到这个问题。 背景 在介绍MySQL二段锁之前,我需要理清一下概念,即MySQL二阶段加锁与二阶段提交的区别: 二阶段加锁:用于单机事务中的一致性和隔离性 二阶段提交:用于分布式事务 何为二段锁 在一...

    XFLY 评论0 收藏0
  • Mysql 事务及数据一致性处理

    摘要:乐观锁顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。 在工作中,我们经常会遇到这样的问题,需要更新库存,当我们查询到可用的库存准备修改时,这时,其他的用户可能已经对这个库存数据进行修改了,导致,我们查询到的数据会有问题,下面我们就来看解决方法。 在MySQL的InnoDB中,预...

    AlphaWallet 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<