<返回更多

MySQL事务处理与并发控制

2019-11-20    
加入收藏

概述

文章有点长哟，需要耐心看哟

数据库系统是现代商业世界有序稳定运行的基石，数据和数据承载的交易事件的结果不会因系统故障而损伤。数据库系统的核心技术是事务处理机制，也是有别于其他系统的关键特质。事务处理机制以其ACID特性保证了在各种环境下的数据一致性。同时：数据库系统又是多用户处理系统，为了提高数据库的性能，数据库常常会并发执行多个事务，事务的并发执行，虽然提高了性能，但是却带来了其他的数据一致性问题，因此，合适的并发控制技术是必须的，本文主要介绍MySQL的事务处理和并发控制技术。

1. 数据库事务系统概述

数据库管理系统是位于用户与操作系统之间的一层数据管理软件，数据库的事务处理机制是数据库的基石，那么为什么需要事务处理机制呢？

1.1 为什么需要事务处理机制

我们先看一个问题：账户A转账50元到账户B，过程如下：

MySQL事务处理与并发控制

账户转账示例

事务处理机制就是要通过实现事务的ACID特性来保证用户对数据的操作是安全的一致的，不管在什么环境下，都必须是安全的一致的。事务概念为应用程序开发人员提供了一个非常好的抽象，同时保证了数据的安全一致性。

1.2 什么是事务呢？具有哪些特性

前面我们通过例子简单的认识了事务处理的必要性，那么事务到底是什么呢？它的ACID特性该如何理解呢？

事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，事务是无法被分割的操作，事务必须作为一个完整的单元成功或失败，不可能存在部分完成的事务。

事务具有如下特性：

那么该如何理解这四个特性呢？

MySQL事务处理与并发控制

 

ACID意义上的隔离性意味着，同时执行的事务是相互隔离的：它们不能相互冒犯。传统的数据库教科书将隔离性形式化为可串行化（Serializability），这意味着每个事务可以假装它是唯一在整个数据库上运行的事务。数据库确保当事务已经提交时，结果与它们按顺序运行（一个接一个）是一样的，尽管实际上它们可能是并发运行的。不过在实际生产环境中，很少使用可串行化，因为这会使得性能大幅降低。

以上介绍了数据库的四大特性，接下来看看为什么需要并发控制呢？

1.3 为什么需要并发控制

我们已经知道，数据库会同时被多个用户（多个事务）访问，如果多个事务访问不同的数据，那么事务之间不会有任何干扰，如果多个事务操作相同的数据，那么各个事务之前就会互相干扰违背事务的隔离性，导致数据不一致。因此，为了满足隔离性，使得各个并发的事务互不干扰，数据库系统必须对并发的事务进行控制，即：并发控制。

那么不进行并发控制会有哪些问题呢？

事务之间的关系有如下三种：

接下来看看三种常见的读异常：

MySQL事务处理与并发控制

读数据异常

如上图：
脏读：如果没有并发控制机制，事务T2在Time1处读到了无效的数据（因为事务T1在Time2时中止了）
不可重复读：事务T1在Time3处发生了不可重复读（同一个事务，在事务执行的过程中，同一行数据，读到了不同的值）
幻读：事务T1在Time3处发生了幻读。

没有并发控制的情况下，读--写操作不仅会造成三种读异常，写写操作也会造成写异常，下边我们看看写-写操作导致的异常：

MySQL事务处理与并发控制

写数据异常

如上图： 脏写：事务T1在time3时刻回滚掉了不是自己修改的数据，即事务T1在time3出发生了脏写。
丢失更新：事务T1在time3处覆盖掉了不是自己修改的数据，即T1在time3出发生了丢失更新异常，如果是T2在time3时提交，道理也是一样的，只不过在T2上发生了丢失更新。

除了以上读写异常，还存在一种**语义约束（事务的特性是要保证语义约束的）**引发的数据异常--写偏序：为了理解写偏序，我们先看一个例子（该例子来自《数据密集型应用系统设计》一书）：

