MySQL 事务与锁机制

疯狂的豆浆
93 阅读18分钟

事务 ACID 特性

先简单介绍下事务的 ACID 特性。所谓 ACID 其实是四个单词的首字母组合：

  • A 是 Atomicity：原子性

  • C 是 Consistency：一致性

  • I 是 Isolation：隔离性

  • D 是 Durability：持久性

下面我们分别介绍并理解下 MySQL 事务的这四个特性。

原子性（Atomicity）

所谓的原子性表述的是事务是一个原子操作单元，其对数据的修改，要么全都执行成功，要么全都执行失败。

我们结合个例子阐述下：

  • 事务提交了，如果此时的 Buffer Pool 的脏页没有刷盘，如何保证数据生效？Redo
  • 如果事务没有提交，但是 Buffer Pool 的脏页刷盘了，如何保证数据不生效？ Undo

每写一个事务，都会修改 BufferPool，从而生成对应的 Redo/Undo 日志，在 BufferPool 中的页被刷到磁盘之前，这些信息会先写入到日志文件中。

如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那么在数据库再次启动的时候，我们可以通过 Redo 日志将其恢复出来，从而保证脏页写的数据不会丢失。如果脏页数据刷新成功，此时数据库也挂了，这个时候就需要通过 Undo 来实现了。

持久性（Durability）

持久性指的是一个事物一旦提交了，它对数据库中数据的改变就应该是永久的，后续的操作或故障不应该针对其任何影响，不会丢失。

如下图：

表示的是一个事务“提交”触发的动作。

  • binlog 落地

  • 发送 binlog

  • 存储引擎提交事务

  • flush_logs

  • check_point

  • 事务提交标记等

通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性。

隔离性

指的是一个事物的执行不能被其他事务干扰，InnoDB 支持 4 种隔离性。从低到高依次是：读未提交、读提交、可重复读、可串行化。锁和多版本控制机制（MVVC）技术就是用于保障隔离性的。

一致性

指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。其中一致性包括两个方面的内容：约束一致性和数据一致性。

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这 3 个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。

WAL 全称 Write-Ahead Logging，先写日志再写磁盘。

事务控制

MVCC （多版本控制）

也就是 Copy On Write 的思想，MVCC 除了支持读和读并行，还支持读和写，写和读的并行，但是为了保证一致性，写和写是无法并行的。

在事务 1 开始写操作的时候会 Copy 一个记录的副本，其它事务读操作会读取这个记录副本，因此不会影响其它事务对此记录的读取，实现写和读并行。

概念

全称为 Multi Version Concurrency Control，多版本控制。目的是为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量以及读写能力。

如何生成的多版本？

在每次事务修改操作之前，都会在 Undo 日志中记录修改之前的数据状态和事务号，该备份记录可以用与其它事务的读取，也可以进行必要时的数据回滚。

MVCC 实现原理

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的。但是 MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

在 MVCC 的并发控制中，读操作分为两类：快照读（Snapshot Read）与当前读（Current Read）

  • 快照读：读取的是记录的快照版本（有可能是历史版本），不要加锁。
    • select ...
  • 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其它事务不会再并发修改这个记录。
    • select... for update 或 lock in share mode，insert/delete/update

我们举个例子看下：

如上图，假设 F1 ~ F6 是表中的字段名称，1~6 是其对应的数据。后面三个隐含的字段分别对应该行的隐含ID、事务号和回滚指针。

具体的更新过程如下：

假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其它两个字段为空。

  1. 当事务 1 更改该行的数据值时，会进行如下操作：

图中主要做了三件事：

  • 用排他锁锁定该行，并记录 Redo log
  • 把该行修改前的值复制到 Undo log（如下）

  • 修改当前行的值，填写事务编号，同时将回滚指针指向 Undo log 中修改前的行
  1. 接下来事务 2 操作，过程与事务 1 一样，此时 Undo log 会有两行记录，并且通过回滚指针连在一起（如下图）。通过当前记录的回滚指针回溯到创建该行创建时的初始内容。

MVCC 已经实现了读读、读写、写读的并发处理，如果想进一步解决写写冲突的话，可以采用下面两种方案：

  • 乐观锁
  • 悲观锁

