As mentioned earlier, InnoDB can lock at any time, but it can’t unlock at any time. Specifically, InnoDB uses a two-phase locking protocol: The lock operation can be performed at any time during the transaction execution, but the lock is released only when the transaction performs COMMIT or ROLLBACK, and all locks are released at the same time.
In addition, row-level locking is only implemented at the storage engine level, while for InnoDB storage engine, there are three types of row-level locking, or three algorithms:
- Record Lock: Record Lock
- Gap Lock: Gap Lock
- Next-key Lock: next-key Lock
Now, let’s explain these three row locking algorithms in detail.
Record Lock Record Lock
As the name implies, a record lock locks a row of records. In fact, it blocks the index record of that row. The InnoDB storage engine uses an “implicit primary key” to lock the table if no index is set when the table is created.
Implicit primary key means that InnoDB storage engine will use the first non-empty column as the primary key if no primary key is specified when creating a table. If not, a 6-byte primary key column is automatically generated.
So, since Record Lock is index-based, a row-level Lock will degenerate into a table Lock if our SQL statement has a condition that invalidates the index (such as using or) or if the condition does not involve an index or primary key at all.
Record the Lock sample
Select * from database where id = primary key;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Copy the codeThe initial data looks like this:
Create two new transactions and execute the first two rows of transaction T1 without committing:
Select * from T1 where id = 1; select * from T1 where id = 1; select * from T1 where id = 2;
Select * from table 1 where id = 2 and id = 2; select * from table 1 where id = 2; select * from table 1 where id = 2;
Nice, sure enough. Commit (id = 1); username (id = 1);
Example of row lock degradation to table lock
Here’s another example without an index:
Similarly, create two new transactions and execute the first two rows of transaction T1 without committing. We tried to use select… Select * from T1 where username = “primary key” and username = “index” where username = “primary key” and username = “index” where username = “primary key”;
Since no COMMIT is performed, transaction T1 does not release the lock at this time and the entire table is locked. Transaction 2 (id = 5); transaction 2 (id = 5);
Can lock conflicts occur when two different records have the same index?
The answer to this question should be simple, as we emphasized above that a row lock locks an index, not a single record (although it makes sense to say which record is locked). So if two different records that two transactions operate on separately have the same index, one transaction will wait because the row lock is occupied by another transaction.
Gap Lock Gap Lock
Unlike Record Lock, which is based on a unique index, Gap Lock and Next-key Lock are based on a non-unique index.
A Gap Lock and a next-key Lock Lock a range of index records. A Gap Lock and a next-key Lock Lock a range of index records.
select * from test where id between 1 and 10 for update;
Copy the codeSQL > Gap Lock (1, 10, 1, 3, 4, 5, 6, 7, 8, 9); Gap Lock (2, 3, 4, 5, 6, 7, 8, 9); Gap Lock (2, 3, 4, 5, 6, 7, 8, 9);
Attention! If you want to insert a new record with id = 6, you can’t. If you want to insert a new record with id = 6, you can’t.
Next-key Lock Next-key Lock
Next-key Lock is a locking algorithm combining Gap Lock and Record Lock. Its main purpose is to solve the phantom reading problem.
For example, if an index has values 10,11,13, and 20, and an index has values 10,11,13, and 20, and an index has values 10,11,13, and 20, and an index has values 10,11,13, and 20, and an index has values 10,11,13, and 20, and an index has values 10,11,13, and 20.
(- up, 10](10, 11](11, 13](13, 20](20 + up]
As you may have noticed, the next-key Lock differs from the Gap Lock in that it locks on the left to the right, that is, it contains the index record being operated on.
Next-key Lock is the default algorithm used for row locking under InnoDB’s default isolation level REPEATABLE-READ. However, if the index being operated on is a unique index or primary Key, InnoDB optimizes the next-key Lock to degrade it to a Record Lock, which locks only the index itself, not the range.
Since a primary Key is also a unique index, we can say that a Record Lock is based on a unique index, and a next-key Lock is based on a non-unique index.
Note that InnoDB will first Lock the non-unique index with Record Lock, then use next-key Lock and Gap Lock to process the non-unique index, rather than just Lock the non-unique index. So let’s do an example.
Next – Key Lock sample
Suppose we add a new column to the test table and set it to a non-unique index:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`class` int(11) NOT NULL.PRIMARY KEY (`id`),
KEY `index_class` (`class`) USING BTREE COMMENT 'Non-unique index'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Copy the codeInsert some data:
Open a transaction 1
select * from test where class = 3 for update;
Copy the code
In this case, InnoDB actually adds three rows (select *… Select * from update where X = X;
Select * from primary key where id = 105
2) for non-unique index class = 3, add Next Key Lock, Lock range = (1,3)
3) In particular, InnoDB stores a Gap Lock on the next key of the non-unique index class (in table class = 3, the next key is 6), so there is a Gap Lock on the index range (3, 6)
To summarize 2) and 3), for this SQL statement, InnoDB storage engine is locked to the class index range of (1, 6).
Let’s test this theory in practice by opening transaction 2 and executing the following statement:
As expected, since the SQL statement executed in transaction 1 already has an X lock on the record in column A =105 in the primary key index, the X lock on the record will be blocked.
Another transaction executes the following SQL statement:
Primary key insertion 104 has no problem, but the class index 2 inserted is in the locked range (1,6), so the execution will also be blocked.
SQL > select * from SQL where SQL > SQL > SQL > SQL
Attention
Note that the degradation of a next-key Lock to a Record Lock only exists when all unique index columns are operated on. If the unique index consists of multiple columns and only one of the columns is operated on, the InnoDB storage engine still uses next-key Lock for locking.
| flying veal 🎉 pay close attention to the public, get updates immediately
- He is a master’s student at Southeast University and a summer intern in Java background development at Ctrip. In his spare time, he operates an official account “Flying Veal”, which was opened on December 29, 2020/12/29. Focus on sharing computer foundation (data structure + algorithm + computer network + database + operating system + Linux), Java technology stack and other related original technology good article. The purpose of this public account is to let you can quickly grasp the key knowledge, targeted. Follow the public number for the first time to get the article updates, growth on the way we progress together
- And recommend personal maintenance of open source tutorial projects: CS-Wiki (Gitee recommended projects, now a total of 1.8K + STAR), is committed to create a perfect back-end knowledge system, less detour on the road of technology, welcome to come to exchange learning partners ~ 😊
- If you do not have a good project in spring and autumn, you can refer to a project I wrote “open source community system Echo” Gitee official recommendation project, has accumulated 900+ STAR, SpringBoot + MyBatis + Redis + Kafka + Elasticsearch + Spring Security +… And provide detailed development documents and supporting tutorials. The public account background reply Echo can get the supporting tutorial, which is still being updated.