MySQL Lock and transaction (i): InnoDB lock (MySQL Official document Coarse)

Written in front, in fact, the type and scope of database locks are affected by a variety of factors, such as database isolation level, SQL statements, whether to use primary keys, indexes, and so on. Can see the blog: http://www.cnblogs.com/zhaoyl/p/4121010.html Learn

This section describes the types of locks used by InnoDB.

• Shared Lock (S) and exclusive lock (X)
• Intent lock
• Row lock (Record lock, do not know if it is better to call record lock, Baidu a bit called row lock)
• Gap locks (Gap Lock)
• Next-key Lock
• Insert Intent Lock
• Auto-inc (self-growing) lock
• Space Lock (predicate Locks for spatial Indexes)

Shared and exclusive locks

The InnoDB implements two standard row-level locks, a shared lock (S-lock) and an exclusive lock (X-Lock).

• A shared lock allows a transaction to read a record.
• An exclusive lock allows a transaction to update or delete a record lock.

If a transaction T1 has a shared lock that records R, the other transaction T2 the request to lock the row R is handled like this:

• If T2 wants to add an S-lock, he can get the lock right away. As a result, T1 and T2 have a shared lock of R.
• T2 want to get an X lock, then this request will be blocked.

If a transaction T1 has an X lock on row R, the other transaction will be blocked regardless of what lock it wants to get R. The transaction T2 must first wait for T1 to release the S lock above R.

Intent lock

The InnoDB supports multi-granularity locks, allowing row and table locks to coexist. To support multi-granularity locks, InnoDB introduces intent locks. An intent lock is a table-level lock that indicates that a transaction is followed (requires later) to get the S-Lock or X-lock of some rows in the table. Two intent locks are used in innodb (assuming that the transaction T has requested a corresponding intent lock to table T)

• Intent shared Lock (IS): Transaction T wants to add an S lock to some rows in table T.
• Intent exclusive Lock (IX): Transaction T wants to add an X lock to these rows.

For example

SELECT inch SHARE MODE  

The is lock is set, and the

SELECT  for UPDATE

An IX lock is set.

The intent Lock Protocol (the intention locking protocol) is as follows:

• Before a transaction acquires an S lock on a row in a table T, he must first obtain the is lock or a more heavyweight lock (x, etc.)
• Before a transaction obtains a row of X locks, he must first obtain an IX lock on the table T.

These rules can be summed up as the following chart (horizontal indicates that a transaction has acquired the corresponding lock, portrait of another lock wants to get the corresponding lock-this phrase I added):

	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	No conflict	Conflict	No conflict
S	Conflict	Conflict	No conflict	No conflict
Is	Conflict	No conflict	No conflict	No conflict

When a lock on a row does not conflict with a lock that already exists (compatible), it can be obtained by a transaction request, not if it is a conflicting word. The transaction must wait for the conflicting lock to be released (to obtain the desired lock). If a lock request conflicts with an existing lock and has not been locked, a deadlock may have occurred, or an error has occurred.

Therefore, intent locks are not blocked except for full table requests (for example, lock TABLES ...). WAITE) other than the request. The primary purpose of the IX and is locks is to indicate that a request (someone) has been locked for a row, or that a row of records will be locked.

The transaction data recorded during the intent lock and lock process resembles the following INNODB monitoring output using the SHOW ENGINE INNODB status statement:

　　

TABLE TABLE ' test. 't'  TRX ID 10080 lock mode IX

　　

Row lock

A row lock is a lock that is added to the index record, for example, SELECT C1 from t WHERE c1 = ten for update; prevents any other transactions from inserting, updating, or deleting records that T.C1 = 10.

Row locks are always locked on the index record, even if a table does not have any indexes, in which case InnoDB creates a hidden clustered index and uses the index to add row locks.

Transaction data recorded during a row lock lock is similar to the following INNODB monitoring output using the SHOW ENGINE INNODB status statement:

RECORDS LOCKSSpaceId -Page no3N Bits the Index 'PRIMARY'  of Table 'Test'.'T'Trx ID10078Lock_mode X Locks Rec but notGaprecord Lock, Heap on 2Physical Record:n_fields3; Compact format; Info bits0 0:Len 4; Hex 8000000a;ASC  ;;1:Len 6; Hex 00000000274f;ASC  'O;; 2:len 7; Hex b60000019d0110; ASC;;

Gap Lock

A gap lock is a lock that is added to the gap in the index record, or a lock that is added to the interval after the first index record and after the last index record. For example, SELECT C1 from T WHERE C1 between and a for UPDATE; Prevents all other transactions from inserting a record of t.c1 = 15, regardless of whether a column in the table already has a value of 15 (or another 10-20 value), because the gap within that range has been added to the lock.

The gap may include only one index value, or it may include multiple index values, or even no index values.