事务隔离级别

前面提到的“更新丢失”、“脏读”、“不可重复读”和“幻读”等并发事务问题，其归根结底都是数据库一致性问题，为了解决这些问题，MySQL 数据库是通过事务隔离级别来解决的。数据库系统提供了以下 4 种事务隔离级别供用户选择。

读未提交（Read-Uncommitted）

可能会发生脏读现象，也就是可能会读取到其它会话中未提交事务修改的数据。

已提交读（Read-committed）

只能读取其它会话中已经提交的数据，解决了脏读的问题。但是可能会发生不可重复读的现象，也就是可能在一个事务中对该数据进行了两次查询，并且查询到的结果不一致。

可重复读（Repeatable Read）

这个隔离级别，解决了不可重复读的问题，它确保了在同一事务的多个实例在并发读取数据时，会看到同样的数据行。但是可能会出现幻读，就是说当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取范围内的数据时，会发现有新的幻影行。

可串行化（Serializable）

所有的增删改查串行执行，它通过强制事务排序，解决相互冲突，从而解决了幻读问题。这个级别的事务隔离可能会导致大量的超时现象和锁竞争，效率十分低。

由此可见，事务隔离级别越高，并发问题就越小，但是并发处理能力就越差。

事务隔离级别和锁的关系

MYSQL 中的 InnoDB 存储引擎，支持事务的功能。像 MyISAM 就没有。MySQL 默认的隔离级别是：Repeatable Read。Oracle、SQLServer 默认的隔离级别是：Read Committed

  1. 锁是数据库实现并发控制的基础，事务的隔离级别是采用锁实现的，对相应操作加不同的锁，就可以防止其它事务同时对数据进行读写操作。
  2. 对用户来说，首先选择使用隔离级别，当隔离级别不能解决并发问题或需求时，可以进行手动设置锁（比如：悲观锁、乐观锁）。

MySQL 隔离级别的控制

MySQL 默认的隔离级别是：Repeatable Read，我们可以通过命令进行查看

	show variables like 'tx_isolation';
  或
  select @@tx_isolation;

设置事务隔离级别的命令如下：

set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';

MySQL 中的锁机制

首先我们看下 MySQL 中有几种类别的锁。

锁的分类

从操作行的粒度来分
  • 表级锁（IS 锁、IX 锁）
    • 每次操作会锁住整张表。锁定粒度大，发送锁冲突的概率比较高，并发度也是最低的
    • 应用场景：
      • MyISAM
      • InnoDB
      • BDB 等存储引擎中
  • 行级锁（S 锁、X 锁）
    • 每次操作会锁住一行数据。锁定的粒度小，发送锁冲突的概率最低，并发最高。
    • 应用场景：
      • InnoDB
  • 页级锁
    • 每次锁定相邻的一组记录。锁定的粒度大小介于表级锁和行级锁之间。开销和加锁的时间介于表级锁和行级锁之间，并发度一般。
    • 应用场景：
      • InnoDB
从操作的类型来分
  • 读锁（也叫 S 锁，共享锁）
    • 针对同一份数据，多个读操作可以同时进行而不会相互影响
    • 事务 A 对记录添加了 S 锁，可以对记录进行读操作，但是不能做修改，其它事务可以对该记录进行追加 S 锁，但是不能追加 X 锁，必须等记录的 S 锁全部释放完才可以追加 X 锁
  • 写锁（也叫 X锁，排它锁）
    • 当前写操作没有完成前，它会阻断其它写锁和读锁
    • 事务 A 对记录添加了 X 锁，可以对记录进行读和修改操作，其它事务不可以对该记录做读和修改操作。

其中表级锁有 IS 锁（意向读锁）、IX 锁（意向写锁）这两个所属于表级锁。在对表记录添加 S 或 X 锁时，会事先对表添加 IS 锁 或 IX 锁。

从操作的性能来分
  • 悲观锁
    • 在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据完成之前先锁定，再修改的控制方式。（共享锁和排他锁都是悲观锁的不同实现）
  • 乐观锁
    • 实现方式：对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发生冲突了，则提示错误信息。

行锁的实现原理

在 InnoDB 引擎中，我们可以使用行锁和表锁。InnoDB 行锁是通过对索引数据页上的记录加锁来实现的。

