Wednesday, December 31, 2008

[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

No comments: