Shulou(Shulou.com)06/01 Report--

Innodb implements standard row-level locks, and there are two modes of locking:

L S (shared locks) shared lock: allows transactions that own locks to read a row of data

L X (exclusive locks) exclusive lock: allows transactions with useful locks to update or delete a row of data

If a transaction T1 has an S-shared lock on row r, a lock on row r is requested from a different transaction T2, as follows:

L transaction T2 can immediately acquire the S-shared lock of row r, so both T1 and T2 hold S-shared lock on row r.

L transaction T2 cannot acquire the X exclusive lock of row r

If one transaction T1 holds an x exclusive lock on row r, other transaction T2 cannot acquire any type of lock.

InnoDB intention locks Innodb supports multi-granularity locking, which allows locks on the row level and table level of the transaction to exist at the same time. To support locking operations at different granularities, innodb supports an additional locking method called intention locks.

Innodb intention locks are table-level locks designed primarily to reveal the type of lock that the next row will be requested in the next transaction.

There are 2 types of intention locks in InnoDB:

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

Transaction T wants to set shared locks on some rows of table t

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

Transaction T wants to set exclusive locks on some rows of table t

For example: SELECT... LOCK IN SHARE MODE sets an IS lock

SELECT... FOR UPDATE sets an IX lock

The intention locking protocol is as follows:

? A transaction must first acquire the IS intention sharing lock of table t when it can acquire some rows of table t.

? A transaction must first acquire the IX intention exclusive lock of table t in the X exclusive lock of some rows of table t that can be met.

Innodb Lock compatibility Matrix

Intentional locks do not block any requests other than full table operations (for example, LOCK TABLES... WRITE). The main purpose of intentional locks is to show that certain operations have locked or are about to lock a row.

The existence value of the intention lock is to provide a coarser-grained lock before locating the lock held by a particular row, which can greatly save the performance of the engine in locating and handling the lock, because inside the storage engine, the lock is maintained by an independent data structure, and the number of locks directly determines memory consumption and concurrent performance. For example, if transaction A modifies some rows of table t (DML usually produces an X lock), you need to add an intention exclusive lock to t. Before the A transaction is completed, the B transaction has a full table operation (alter table, etc.). At this time, the intention exclusive lock at the table level can tell B to wait (because of the intentional lock on t), and there is no need to judge the row level.

Intention locks can actually be understood as a "hint" of what kind of row-level locks are needed in the future:

IS indicated that shared locks may need to be added to some records of this table in the future.

IX indicates that you may need to add exclusive locks to some records of this table in the future.

The intention lock is table-level, and the IS and IX locks do not conflict with each other, but conflict with the table-level Splink X lock.

When adding an S lock or an X lock to a record, it must be ensured that it has a corresponding intention lock or a table level lock with higher lock strength on the same table.

Types of row locks for InnoDB record locks, gap locks, and Next-Key locks Innodb:

? Record lock: lock an index record

? Gap lock: locks a range that does not include the record itself

? Next-key lock: Gap lock+ Record lock, lock a range and lock the record itself

Record Locks always locks the index record, and even if the table does not have an index defined, Innodb creates a hidden cluster index to lock it.

The Next-key Locks default Innodb uses the isolation level of RR. In this case, InnoDB uses the next-key lock mechanism to query data and index scanning to prevent phantom problems

Next-key locking combines index-row locks with Gap locks. When searching and querying index operations, InnoDB uses row locks to set shared or exclusive locks on the corresponding index records. Therefore, the row-level lock is actually an index record lock. The Next-Key lock is an index record lock plus a range lock. If a session adds a shared or exclusive lock to index record R, other sessions cannot immediately insert a new index record in the gap before index record R.

Assuming that the values of an index are 10, 11, 13, and 20, then the interval of the index may be Next-Key Locking:

For the last interval range, the range that Next-Key actually locks is only the range after the maximum index value.

When the operating index is unique, Innodb optimizes Next-key Locking by downgrading it to Record Lock, locking only the index itself, not the range.

As follows:

Session A

Session B

Session A > drop table t

Session A > create table t (an int primary key)

Session A > insert into t select 1

Session A > insert into t select 2

Session A > insert into t select 5

Session A > begin

Session A > select * from t where axi5 for update

+-- +

| | a |

+-- +

| | 5 |

+-- +

1 row in set (0.00 sec)

