Mysql 中的事务与锁

Mysql 中的事务与锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

 

事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有如下4个属性，通常称为事务的ACID属性：

• 原子性（Actomicity），事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行；
• 一致性（Consistent），在事务开始和完成时，数据都必须保持一致状态；
• 隔离性（Isolation），MySQL提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然；
• 持久性（Durable），事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

由于事务的并发执行，可能会引起一些问题，比如：

• 更新丢失（Lost Update），当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道彼此的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新；
• 脏读（Dirty Reads），一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理；
• 幻读（Phantom Reads），一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据（通常对应于INSERT）；
• 不可重复读（Non-Repeatable Reads），一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了（通常对应于UPDATE）。 

 

举例：

“脏读”，会话 2 更新 age 为 10，但是在 commit 之前就被 会话1 读到了，此时 会话2 即使 rollback，会话1 读到的 age还是10。

 

 

“幻读”：由于在会话 1 之间插入了一个新的值，所以得到的两次数据就不一样了。

 

 

“不可重复读”：由于在读取中间变更了数据，所以会话 1 事务查询期间的得到的结果就不一样了。

 

注意，“更新丢失”不能单靠数据库事务控制器来解决，而应该由用户自己想办法解决（比如加锁）；

但其它三个问题（脏读、幻读、不可重复读）都是 读一致性 问题，应该由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种：

1. 加锁，即在读取数据前，对其加锁，阻止其他事务对数据进行修改；
2. 多版本并发控制（ＭultiVersion Concurrency Control, MVCC），通过生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取；从用户的角度，好像是数据库可以提供同一数据的多个版本。 

数据库的事务隔离级别越严格，事务并发的副作用越小，但付出的性能代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的，同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了４个事务隔离级别：

• Read uncommitted（读未提交，允许脏读），如果一个事务已经开始写数据，则不允许其它事务同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现；
• Read committed（读已提交，允许不可重复读），未提交的写事务将会禁止其他事务访问该行，这可以通过“瞬间共享读锁”和“排他写锁”实现；
• Repeatable read（可重复读，允许幻读），读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务；这可以通过“共享读锁”和“排他写锁”实现；
• Serializable (串行化)，事务最高隔离级别，在该级别下，事务串行化顺序执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

 

每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己业务逻辑要求，通过选择不同的隔离级别来平衡＂隔离＂与＂并发＂的矛盾

最后要说明的是：各具体数据库并不一定完全实现了上述４个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准级别，另外还自己定义的Read only隔离级别：SQL Server除支持上述ISO/ANSI SQL92定义的４个级别外，还支持一个叫做＂快照＂的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。ＭySQL支持全部４个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级下是采用MVCC一致性读，但某些情况又不是。

Mysql的默认隔离级别是Repeatable read。

锁

MySQL中的锁大致可分为以下3种：

1. 表级锁：主要是 MyISAM引擎使用，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
2. 行级锁：主要是 Innodb引擎使用，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

可见，锁的粒度越大，加锁越快，但并发度越低。 

表级锁 (MyISAM引擎)

ＭySQL的表锁有两种模式：

• 表共享读锁（Table Read Lock），主要由读操作（SELECT）使用，不会阻塞其他用户对同一表的读请求，但会阻塞写请求；
• 表独占写锁（Table Write Lock），主要由写操作（INSERT/UPDATE等）使用，会阻塞其他用户对同一表的读、写请求。

可见，当一线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。ＭyISAM表的读和写操作之间，以及写和写操作之间是串行的！

如下表，横向title表示当前锁模式，纵向表示请求锁模式，是否兼容的是表示不会阻塞，否表示要阻塞：

当前锁模式/是否兼容/请求锁模式	None	读锁	写锁
读锁	是	是	否
写锁	是	否	否

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。但在涉及到多张表的操作时，可能会需要使用者显式加锁，例如有一个订单表orders，其中记录有订单的总金额total，同时还有一个订单明细表order_detail，其中记录有订单每一产品的金额小计subtotal，假设我们需要检查这两个表的金额合计是否相等，可能就需要执行如下两条SQL：

SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;

这时，如果不先给这两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

