Mysql 中的事务与锁 Mysql 中的事务与锁

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

事务

• 原子性（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定义了４个事务隔离级别：

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

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

锁

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

表级锁 (MyISAM引擎)

ＭySQL的表锁有两种模式：

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

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

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

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 的并发是指在有SELECT的读锁时允许一个写入操作（即读和写之间并发），而不是指多个写入可以并发执行，如果有多个写入操作之间仍然需要串行顺序执行。

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

以上几个方法都是要么更新优先，要么查询优先，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操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁。

行锁与索引

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行 所在的表先增加意向排他锁。

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

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

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. 

行锁可以再分为记录锁、临健锁、间隙锁，假如表中某字段值有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也会对这个“间隙”加锁。

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)

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");

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

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

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)

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

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)