A statement that uses a unique index to find a unique record is not used to a gap lock (this does not include statements that have only one part of a unique index in the query condition). For example, if the ID column has a unique index, the following statement will only use row locks above the line with id = 100, without caring whether the other sessions (session) are inserting data in the gaps above.

SELECT *  from WHERE =  ;  (The sense should be added a for UPDATE or in SHARE MODE or database isolation level serializable)

If the ID column does not have an index or is not a unique index, the statement is locked on the above gap.

It is worth reminding that different transactions can have conflicting locks on the gap. For example, transaction A can have a shared lock on one gap (Gap S-lock), while another transaction can have an exclusive lock on the same gap (Gap X-lock). This is because if an index record is deleted, the corresponding clearance locks owned by the other transaction must be merged (forcibly translated, not quite understood, original: The reason conflicting gap locks is allowed was that if a record is purged From a index, the gap locks held on the record by different transactions must is merged.)

Gap locks are "completely forbidden" (forced translation: Purely inhibitive, see below), meaning that they only prohibit other transactions from inserting data into the gap, without prohibiting other transactions from acquiring a gap lock in the same interval. Therefore, the clearance X lock and the gap S lock play the same effect.

The clearance lock can be explicitly prohibited. You can set the transaction isolation level to READ COMMITTED or enable the Innodb_locks_unsafe_for_binlog system variable (deprecated is now not recommended). In these cases, the gap lock is disabled in the query and index scans and will only be used when the foreign KEY constraint checks and the duplicate index checks.

Using the Read COMMITTED isolation level and enabling Innodb_locks_unsafe_for_binlog system variables can also cause some additional impact. After MySQL has parsed the WHERE condition, the row locks above the mismatched rows are freed. For the UPDATE statement, InnoDB makes a "semi-consistent" read, which returns the latest committed version (the line record) to MySQL, and then MySQL

Determines which rows match the WHERE condition of the UPDATE.

　　

Next-key Locks

A Next-key lock is a union of a gap lock on a line lock and a gap before the row lock.

InnoDB implements row-level locks in this form, and when he finds or scans a table index, it encounters a matching index record, with a corresponding shared lock or exclusive lock on it. Therefore, row-level locks are actually index record locks. The Next-key lock is the same as the gap before the index record is affected. That is, Next-key Lock is an index record lock plus a gap lock on the gap before the index record. If a session has a shared or exclusive lock above the index of the record R, another session cannot immediately insert a new record in the gap before the R record index order.

Suppose you have an index that contains values 10,11,13 and 20. A next-key lock may be added to the following interval (the symbol means that it does not contain an endpoint, and the] symbol represents the containing endpoint.

(Negative infinity,ten] (all,each) ( (positive Infinity)

In the last interval, Next-key lock locks the maximum value in the index to an "upper bound" (a false index record whose value is greater than all the values in all index Records). The upper bound is not a real index record, so, in fact, this nex-key lock only locks the interval after the maximum index value.

By default, InnoDB enables repeatable READ transaction isolation level. In this case, InnoDB uses Nex-key lock when locating and scanning the index, which avoids phantom (shadow) rows.

The transaction data recorded during the Next-key lock lock process resembles the following INNODB monitoring output using the SHOW ENGINE INNODB status statement:

　　

RECORD LOCKSSpaceId -Page no3N Bits the Index`PRIMARY` of Table' test '. ' t ' Trx ID10080Lock_mode Xrecord Lock, Heap no1Physical Record:n_fields1; Compact format; Info bits00:Len 8; Hex 73757072656d756d;ASCsupremum;; Record Lock, Heap no2Physical Record:n_fields3; Compact format; Info bits00:Len 4; Hex 8000000a;ASC ;;1:Len 6; Hex 00000000274f;ASC 'O;; 2:len 7; Hex b60000019d0110; ASC;;

Insert Intent Lock

Insert Intent Lock

　

Auto-inc Locks (self-growing lock)

Self-growing locks are special table-level locks that are used when inserting records into a table with auto_increment rows. In the simplest case, when a transaction inserts data into a table, any other transaction waits, so the row inserted by the first transaction can have a contiguous primary key value.

The Innodb_autoinc_lock_mode configuration option controls the algorithm used by the self-growing lock. He allows you to weigh the tradeoffs between predictable self-growth values and maximizing concurrent insertions.

Assertion locks for Spatial indexes

The INNODB supports the spatial index above the spatial column.

Next-key locking does not work well with support for repeatable READ and SERIALIZABLE transaction isolation levels when dealing with related locking operations that include spatial indexes. In the multidimensional data, there is no absolute order, so the "next" key is not very clear where.

To support isolation levels with spatial indexes, InnoDB uses an assertion lock. The spatial index contains the minimum add-on matrix (minimum bounding rectangle = MBR) value, so InnoDB enforces an assertion index on the MBR value used in the query statement when the index consistency is read. Other transactions cannot insert or modify rows that match this query condition.

　　

MySQL Lock and transaction (i): InnoDB lock (MySQL Official document Coarse)