MySQL锁与MVCC

时间:2022-04-07 23:56:31

--MySQL锁与MVCC

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

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

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

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.

各种类型的锁兼容性:

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

除此以外,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空间的清理,可能读不到对应的版本而报错的情况...

问题:通过mysqldump --single-transcation模式获得一致读,由于备份时间很长,会导致undo表空间暴增吗?