LOCK tables orders read local,order_detail read local;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;

这里要注意：

1. 在用LOCKTABLES给表显式加表锁是时，必须同时取得所有涉及表的锁，假如多个表加锁有先后熟悉，则可能引发deadlock；
2. 上面的例子在LOCK TABLES时加了‘local’选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录（表尾新增不会引起竞争条件）。

例子中LOCK TABLE语句使用 local 关键字可以使得如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。事实上，MyISAM存储引擎有一个系统变量  concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• concurrent_insert = 0，不允许并发插入（此时读和写之间也是串行的）；
• concurrent_insert = 1，如果MyISAM允许在一个读表的同时，且表文件没有空洞（被删除的行），另一个进程从表尾插入记录（这也是MySQL的默认设置）；
• concurrent_insert = 2，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。

注意，concurrent_insert 的并发是指在有SELECT的读锁时允许一个写入操作（即读和写之间并发），而不是指多个写入可以并发执行，如果有多个写入操作之间仍然需要串行顺序执行。

另外，对多数应用场景下，读操作会远多于写操作，那这是否会导致频繁的读操作一直持有read锁，而更新操作很难得到write锁呢？答案是写操作会优先获得锁，即使读请求先进入锁等待队列，写请求后到，写锁也会插到读请求之前！这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

我们可以通过一些设置来调节MyISAM的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利（服务级）；
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低（连接级）；
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级（语句级）；

以上几个方法都是要么更新优先，要么查询优先，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。

行级锁 (Innodb引擎)

ＭySQL的行级锁也有两种模式：

• 共享锁（S Lock），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
• 排它锁（Ｘ Lock），允许获取排它锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。

可以想到这里SS相互兼容，XX、XS、SX不兼容。

对于普通SELECT语句，InnoDB不会加任何锁；对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁（Ｘ）；

事务可以通过以下语句显式给记录集加共享锁或排它锁：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT .. IN SHARE MODE获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁。

对于锁定行记录后需要进行更新操作的应用，应该使用SELECT ... FOR UPDATE方式获取排他锁。

行锁与索引

Innodb的行锁到底锁住的是什么呢？我们先假设它锁定的是一行数据或者记录（Record）。

看个例子，事务A 对user表中 id = 1的记录执行 select ... for update，如果锁的是Record，那么应该只对id=1的记录加X锁；

mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | Green |
|  6 | Bush  |
+----+-------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
+----+-------+
| id | name  |
+----+-------+
|  1 | Green |
+----+-------+
1 row in set (0.00 sec)

mysql> 

另外一个 事务B 对记录id=3加X锁，它和事务A 操作的记录并非同一条，此时似乎不应该被阻塞，但事实上它被阻塞了！

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=6 for update;  // blocked

至此，我们可以确定前面的假设是错误的，行锁住的不是数据行（或者叫Record），否则不会出现整张表都被锁住的情况。

如果我们对上面例子中的user表执行如下语句，增加主键id

mysql> alter table user add primary key (id);
Query OK, 0 rows affected (12.97 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

然后重复上述例子，事务A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
+----+-------+
| id | name  |
+----+-------+
|  1 | Green |
+----+-------+
1 row in set (0.00 sec)

事务B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=6 for update; // ok
+----+-------+
| id | name  |
+----+-------+
|  1 | Green |
+----+-------+
1 row in set (0.00 sec)

结论：如果InnoDB的行锁不是通过锁定记录实现的，那么可能和索引有关？

事实上，InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁 (行锁升级为表锁)！

意向锁（Intention Locks）

需要注意的是，当给某一行增加共享锁、排他锁时，数据库会自动给这一行所处的表添加意向共享锁（IS Lock）、意向排他锁（IX Lock）也就是说，如果想给 r行 增加锁，需要给 r行 所在的表先增加意向排他锁。

• 意向共享锁（IS Lock），事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁；
• 意向排他锁（IX Lock），事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。

下面来看一下意向锁的作用，假设事务A对 r行 加了S锁，之后事务B申请整个表的写锁，那么数据库需要做的事情包括：

step1：判断该表是否已被其他事务用表锁锁表；
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

如果没有意向锁，那么在进行step2的时候，需要遍历整个表判断是否有行锁的存在，以免发生冲突；但如果有了意向锁，则只需要判断该意向锁与即将申请的表锁是否兼容即可。因为意向锁的存在，代表了有（或即将有）行级锁的存在。

引用官方的话

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). 
The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table. 

