[MySQL] The InnoDB Transaction Model and Locking

前段时间研究了sqlserver2005的事务与锁模式，最终的结果与自己以前的认识有相当大的出入，感觉受益非浅。因为我也明白，不同的数据库对ansi-sql定义的四个事务隔离级别的支持是不一样的，所以现在是时候看看mysql是怎么支持这些事务隔离级别的，它的锁行为又是怎么样的。
* 这里说的Mysql实际上是说Mysql5.0的InnoDB存储引擎，这是一个支持事务的引擎。
Mysql很早就实现了基于行版本的快照隔离级别（sqlserver2005中增加的实现），其实我也不清除这样的说法是不是正确，但是意思大致是相同的，就是数据库服务器会管理行数据的多个版本，每个事务只能获得一个快照。
Mysql支持不同粒度的锁，即row(record) lock和table lock. row lock表示应用到特定行上的锁， table lock表示应用到整个表上的锁。
LOCK TYPE
此外锁有两种类型：共享（S）允许事务读取一行，独占（X）允许事务更新或者删除一行。还有一种意向锁(intention locks)，这种锁只会应用到table上，即这是一种table lock。它的作用就是用来说明当前事务（已经获得Is或者iX）想要请求表中某行的S或者X锁（这个概念与sqlserver的意向锁是相同的）。意向锁之间都是兼容的，即使IX与IX也是兼容的，这样多个事务之间可以同时在table上获得IX锁，但是只有一个事务可以获得某一行的X锁。而S和X锁即可以应用到行也可以应用到表，比如alter table就会在标上应用X锁。 关于锁的兼容性，可以参照下表：

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

此外，Mysql有个很怪的行为，如果一个事务Ta读取记录R（select ...lock in share mode),获得了S锁，然后Tb在记录R上请求X锁（应该在表上应用IX锁），这个时候Tb会被阻塞，然后Ta要更新R，这个时候Ta会尝试从S锁升级到X锁（也会在表上应用IX锁，iX与IX是兼容的），这种情况下，我的理解是Tb会继续被阻塞，直到Ta事务结束释放所有的锁。但是mysql会抛出死锁异常，我都不知道怎么解释，难道先来先得，mysql必须先给Tb分配X锁？？sqlserver采用的是阻塞行为，不会认为是死锁。

CONSISTENT NON-LOCKING READ
MySQL默认的隔离级别是repeatable read，如果要修改这个默认的隔离级别，可以通过修改my.cnf，设置如下：
[mysqld]
transaction-isolcation=REPETABLE READ
在READ COMMITTED和REPEATABLE READ的隔离级别下，MySQL默认会采用Consistent Read（一致读）。Consistent Read不会在行或者表上施加任何的锁（是指select...，不是select...lock in share mode或者select ... for update, 后面两种稍后会进行说明）。这样读不会阻塞写，写不会阻塞读，可以大大提高并发处理能力，但是数据不一致的风险也更大了。
在Read Committed下，select总是获得语句执行时数据库已提交数据的快照，而repeatable
Read下，同样的select语句，总是返回第一条select执行时数据库已提交数据的快照。比如事务Ta和事务Tb同时访问记录R，Tb稍后更新R到R1并且提交。如果Ta是Read Committed的，那么在Ta在Tb提交前后分别得到R和R1，而如果是repeatable read的话，Ta总是得到R。老实说，这种逻辑我很难理解，这里我觉得sqlserver处理的方式更合理。在sqlserver中
，如果Ta是Repeatable read，那么Ta在第一次读取R的时候就会获得R的S锁，并且不到事务结束不会释放，这样，Tb在想要修改的R的时候，会被阻塞，这样就保select...lock in share mode / select...for update证了Ta的可重复读。 而MySQL的这种处理方式有点怪，有种被蒙住眼睛，被欺骗的感觉。
select...lock in share mode / select...for update
Mysql默认会采用Consistent read，这样的select不会应用任何锁到资源（行或者表）上。但是可以显示的要求mysql的资源上应用锁，这就是select ... lock in share mode和select ...for update.
select ... lock in share mode会在匹配的行上应用S锁，直到事务结束才会释放。在什么情况下会需要应用S锁呢？ 这里有表Parent和Child，每个Child中的记录必须关联到Parent中的一条记录。如果不采用S锁，我们在向Child中插入数据的时候，很可能Parent中关联行会被其他事务删除，这样数据就不一致了（当然也可以用数据库外键来保证）。如果我们在想child中进行插入的时候，先在parent管理记录上获得S锁，这样其他事务就无法修改这条记录了，就可以保证child与parent的一致性。
select ... for update这个与sqlserver的行为一致，不过sqlserver会加更新锁（U），而mysql会在资源上应用X锁。如果需要读取一个记录来进行更新的话，就可以使用select..for update。
InnoDB Record-level Lock

