MySQL 数据库事务隔离性的实现

标签: mysql 数据库 事务隔离 | 发表时间:2021-05-29 03:52 | 作者:华为云开发者社区
出处:https://juejin.cn/backend

​​​​摘要: 事实上在数据库引擎的实现中并不能实现完全的事务隔离,比如串行化。

本文分享自华为云社区 《【数据库事务与锁机制】- 事务隔离的实现》,原文作者:技术火炬手 。

事实上在数据库引擎的实现中并不能实现完全的事务隔离,比如串行化。这种事务隔离方式虽然是比较理想的隔离措施,但是会对并发性能产生比较大的影响,所以在 MySQL 中事务的默认隔离级别是 REPEATABLE READS(可重复读),下面我们展开讨论一下 MySQL 对数据库隔离性的实现。

MySQL 事务隔离性的实现

在 MySQL InnoDB (下称 MySQL)中实现事务的隔离性是通过锁实现的,大家知道在并发场景下我常用的隔离和一致性措施往往是通过锁实现,所以锁也是数据库系统常用的一致性措施。

MySQL 锁的分类

我们主要讨论 InnoDB 锁的实现,但是也有必要简单了解 MySQL 中其他数据库引擎对锁的实现。整体来说 MySQL 中可以分为三种锁的类型 表锁、行锁、页锁,其中使用表锁的是 MyISAM 引擎,支持行锁的是 InnoDB 引擎,同时 InnoDB 也支持表锁,BDB 支持页锁(不是太了解)。

表锁 table-level locking

表级别的锁顾名思义就是加锁的维度是表级别的,是给一个表上锁,这种锁的特点是开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,但是并发度也是最低的,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用。

MySQL 表锁的使用

在 MySQL 中使用表锁比较简单,可以通过 LOCK TABLE 语句对一张表进行加锁,如下:

   # 加锁
LOCK TABLE T_XXXXXXXXX;
# 解锁
UNLOCK TABLES;
复制代码

加锁和解锁的语法

   LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}
 
UNLOCK TABLES
复制代码

需要注意的是 LOCK TABLE 是指当前会话的锁,也就是通过 LOCK TABLE 显示的为当前会话获取表锁,作用是防止其他会话在需要互斥访问时修改表的数据,会话只能为其自身获取或释放锁。一个会话无法获取另一会话的锁,也不能释放另一会话持有的锁。同时 LOCK TABLE 不单单可以获取一个表的锁,也可以是一个视图,对于视图锁定,LOCKTABLES 将视图中使用的所有基本表添加到要锁定的表集合中,并自动锁定它们。

LOCK TABLES 在获取新锁之前,隐式释放当前会话持有的所有表锁

UNLOCK TABLES 显式释放当前会话持有的所有表锁

LOCKTABLE 语句有两个比较重要的参数 lock_type 它可以容许你指定加锁的模式,是读锁还是写锁,也就是 READLOCK 和 WRITE LOCK。

  • READ 锁

读锁的特点是 持有锁的会话可以读取表但不能写入表,多个会话可以同时获取 READ 该表的锁

  • WRITE 锁

持有锁的会话可以读取和写入表,只有持有锁的会话才能访问该表。在释放锁之前,没有其他会话可以访问它,保持锁定状态时,其他会话对表的锁定请求将阻塞

WRITE 锁通常比 READ 锁具有更高的优先级,以确保尽快处理更新。这意味着,如果一个会话获取了一个 READ 锁,然后另一个会话请求了一个 WRITE 锁,则随后的 READ 锁请求将一直等待,直到请求该 WRITE 锁的会话已获取并释放了该锁

通过上面对表锁的简单介绍我们引出两个比较重要的信息,就是读锁和写锁,那么答案就浮出水面,在表级别的锁中其实 MySQL 是通过 共享读锁,和排他写锁来实现隔离性的,下面我们减少共享读锁和排他写锁。

共享读锁(Table ReadLock)

共享锁又称为读锁,简称 S 锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改

对 MyISAM 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;也即当一个 session 给表加读锁,其他 session 也可以继续读取该表,但所有更新、删除和插入将会阻塞,直到将表解锁。MyISAM 引擎在执行 select 时会自动给相关表加读锁,在执行 update、delete 和 insert 时会自动给相关表加写锁

独占写锁(Table WriteLock)

排他锁又称为写锁,简称 X 锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改

独占写锁也被称之为排他写锁,MyISAM 表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。也即当一个 session 给表加写锁,其他 session 所有读取、更新、删除和插入将会阻塞,直到将表解锁

共享锁和独占锁的兼容性

​行锁 Row -level locking

在 MySQL 中 支持行锁的引擎是 InnoDB,所以我们这里我们指的行锁主要是说 InnoDB 的行锁。

InnoDB 锁的实现和 Oracle 非常类似,提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销,并可以同时得到并发性和一致性。

lock 与 latch

Latch 一般称为闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在 InnoDB 中,latch 又可以分为 mutex(互斥量)和 rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。