可见意向锁的作用主要是协调行锁与表锁的关系，如下表，注意，该表里的X和S都是表级锁，而非行锁！

绿色部分表示意向锁之间可以兼容，例如如果表上已经被加了 IX锁，证明此时有 事务A 在修改表中的具体某行的数据（对应行的数据这时可能被加了X锁），此时如果又有 事务B 要对表加 IS锁（或IX锁），仍可以成功，但事务B后续查询或者修改的是某行的数据，这行和事务A修改的数据可能会有冲突、也可能没有冲突。

红色部分表示当有事务对表加写锁（X）时，其他事务将无法获得任何意向锁；如果有事务对表加共享锁（S）时，其它事务可以获得IS锁，但无法获得IX锁。
 

当前锁模式/是否兼容/请求锁模式	X	IX	S	IS
X	否	否	否	否
IX	否	是	否	是
S	否	否	是	是
IS	否	是	是	是

行锁可以再分为记录锁、临健锁、间隙锁，假如表中某字段值有1、4、7、10几条记录，如下图

图中间隙锁的记录都是不存在的，而临健锁是一个左开右闭区间。 

记录锁（Record Locks）

上面在讲到 行锁与索引 关系的时候，提到行锁实际锁的是index，而非record，也就是说 Record locks 其实是锁索引数据，那么当表中没有index怎么办呢？

这种情况下Innodb会创建一个隐藏的clustered index，并对该聚集索引加锁。 

临键锁（Next-key Locks）

临健锁 是记录锁和间隙锁的组合，它的封锁范围，既包含索引记录（record），又包含索引区间（gap），所以它是一个左开右闭区间。

临健锁 只有在事务隔离级别RR（Repeated Read）下才生效，用于避免幻读。 

间隙锁（Gap Locks）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁。

 

 

举个例子，有一个表user，事务A执行如下语句，启动一个读事务，where子句检索 id > 6 的范围 (这里利用for update加X锁)，

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  6 | Bush |
+----+------+
1 row in set (0.00 sec)

mysql> 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 6 for update;
Empty set (0.00 sec)

此时事务A还未执行commit，然后另一个事物B先插入一条 id = 1 的记录，可以成功；然后再插入一条 id = 7 的记录，发现被夯住，这是因为 id > 6 的记录被事务A 加上了间隙锁。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values (1, "Green");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values (7, "Brown");

再举个例子，唯一索引有值1、5、7、11，那么该表隐藏的next-key lock包括（左开右闭区间）

(-infinity, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +infinity]

如果 事务A 执行如下范围检索

begin;
SELECT * FROM `user` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;

此时，另一个事务B 执行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values (4, "4");
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values (6, "6");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into user values (11, "11");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into user values (12, "12"); 
Query OK, 1 row affected (0.00 sec)

可见，事务A产生的间隙锁会锁住 (5, 7] 和 (7, 11] 两个区间。

再比如，若 事务A 执行如下检索（不存在的记录）

BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `user` WHERE `id` = 3 FOR UPDATE;

此时，另一个事务B执行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `user` (`id`, `name`) VALUES (2, '2');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO `user` (`id`, `name`) VALUES (6, '6');
Query OK, 1 row affected (0.00 sec)

可见，事务A产生的间隙锁会锁住 (1, 5] 。当然，如果事务A 检索的条件能够命中记录(比如where id=5)，就不会产生间隙锁，而只会产生记录锁。

综上对行锁的一些结论：

1. 加锁的基本单位是 next-key lock；
2. 加锁是基于索引的，查找过程中访问到的对象才会加锁（如果没有索引会退化为表锁）；
3. 在唯一索引上的等值查询，如果该记录不存在，会产生gap lock，如果记录存在，则只会产生record lock；
4. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE `id` BETWEEN 5 AND 7 FOR UPDATE；