preface

We often hear a variety of MySQL locks, will not be confused about their relationship and their role in MySQL, today we come to learn about it seriously ~

The lock type

Lock is introduced

Locking granularity

Table locks

Table locking is the largest-grained locking mechanism in MySQL. It locks the entire table and avoids deadlocks. It is the largest-grained locking mechanism in MySQL.

TABLE locking is implemented by MySQL Server. It is generally used to lock the entire TABLE when executing DDL statements, such as ALTER TABLE operations. LOCK TABLES can also be used to explicitly specify that a table is locked when a DML statement is executed.

Page locks

Page-level locking is a unique locking level in MySQL and is not common in other database management software.

The granularity of page-level locks is between row-level locks and table-level locks, so the resource cost of obtaining locks and the concurrent processing power provided by locks are also in between. In addition, page-level locks, like row-level locks, can be deadlocked.

Page-level locking is mainly used in BDB storage engines.

Row locks

The lock granularity of row-level lock is the smallest in MySQL. The lock is only applied to the current operation, so row-level lock has the lowest probability of locking resource contention.

Row-level locking is mainly used in InnoDB storage engine.

The features of the three types of locks in MySQL can be summarized as follows:

engine	Table locks	Row locks	Page locks
InnoDB	Yes	Yes	No
MyISAM	Yes	No	No
DBD	Yes	No	Yes

compatibility

Shared/exclusive lock

InnoDB implements standard row-level locking, with two types of locks, shared (S) and exclusive (X).

• Shared locks allow transactions to hold locks and read rows.
• Exclusive locks allow transactions to hold locks, update or delete rows.

Share lock SELECT... LOCK IN SHARE MODECopy the code

SELECT... FOR UPDATECopy the code

The lock mode

Record locks

Record Locks are also called row Locks, and as the name implies, a row of records is locked.

SELECT * FROM test WHERE id = 10 FOR UPDATE;Copy the code

The row with id 1 is locked to prevent rows from being inserted, updated, or deleted.

Record locks always lock index records (both SELECT and UPDATE are locked), even if the table has no defined index. In this case, InnoDB creates a hidden clustered index and uses it for record locking. But because a full table scan is possible, the lock degrades to a table lock.

Note:

1. The ID column must be a unique index or primary key column, otherwise the lock added by the above statement becomes a temporary key lock.
2. The query statement must be an exact match=, can’t>,<,likeAnd so on, otherwise it will degenerate into a key lock.

Clearance lock

Gap Locks lock an interval for gaps between indexed (not unique) records. Attention! A gap lock locks an interval, not just the rows that currently exist within that interval.

SELECT * FROM test WHERE id BETWEEN 10 and 15 FOR UPDATE;
Copy the code

Insert rows 11,12,13,14 are blocked, but rows 10 and 15 are not locked.

It is also worth noting here that different transactions can hold conflicting locks on gaps. For example, transaction A can hold A shared gap lock (A gap S lock) on A gap, while transaction B holds an exclusive gap lock (A gap X lock) on the same gap. The reason for allowing conflicting gap locks is that if records are cleared from the index, the gap locks held by different transactions on the record must be merged.

Note:

1. Searching a unique row using a unique index does not require gap locking. (This does not include cases where search criteria contain only some columns with multiple unique indexes; In this case, gap locking does occur.)

SELECT * FROM test WHERE id = 10 FOR UPDATE;Copy the code

If the ID is a unique indexed column, no gap locking is required. But if the ID is a normal index or not indexed, the statement locks the preceding gap.

2. At the RC isolation level, gap locks are not used. Gap locks exist only when the isolation levels are RR and Serializable.
3. Gap locks can coexist. A gap lock adopted by one transaction does not prevent another transaction from adopting a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other and perform the same function.

Key in the lock

Next-key is a combination of record lock and gap lock. The illusory problem can be solved by using temporary key locking.

By default, InnoDB runs at REPEATABLE READ transaction isolation level. In this case InnoDB uses near-key locks for searches and index scans to prevent phantom rows such as SELECT… In share mode or select… The for UPDATE statement.

But even if your isolation level is RR, if you are using a normal SELECT statement, InnoDB will be snapshot reads and will not use any locks, so there is still no protection against phantom reads.

Remember, the gap in front of the index is locked! For example, an index contains the values 10,11,13, and 20. So, the scope of a key lock is as follows:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
Copy the code

Summary: When InnoDB performs UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE on a row based on a non-unique index IN RR transaction isolation, InnoDB acquires a temporary key LOCK FOR the row and the next interval gap LOCK FOR the row.

Intent locks

Intention Locks are table-level Locks that indicate what type of lock (shared or exclusive) a transaction needs to use later on the row in the table.

There are two types of intent locks:

• Intentional shared lock (IS) : before a transaction acquires an S lock (on any row or table), an IS lock must be placed on the table in which it resides.
• Intentional exclusive lock (IX) : a transaction must place IX locks on its table before acquiring X locks (on any row/table).

The intent locking protocol is as follows:

• Before a transaction can acquire a shared lock for a row in a table, it must first acquire itISTable lock or stronger lock.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire itIXTable locks.

