Three algorithms for row locking

InnoDB storage engine has three algorithms for row locking, which are:

• Record Lock: Scope on a single row Record
• Gap Lock: A Gap Lock that locks a range but does not contain the record itself
• Next-key Lock: Gap Lock + Record Lock locks a range, and locks the Record itself

Record Lock always locks index records. If InnoDB storage engine is set up without any index, InnoDB storage engine uses an implicit primary key to Lock records.

Next-key Lock is a locking algorithm that combines Gap Lock and Record Lock. Under next-key Lock algorithm, InnoDB uses this locking algorithm for row queries. For example, an index with 4 values 9,11,13,20 May be next-key Locking: (- &, 9] (9,11] (13,20] (20,+ &)

The next-key Locking technology is called next-key Locking. This design is designed to solve the Phantom Problem. With this locking technique, instead of a single value, a range is locked.

Innodb storage engine optimizes next-key Lock to downgrade it to a Record Lock, which locks the index Record itself rather than the range, when the query index contains unique attributes. For unique indexes, a Record Lock is added, which locks only the Record itself. There are special cases where the unique index consists of multiple columns and the query is only looking for one of the multiple unique index columns, so the next-key lock is still used.

DROP TABLE
IF EXISTS t;

CREATE TABLE t (a INT PRIMARY KEY);

INSERT INTO t
VALUES
	(1),
	(2),
	(5);
Copy the code

Next-key degrades to a Record Lock only if the column being queried is the only index. With secondary indexes, the situation is completely different.

CREATE TABLE Z (
	a INT,
	b INT,
	PRIMARY KEY (a),
	KEY (b)
);

INSERT INTO Z
VALUES
	(1, 1),
	(3, 1),
	(5, 3),
	(7, 6),
	(10, 8);
Copy the code

Column B of table Z is the secondary index if the following SQL statement is executed in session A:

SELECT * FROM Z WHERE b=3 FOR UPDATE;
Copy the code

Obviously, at this time, the SQL statement is queried through index column B, so it uses the traditional next-key Locking technology to lock, and since there are two indexes, they need to be locked separately. For clustered indexes, it adds a Record Lock only to indexes where column A equals 5. For secondary indexes, next-key Locking is added, and the Locking scope is (1,3). It should be noted that InnoDB storage engine adds gap lock to the Next Key value of secondary indexes, that is, there is another secondary index lock with the secondary index scope of (3,6). Therefore, if you run the following SQL statement in a new session B, it will block:

SELECT * FROM Z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO Z SELECT 4,2;
INSERT INTO Z SELECT 6,5;
Copy the code

The first SQL statement cannot execute because the SQL statement executed in session A has aggregated the value of column A =5 in the index plus the X lock, so execution will be blocked. The second SQL statement, primary key insert 4, is fine, but the secondary index insert value 2 is in the locked range (1,3) so execution is blocked as well. SQL > insert primary key 6 not locked and 5 not in range (1,3); But the inserted value 5 is in another lock range (3,6), so it also needs to wait. The following SQL statement, however, is not blocked and can be executed immediately:

INSERT INTO Z SELECT 8,6;
INSERT INTO Z SEELCT 2,0;
INSERT INTO Z SELECT 6,7;
Copy the code

As you can see from the above example, Gap Lock is used to prevent multiple transactions from inserting records into the same range, which can cause Phantom problems. For example, in the example above, the user in session A has locked the record b=3. If there is no Gap Lock at this time (3, 6), then the user can insert the record in index B column 3, which causes the user in session A to return A different record when the same query is executed again, causing Phantom Problem.

Users can explicitly close Gap Locks in two ways:

• Set the isolation level of the transaction to READ COMMITTED
• Set innodb_locks_unsafe_for_binlog to 1

In this configuration, only Record Locks are used except for foreign key constraints and Gap locks, which are still required for uniqueness checking. Keep in mind, however, that this setup breaks transaction isolation and, in the case of Replication, can lead to inconsistencies between master and slave data. In addition, READ COMMITTED is no better than the default transaction isolation level READ REPEATABLE in terms of performance.

In the InnoDB storage engine, for INSERT operations, it checks whether the next record of the inserted record is locked, and if it is locked, queries are not allowed. In the above example, session A has locked the table z where b=3, i.e., the range (1,3) has been locked, and the following inserts in other sessions will block as well:

INSERT INTO Z SELECT 2,2;
Copy the code

Because when a record of value 2 is inserted into secondary index column B, the next record 3 is detected to have been indexed. Instead, change the insert to the following value, which can be performed immediately:

INSERT INTO Z SELECT 2,0;
Copy the code

Finally, for unique Key locking, next-key locking is degraded to a Record Lock that exists only in a column of all unique indexes queried. If the unique index consists of multiple columns and the query is looking for one of multiple unique index columns, then the query is actually a range query, not a point query, so InnoDB storage engine still uses next-key Lock.

To solve the Phantom Problem

At the default transaction isolation level, REPEATABLE READ, InnoDB storage engine uses next-key Locking to avoid Phantom Problem. This may be different from other databases, such as Oracle, which may require transaction isolation level SERIALIZABLE to resolve Phantom Problem.

Phantom Problem means inThe same transactionUnder, executing the same SQL statement twice in a row may result in different results, and the second SQL statement may return a row that did not exist before.

This example will be demonstrated below, using the table T created in the previous section. Table T consists of three values: 1, 2 and 5:

DROP TABLE
IF EXISTS t;

CREATE TABLE t (a INT PRIMARY KEY);

INSERT INTO t
VALUES
	(1),
	(2),
	(5);
Copy the code

Transaction T1 execute the following SQL statement:

SELECT * FROM t WHERE a> 2 FOR UPDATE;
Copy the code

Note that transaction T1 did not commit and should return 5. If, at the same time, another transaction T2 inserts the value 4, and the database allows this operation, then transaction T1 executes the above SQL statement again, resulting in results 4 and 5. This is different from the first result and violates the isolation of the transaction, meaning that the current transaction can see the results of other transactions. Table 6-13 shows the process:

The default transaction isolation level of InnoDB storage engine is REPEATABLE READ. In this isolation level, next-key Locking is adopted for Locking. In READ COMMITTED transaction isolation level, only Record Lock is used, so in the above example, session A needs to set the transaction isolation level to READ COMMITTED.

In addition, users can check uniqueness at the application level through the next-key Locking mechanism of InnoDB storage engine. Such as:

SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;
If not found any row:
# unique for insert valueINSERT INTO table VALUES (...) ;Copy the code

If the user queries a value through an index and applies an SLock to the row, the range is locked even if the queried value is not there, so if no rows are returned, the interpolated value must be unique. Some readers may wonder if there is a problem with this uniqueness check if there are multiple transactions running concurrently during the first SELECT •••LOCK IN SHARE MODE operation. It does not, because a deadlock occurs. Only one transaction inserts successfully, and the remaining transactions throw deadlock errors, as shown in Table 6-14.

This article is adapted from inside MySQL Technology :InnoDB Storage Engine, 2nd edition

Personal wechat official Account:

Individual making:

github.com/jiankunking

Personal Blog:

jiankunking.com