MySQL事务处理与并发控制

写偏序

在两个事务中，应用首先检查是否有两个或以上的医生正在值班；如果是的话，它就假定一名医生可以安全地休班。由于两次检查都返回 2 ，所以两个事务都进入下一个阶段。Alice更新自己的记录休班了，而Bob也做了一样的事情。两个事务都成功提交了，现在没有医生值班了。违反了**至少有一名医生在值班（数据库的一致性）**的要求。

写偏序的特征：

这种异常称为写偏序。它既不是脏写，也不是丢失更新，因为这两个事务正在更新两个不同的对象（Alice和Bob各自的待命记录）。

由上可见：如果不进行并发控制，会使得多个并发执行的事务造成各种各样的问题，因此需要并发控制技术。

那么是不是只要简单的对各个事务进行排队，并一个接一个的执行就可以了呢？是的，这样的确可以，但是，这样会使得数据库的性能非常差，为了提高数据库的性能，SQL标准定义了四种隔离级别，这四种隔离级别分别在三种数据读异常前后插入，对应的解决了读异常的发生。

1.4 在正确性和效率之间做权衡

为了提高数据库的效率，同时兼顾正确性，SQL标准定义了四种隔离级别，分别解决各种读数据异常：
这四种隔离级别如下：

实际应用中，已提交读和可重复度比较常用（MySQL默认情况下为可重复性读）。
四种隔离级别都不允许脏写和丢失更新发生。

2 ACID的实现技术

在第一节，学习了数据库的基本概念，特别是事务及其特性，本节梳理一下实现事务和ACID特性的技术。

2.1 实现原子性的技术

  1. 事务管理
begin;
rollback;
commit;
  1. 并发控制，避免并发事务之间的干扰
  2. 日志技术

2.2 实现隔离性的技术

并发控制技术。主要包含：

  1. 加锁
  2. MVCC

2.3 实现 持久性的技术

日志技术

3 MySQL事务处理

3.1 MySQL事务模型

MySQL本质上是一个数据库软件的框架，支持插件化存储引擎，MySQL的事务处理也与具体的存储引擎相关，MySQL只是定义了事务处理的框架具体实现由存储引擎完成。因此MySQL是否支持事务，是与存储引擎相关的。从mysql-5.5.5开始,InnoDB作为默认存储引擎，该存储引擎是支持事务的，同时，InnoDB也是MySQL做为OLTP场景标准的存储引擎，这里主要研究InnoDB的事务模型。

MySQL的事务管理语句如下：

