【译文】MySQL InnoDB 事物模型
InnoDB事物模型
事物的隔离级别
自动提交,提交和回滚
一致的非锁定读
锁定读
在InnoDB事物模型中,目标是为了多版本数据库和传统的俩段锁协议的最佳实践(多版本并发控制)。InnoDB在行级别执行锁行为,并且默认执行数据库查询为非锁定连续读取。
事物的隔离级别:
事物隔离是数据库中的一个基础,是ACID中的I原则。事物的隔离级别权衡性能和并发度、可靠性以及可重复可追溯能力的结果。
InnoDB提供了四种隔离级别:未提交读-RU,提交读-RC,可重复读-RR,可序列化-S。InnoDB默认是可重复读。
InnoDB支持每一个隔离级别,这里描述为不同的锁策略。你可以使用高度一致性协议(RR),此时在关键数据上符合ACID是重要的考虑。也可以降低一致性考虑,使用RC,RU
可重复读 - RR
这是默认的级别。相同事物内一致性读取,是基于第一次读取的快照版本。这意味着如果你在同一个事物中执行多个普通的查询语句,这些查询语句是一致的。
对于锁定读,select ... lock in share mode 或者 select ... for update 或者update或者delete语句,如何锁定取决与语句是否使用了唯一检索条件检索唯一性索引,或者一个范围检索条件。
对于唯一条件检索唯一索引,仅锁住检索条件相关的单一索引记录,并不会包括记录之前的gap间隙。
对于其他检索条件,InnoDB锁住扫描的索引范围,使用gap lock或者next-key lock来阻塞其他并发事物的插入索引间隙范围内。
提交读 - RC
每一个读取操作,甚至是在同一个事物中,都会是数据库中最新的版本。
对于锁定读,select ... lock in share mode 或者 select ... for update 或者update或者delete语句,InnoDB仅锁住索引记录,不会锁住gap,因此它允许自由的插入新纪录。Gap锁仅在外键约束检查和重复值检查时使用。
如果你使用RC,你必须使用row-based的binlog,使用RC还有额外的影响:
对于更新和删除语句,InnoDB仅持有更新或者删除的行的锁,不符合的记录锁会在Where条件被解释后释放,这能极大的减少死锁的可能性。
对于更新语句,如果一行已经被锁住,InnoDB执行"半一致性"读,返回最近Mysql提交的版本,以便于Mysql可以决定是否符合update语句的Where条件,如果匹配,Mysql再次读取该行,同时锁住他或者等待锁。
举个例子:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;
这个例子中,表t没有索引,因此检索和扫描索引使用隐式的聚簇索引锁记录。
假设一个客户端执行如下Update语句:
SET autocommit = 0; UPDATE t SET b = 5 WHERE b = 3;
另外一个客户端在上述语句执行后执行如下语句:
SET autocommit = 0; UPDATE t SET b = 4 WHERE b = 2;
当InnoDB执行每一个Update时,他首先请求获取X锁给每一行,然后决定是否修改他。如果InnoDB不修改该行,就释放锁。否则,InnoDB会持有锁直到事物结束。
当使用默认的RR级别时,第一个update获取X锁,并且不会释放任何一个:
x-lock(1,2); retain x-lock x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); retain x-lock x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); retain x-lock
第二个update阻塞到获取任意的锁,因为第一个update已经在所有的行锁住,所以第二个update不会执行直到第一个update提交或者回滚。这就是为啥尽量对where子句中出现的字段建立索引的原因。
如果是用RC级别,第一个update获取X锁,并且释放那些不符合修改条件的行锁:
x-lock(1,2); unlock(1,2) x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); unlock(3,2) x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); unlock(5,2)
对于第二update,InnoDB做执行“semi-consistent”读取操作,返回每行的最近提交版本,以便MySQL可以判断是否该行符合Where筛选条件,不符合的行会释放锁。
x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock
未提交读 - RU
序列化 - SERIALIZABLE
自动提交,提交和回滚
如果AutoCommit被启动,每一条sql语句形成了属于他自己的事物。MySQL启动的时候默认开启会话是启动AutoCommit的。你可以使用begin,start transaction来开启一个事物,使用commit或者rollback来提交一个事物。
如果AutoCommit被禁用(SET autocommit = 0),会话总是开启一个事物,一个commit或者rollback执行结束当前会话时会重新开启一个事物。如果AutoCommit被禁用,但在会话没有使用commit提交,MySQL自动回滚rollback。
有一些语句会自动的结束事物,例如数据定义语句(创建表,更改表结构)、使用或者修改MySQL自带mysql的数据库、事物控制或者锁语句(lock/unlock tables),加载数据(load data),管理操作,主从复制控制。
一致性非锁定读
一致性读取指:InnoDB使用多版本给query呈现数据库在某一个时间点的一个快照。Query可以感知到在那个时间点之前事物提交的变化,但是感知不到那个时间点之后的事物或者未提交事物做的变化。
同一事物俩个Query之间夹着修改语句,后一个Query可以感知。这个规则会导致以下的异常:如果你更新了表的某些行,select感知到了更新的变化,但他同样可以看见其他行的老版本。如果其他会话同时更新相同的表,异常就是意味着你感知到的表的状态可能从来没有出现在数据库中。
如果事物隔离级别是RR,相同事物内的所有的一致性读,读取的是第一次读的数据库快照,你的Query可以获取新的快照,通过提交当前的快照同时在之后重新发起一个Query。
注:相同事物内俩个一致性读取之间夹着一个update,后一个一致性读取会感知到,即上述注明地方会发生。
如果事物隔离级别是RC,事物内每一个一致性读都会设置并刷新他自己的快照。
一致性读是InnoDB默认的,此时InnoDB使用RC, RR级别。一个一致性读不加任何的锁,在他访问的表上。
注:
数据库状态的快照只用在select上,如果你插入或者修改了数据库的某些行,然后提交了,从另外的会话发起的update或者delete,同样会影响刚刚提交的行,及时会话不能查询到他们。
如果一个事物更新或者删除了另外一个事物提交的行,这些变化在当前的会话中是可见的。类似如下:
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match. DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction. SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match. UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values. SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.
注:此类现象称为幻读,通常发生在一个事物更新,一个事物插入(删除),更新的时候更新插入的数据,感觉出现了幻觉。
如果你想要感知到最新的数据库状态(快照),使用RC级别,或者锁定读。注:在RR级别尽量不要用锁定读,这样会增加死锁概率。未经过
在不同的select子句的读类型,INSERT INTO ... SELECT
, UPDATE ... (SELECT)
, and CREATE TABLE ... SELECT不指定
FOR UPDATE
or LOCK IN SHARE MODE
:
默认地,InnoDB使用强壮的锁,并且select部分像RC级别一样执行,此时,每一个一致性读(甚至是在相同的事物中)会设置和读取最新的快照。
为了在上述情况使用一致读,启用innodb_locks_unsafe_for_binlog(废弃)选项,并且设置事物的隔离级别为RU,RC,RR(启用这个选项会影响gap锁的使用,此时RR和RC很大程度上一致)。这样,就不会在选中的表中的行读取时加锁。
锁定读
SELECT ... LOCK IN SHARE MODE
设置共享锁到所有被读取的行,其他会话可以读取,但不能修改知道事物提交。如果加共享锁的这些行被任何其他事物修改。
对于检索遇到的索引记录, SELECT ... FOR UPDATE会锁住索引记录以及关联的索引区间,就像是在这些行上发起一个update请求。