--MySQL锁与MVCC

--------------------2014/06/29

myisam表锁比较简单，这里主要讨论一下innodb的锁相关问题。

innodb相比oracle锁机制简单许多，锁的类型有如下几类：

A shared (S) lock permits the transaction that holds the lock to read a row.

An exclusive (X) lock permits the transaction that holds the lock to update or delete a row. 

An Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.

An Intention exclusive (IX): Transaction T intends to set X locks on those rows.

各种类型的锁兼容性：

除此以外，innodb还有几种锁：InnoDB Record, Gap, and Next-Key Locks。

• Record lock: This is a lock on an index record.

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

--InnoDB operates in REPEATABLE READ transaction isolation level and with theinnodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

--注意这里的锁全部为index record，如果没有索引就使用默认创建的innodb索引。

下面主要解释一下在repeatable_read和read_committed两种隔离级别下的锁表现。

--REPEATABLE_READ

在一个A事务内，保证多次读的结果不会受到别的事务影响。如果A事务中没有数据修改，那么A事务中应该所有的读结果都是一致的。

可能会有疑问，MVCC多版本控制难道默认不是这样子的吗，在一个事务中，不能的隔离级别MVCC会有何不同？

--------------事务A-------------

------begin　　--->scn:xxx0a1

------insert　　--->　　scn:xxx0a4

------------------------------------------------>C事务scn：xxx0a7同样insert到表中数据并提交。

------select　　--->　　scn:xxx0a4

------select　　--->　　scn:xxx0a4

------select　　--->　　scn:xxx0a4

------commit　--->scn:xxx0a5

正如上面的事务图，在repeatable_read隔离级别下，后面3此select的MVCC scn参考都是参考A事务所修改的scn情况读取，不会管其他事务。

再看下面这个例子

--------------事务B-------------

------begin　　--->scn:xxx0b1

------select　　--->scn:xxx0b1　　　　　　　--结果集2条数据。

------------------------------------------------C事务scn：xxx0b3 insert一条符合上面select条件的数据。

------update　 --->scn:xxx0b5　　　　　　　--update select条件的数据，3条数据被更改。

------select　　--->scn:xxx0b5　　　　　　   --如上文提到的select mvcc scn参考自己事务内的scn。

------不提交

------------事务D--------------

------insert　　　　　　　　　　　　　　　　　 --insert数据到上面的表中，被柱塞！！！

非常诡异，按照ORACLE的经验，只有被修改的行且没有提交的行才会被锁住，这是行级锁的基本特性，而且innodb也实现了行级锁，居然本锁住，觉得不可思议，那究竟是什么原因呢？

这是由于bin_log的原因，由于innodb的binlog为提交后才会写入的，为了保证binlog的写入是安全的，由参数innodb_locks_unsafe_for_binlog决定。在使用基于语句的binlog模式时，执行insert ... select操作会锁定原表上的所有行。同样如上面的update语句，由于也是更新一个结果集，不管是否有where子句，innodb会锁住扫描过的索引和next-key lock机制锁住目标。

这一点就和db2中锁很类似了，表现为无mvcc特性，通过在过滤建上添加索引（而且innodb选择此索引），让结果集只扫描部分索引可以减少锁的行数。

--READ COMMITTED

在一个事务中，读取已经提交的行，不保证读一致性，在MVCC的机制中表现如下。

--------------事务E---------------

------begin　　--->scn:xxx0b1

----------------------------------------------->scn:xxx0b2 事务F insert一行数据并提交。

------select　　--->scn:xxx0b2

----------------------------------------------->scn:xxx0b4 事务J update一行数据并提交。

------select　　--->scn:xxx0b4

--区别在读，在read committed隔离级别中，MVCC的select的参考scn是参考最近的已提交的所有事务，而不是本事务内的。

--思考，由于在repeatable-read模式下，如果一个事务一直不提交，它的select scn一直不推进，应该会出现由于undo空间的清理，可能读不到对应的版本而报错的情况...