START TRANSACTION
 [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
 WITH CONSISTENT SNAPSHOT
 | READ WRITE
 | READ ONLY

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

以上这些语句提供了对事务的控制：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

mysql看到START TRANSACTION，将会禁用自动提交，直到用COMMIT或者ROLLBACK结束事务，结束之后，自动提交模式将恢复到之前的状态。

为了要显式禁用自动提交模式，请使用以下语句：

SET autocommit=0;

通过将autocommit变量设置为零禁用自动提交模式后 ，对事务安全型表（例如InnoDB或NDB表）的更改不会立即生效。你必须使用COMMIT将更改存储到磁盘或ROLLBACK忽略更改。autocommit是一个会话变量，必须为每个会话设置。要为每个新连接禁用自动提交模式，需要修改系统变量。

一些语句不能回滚。通常包括数据定义语言（DDL）语句。

MySQL事务有三种提交模式：

MySQL事务有三种回滚模式:

3.2 InnoDB日志技术

日志技术是实现数据库原子性和持久性的核心技术，InnoDB通过UNDO LOG、REDO LOG、doublewrite buffer实现事务的原子性和持久性。

3.2.1 REDO LOG

重做日志是在崩溃恢复期间使用的基于磁盘的数据结构，用于纠正由于写入不完整导致数据错误，从而保证已提交事务的原子性和持久性。

为什么需要redo log？
为了提高数据库的性能，MySQL 中使用了大量的内存 Cache（Innodb 的buffer poll） ，对数据的修改操作会先修改内存中的 Page，但这些页不会立刻同步磁盘，这时内存中的数据已经和磁盘上的不一致了，我们称这种 Page 为脏页。试想一下这时候如果数据库宕机了，内存中这部分被修改的数据记录就丢失了，重启后也没办法恢复。因此需要redo log，即使数据库不用缓存，也需要使redo log，比如：一个事务写磁盘写了一半，数据库重启了，重启之后数据库根本无法知道事务进行到何种程度了。

因此为了保证数据的安全性，在修改内存中的 Page 之后 InnoDB 会写 redo log，因为 redo log 是顺序写入的，而众所周知磁盘的顺序读写的速度远大于随机读写，因此这部分日志写操作对性能影响较小。然后，InnoDB 会在事务提交前将 redo log 保存到磁盘中（可以通过innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。该参数默认值为1，表示事务提交必须进行一次fsync操作，还可以设置为0和2。0表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成，2表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync操作。由此可见，设置为0时，性能最高，但是丧失了事务的一致性）。这里所说的 redo log 是物理日志而非逻辑日志，记录的是数据页的物理修改（比如：将某个页面的某个偏移量处的值加2），而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置）。

当数据库意外重启时，会根据 redo log 进行数据恢复，如果 redo log 中有事务提交，则进行事务提交修改数据。

3.2.2 UNDO LOG

与 redo log 不同，undo log 一般是逻辑日志，根据每行记录进行记录。例如当 DELETE 一条记录时，undo log 中会记录一条对应的 INSERT 记录，反之亦然当 UPDTAE 一条记录时，它记录一条对应反向 UPDATE 记录。

当数据被修改时除了会记录 redo log 还会记录 undo log，通过 undo log 一方面可以实现事务回滚，另一方面可以根据 undo log 回溯到某个特定的版本的数据，InnoDB实现 MVCC 的功能时就会用到undo log。

3.2.3 双写（doublewrite buffer）

有了redo log可以用来恢复数据，那么doublewrite buffer是做什么的呢？doublewrite buffer也是用于实现数据的持久性的。

因为InnoDB并不会将整个页面的内容写入重做日志，而是记录的对页面的操作，例如将某个偏移量处的值加2，使用重做日志进行恢复的基础是表空间中的实际数据页面在内部是完整的一致的，它是哪个页面版本无关紧要 ，但是如果页面不一致，则无法继续恢复，因为你的基础数据就是不一致的。为此引入了Doublewrite Buffer来解决问题。

但是为什么会出现“表空间的数据页面不完整呢？” 这是因为会出现InnoDB部分页面写入的情况：InnoDB中，在将缓冲池中的数据刷新到磁盘时是以页面（InnoDB的页面，通常为16KB）为单位的，这时可能会出现部分页面写入的问题。所谓部分页面写入是指向操作系统提交的页面写入请求仅部分完成。例如，在16K 的Innodb页面中，只有第一个4KB（文件系统的块通常为4KB）的块被写入磁盘，其他部分保持原来的状态。最常见的部分页面写入一般在发生电源故障时发生。也可能发生在操作系统崩溃时。另外，如果使用软件RAID，页面可能会出现在需要多个IO请求的条带边界上。如果硬件RAID没有电池备份，电源故障时也会发生这种情况。如果对磁盘本身发出单个写入，即使电源掉电，在理论上也应完成写入，因为驱动器内部应该有足够的电源来完成它。但是真的很难检查是否总是这样，因为它不是部分页面写入的唯一原因。

理解了为什么需要Doublewrite Buffer，也就不难理解Doublewrite Buffer如何工作了。具体来说就是：你可以将Doublewrite Buffer视为系统表空间中的一个短期日志文件，它包含100个页的空间。当Innodb从Innodb缓冲池中刷新页面时，InnoDB首先会将页面写入双写缓冲区（顺序），然后调用fsync()以确保它们保存到磁盘，然后将页面写入真正的数据文件并第二次调用fsync()）。现在Innodb恢复的时候会检查表空间中数据页面的内容和Doublewrite Buffer中页面的内容。如果在双写缓冲区中的页面不一致，则简单地丢弃它，如果表空间中的数据页面不一致，则从双写缓冲区中恢复。

4 MySQL并发控制

前面我们已经探讨了需要并发控制技术的原因，本节介绍InnoDB使用的并发控制技术。
InnoDB的并发控制技术包含两种：

4.1 基于锁的并发

Innodb提供四种粒度的锁：

InnoDB锁的想容性矩阵如下：

MySQL事务处理与并发控制

 

InnoDB的加锁机制符合SS2PL（增强两阶段封锁协议），即：
每个事务分为两个阶段：

show status like 'innodb_row_lock%'; 
select * from information_schema.innodb_locks; -- 5.7即以前
select * from performance_schema.data_locks; -- 8.0即以后

来查看行锁的争用情况。更多信息，可以参考MySQL官方手册。

意向锁作用解读

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

意向锁分为两种： 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

即：意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。换言之，数据库引擎看到某表上有事务T持有IX，就可以得知事务T必然持有该表中某些数据行的排他锁。共享锁同理。后面会看到，这个对性能提升意义非凡。

意向锁可以提高性能，例如：
事务 A 获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

此时 users 表存在两把锁：users 表上的意向排他锁与 id 为 6 的数据行上的排他锁。
事务 B 想要获取 users 表的共享锁：

LOCK TABLES users READ;

此时事务 B 检测事务 A 持有 users 表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁，那么事务 B 对 users 表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁(如果是一张大表，这个检查的性能是非常低的)。

4.1.1 锁的种类

InnoDB为了实现基于锁的并发控制，实现了多种类型的锁，包括在聚集索引上实现的行级记录锁，间隙锁，记录锁+间隙锁组合成的范围锁，插入意象锁。这些锁的实现依赖于InnoDB的聚集索引额封锁机制。

4.2 基于MVCC的并发控制

InnoDB在锁的基础上实现了MVCC，增加了并发度。
概括来说就是当一个事务开始时，它被赋予一个唯一的，永远增长的事务ID（ txid ）。每当事务向数据库写入任何内容时，它所写入的数据都会被标记上写入者的事务ID，一个事务只能看到在他之前提交的事务修改的数据。

4.3 InnoDB的四种隔离级别

InnoDB实现了四种隔离级别，InnoDB的四种隔离级别的语义除了可重复读之外与标准隔离级别一致。默认情况下为可重复性读，不同之处在于InnoDB的可重复读通过MVCC和范围锁（Next-Key lock）避免了幻读。不同的场景下避免幻读的机制不一样。

5、隔离级别与数据异常

本节演示一下各种隔离级别下的数据异常。
分别启动两个客户端client1，client2连接到mysql服务器，client1为主事务端，在此改变隔离级别。
创建表如下：

create table bluesea(c1 int, c2 varchar(32), c3 int, primary key(c1));

5.1 可串行化隔离级别

MySQL事务处理与并发控制

可串行化

client2执行insert后，阻塞，这时在client3查看加锁情况如下：

mysql> select * from data_locks;
+--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 18262823:7872 | 18262823 | 50 | 18 | test | bluesea | NULL | NULL | NULL | 139693811345432 | TABLE | IX | GRANTED | NULL |
| INNODB | 18262823:6815:4:1 | 18262823 | 50 | 18 | test | bluesea | NULL | NULL | PRIMARY | 139693811342392 | RECORD | X | WAITING | supremum pseudo-record |
| INNODB | 421168866928480:7872 | 421168866928480 | 49 | 209 | test | bluesea | NULL | NULL | NULL | 139693811339480 | TABLE | IS | GRANTED | NULL |
| INNODB | 421168866928480:6815:4:1 | 421168866928480 | 49 | 209 | test | bluesea | NULL | NULL | PRIMARY | 139693811336440 | RECORD | S | GRANTED | supremum pseudo-record |
+--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
4 rows in set (0.00 sec)

5.2 可重复读隔离级别

不存在幻读，mysql通过间隙锁解决了幻读问题，sql标准在这个隔离级别下允许幻读。

MySQL事务处理与并发控制

可重复读

client2的事务在更新的时候会加锁，client1的不加锁：

select * from data_locks;
+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 18262831:7872 | 18262831 | 50 | 24 | test | bluesea | NULL | NULL | NULL | 139693811345432 | TABLE | IX | GRANTED | NULL |
| INNODB | 18262831:6815:4:2 | 18262831 | 50 | 25 | test | bluesea | NULL | NULL | PRIMARY | 139693811342392 | RECORD | X | GRANTED | 1 |
+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

5.3 已提交读隔离级别

存在幻读，不可重复读

MySQL事务处理与并发控制

已提交读

5.4 未提交读隔离级别

存在脏读

MySQL事务处理与并发控制

未提交读

5.5 写偏序再讨论

写偏序一般遵循如下模式：

这个写入的效果改变了步骤2 中的先决条件。换句话说，如果在提交写入后，重复执行一次步骤1 的SELECT查询，将会得到不同的结果。因为写入改变了符合搜索条件的行集（现在少了一个医生值班）

MySQL的可重复读避免不了写偏序，为了避免写偏序，需要使用可串行化隔离级别或者在可重复读隔离级别下使用(select ... for update)：

BEGIN TRANSACTION;
SELECT count(*) FROM doctors WHERE on_call = TRUE
	AND shift_id = 1234 FOR UPDATE;
UPDATE doctors SET on_call = FALSE
	WHERE name = 'Alice' AND shift_id = 1234;
COMMIT;

5.5.1 写偏序异常举例

首先构造数据：

我们这里举出的例子还是接着之前的医生值班的例子。

CREATE TABLE `docts` (
 `id` int(11) NOT NULL,
 `name` varchar(16) DEFAULT NULL,
 `status` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 insert into docts values(1, 'alice', 1);
 insert into docts values(2, 'bob', 1);
 insert into docts values(3, 'test', 0);
mysql> select * from docts;
+----+-------+--------+
| id | name | status |
+----+-------+--------+
| 1 | alice | 1 |
| 2 | bob | 1 |
| 3 | test | 0 |
+----+-------+--------+
3 rows in set (0.00 sec)
select @@transaction_isolation,@@global.transaction_isolation;
+-------------------------+--------------------------------+
| @@transaction_isolation | @@global.transaction_isolation |
+-------------------------+--------------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-------------------------+--------------------------------+

status == 1表示在值班，0表示未值班

接下来看看在可重复读下写偏序的发生：

MySQL事务处理与并发控制

写偏序

set @v1=(select count(*) from docts where `status`=1 );的加锁情况
mysql> select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_MODE,LOCK_STATUS from data_locks;
+-----------+---------------+-------------+-----------+-------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_MODE | LOCK_STATUS |
+-----------+---------------+-------------+-----------+-------------+
| 49 | test | docts | IS | GRANTED |
| 49 | test | docts | S | GRANTED |
| 49 | test | docts | S | GRANTED |
| 49 | test | docts | S | GRANTED |
| 49 | test | docts | S | GRANTED |
+-----------+---------------+-------------+-----------+-------------+
5 rows in set (0.00 sec)

5.5.2 写偏序异常解决

MySQL事务处理与并发控制

避免写偏序

set @v1=(select count(*) from docts where `status`=1 for update);的加锁情况
mysql> select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_MODE,LOCK_STATUS from data_locks;
+-----------+---------------+-------------+-----------+-------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_MODE | LOCK_STATUS |
+-----------+---------------+-------------+-----------+-------------+
| 49 | test | docts | IX | GRANTED |
| 49 | test | docts | X | GRANTED |
| 49 | test | docts | X | GRANTED |
| 49 | test | docts | X | GRANTED |
| 49 | test | docts | X | GRANTED |
+-----------+---------------+-------------+-----------+-------------+
5 rows in set (0.00 sec)
声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号