Lock 的对象是事务,用来锁定的是数据库中的对象,如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同)。

lock 与 latch 的比较

latch 可以通过命令 SHOWENGINE INNODB MUTEX 查看,Lock 可以通过命令 SHOW ENGINE INNODB STATUS 及 information_schema 架构下的表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 来查看

和上面表锁中讲的一样 MySQL 行锁也是通过 共享锁和独占锁(排他锁)实现的,所以关于这两种锁的概述就不过多简绍。

InnoDB 还支持多粒度(granular)锁定,允许事务同时存在行级锁和表级锁,这种种额外的锁方式,称为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁

如果对最下层(最细粒度)的对象上锁,那么首先需要对粗粒度的对象上锁,意向锁为表级锁,不会阻塞除全表扫描以外的任何请求。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。两种意向锁。

  • 意向共享锁(IS Lock),事务想要获得一张表中某几行的共享锁
  • 意向排他锁(IX Lock),事务想要获得一张表中某几行的排他锁

表级意向锁与行级锁的兼容性

下面命令或表都可以查看当前锁的请求

   SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
复制代码

一致性非锁定读

一致性的非锁定读(consistent nonlocking read)是指 InnoDB 通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作,这时不会去等待行上锁的释放。而是去读取行的一个快照数据(之前版本的数据)。

一个行记录多个快照数据,一般称这种技术为行多版本技术。由此带来的并发控制,称之为多版本并发控制(Multi Version Concurrency Control,MVCC)。

之所以称为非锁定读,因为不需要等待访问的行上 X 锁的释放。实现方式是通过 undo 段来完成。而 undo 用来在事务中回滚数据,快照数据本身没有额外的开销,也不需要上锁,因为没有事务会对历史数据进行修改操作。非锁定读机制极大地提高了数据库的并发性。在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外,即使都是使用非锁定的一致性读,但是对于快照数据的定义也不相同。在事务隔离级别 READ COMMITTED 和 REPEATABLE READ 下,InnoDB 使用非锁定的一致性读。但对快照数据的定义不相同。在 READCOMMITTED 事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新一份快照数据。而在 REPEATABLEREAD 事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。

自增长与锁

自增长在数据库中是非常常见的一种属性,也是首选的主键方式。在 InnoDB 的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-incrementcounter)。

插入操作会依据这个自增长的计数器值加 1 赋予自增长列。这个实现方式称做 AUTO-INC Locking,采用了一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的 SQL 语句后立即释放。

因此 InnoDB 提供了一种轻量级互斥量的自增长实现机制,大大提高了自增长值插入的性能。同时提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式,该参数的默认值为 1。了解其实现之前,先对自增长的插入进行分类,如下表:

参数 innodb_autoinc_lock_mode 的说明

InnoDB 中自增长的实现和 MyISAM 不同,MyISAM 存储引擎是表锁设计,自增长不用考虑并发插入的问题。如果主从分别使用 InnoDB 和 MyISAM 时,必须考虑这种情况。

另外,在 InnoDB 存中,自增长值的列必须是索引,同时必须是索引的第一个列。如果不是第一个列会抛出异常,而 MyISAM 没有这个问题。

外键和锁

外键主要用于引用完整性的约束检查。InnoDB 对于一个外键列,如果没有显式地对这个列加索引,会自动对其加一个索引,可以避免表锁。而 Oracle 不会自动添加索引,需要手动添加,可能会产生死锁问题。

对于外键值的插入或更新,首先需要查询(select)父表中的记录。但是 select 父表操作不是使用一致性非锁定读,因为这会导致数据不一致的问题,因此这时使用的是 SELECT…LOCK IN SHARE MODE 方式,即主动对父表加一个 S 锁。如果这时父表上已经加了 X 锁,子表上的操作会被阻塞。如下表:

​行锁的 3 种算法

InnoDB 有如下 3 种行锁的算法

  • Record Lock:单个行记录上的锁。总去锁住索引记录,如果表没有设置任何索引,会使用隐式的主键来进行锁定
  • Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
  • Next-Key Lock:Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身。行的查询采用这种锁定算法

例如一个索引有 10,11,13 和 20 这四个值,那么该索引可能被 Next-KeyLocking 的区间为

采用 Next-Key Lock 的锁定技术称为 Next-Key Locking。其设计的目的是为了解决幻读问题(Phantom Problem)。Next-Key Lock 是谓词锁(predict lock)的一种改进。还有 previous-key locking 技术。同样上述的索引 10、11、13 和 20,若采用 previous-key locking 技术,那么锁定的区间为

当查询的索引含有唯一属性时,会对 Next-Key Lock 进行优化。对聚集索引,将其降级为 Record Lock。对辅助索引,将对下一个键值加上 gap lock,即对下一个键值的范围为加锁

Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内,而这会产生导致幻读问题,用户可以通过以下两种方式来显式地关闭 Gap Lock

  • 将事务的隔离级别设置为 READ COMMITTED
  • 将参数 innodb_locks_unsafe_for_binlog 设置为 1

