数据库事务浅析

“那么古尔丹，代价呢？”

看完下面的内容，或许会对不同事务级下解决并发问题所带来的代价有些许了解，或只是记住上面那句话🤣。

关于事务的文章，本篇是主要介绍 MySQL，以后要是有机会的话再介绍介绍 PostgreSQL 的。

事务基本概念

首先的需要了解下关于事务的基本概念。

事务的四大特性 ACID

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

并发事务中的问题

脏读 - Dirty Read：事务 A 修改了一个数据，但未提交，事务 B 期间读到了事务 A 未提交的更新结果，如果事务 A 提交失败，事务 B 读到的就是脏数据；即事务期间的数据的修改未隔离，直接持久化到数据库，导致其他事务能够读到事务期间的临时、中间数据，归结为事务期间的数据没有于持久化数据相隔离，能够读取到事务临时数据。
不可重复读 - Non-repeatable Read：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务 B 期间读到事务 A 提交前的结果，事务 A 提交了修改数据，事务 B 后再次读取该数据，读到的结果和之前不一致；即一个事务内能够读取到被其他事务不断提交更新修改（非增加）的数据，导致事务期间某一条数据是时变的，归结为被读取的行数据（及相应的相关行数据）没有被锁。
幻读 - Phantom Read：在同一个事务中，同一个查询多次返回的结果不一致。事务 A 新增了一条记录，事务 B 在事务 A 提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。即一个事务内能够读取到被其他事务新增的数据，归结为读取的数据的表没有被锁。

需要注意的是：可重复读重点在于 update 和 delete，而幻读的重点在于 insert，幻读是针对于读操作 select 来说的，是否试用于 insert 有待考量。

事务隔离级别

针对上面的并发事务中的问题，一般有如下的事务隔离级别：

读未提交 - Read Uncommitted：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
读提交 - Read Committed：只有在事务提交后，其更新结果才会被其他事务看见。即增加事务期间的数据隔离。可以解决脏读问题。
可重复读 - Repeated Read：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
串行 - Serialization：事务串行化执行，隔离级别最高，牺牲了系统的并发性，可以解决并发事务的所有问题。

为了方便后面使用简称表明隔离级别，如 RR 就是 Repeated Read。

每一个事务隔离解决的事务问题的一个通用的标准的定义 (ANSI SQL 1992) 如下：

Isolation Level	Dirty Read	NonRepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not Possible	Possible	Possible
Repeatable read	Not Possible	Not Possible	Possible
Serializable	Not Possible	Not Possible	Not Possible

其实关于数据库的隔离的标准其实是一直在发展的，参见数据库事务隔离发展历史。但事实上各个数据实现的数据隔离不一定四种状态都有，如 PostgreSQL 就只没有 Read Uncommitted 级别，同时基于性能代价的考虑每个数据库对每个级别的解决的事务问题也和标准的实现不一定一一对应，这一点在后面的 MySQL RR 级别会有体现。

不同的隔离级别分别在不同程度上解决的并发事务中存在的问题。隔离级别越高安全，但是一般的性能损失也会越大，这有可能就是开篇问的那句话的答案。所以针对不同的使用场景，使用恰当的隔离级别，如何选用合适的隔离级别，那么就得了解下 MySQL 的事务了。

锁机制

那么数据库如何实现事务的各种级别呢？答案就是锁的运用。

通常来说，乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）。正如其名，悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会。

乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观锁也是对于处理并发下的读写者问题区别于完全使用锁的另一种思路。

乐观并发控制不会使用锁机制，针对不同的乐观程度又不同的实现，不同乐观程度的机制本质的区别在于，检查或预判冲突的时机。

按照其思想一般有 CAS、MVCC 等，由于 CAS 存在 ABA 问题，所以通常在数据库的实现中会使用 MVCC，即在提交数据时候通过版本标识是否变化来判断本次提交是否有效，版本标识的实现通常有：

基于自定义的版本号字段，每次修改自增更新
基于自更新的时间戳字段