Session A > begin

Query OK, 0 rows affected (0.00 sec)

Session A > insert into t select 4

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

# there is no wait at this time, you can insert and submit

Session A > commit

Query OK, 0 rows affected (0.00 sec)

# session A commits the above transaction Session A > commit

Query OK, 0 rows affected (0.00 sec)

In the above experiment, table t has three values: 1, 1, 2, 5. In session A, read X locks are performed on axi5, but because field an is the primary key (unique index), only the index record 5 is locked at this time, not in the range of (2meme 5)], so that it can be inserted smoothly in Session B (4 between (2magin5)) without blocking. The above formal reason is that the record of the unique index in Next-Key Lock is downgraded to record lock, which can improve the concurrency of the application.

When the column of the query is a non-unique index, the situation is as follows:

Session A

Session B

Session A > drop table t

Session A > create table t (id int primary key,v int, key (v))

Session A > insert into t values (2, 20), (6, 13), (10, 15), (9, 15), (3, 20), (8, 25)

Session A > begin

Session A > select * from t where vault 20 for update

In this case, V is a non-unique index, and for update lock reads add X row locks to the corresponding rows of clustered index 3 that satisfies Vroom20, while index V adds Next-key Lock, and the locking range is [15 ~ 20] and the next range (20 ~ 25).

Note: Session B on the right sees that 15 is blocked and 20 is not blocked; therefore, it is speculated that the range of Gap Lock here is [15 ~ (20)] and (20 ~ (25)). This is contrary to the possible interval range given by the official http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html, which happens to be a closed interval.

# 18 between [155.20), blocked

Session B > insert into t select 11pr 18

# 20 the X lock that holds the secondary index on the record is blocked

Session B > insert into t select 11pr 20

# 21 is blocked between (20,025)

Session B > insert into t select 115021

# 15 between [155.20), blocked

Session B > insert into t select 110.15

# 25 inserted successfully

Session B > insert into t select 11. 25

Query OK, 1 row affected (0.00 sec) # 14 and 26 were successfully inserted outside the gap range.

Session B > insert into t select 12. 14

Query OK, 1 row affected (0.00 sec)

Session B > insert into t select 130.26

Query OK, 1 row affected (0.00 sec)

Gap Locks does not need Gap locks. Exe when querying unique rows with a unique index.

The previous example of next-key shows that gap may override a single index value, multiple index values, or may be empty.

For example, the following statement requires only one index-record lock on a row with an id of 100 without affecting other sessions to insert data:

SELECT * FROM child WHERE id = 100for update

If ID is not an index or a uniq index, the statement uses Gap Locking. Exe.

The insert intention Gap lock (insert intention gap lock) is set before the Insert operation. The purpose of this lock is that when multiple transactions insert data into the same index gap, they do not have to wait for each other as long as they are in different positions within the index gap. If there are index values 4 and 7, a transaction view inserts 5 and 6 between 4 and 7, and an insert intention Gap lock is assigned on the inserted row, but because the rows of 5 and 6 do not conflict, they do not block each other.

Note: different transactions can hold mutually exclusive locks on a Gap. For example, when transaction B has an exclusive Gap lock on a Gap, transaction A can hold a shared Gap lock on the same Gap. The reason why lock conflicts are allowed to coexist here is that when a record is purge from an index, the Gap locks imposed by other transactions on that record are merged.

Gap locks are "completely prohibited" in Innodb. This means that they can only prevent other transactions from inserting in a Gap. Therefore, exclusive Gap locks and shared Gap locks have the same effect.

Disabling Gap Locking

Gap locks can be completely closed by setting the transaction isolation level to change to RC, or by turning on the innodb_locks_unsafe_for_binlog system variable (options 5.6 and 5.7 have been deprecated). When Gap is turned off, gap locks do not take effect except for foreign key consistency checks and progressive conflict checks.

The RC isolation level and turning on the innodb_locks_unsafe_for_binlog parameter also have other effects, for example, after mysql analyzes the where condition, the record lock that does not match the row is released (in violation of the 2PL:Two Parsing Lock principle). For example, in update, innodb performs a "semi-consistent" read, so that the latest commit version is told to mysql, and it is then up to mysql to decide whether the row matches the where condition for performing the Update operation.

Reference link:

Http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html

Http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html

Http://hedengcheng.com/?p=771#_Toc374698322

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.