上述设置破坏了事务的隔离性,并且对于 replication,可能会导致主从数据的不一致。此外,从性能上来看,READCOMMITTED 也不会优于默认的事务隔离级别 READ REPEATABLE。

解决幻读问题

幻读问题是指在同一事务下,连续执行两次同样的范围查询操作,得到的结果可能不同

Next-KeyLocking 的算法就是为了避免幻读问题。对于上述的 SQL 语句,其锁住的不是单个值,而是对(2,+∞)这个范围加了 X 锁。因此任何对于这个范围的插入不允许,从而避免了幻读问题。Next-Key Locking 机制在应用层还可以实现唯一性的检查。例如:

   select * from table_name where col = xxx LOCK IN SHARE MODE;
复制代码

如果用户通过索引查询一个值,并对该行加上一个 SLock,那么即使查询的值不在,其锁定的也是一个范围,因此若没有返回任何行,那么新插入的值一定是唯一的。如果此时有多个事务并发操作,那么这种唯一性检查机制也不会存在问题。因为这时会导致死锁,只有一个事务的插入操作会成功,而其余的事务会抛出死锁的错误。

通过 Next-Key Locking 实现应用程序的唯一性检查:

总结

以上我们简单简绍了 MySQL 如何通过锁机制实现对事务的隔离,也简绍了一些实现这些所的算法,如果对细节比较感兴趣的同学可以参考 官方文档 中对 InnoDB 的详细简绍。

点击关注,第一时间了解华为云新鲜技术~

相关 [mysql 数据库 事务隔离] 推荐:

MySQL数据库事务隔离级别(Transaction Isolation Level)

- - RSS - IT博客云
修改事务隔离级别的方法:. 1.全局修改,修改 mysql.ini配置文件,在最后加上. 1 #可选参数有: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. 这里全局默认是 REPEATABLE-READ,其实MySQL本来默认也是这个级别.

MySQL 数据库事务隔离性的实现

- - 掘金 后端
​​​​摘要: 事实上在数据库引擎的实现中并不能实现完全的事务隔离,比如串行化. 本文分享自华为云社区 《【数据库事务与锁机制】- 事务隔离的实现》,原文作者:技术火炬手. 事实上在数据库引擎的实现中并不能实现完全的事务隔离,比如串行化. 这种事务隔离方式虽然是比较理想的隔离措施,但是会对并发性能产生比较大的影响,所以在 MySQL 中事务的默认隔离级别是 REPEATABLE READS(可重复读),下面我们展开讨论一下 MySQL 对数据库隔离性的实现.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

MySQL数据库的IO操作

- - haohtml's blog
         淘宝丁奇分享的PPT:MySQL数据库的IO操作,详细分享了四块的内容,并且告诉大家如何调整MySQL数据库IO操作相关的参数,给出了详细的选择策略,现替其整理成文章分享与此. 4.影响io行为的一些参数和选择策略. 一个简单的查询 select * from t where id>=(  select id from t where k1=100 limit 100000,1) limit 2;.

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

Google数据库产品LevelDB对决MySQL

- - HTML5研究小组
去年一月份,Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库,目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库,可按照字符串键值顺序映射. 源于其本身的良好设计,特别是LSM算法,LevelDB性能非常之高. 在一台4个Q6600的CPU机器上,每秒钟写数据超过40w,而随机读的性能每秒钟超过10w.

excel数据导入mysql数据库

- - 互联网 - ITeye博客
1、excel另存为txt.       选中将要导出的数据列,然后另存为选择其它格式=>文本文件(制表符分割). E:\项目\fblike\game_code_san.txt. 2、txt导入到mysql数据库. load data infile 'E:\\项目\\fblike\\game_code_san.txt' into table game_code_san(code).

c/c++连接mysql数据库

- - CSDN博客数据库推荐文章
        由于项目需要,要用c/c++链接mysql数据库. 网上很多类似的解说,但是大部分都需要在本机器上安装完整版的msyql. 其实,有时候我们并不想在改变自己电脑上原有的环境,但是我们却希望通过我们的程序链接数据库. 比如:我在本机上已经安装了一个mysql,但并不是完整版的(比如appserv集成mysql或者wamp集成mysql),或者我的工作在局域网中,我只需要链接另外一台机器上的mysql.

理解MySQL数据库覆盖索引

- - haohtml's blog
看AUTO_INCREMENT就知道数据并不多,75万条. 很简单对不对?怪异的地方在于:. 如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事,但是真实的业务需求比这个复杂,造成的差距也很大:MyISAM只需要0.12s,InnoDB则需要2.2s.,最终定位到问题症结是在这条SQL.

MySQL数据库设置主从同步

- - CSDN博客架构设计推荐文章
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. 1、可以作为一种备份机制,相当于热备份. 2、可以用来做读写分离,均衡数据库负载. 1、主从数据库版本一致,建议版本5.5以上. # 日志文件名 log-bin = mysql-bin # 日志格式,建议mixed binlog_format = mixed # 主数据库端ID号 server-id = 1.