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

时间  2021-05-31 标签 html mysql 算法 sql 数据库 session 架构 并发 性能 优化
摘要：事实上在数据库引擎的实现中并不能实现彻底的事务隔离，好比串行化。

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

事实上在数据库引擎的实现中并不能实现彻底的事务隔离，好比串行化。这种事务隔离方式虽然是比较理想的隔离措施，可是会对并发性能产生比较大的影响，因此在MySQL中事务的默认隔离级别是 REPEATABLE READS（可重复读），下面咱们展开讨论一下MySQL对数据库隔离性的实现。mysql

MySQL 事务隔离性的实现

在MySQL InnoDB （下称MySQL）中实现事务的隔离性是经过锁实现的，你们知道在并发场景下我经常使用的隔离和一致性措施每每是经过锁实现，因此锁也是数据库系统经常使用的一致性措施。算法

MySQL锁的分类

咱们主要讨论InnoDB 锁的实现，可是也有必要简单了解MySQL中其余数据库引擎对锁的实现。总体来讲MySQL 中能够分为三种锁的类型 表锁、行锁、页锁，其中使用表锁的是 MyISAM引擎，支持行锁的是 InnoDB 引擎，同时InnoDB也支持表锁，BDB 支持页锁（不是太了解）。sql

表锁 table-level locking

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

MySQL 表锁的使用

在MySQL中使用表锁比较简单，能够经过 LOCK TABLE 语句对一张表进行加锁，以下：session

# 加锁
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 不仅仅能够获取一个表的锁，也能够是一个视图，对于视图锁定，LOCK TABLES将视图中使用的全部基本表添加到要锁定的表集合中，并自动锁定它们。并发

LOCK TABLES 在获取新锁以前，隐式释放当前会话持有的全部表锁
UNLOCK TABLES显式释放当前会话持有的全部表锁

LOCK TABLE 语句有两个比较重要的参数 lock_type 它能够允许你指定加锁的模式，是读锁仍是写锁，也就是 READ LOCK 和 WRITE LOCK。性能

  • READ 锁
    读锁的特色是 持有锁的会话能够读取表但不能写入表，多个会话能够同时获取READ该表的锁
  • WRITE 锁
    持有锁的会话能够读取和写入表，只有持有锁的会话才能访问该表。在释放锁以前，没有其余会话能够访问它，保持锁定状态时，其余会话对表的锁定请求将阻塞
    WRITE锁一般比READ锁具备更高的优先级，以确保尽快处理更新。这意味着，若是一个会话获取了一个READ锁，而后另外一个会话请求了一个WRITE锁，则随后的 READ锁请求将一直等待，直到请求该WRITE锁的会话已获取并释放了该锁

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

共享读锁(Table Read Lock)

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

对MyISAM表的读操做，不会阻塞其余用户对同一表的读请求，但会阻塞对同一表的写请求；也即当一个session给表加读锁，其余session也能够继续读取该表，但全部更新、删除和插入将会阻塞，直到将表解锁。MyISAM引擎在执行select时会自动给相关表加读锁，在执行update、delete和insert时会自动给相关表加写锁

独占写锁（Table Write Lock）

排他锁又称为写锁，简称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能够经过命令SHOW ENGINE 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使用非锁定的一致性读。但对快照数据的定义不相同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读老是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读老是读取事务开始时的行数据版本。

自增加与锁

自增加在数据库中是很是常见的一种属性，也是首选的主键方式。在InnoDB的内存结构中，对每一个含有自增加值的表都有一个自增加计数器（auto-increment counter）。

插入操做会依据这个自增加的计数器值加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-Key Locking的区间为

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决幻读问题（Phantom Problem）。Next-Key Lock是谓词锁（predict lock）的一种改进。还有previous-key locking技术。一样上述的索引十、十一、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，可能会致使主从数据的不一致。此外，从性能上来看，READ COMMITTED也不会优于默认的事务隔离级别READ REPEATABLE。

解决幻读问题

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

Next-Key Locking的算法就是为了不幻读问题。对于上述的SQL语句，其锁住的不是单个值，而是对（2，+∞）这个范围加了X锁。所以任何对于这个范围的插入不容许，从而避免了幻读问题。Next-Key Locking机制在应用层还能够实现惟一性的检查。例如：

select * from table_name where col = xxx LOCK IN SHARE MODE;

若是用户经过索引查询一个值，并对该行加上一个SLock，那么即便查询的值不在，其锁定的也是一个范围，所以若没有返回任何行，那么新插入的值必定是惟一的。若是此时有多个事务并发操做，那么这种惟一性检查机制也不会存在问题。由于这时会致使死锁，只有一个事务的插入操做会成功，而其他的事务会抛出死锁的错误。

经过Next-Key Locking实现应用程序的惟一性检查：

总结

以上咱们简单简绍了MySQL 如何经过锁机制实现对事务的隔离，也简绍了一些实现这些所的算法，若是对细节比较感兴趣的同窗能够参考 官方文档 中对InnoDB 的详细简绍。

 

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

联系我们 沪ICP备2021010478号-7