Table lock type compatibility is summarized in the following matrix:

Request lock \ existing lock	X	IX	S	IS
X	conflict	conflict	conflict	conflict
IX	conflict	Compatible with	conflict	Compatible with
S	conflict	conflict	Compatible with	Compatible with
IS	conflict	Compatible with	Compatible with	Compatible with

A lock is granted to the requesting transaction if it is compatible with an existing lock, but not if it conflicts with an existing lock. Transactions wait until conflicting existing locks are released. An error occurs if a lock request conflicts with an existing lock and cannot be granted because it would cause a deadlock.

In addition to full table requests (for example, LOCK TABLES… Intent locks do not block anything except WRITE. The main purpose of an intentional lock is to indicate that someone is locking a row or intends to lock a row in a table.

Attention! Here’s the purpose of the intent lock: You can quickly determine if the table has a row lock. Suppose that transaction T1 locks several records on the table with X locks, then there is an IX lock on the table, i.e., intentional exclusive lock. Transaction T2 will LOCK TABLE… WRITE’s request for a table level lock can determine whether there is a lock conflict based on whether the intended lock exists.

Insert intent lock

Insert Intention Locks are gap Locks created by an Insert operation before a row is inserted. This lock is used to indicate the insertion intention. When multiple transactions insert multiple pieces of data at different positions in the same interval, transactions do not need to wait for each other. Suppose there are two records with values 4 and 7, and two different transactions attempt to insert two records with values 5 and 6, respectively. Each transaction obtains a gap lock between (4, 7) before acquiring an exclusive (exclusive) lock on the inserted row, but because rows do not conflict, So there is no conflict (blocking wait) between the two transactions.

In summary, the insert intent lock feature can be divided into two parts:

1. Inserting intent locks is a special kindClearance lockIf a gap lock locks an interval, then an insert intent lock locks a point.
2. Insert between intent locksMutual exclusion, so even if multiple transactions insert multiple records in the same interval, as long as the records themselves (primary key, unique index) do not conflict, there will be no conflicting wait between transactions.

It should be emphasized that although intent lock insertion contains three words, it does not belong to intent lock but to gap lock, because intent lock is a table lock while intent lock insertion is a row lock.

On the lock

Auto-inc Locks are a special type of table-level lock that is used primarily for inserting auto-increment fields, the most common auto-increment primary key IDS, into transactions. In the simplest case, if one transaction is inserting a value into the table, any other transaction must wait for itself to be inserted into the table so that the row inserted by the first transaction receives consecutive primary key values.

The innodb_autoinc_lock_mode configuration item can be used to adjust the auto-increment lock algorithm.

Lock Example Analysis

Data table status

Table structure:

CREATE TABLE `test1` ( 
    `id` int(20) NOT NULL,
    `name` varchar(20) DEFAULT NULL, 
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Table initialization data:

mysql> select * from test1; +----+--------+ | id | name | +----+--------+ | 1 | user1 | | 5 | user5 | | 10 | user10 | | 15 | user15 | | 20 | user20 | | | 25 user25 | + - + -- -- -- -- -- -- -- -- + 6 rows in the set (0.00 SEC)Copy the code

Example 1

time	A transaction	Transaction B
T1	begin;
T2		begin;
T3	select * from test1 where id=11 for update; First request IX lock and obtain it successfully; The row record does not exist, so we get a gap lock (10,15).
T4		select * from test1 where id=12 for update; First request IX lock and obtain it successfully; The row record does not exist, so we get a gap lock (10,15).
T5	insert into test1(id,name) values(11, “user11”); Request to insert intent lock (11); The request must wait because transaction B has a gap lock
T6	In the lock wait	insert into test1(id,name) values(12, “user12”); Request insert intent lock (12); The request must wait because transaction A has A gap lock
T7	Transaction B is rolled back, transaction A immediately acquires the lock and is successfully inserted	Deadlock, transaction B is rolled back

Transaction B insert error:

Explanation:

In scenario 1, because IX locks are table locks and IX locks are compatible, both transaction 1 and transaction 2 can acquire IX locks and gap locks.

In addition, it should be noted that since our isolation level is RR and none of the corresponding records in the query exist when the X lock is requested, all gap locks are returned.

Example 2

time	A transaction	Transaction B
T1	begin;
T2		begin;
T3	select * from test1 where id=11 for update; First request IX lock and obtain it successfully; The row record does not exist, so we get a gap lock (10,15).
T4		select * from test1 where id=16 for update; First request IX lock and obtain it successfully; The row record does not exist, so we get a gap lock (15,20).
T5	insert into test1(id,name) values(11, “user11”); Request to insert intent lock (11), succeeded
T6	commit;	insert into test1(id,name) values(16, “user16”); Request to insert intent lock (12), succeeded
T7		commit;

Explanation:

There is no intersection between the two gap locks, and the insertion intent locks obtained by them are not the same point and there is no conflict, so they can be successfully executed.

conclusion

Through this study, we have already had a general understanding of MySQL lock classification. In the future, we will continue to think and find out what locks are added to different statements and whether they will cause deadlocks.