InnoDB has several types of record-level locks:

• Record lock: 用来锁住一条索引记录。

• Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

• Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Record lock总是会锁住索引记录（index record），即使该表没有建立任何索引（这种情况下，mysql会自动创建一个clusterindex，然后record lock就会锁这些索引），实际上锁行就是锁索引。
默认情况下，事务隔离级别是repeatable read并且Innodb_locks_unsafe_for_binlog没有被激活，这个时候InnoDB会采用next-key lcok来搜索和扫描索引，从而避免幻影读取（为什么在repeatable read中避免幻影读取？）。 Next-key lock 组合了record lock和gap lock，除了会锁住索引记录以外，还会锁住索引之间的间隔。InnoDB会对访问到的每条记录加锁，尽管有些行根本不满足where子句的约定条件，这些记录会被加上Next-key lock。这就说明，我们在进行查询的时候，最好对索引行来查询，减少查询的访问范围，防止无辜的记录被锁上。如果针对一个没有索引的列来进行查询，那么会进行全表扫描，这就会导致整个表的记录都被锁上。
举例说明一下：
假设有表t，包含两列id int not null, name varchar(30) not null,其中id为primary key（即为一个cluster index)，其中包含6条记录。

mysql> select * from t;
+----+------+
| i | name |
+----+------+
| 0 | t-0 |
| 2 | t-2 |
| 3 | t-3 |
| 10 | t-10 |
| 14 | t-14 |
| 15 | t-15 |
+----+------+
6 rows in set (0.00 sec)
首先在默认的事务隔离级别repeatable read下测试，假设两个事务Ta和Tb：
Ta>start transaction;
Ta>select * from t where name='t-2' for update;
Tb>start transaction;
Tb>insert into t(i,name) value(25,'t-25');
这个时候Tb会被阻塞，因为name不是索引字段，Ta会进行全表扫描，每条记录都被应用next-key lock（但是实际上这些锁又是锁在cluster indx record上的，这个概念和sqlserver应该是相同的，有cluster index的表就是cluster表，索引和记录是在一起的，相对于索引和记录分开的情况，cluster table会减少磁盘io，提高访问性能），所以Tb被阻塞。
接下来，我们使用索引进行查询。
Ta>start transaction;
Ta>select * from t where i=3 for update;
Tb>start transaction;
Tb>insert into t(i,name) value(25,'t-25');
这个时候Tb不会被阻塞，这里应该也并不表示Ta只会在i=3的索引记录上加锁，因为cluster index本质上是一个B树，所以应该是访问到的节点都被加锁（？？）。

我们可以通过以下两种方式来取消gap lock（取消了gap lock，自然也取消了next-key lock，但是在readcommitted下仍然有next-key lock）：

• 如果事务的隔离级别 是read committed
• 激活Innodb-locks-unsafe-for-binlog.

Locks Set by Different SQL Statements in InnoDB

实际上不论select还是update，delete都会把扫描的记录加锁，而不是仅仅将匹配where clause子句的记录加锁，可以通过下面的实验来验证。
Ta>set transaction isolation level read committed;
Tb>set transaction isolation level read committed;
Ta>start transaction;
Ta>update t set name='t-2-2' where name='t-2';
Tb>start transaction;
Tb>select * from t where name='t-1' for update;
这个时候Tb会被阻塞,因为Ta进行了全表扫描，所以的记录都被加上了X锁。为什么采取这种策略？？SQLServer这里表现的更好，它只会锁住符合查询条件的记录。

InnoDB sets specific types of locks as follows:

• 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.

• SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.

• SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the search encounters.

• UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• INSERT INTO ... VALUES (...) sets an exclusive lock on the inserted row. This lock is not a next-key lock and does not prevent other sessions from inserting into the gap before the inserted row. If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

更多信息请参考Mysql5.0 Mannual：13.2.9 The InnoDB Transaction Model and Locking