MySQL 并发控制

数据库并发基础

事务的四大特性ACID

• 原子性（Atomicity）：是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。

• 一致性（consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000。

• 隔离性（Isolation）：是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

• 持续性（Durability）：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

事务的并发一致性问题

• 丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
• 脏读：一个事务中访问到了另外一个事务未提交的数据
• 不可重复读：一个事务读取同一条记录2次，得到的结果不一致
• 幻读： 一个事务读取2次，得到的记录条数不一致

事务的四种隔离级别

• 读未提交（READ UNCOMMITTED）：最低的隔离等级，允许其他事务读取到没有提交的数据，会导致脏读。
• 读已提交（READ COMMITTED） ：被读取的数据可以被其他事务修改，这样可能导致不可重复读。
• 可重复读（REPEATABLE READ）：所有被读取的数据都不能被修改，这样就可以避免一个事务前后读取不一致的情况，其他事务不能更改所选的数据。
• 串行化（SERIALIZABLE）：事务只能一个接着一个地执行，不能并发执行。

---

数据库锁

什么是锁

锁是计算机协调多个进程或线程并发访问同一资源的机制。

在数据库中，除传统的计算资源（如 CPU、RAM、IO 等）争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

1. 从对数据操作的粒度分

• 行锁：锁住某一行

• 页锁：锁住相邻的一组记录，介于行锁与表锁之间

• 表锁：锁住整张表，当一个线程对一个表加锁时，该线程只能对该表进行操作，不能对其他表操作（包括读操作）

• 全局锁：对整个数据库加锁，数据库处于只读状态，拒绝任何写操作。

2. 从对数据操作的类型分

• 写锁（排他锁）：当前的写操作没有完成前，不允许对写对象其他的写锁和读锁

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响，允许对读对象加读锁，不可加写锁

索引与锁

行锁是通过索引添加的，当事务通过索引读取到对应行时，会对该行加行锁，如果无法使用索引，将会导致行锁升级成表锁。

InnoDB 只有在访问行的时候才会对器加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。但这只有当 InnoDB 在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤无效的行，那么在 InnoDB 检索到数据并返回给服务器层以后，MySQL 服务器才能应用 WHERE 子句，此时已无法避免锁定行了。

当执行如下 SQL 语句时：

mysql> SET AUTOCOMMIT=0;
mysql> SELECT actor_id FROM actor WHERE actor_id < 5
 -> AND actor_id <> 1 FOR UPDATE;

SELECT [QUERY] FOR UPDATE为加排他锁（行锁）操作。

SELECT [QUERY] LOCK IN SHARE MODE为加共享锁（行锁）操作。

上面的 SQL 语句将会返回 2~4 行之间的行，但是实际上获取了 1~4 之间的行的写锁。InnoDB 会锁住第 1 行，是因为 MySQL 为该查询选择的执行计划时索引范围扫描。换句话说，底层存储引擎的操作是：从索引的开头获取满足条件的 actor_id < 5 的记录。服务器没有告诉 InnoDB 可以过滤第 1 行的 WHERE 条件。

由此可以看出，即使使用了索引，InnoDB 也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话，将会导致行锁升级成表锁，MySQL 会做全表扫描并锁住所有的行。

此外，InnoDB 在二级索引上使用共享锁，但访问主键索引需要排他锁，这使得共享锁的查询要慢很多。

---

MVCC 多版本并发控制

什么是MVCC

MySQL 的大多是事务性存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都实现了 MVCC。通常可以认为 MVCC 是行表的一个变种，但是他在很多情况下避免了加锁操作，因此开销更低。不同的数据库实现 MVCC 的原理不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

InnoDB 的 MVCC 实现原理

MVCC 的实现，是通过数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一样的。根据事务开始的时间不同，每个事务对同一张表、同一时刻看到的数据可能不一样。

InnoDB 的 MVCC 是通过乐观锁、悲观锁的版本号机制实现的。通过每行记录后面保存两个隐藏的列实实现。这两个列，分别保存了行的创建时间和过期时间（或者删除时间）。当然存储的不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行对比。

下面是可重复读隔离级别下，MVCC 的集体操作：

Select 操作

InnoDB 会根据一下两个条件检查每行记录：

1. InnoDB 只查找版本号早于当前事务版本的数据行（也就是说，行的版本号小于或等于当前事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前就已经存在，要么是事务自身插入或修改过的。

2. 行的删除版本号要么未定义，要么大于当前的事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

• 只有符合上述两个条件，才能返回作为查询结果

Insert 操作

• InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。

Delete 操作

• InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。

Update 操作

• InnoDB 为插入一行新数据，保存当前系统版本号，同时保存当前系统版本号到原来的行 作为行删除标识。

保存这两个额外的系统版本号，使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC 只在可重复读和读已提交两个隔离级别下工作。其他的两个隔离级别都不支持MVCC，因为读未提交总是会读取到最新的数据，即使该数据产生在当前事务之后。而序列化则会对所有读取的行都加锁。