主要实现的算法有 3 种：Record Lock、Gap Lock、Net-key Lock

  • Record Lock（记录锁） ：锁定单个行记录的锁
    • RC、RR 隔离级别支持
  • Gap Lock（间隙锁） ：锁定的是索引记录间隙，确保索引记录的间隙不变
    • RR 隔离级别支持
  • Next-key Lock（记录锁和间隙锁的组合） ：同时锁住数据，并且锁住数据的前后范围
    • 也叫范围锁，RR 隔离级别支持

在 RR 隔离级别，InnoDB 对于记录都是先采用 Next-Key Lock 加锁，但是当 SQL 操作含有唯一索引时，Innodb 会对 Next-Key Lock 进行优化，将锁降级为 RecordLock，仅锁住索引本身而非范围 。

下面我们结合点案例去深入理解下：

  1. select ... from 语句：InnoDB引擎采用 MVCC 机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
  2. select ... from lock in share mode 语句：追加了共享锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。
  1. select ... from for update 语句：追加了排他锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。
  2. update ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock 锁。
  1. delete ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock 锁。
  2. insert 语句：InnoDB 会在将要插入的那一行设置一个排他的 RecordLock 锁。

悲观锁

指在数据处理过程中，将数据处于锁定的状态（一般使用数据库的锁机制实现悲观锁）。在 MySQL 中的数据库中行锁、表锁、写锁、共享锁、排他锁等都属于悲观锁。

表级锁

每次操作都锁住整张表，并发度最低，我们可以用一些命令查看：

-- 手动增加表锁
lock table 表名称 read|write，表名称2 read|write;
-- 查看表上加过的锁
show open tables;
-- 删除表锁
unlock tables;

表级读锁会阻塞写操作，不会阻塞读操作。但是表级写锁会阻塞读操作和写操作。当我们像当前表追加 read 锁时，当前连接和其它的连接都可以进行读操作；但是当前连接增删改操作会报错，其它连接增删除会被阻塞住。如果我们追加的是 write 锁，当前连接可以对表做增删改查操作，其它连接对该表所有操作都会被阻塞（包括查询）。

共享锁

共享锁又称为读锁（S 锁）。就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。只适用于用在查询语句上：

-- 添加读锁
select ... lock in share mode

事务使用了共享锁（读锁），只能读取，不能修改，修改操作会被阻塞。

排它锁（行级锁-写锁）

排它锁又称之为写锁，简称 X 锁。如果一个事务获取了一个数据行的排它锁，其它事务就不能对该行记录做其它操作，也无法获取到该行的锁（是能等待这个锁的持有者释放）。

InnoDB 会默认在 update、delete 语句上加上 for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么这个查询会锁住全表的记录。

使用方法：在 SQL 语句上，加上 for update

事务使用了排他锁（写锁、X 锁），当前事务可以进行读取和修改操作，其它事务不能修改，也不能获取记录锁。如果查询的时候没有使用到索引，将会锁住整个表。

乐观锁

乐观锁是相对于悲观锁而言的。需要开发者自己去实现。这个思想是这样的，在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理（不加锁处理）。而是在进行事务提交的时候再去判断是否有冲突了。

乐观锁实现原理
  • 使用版本字段（version）
    • 先给数据表增加一个版本 （version）字段，每操作一次，将那条记录的版本号加 1。version 是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其它事务修改。
  • 使用时间戳（TimeStamp）
    • 与使用 version 版本字段相似，同样需要给在数据表增加一个字段，字段类型用 timestamp 时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前的取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消当前操作。
案例

我们以下单为例，描述下乐观锁的使用：

  • 第一步：查询商品信息
select (quantity,version) from products where id =1;
  • 第二步：根据商品信息生成订单
insert into orders ...
insert into items ...
  • 第三步：修改商品库存
update products set quantity=quantity-1,version=version+1 where id=1 and version = #{version}

除了自己手动实现乐观锁之外，许多数据库访问框架也封装了乐观锁的实现。MyBatis 框架可以使用 OptimisticLocker 插件来扩展。

常见的死锁与解决方案

