MySQL Lock and MVCC

--mysql Lock and MVCC

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

MyISAM table lock is relatively simple, here is the main discussion of InnoDB lock-related issues.

InnoDB is a lot simpler than the Oracle lock mechanism, with the following types of locks:

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 gkfx ( IS ): Transaction T intends to set S locks on individual rows in table .

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

Various types of lock compatibility:

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

In addition, InnoDB also has several locks: InnoDB Record, Gap, and Next-key Locks.

• Record Lock:this is a lock in 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 rec Ord.

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

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

-Note that the lock here is all index record, and the InnoDB index created by default is used if there is no index.

The following mainly explains the locking performance under the two isolation levels of repeatable_read and read_committed.

--repeatable_read

Within a transaction, the results of multiple reads are guaranteed to be unaffected by other transactions. If there is no data modification in a transaction, all the read results in a transaction should be consistent.

There may be doubts, MVCC versioning is not the default, in a transaction, the isolation level MVCC will not be different?

--------------Transaction A-------------

------BEGIN--->SCN:XXX0A1

------Insert---> SCN:XXX0A4

------------------------------------------------>c Transaction SCN:XXX0A7 also inserts data into the table and submits it.

------Select---> SCN:XXX0A4

------Select---> SCN:XXX0A4

------Select---> SCN:XXX0A4

------Commit--->SCN:XXX0A5

As in the above transaction diagram, under the Repeatable_read isolation level, the MVCC SCN reference for the next 3 of this select is read by reference to the SCN condition modified by a firm, and does not control other transactions.

Let's look at the following example

--------------Transaction B-------------

------BEGIN--->SCN:XXX0B1

------Select--->scn:xxx0b1--result set 2 data.

------------------------------------------------C Transaction SCN:XXX0B3 Insert a data that conforms to the above select condition.

------Update---The data for the >SCN:XXX0B5--update select condition, 3 data is changed.

------Select--->scn:xxx0b5-as mentioned above, the select MVCC SCN references the SCN within its own transaction.

------Do not submit

------------Transaction D--------------

------Insert--insert data into the table above, by the plunger!!!

Very weird, according to Oracle's experience, only modified lines and not committed lines will be locked, this is the basic characteristics of row-level lock, and InnoDB also implemented a row-level lock, incredibly locked, feel incredible, what is the reason?

This is due to bin_log, because the InnoDB Binlog is written after the commit, in order to ensure that the Binlog write is safe, by the parameterinnodb_locks_unsafe_for_binlog决定。在使用基于语句的binlog模式时，执行insert ... select操作会锁定原表上的所有行。同样如上面的update语句，由于也是更新一个结果集，不管是否有where子句，innodb会锁住扫描过的索引和next-key lock机制锁住目标。

This is similar to the DB2 lock, which behaves as a non-MVCC feature, by adding an index to the filter build (and InnoDB selecting this index), so that the result set only scans a partial index to reduce the number of locks.

--read COMMITTED

In one transaction, the read-committed row is not guaranteed read consistency, as shown in the MVCC mechanism.

--------------Transaction E---------------

------BEGIN--->SCN:XXX0B1

----------------------------------------------->SCN:XXX0B2 Transaction F Inserts a row of data and commits it.

------Select--->scn:xxx0b2

----------------------------------------------->SCN:XXX0B4 Transaction J update a row of data and commit.

------Select--->scn:xxx0b4

--the difference is in reading, in the Read Committed isolation level, the reference SCN for the MVCC Select is reference to all recent committed transactions, not within this transaction.

--think, because in the Repeatable-read mode, if a transaction has not been committed, its select SCN has not been advanced, should appear due to undo space cleanup, may not be able to read the corresponding version of the error situation ...