乐观并发控制相信事务之间的数据竞争 (data race) 的概率是比较小的，因此尽可能直接做下去，直到提交的时候才检查提交，所以不会产生任何锁和死锁。这样的话，有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行（这时候一般会拍一个快照），经过修改以后写回数据库，晚结束的事务会发现当前的数据和快照的版本号不一致，这时候一般的策略可以尝试自旋一会儿，如果还是失败直接回滚抛出错误。

当大量的并发时候会造成大量的事务失败性能反而不如悲观锁，所以，乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。

版本号的乐观锁 SQL 模拟：

1
2
3
4
5
6


/* 查询出商品信息、需要带上版本 */
SELECT (status, version AS versionValue) FROM t_goods WHERE id=0;
/* 根据商品信息生成订单、修改商品版本号 */
UPDATE t_goods
    SET status=2, version=versionValue+1
    WHERE id=0 AND version=versionValue;

锁的种类

这一部分谈一谈在数据库中锁的具体的实现的种类，为之后的叙述做准备。

共享锁和排它锁是可以解决读写者并发问题的两种锁：

共享锁 (Share lock, S lock)，共享锁不阻止其它 session 同时读资源，但阻止其它 session update。
排它锁 (Exclusive lock, X lock)，排他锁表示对数据进行写操作，如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

如果定位条件有索引（能给个较快找到相应的行数据）排它锁的存在时间会很短，如果定位条件没有索引（查找慢）这是时候会全表扫描、排它锁存在的时间会较长，这一点后面会有分析。

上述的查找慢可能会导致死锁，即两个更新事务，需要先选取再更新，筛选时候两个事务的共享锁同时加上，两个事务都在等待其他事务释放共享锁以便自己升级为排他锁进行数据更新，解决方式可以直接在 select 的时候就加上排它锁即 select ... for update。

对于两种基本的锁，通常会用一个矩阵来描述他们之间的冲突关系：

	S	X
S	兼容	不兼容
X	不兼容	不兼容

一般的还会有下面的几种类型的锁：

更新锁 Update lock，“我现在只想读，你们别人也可以读，但我将来可能会做更新操作，我已经获取了从共享锁（用来读）到排他锁（用来更新）的资格”。一个事物只能有一个更新锁获此资格。

意向锁 Intent locks，即该表中是否存在排它锁，当一个表中的某一行被加上排他锁后，该表就不能再被加表锁。数据库程序如何知道该表不能被加表锁？一种方式是逐条的判断该表的每一条记录是否已经有排他锁，另一种方式是直接在表这一层级检查表本身是否有意向锁，不需要逐条判断。

计划锁 Schema locks，用 jdbc 向数据库发送了一条新的 sql 语句，数据库要先对之进行编译，在编译期间，也会加锁。

记录（行）锁 Record locks，mysql 中用于锁定数据用于更新。

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

间隙锁 Gap locks，mysql 中范围选择更新时候会使用。

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks，记录锁和间隙锁的结合，用于非唯一索引，如有 SELECT...UPDATE 一个索引为 10 的记录的值，这个索引对应两个记录其 pk 分别为 5、10，那么就会锁住对应记录 5、10 的数据，同时会在 (5, 9)、(9, 正无穷）两个区间内加间隙锁。

自增锁 AUTO-INC Locks，主键如果是自增的，插入数据的时候为了正确自增会锁住整张表。

锁的粒度

锁的粒度就是指锁的生效范围，范围有行锁，页锁，整表锁. 锁的粒度同样既可以由数据库自动管理。

对于 InnoDB，只有通过索引检索数据才会使用行锁，可以通过 EXPLAIN SELECT * FROM test_table 可以看到其操作的行数，对于 MyISAM 来说只有表锁这一个粒度。

MySQL 的事务实现

前面的 SQL 标准的事务隔离与事务问题的对应提到过不同数据库的实现对应不一定符号标准。如 MySQL 下的 REPEATABLE READ 是可以利用解决 gap locks 和 next-key locks 解决更新时候的幻读，同时利用快照“解决” select 时候幻读，参考官方文档。

InnoDB 存储引擎，为了提高并发，在 RR 和 RC 级别使用了 MVCC 机制，即在并发事务时，通过读取数据行的历史数据版本，通过不加锁来提高并发的一致性读 (Consistent Nonlocking Read)。MySQL 的 MVCC 的版本的实现具体是通过 DATA_TRX_ID（产生当前记录项的事务 ID) 和 DATA_ROLL_PTR（数据何时过期或者被删除）这两个隐藏列来实现的。