表级锁死锁
  • 场景：
    • 用户 A 访问 A 表（这个时候锁住了 A 表），然后又访问 B 表；此时另一个用户 B 访问了 B 表（锁住了 B 表），然后用户 B 企图访问表 A；这个时候由于用户 B 已经锁住了 B 表，用户 A 必须等到用户 B 释放了 B 表才能继续。同样用户 B 要等用户 A 释放 A 表的锁，这个时候就产生了死锁（如下图）。

  • 解决方案：
    • 这种死锁比较常见，多事由于程序的 BUG 产生的。首先我们仔细分析程序的逻辑，然后调整程序的逻辑。特别是对数据库进行多表操作时，尽量按照相同的顺序进行处理，这样的话可以避免同时锁定两个资源（如上面的 A 和 B 两张表，总是按照先 A 后 B 的顺序处理，必须同时锁定两个资源的时候，要保证正在任何时刻都应该按照相同的顺序来锁定资源。）
行级锁死锁
  • 场景 1：
    • 如果在事务中执行了一条没有索引条件的查询，引发了全表扫描，把行级锁升级成了全表记录的锁定（等价于表级锁），多个这样的事务执行后，就很容易发生死锁和阻塞，最终会导致应用系统越来越慢
  • 解决方案：
    • SQL 语句中不要使用太复杂的关联多表的查询，在使用的时候可以先用 explain 在数据库执行，对 SQL 语句做下分析，然后对有全表扫描和全表锁定的 SQL 语句，建立相应的索引进行优化
  • 场景 2：
    • 两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁
  • 解决方案：
    • 在同一个事务中，尽可能做到一次锁定所需要的所有资源
    • 按照 id 对资源排序，然后按顺序进行处理
共享锁转为排它锁
  • 场景 1：
    • 事务 A 查询一条记录，然后更新该条记录此时事务 B 也要更新该条记录，这个时候事务 B 持有了排它锁，而事务 A 持有了共享锁，必须先释放掉共享锁才可以获取，只能进行排队等待。事务 A 再执行更新操作的时候，此处发生会发生死锁，因为事务 A 需要持有排它锁才能进行更新操作。但是事务 B 持有了排它锁并且在对该条记录进行操作所以需要等待事务 B 释放排它锁，然后事务 B 需要等待事务 A 释放共享锁，才能去查询该条记录。
-- 事务 A
select * from dept where deptno=1 lock in share mode; -- 共享锁 1
update dept set dname = 'java' where deptno=1; -- 排它锁 3
-- 事务 B
update dept set dname = 'java' where deptno=1; -- 由于 1 有共享锁，没发获取排它锁，需要等待 2
  • 解决方案：
    • 对于页面上的按钮等控件，让他点击后立刻失效，不让用户重复点击，从而避免引发同一时刻对同一记录进行多次操作
    • 使用乐观锁进行控制。我们知道乐观锁机制避免了长事务中的数据库的加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在业务系统中实现的，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
排查死锁

我们如何去排查死锁呢？MySQL 提供了几个有关于锁的参数和命令，可以辅助我们优化锁操作。

  • 方式一：查看死锁日志
    • 通过命令 show engine innodb status \G 查看近期死锁的日志信息
    • 使用 explain 查看下 SQL 的执行计划
  • 方式二：查看锁状态变量
    • 通过命令 show status like 'innodb_row_lock%' 检查状态变量，分析系统中的行锁的竞争情况，如果等待次数高，而且每次锁等待的时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化方案
    • 相关的参数解释：
      • Innodb_row_lock_current_waits：当前正在等待锁的数量
      • Innodb_row_lock_time：从系统启动到现在锁定的总时间长度
      • Innodb_row_lock_time_avg：每次等待锁的平均时间
      • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
      • Innodb_row_lock_waits：系统启动后到现在总共等待的次数
avatar
文章
阅读
粉丝
相关推荐
mysql之锁与事务
4.0k阅读
 · 
156点赞
MySQL 笔记 - 事务&锁
7.3k阅读
 · 
337点赞
「MySQL高级篇」MySQL锁机制&事务
4.7k阅读
 · 
44点赞
Mysql事务隔离级别与锁机制
1.6k阅读
 · 
8点赞
MySQL 表锁和行锁机制
16k阅读
 · 
163点赞