如在 RR(REPEATABLE READ) 级别下其流程：

SELECT 时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号的记录
INSERT 时，保存当前事务版本号为该行的创建版本号
DELETE 时，保存当前事务版本号为该行的删除版本号
UPDATE 时，插入一条新纪录，保存当前事务版本号为该行创建版本号，同时保存当前事务版本号到原来删除的行

同时在 MVCC 并发控制中，读操作可以分成两类：快照读 (snapshot read) 与当前读 (current read)。

快照读：简单的 select 操作属于快照读，不加锁。（也有例外：显式加锁读）。
当前读：特殊的读操作（显式加锁），插入/更新/删除操作，属于当前读，需要加锁。

在下面的具体部分探究之前，可以先简单的说下 innodb 的事务实现机制，RU 级别这里就忽略了；RC 级别读采用即时快照，更改会使用锁；RR 级别读采用快照，更改会使用锁；Serializable 读和更改都使用锁；而不同级别的锁的使用又各不相同，下面娓娓道来。

MySQL 怎么加锁

为了方便下面的叙述，新建一张表，结构如下，如不特殊注明，都是在 MySQL 5.7.20 版本下测试。

1
2
3
4
5
6
7
8
9


CREATE TABLE `test` (
    `id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    `tel` INT UNSIGNED NOT NULL,
    `socre` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`tel`),
    INDEX(`socre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

对于 innodb 来说，不同事务隔离级别和索引类别最后会使用不同的加锁方式，事务中获取的锁在事务结束后才会释放。如下面的两条 SQL:

1
2


select * from test where col_name = xxx;
delete from test where col_name = xxx;

在下面的几个前提下会有不同的加锁策略，参考 MySQL 加锁处理分析：

前提一：col_name 列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：col_name 列如果不是主键，那么 col_name 列上有索引吗？
前提四：col_name 列上如果有二级索引，那么这个索引是唯一索引吗？
前提五：两个 SQL 的执行计划是什么？索引扫描？全表扫描？

需要注意的是，在 RR 和 RC 的隔离情况下，select 操作均不加锁，采用的是快照读，所以下面的分析着重与 delete 语句。

id 主键+RC 情况

对符合删除的条件的记录加 X 锁即可。注意的是这时候的脏读的避免是基于快照来实现的，即每个事务都在自己快照里进行修改。

id 唯一索引+RC 情况

需要给唯一索引加 X 锁，同时由于要回表，也需要给对应的聚簇索引（主键索引）记录加 X 锁。

id 非唯一索引+RC 情况

满足条件的 id 的二级索引（非唯一索引）加 X 锁，同时对回表的主键索引的记录加 X 锁。

id 无索引+RC 情况

若 id 列上没有索引，会走聚簇索引的全扫描进行过滤，即每条记录，无论是否满足条件，都会被加上 X 锁。

但是为了效率考量，MySQL 做了优化，对扫描到的记录加锁之后，会使用 unlock_row 方法，把不满足条件的记录放锁，不过这就违背了 2PL 的约束。

2PL （Two-Phase Locking，二阶段锁），说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

id 主键+RR 情况

与 id 主键+RC 下的情况一样，对 delete 的记录加 X 锁即可。

id 唯一索引+RR 情况

同 id 唯一索引+RC 情况，对满足条件的二级索引和主键索引记录加锁。

id 非唯一索引+RR 情况

这里就需要注意了，由于非唯一索引，所以可能会有多条记录。考虑到 B+ 树索引的有序性，满足条件的项在二级索引上一定是连续存放的，除了对查询到记录加 X 锁，为了防止被插入新纪录，需要在这些记录的间隙加锁。这时候其他事务是无法在其区间内进行插入，会得到超时的错误。同时，如果以主键索引列为条件范围更新数据，也会存在间隙锁、下一键锁的。

同时这时候你可能会注意到，RR 级别的 select 也可能选取到多个记录，怎么保证不发生幻读？答案就是前面提到的快照读。

同时这又引入另外的一个问题，前面的 RC 级别也是快照读，那解决幻读了么？答案是：没有，因为虽然 MySQL 在 RR 和 RC 级别利用快照实现了一致性读 (consistent read)，准确的说应该是非阻塞的一致性读，但会在不同级别下有些许区别：

总的来说，事务总能够读取到，自己写入 (update /insert /delete) 的行记录。
RC 下，快照读总是能读到最新的行数据快照（已提交的），即事务在每次 Read 操作时，都会建立 Read View（快照）。
RR 下，某个事务首次 read 记录的时间为 T，未来不会读取到 T 时间之后已提交事务写入的记录，以保证连续相同的 read 读到相同的结果集，即事务在第一个 Read 操作时，会建立 Read View。

具体可以可以看官方文档 Consistent Nonlocking Reads，也可以根据下面的验证下 RC 级别下的每次读都会新建快照：

事务 A	事务 B
set session transaction isolation level read committed;	insert into test values(1,‘Tom’,11,60);
start transaction;
select name from test where id=1; // Tom
	update test set name=‘Jack’ where id=1;
select name from test where id=1; // Jack
commit;

需要补充的一点是，这里提到的幻读和一致性读可能不能完全 match，RR 级别在事务期间可以通过显式加锁来读取其他事务里已提交的数据，如使用 select * from test lock in share mode;，这是因为加锁读的时候不是使用的快照读而是当前读了，可以尝试如下。

事务 A	事务 B
set session transaction isolation level repeatable read;
start transaction;
	insert into test values(2,‘Jack’,12,60);
select name from test where id=2; // Jack
	update test set name=‘Tony’ where id=2;
select name from test where id=2; // Jack
select name from test where id=2 lock in share mode; // Tony
commit;

对于事务 A ，第一次 select 建立快照，所以是可以看到事务开始之后加入的内容的；第二次是虽然数据已更新，但是读的快照了；第三次显示加锁，就是直接当前读，可以读到更改后的数据。

id 无索引+RR 情况

此时删除操作，聚簇索引上的所有记录，都会被加上了 X 锁。其次，聚簇索引每条记录间的间隙 (GAP)，也同时被加上了 GAP 锁。相比 id 无索引+RC 值锁行可能会糟糕很多。

Serializable 情况

在 RC，RR 隔离级别下，select 都是快照读，不加锁的。但是在 Serializable 隔离级别，读也会加读锁 S，也就是不再使用 MVCC 并发控制，读写都为悲观锁控制。

这时候可能会有另外的疑问了，串行和 RR 有什么区别？既然“都解决了幻读”，如果仔细看了 id 非唯一索引+RR 情形下的读取时候的分析，可以知道，其 RR 幻读在 select 时候是使用的快照实现的，可以通过显式加锁使用当前读产生幻读，也就是这时候的幻读是解决不完全的，官方文档摘抄：

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT … FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction.

SELECT … FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

所以 Serializable 级别实现是强一致性的，即抛弃快照读，完全基于锁，即：读加共享锁，写加排他锁。

MySQL 默认级别

MySQL 默认的隔离级别设为可重复读 (Repeatable Read)，前面提到 Serializable 是强一致性事务，性能不佳！互联网的分布式方案，多采用最终一致性的事务解决方案！

RR 级别可以通过显式的加锁如 select ... for update, select ... lock in share lock 可以有与 Serializable 级别的查询一样的行为。这就是为什么默认是 RR 级别，因为更新操作已是避免幻读的，那么在某些重要的场合可以显式加锁自行实现 Serializable 级别的查询一样的行为来保证一致性。

同时对比 RC 和 RR ，有一种说法是在互联网界一般采用 RC 级别，互联网项目中 mysql 应该选什么事务隔离级别，其论点上面的锁分析里也提到过：

在 RR 隔离级别下，存在间隙锁，导致出现死锁的几率比 RC 大的多。
在 RR 隔离级别下，条件更新语句列未命中索引会锁表！而在 RC 隔离级别下，只锁行。
在 RC 隔离级别下，半一致性读 (semi-consistent) 特性增加了 update 操作的并发性！

另外，行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这也是对上面的几个锁表的情况的解释。

MySQL MVCC

既然上面提到了 MVCC，那么这里在继续深化下。consistent_read 这里面提到：

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

上面的话在前面 id 非唯一索引+RR 的部分也分析过，也就是开始拍快照的时候是取决于事务里的第一条语句的。或者在启动事务时候加上参数 WITH CONSISTENT SNAPSHOT 这样就是 begin/start transaction 语句即事务一开始就拍下快照，注意的是这个参数不会改变隔离级别，所以说只适合拥有 consistent_read 的事务级别即 RR。

事务 A	事务 B
set session transaction isolation level repeatable read;
start transaction with consistent snapshot;
	insert into test values(3,‘Scott’,13,60);
select name from test where id=3; // Empty set
commit;

可以看到上面的使用了 with consistent snapshot 的事务 A 中在开始时候就拍下了快照，所以事务开始之后的其他修改内容对 A 不可见，不同于 id 非唯一索引+RR 时候的第一条读触发快照。

那么其他的隔离级别在使用 with consistent snapshot 开始事务会发生什么呢？在 MySQL 5.7 下会出现 warning 提示该参数被忽略，也就是 with consistent snapshot 只会对 RR 有效。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning |  138 | InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+

既然 RR 运用了快照读，那么可以设想下，如果 socre 是 60，两个事物都在自己的快照上将 socre + 1 更新，那么最后结果是 61 还是 62？

事务 A	事务 B
	insert into test values(4,‘Bob’,14,60);
start transaction with consistent snapshot;	start transaction with consistent snapshot;
update test set socre=socre+1 where id=4;
	update test set socre=socre+1 where id=4; // 阻塞
	// 如果上面阻塞时间过长会超时错误
commit;
	// 事务 A 提交后，B 的 update 才会执行
	commit;

也就是最后会变为 62，原因很简单，该数据的行级 X 锁已经被事务 A 提前获取，提交之前都不会释放，事务 B 要想再加上 X 锁修改就必须等待，等到 A 提交、释放锁之后，B 却是在最新的 61 数据上进行更改而不是快照值 60 这是为什么？原因是之前提到的 RR 下显示加锁可以“击穿快照”直接当前读，那么这里的更新操作其实是读+写的操作，整个操作是一个锁，那么有锁的读就会使用当前读，即读到事务 A 提交之后的数据。

同时这里会涉及到一个问题，就是读时或者事务开始是会生成快照，那么这个快照是如何保存的？答案是 undo log，具体可以参考 MySQL · 引擎特性 · InnoDB undo log 漫游这片文章，简要的说的话就是：

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records.

MySQL 事务语句

设置、查看当前事务的隔离级别

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 设置当前回话、全局的事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
-- MySQL<5.7.20，查看全局、当前会话、当前的事务隔离级别
SELECT @@global.t_isolation;
SELECT @@session.t_isolation;
SELECT @@t_isolation;
-- MySQL>5.7.19
SELECT @@global.transaction_isolation;
SELECT @@session.transaction_isolation;
SELECT @@transaction_isolation;

显式设置锁：

1
2


SELECT * FROM test_table lock IN share mode;
SELECT * FROM test_table for update;

开始事务：

1
2
3


start transaction;
...
rollback/commit;

查看锁的各种状态，和当前连接的状态：

1
2


show status like '%lock%';
show full processlist;

MySQL 死锁

同并发编程下的多个线程争抢锁一样，当两个事务分别占有对方想要的记录的锁时，会造成死锁，如下：

事务 A	事务 B
insert into test values(5,‘Alice’,15,60);
set session transaction isolation level serializable;	set session transaction isolation level serializable;
start transaction;	start transaction;
select name from test where id=5;	select name from test where id=5;
update test set socre=socre+1 where id=5; // 阻塞
// 更新成功	update test set socre=scroe+1 where id=5; // 检测到死锁，退出事务
commit;

上面就是典型的死锁场景，事务 A 更新时候阻塞是对方也获取了 S 锁，如果事务 B 没有修改该数据然后结束事务（释放 S 锁）的话，那么阻塞在更新的事务 A 即可以完成更新操作然后提交。但是，事务 B 期间也要更新，那就会试图等待事务 A 释放 S 锁，这样一来就死锁了。对于上面的情况，MySQL 在发现死锁的时候，即事务 B 更新的时候，会输出 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction，强制结束事务 B。

还有比较容易出现死锁的场景是批量更新，两个事务以非唯一索引（有可能是两个不同的非唯一索引）进行数据的更新，最后 A 的对主键加锁顺序是 [1, 2, 3, 4] 而 B 可能是 [4, 5, 6, 1]。

同时，RR 级别下的 gap 锁可能增加死锁的概率，而且通常不容易发现。

死锁的预防

增加索引，通过之前的 MySQL 怎么加锁，可以看到不管是 RR 还是 RC 在使用非索引的条件筛选数据更新时候都会遇到，锁表的这一个步骤，这回造成很多无辜的死锁。
合理设置索引，设置索引后锁的粒度有所下降，不至于锁表，但是在 RR 级别下可以看到对于非唯一索引，由于 gap 锁的存在，也很容易产生死锁，所以尽可能的采用区分度高的列作为索引，合理使用 where。
对于批量的数据更新，建议根据避免死锁的理论，对加锁/解锁顺序进行统一，比如可以统一按照主键排序。
然后就是合理使用事务，尽可能的将大的事务拆分为小的事务。

事务与 Django

参考文档

可以使用 select_for_update 锁行直到事务结束，默认是让其他的事务等待，可以使用 select_for_update(nowait=True) 抛出 DatabaseError 错误。

锁行需要事务，所以使用 transaction.atomic 确定事务范围。

使用静态方法时候，保证不能使用 self，从而保证 orm 映射的 Python 对象数据不能使用，规避了 orm 化的 Python 数据被外部更改的风险。

We use a classmethod instead of an instance method — to acquire the lock we need to tell the database to lock it. To achieve that we need to be the ones fetching the object from the database. When operating on self the object is already fetched and we don’t have any guaranty that it was locked

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


class Account(models.Model):
    id = models.AutoField(primary_key=True,)
    user = models.ForeignKey(User,)
    balance = models.IntegerField(default=0,)
    @classmethod
    def deposit(cls, id, amount):
        with transaction.atomic():
                account = cls.objects.select_for_update().get(id=id)
                account.balance += amount
                account.save()
        return account

    @classmethod
    def withdraw(cls, id, amount):
        with transaction.atomic():
            account = cls.objects.select_for_update().get(id=id)
            if account.balance < amount:
                    raise errors.InsufficentFunds()
            account.balance -= amount
            account.save()
        return account

总结

数据库的事务问题，最终可以简要的概括为并发模型下的读写者的问题。

而数据库会比简单的一个文件的读写者问题要更复杂，因为数据库有表、行等不同的粒度，若是不介意可以直接对所涉及到的整个表都用上简单的读写锁（就像直接锁文件一样），都不用各种事务级别 (MyISAM 就是表锁，没有事务，像极了文件的读写者的解决方法），结果就是绝对安全但是部分情况下性能极差。

而事务问题和事务级别的出现是在探讨如何一步步的降级“安全的标准”到实际生成环境下可以接受的几种程度，即最后的事务级别，之后如何在不同的隔离级别上尽可能的提高系统吞吐就是数据库引擎的问题了，可以说隔离级别选择决定上限，而并发控制实现决定下限。

实现方式总的思路就是悲观锁和乐观锁这两种，亦或是其不同程度的组合，正是这样混合的模式使得 MySQL 的事务的细节理解起来没那么容易，但这也正是我们需要下功夫的地方。

Contents