preface
In the article “What locks are there in MySQL?”, we describe the various locks that exist in MySQL. We also give a general description of the various locks that occur in MySQL. Today’s article will look for some practical examples to explain specifically what SQL locks. This gives you a better understanding of how to deal with deadlocks in real life development.
Lock constraints
A SQL record to add what lock, this inside actually have a lot of road, not a word or two words can say clearly. SQL > lock SQL > lock SQL > lock SQL > lock SQL
- Transaction isolation level: As mentioned in the previous article, gap locks are generally only present at transaction isolation levels that are no less than repeatable reads, but are also present at read committed isolation levels in special cases. This clearly tells us that what locks SQL places on records are affected by the transaction isolation level.
- Whether to use indexes and index types in SQL execution: We all know that indexes can be divided into normal indexes, unique indexes, primary key indexes, union indexes, full-text indexes, and so on. MySQL lock is also based on index implementation, SQL to use different indexes and no index, the lock must be different.
- Query method: SQL is accurate query, fuzzy query or range query.
- Types of SQL statements: SELECT, INSERT, UPDATE, and DELETE statements lock records differently.
Knowledge supplement
- Lock read: refers to the following four statements:
- The SELECT… LOCK IN SHARE MODE
- The SELECT… FOR UPDATE
- UPDATE
- DELETE
The snapshot to read:
- At uncommitted levels, the latest version of the record is read each time.
- Read the committed version each time at the committed level.
- The impact of transaction isolation level on locking
MySQL transaction isolation level has a significant impact on what locks are added, so the first thing to do when analyzing specific locking scenarios is to determine the current transaction isolation level.
-
Read Uncommitted (RU) : A Read is not committed. Dirty reads, unrepeatable reads, and phantom reads can occur at this level. There are almost no business scenarios for the application of this isolation level, so it is ignored.
-
Read Committed (RC) : Unrepeatable reads and magic reads can occur at this level, but dirty reads are not allowed. Locks the current read data.
-
Repeatable Read (RR) : Phantom Read may occur at this level, and dirty Read and non-repeatable Read are impossible. Record lock is added to the data obtained from the current Read, and gap lock is added to the involved range to prevent phantom Read caused by new data insertion.
-
Serializable: No MVCC concurrency control, direct lock-based concurrency control, no snapshot read, all current read, concurrency efficiency drops dramatically. This transaction isolation level is not recommended and is therefore not the focus of this article.
The read uncommitted and serialized isolation levels are not used much in real business scenarios, so we will focus on read committed and repeatable reads instead.
Which of the two isolation levels did your project choose? So why choose a certain level? Have you thought about it?
In general, at the isolation level of read committed transactions, the basic unit of locking is the record lock. At the repeatable read transaction isolation level, the basic unit of locking is the Next key lock. In addition, regardless of the isolation level, a record lock is added to the read records as long as the search is unique and the read records are not tagged for deletion.
It can be called a unique search if the following four conditions are met:
- The matching mode is exact matching
- The index used is a primary key or unique secondary index
- If the index used is a unique secondary index, the index must not hold NULL values
- If the only secondary index contains multiple columns, each column will be used (note the leftmost matching rule for federated indexes)
What locks are added to different SQL statements?
The experiment to prepare
Before specific examples, we first prepare some experimental materials, such as creating a table test. The specific mechanism is as follows:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT 'name',
`no` varchar(32) NOT NULL COMMENT 'number',
`city` varchar(255) NOT NULL COMMENT '所在城市',
`gender` char(1) NOT NULL COMMENT 'gender',
`remark` varchar(32) DEFAULT NULL COMMENT 'note'.PRIMARY KEY (`id`),
UNIQUE KEY `uk_no` (`no`) USING BTREE,
KEY `idx_name` (`name`) USING BTREE,
KEY `idx_city_gender` (`city`,`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
Copy the codeInsert some data into test:
INSERT INTO `test` VALUES ('5'.'Joe'.'133132'.Nanjing 'n'.'male'.'epbujch');
INSERT INTO `test` VALUES ('9'.'Cathy'.'123009'.Hefei 'h'.'male'.'epbujchuf1k');
INSERT INTO `test` VALUES ('10'.'李翔'.'123000'.Nanjing 'n'.'male'.'3roa0');
INSERT INTO `test` VALUES ('15'.'李建'.'123132'.Beijing 'b'.'male'.'g3ruwrnoa0');
INSERT INTO `test` VALUES ('16'.'bill'.'123001'.'s Shanghai'.'male'.'ufuwrn1kg3roa0');
INSERT INTO `test` VALUES ('17'.'lily'.'123002'.Guangdong 'g'.'woman'.'ejchufuwrn1kg');
Copy the codeRead under committed transaction isolation level
Because there are many conditions involved, the transaction isolation level is limited here: read committed.
A normal SELECT statement
In general, the system is read more than write, so let’s take a look at the normal SELECT statement lock. For example, SQL:
SELECT * FROM test WHERE id = 10;
Copy the codeSQL > select * from user where id=10; SQL > select * from user where id=10; SQL > select * from user where id=10;
- At the READ COMMITTED isolation level, a ReadView is generated every time a normal SELECT statement is executed, which means a snapshot is generated at execution time. Execute without locking.
- In REPEATABLE READ isolation, a ReadView is generated only on the first execution of a normal SELECT statement and reused for subsequent SELECT operations. Execute without locking.
Conclusion: Normal SELECT statements are not locked.
The process of locking
The process of reading qualified records in MySQL is as follows:
- Start by locating the first record that meets the boundary condition (that is, the index to) and treating that record as the current record.
- Locks the current record.
Different transaction isolation levels have different locking types. Record locks are added at read committed levels. Add next key lock at repeatable read isolation level. 3. Check whether the conditions for index push-down are valid. It is to push the secondary index related conditions used in the query to the storage engine to judge. If the current record meets the criteria for pushing down to the engine layer, skip to Step 4 and continue. If not, simply get the next record of the current record as the first record, skip back to Step 2, and continue. In addition to judging whether the record meets the conditions of push-down, it will also judge whether the record meets the boundary conditions and whether it is the last record. If it does not meet or is the last record, it will directly return the information of “query completed” to the Server layer. In addition, the lock added in Step 2 is not released in this step, regardless of whether the record meets all the conditions. 4. Perform operations back to the table. If a secondary index record is read, a table back operation is required to obtain the cluster index record corresponding to the record and add a record lock to the modified cluster index record. 5. Determine whether the boundary conditions are valid. If the records meet the boundary conditions, skip to Step 6 to continue the execution; otherwise, under the isolation level of committed transactions, the added locks will be released. At the isolation level of repeatable reads, locks are not released; The query completed message is returned to the Server layer. 6. The server layer checks whether the other conditions are true. Excluding the index pushdown condition, the server layer determines whether the condition is true. If yes, the record will be sent to the client. If no, the lock will be released under the isolation level of the committed transaction. At the isolation level of repeatable reads, locks are not released. 7. Get the next record in the one-way linked list, skip to step 2 as the first record, and repeat the above steps.
The process of obtaining a complete record from the primary key to the dinner table index is called back table, because the primary key information carried under the secondary index is not a neighboring ID, that is, the primary key carried is out of order, so the process of back table is a random time consuming IO operation.
Index Condition PushDown (ICP) is used in the query process to push the secondary Index to the storage engine under the relevant search conditions, rather than back to the server layer for judgment. Index push-down is a simple but effective optimization when using secondary indexes to reduce the number of table calls. It applies to union indexes in secondary indexes and only to SELECT statements, not other types of SQL.
Equivalent query using primary keys
Use the SELECT… LOCK IN SHARE MODE scenario
Suppose you have the following SQL:
begin;
SELECT * FROM test WHERE id=5 LOCK IN SHARE MODE;
Copy the codeWhy use SELECT in transactions… LOCK IN SHARE MODE, because IN MySQL, if transactions are not explicitly opened, then each statement is an independent transaction. SELECT * FROM test LOCK IN SHARE MODE; The statement, a moment to complete the execution, can not see the effect of the lock.
Select * from table where id=5; select * from table where id=5; If id=5 does not exist, the lock is not unlocked.
Use the SELECT… FOR UPDATE scenario
Suppose you have the following SQL:
begin;
SELECT * FROM test WHERE id=5 FOR UPDATE;
Copy the codeSelect * from table where id=5 and lock type X on table where id=5 If id=5 does not exist, the lock is not unlocked.
UPDATE scenario
UPDATE scenarios also need to distinguish between those in which the index is updated and those in which the index is not.
Case 1:
UPDATE test SET remark = 'xx' WHERE id = 5;
Copy the codeThe updated columns do not involve indexed columns, so lock and SELECT… The same scenario applies to the primary key with an X-type exclusive lock.
Situation 2:
UPDATE test SET no = '133132' WHERE id = 5;
Copy the codeSelect * from table uk_no where id = 5; select * from table uk_no where id = 5; delete from table uk_no where id = 5;
Conclusion: X-lock is applied to primary key and x-lock is applied to secondary index.
A DELETE scenario
DELETE FROM test WHERE id = 5;
Copy the codeDELETE needless to say, this must also be the case with type X exclusive lock. He does this by locating the record in the B+ tree, acquiring the X lock for the record, and then performing the Delete mark operation. If a secondary index is involved, the x-lock for the primary key must be obtained, and the X-lock for the secondary index must be obtained.
** Conclusion: ** first add X lock to primary key, and then add X lock to secondary index. This result is the same as the result of the second case in the UPDATE above.
Range query scenarios using primary keys
Use the SELECT… LOCK IN SHARE MODE scenario
Suppose you have the following SQL:
begin;
SELECT * FROM test WHERE id < = 15 LOCK IN SHARE MODE;
Copy the codeSQL > select record (id=5), record (id=5), record (id=5), record (id=5), record (id=5) MySQL > select * from user where id=15; MySQL > select * from user where id=15; MySQL > select * from user where id=15; MySQL > select * from user where id=15; Therefore, in this process, records with ID =16 are locked first and then released.
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
Use the SELECT… FOR UPDATE scenario
And use SELECT… The scenario for LOCK IN SHARE MODE is similar, but with a type X record LOCK.
UPDATE scenario
UPDATE scenarios also need to distinguish between those in which the index is updated and those in which the index is not.
- Update secondary index
Add an X lock to the cluster index, then add an X lock to the secondary index, and so on.
- The secondary index is not updated
Only add X locks to the cluster index, and so on.
Note the above mentioned boundary records, lock first and then determine the condition does not meet the lock release
A DELETE scenario
The use of DELETE is the same as the use of UPDATE.
Equivalent query scenario using secondary indexes
The secondary index here not only refers to the ordinary secondary index, but also contains a unique secondary index. Because the locking process is the same for both of them, it’s illustrated here. We all know the difference between a secondary index and a clustered index, as shown in the following figure:
Secondary indexes only hold index columns and primary keys, while clustered indexes hold data for the entire record. If SQL locks secondary indexes, but does not lock primary keys, what problems can occur? The primary key can be modified by other transactions, resulting in errors in the secondary index. So when a transaction locks the secondary index, it also acquires the lock on the dinner party index.
Use the SELECT… LOCK IN SHARE MODE scenario
SELECT * FROM test WHERE no='123132' LOCK IN SHARE MODE;
Copy the codeSelect * from table where no=’123132′; select * from table where no=’123132′; select * from table where no=’123132′;
If the secondary index is a normal secondary index, such as IDx_name, the result is the same. Because the Innodb engine has carried on the special treatment of the condition of contour matching, Innodb engine when dealing with contour matching, searching for the current record of a record, directly in front of the lock to judge whether the record, to meet the contour matching conditions, if not satisfied return directly, meet again after the lock is returned to the server layer.
** SELECT with secondary index… LOCK IN SHARE MODE Adds s-type record LOCK to secondary index and subsequent index, and adds S-type record LOCK to clustered index.
Use the SELECT… FOR UPDATE scenario
SELECT * FROM test WHERE no='123132' LOCK IN SHARE MODE;
Copy the codeThis lock is basically the same as using SELECT… The scenario of LOCK IN SHARE MODE is similar, except that the former is an S-type record LOCK, while the latter is an X-type record LOCK for secondary and clustered indexes.
UPDATE scenario
This and SELECT… The scenario FOR UPDATE is similar. When only regular fields are updated, only the secondary index and the corresponding cluster index are x-locked. If other indexes are updated at the same time, an X lock is added to the corresponding index record.
A DELETE scenario
This is the same as the UPDATE scenario and will not be repeated.
A range query scenario using secondary indexes
The secondary index here consists of a normal secondary index and a unique secondary index.
Use the SELECT… LOCK IN SHARE MODE scenario
SELECT * FROM test FORCE INDEX(uk_no) WHERE no< ='123001' LOCK IN SHARE MODE;
Copy the codeMySQL > alter table select * from table; MySQL > alter table select * from table; MySQL > alter table select * from table;
** SQL lock [123000,123001,123002]; cluster lock [10,16]
Use the SELECT… FOR UPDATE scenario
This is the same as using SELECT… The scenario for LOCK IN SHARE MODE is similar, but with a type X record LOCK.
Conclusion: as described in the SQL lock process above, x-lock is added to the uk_no index [123000,123001,123002] and the corresponding cluster index [10,16].
UPDATE scenario
Similarly, there are two cases of UPDATE and unupdated indexes.
# SQL1
UPDATE test SET remark='222' WHERE no< ='123002';
# SQL2
UPDATE test SET name='King of the Blades' WHERE no< ='123002';
Copy the codeHypothesis SQL1 used to index uk_no, the index of SQL is given first uk_no [123000123, 001123, 002123, 009] add X record locks and corresponding clustering index record,16,17 [10] also add X lock.
Hypothesis SQL2 used to index uk_no, the index of SQL is given first uk_no [123000123, 001123, 002123, 009] add X record locks, index idx_name [li xiang, li si, li li] and corresponding clustering index record,16,17 [10] also add X lock.
A DELETE scenario
This is the same as the UPDATE scenario and will not be repeated.
INSERT scenarios
INSERT what lock to add is complicated. Normally, INSERT statements do not need to be locked, but if one transaction T1 inserts data, another transaction T2 uses DELETE, UPDATE, INSERT, SELECT… LOCK IN SHARE MODE and even SELECT… FOR UPDATE, that’s a different story. This scenario was mentioned in the previous article (what locks actually exist in MySQL). INSERT here most of the scene can be used to show the specific lock, I can show you directly, after all, to speak with the truth!
At the repeatable read transaction isolation level, if a Gap is being inserted that has already been locked by another transaction, the insert will block and a Gap lock of its own will be added to the Gap. This is the difference between the isolation level of repeatable reads and read committed transactions.
Suppose transaction T1 inserts a record but does not commit a transaction. Other transactions at this time use DELETE, UPDATE, INSERT, SELECT… LOCK IN SHARE MODE and even SELECT… MySQL > select * from ‘UPDATE’ where lock = ‘UPDATE’;
Don’t even think about it. Other transactions cannot acquire any kind of lock on this record. It’s the same old answer: dirty reading and writing.
- If another transaction T2 is able to acquire the s-lock on this record, then either one transaction has read uncommitted data from another transaction, which is a dirty read.
- If another transaction T2 is able to acquire the X lock of the record and modify it, then either one transaction has modified the data modified by another uncommitted transaction, or a dirty write has occurred.
This is both dirty read and dirty write, and this is unacceptable for usable transactions. So the MySQL developers do the following to solve this problem: Transaction T2 helps transaction T1 by having transaction T1 add an X lock to the record, and transaction T2 waits for the corresponding lock. Things worked out perfectly again, no dirty reading or phantom reading.
Next, we will introduce the locking differences in different scenarios one by one:
- The same SQL is executed in different transactions
BEGIN;
INSERT INTO test ( name, no, city, gender, remark)
VALUES ( 'Yang'.'123003'.'d dongguan'.'woman'.'ejufuwrn1kg');
Copy the codeThe specific execution screenshot is as follows: transaction T1:
Transaction T2:
As you can see from the figure, when transaction 1 executes the INSERT statement and then executes the same SQL in transaction 2, transaction 2 blocks. Use SQL to query the lock status of the current transaction as follows:
SELECT trx.trx_id,trx.trx_state,trx.trx_query,trx.trx_isolation_level,
trx.trx_rows_locked,lock_mode,locks.lock_type,lock_index,lock_data
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.INNODB_LOCKS locks ON trx.trx_id = locks.lock_trx_id;
Copy the code
uk_noTransaction T2 acquires the S lock for the record. (here I am confused, why get S lock, should be X lock)
- The same SQL specifies the same ID to be executed in different transactions
SQL > insert primary key (id=20); SQL > insert primary key (id=20);
INSERT INTO test (id, name, no, city, gender, remark)
VALUES ('20'.'Yang'.'123003'.'d dongguan'.'woman'.'ejufuwrn1kg');
Copy the code
uk_noOtherwise, transaction T1 gets the primary keyX lockTransaction T2 gets the primary keyS lockRead and write cannot be done in parallel, so transaction T2 must block there.
- At secondary index
uk_noThe gap behind inserts data
Insert record 123004 into secondary index uk_no; insert record 123003 into secondary index uk_no;
INSERT INTO test (name, no, city, gender, remark)
VALUES ( 'Kelly Wang'.'123004'.'d dongguan'.'woman'.'ejufuwrn1kg');
Copy the code
- use
The INSERT... ON DUPLICATE KEY UPDATEstatements
Transaction T1 executes the same SQL as before, and transaction T2 executes the following SQL
INSERT INTO test ( name, no, city, gender, remark)
VALUES ( 'Yang'.'123003'.'d dongguan'.'woman'.'ejufuwrn1kg')
ON DUPLICATE KEY UPDATE no='123004';
Copy the code
The two transactions are locked as follows:
X lockTransaction T2 also retrieves the recordX lock. The situation here is normal. It’s all recordedX lock.
Conclusion: After using INSERT… ON DUPLICATE KEY UPDATE (ON DUPLICATE KEY UPDATE) if a DUPLICATE primary KEY or unique secondary index column is encountered, an X lock is applied to the existing records in the table instead of an S lock.
- Duplicate key (s)
SQL > insert primary key ();
INSERT INTO test ( id,name, no, city, gender, remark)
VALUES ( 17.'Yang'.'123003'.'s shenzhen'.'woman'.'ejufuwrn1kg');
Copy the code
Here directly use the above SQL query to view the transaction lock condition, is unable to query the data, the specific reason is also mentioned in the previous article. Select * from primary key where id=17; select * from primary key where id=17
As you can see from the figure, the former acquires the S-lock of the record, while the latter acquires the X-lock of the record. Because the former acquires the S lock, the latter blocks there.
Conclusion: In the case of duplicate primary keys, the transaction places S locks on duplicate records.
There is also a case where, regardless of the isolation level, a next key lock will be added to the secondary index that already exists in the B+ tree when the only secondary index column is duplicated when a new record is inserted. This is a special scenario where a gap lock appears to read committed isolation levels.
A full table scan
The corresponding field does not have an index or the query does not use an index, resulting in a full table scan. Such as the following SQL:
SELECT * from test WHERE remark = 'epbujch' lock in share mode;
Copy the codeSince there is no index in the remark field, we can only execute SQL in full table scan mode. After locating the first cluster index, we add s-lock to it. Because there is no index push-down condition, it is returned to the server layer for judgment. Records that meet the condition are returned to the client, and those that do not meet the condition are released.
Use the SELECT… The scenario FOR UPDATE is similar to the above, but with an X-lock. UPDATE and DELETE also add type X record locks to the corresponding cluster index. If a secondary index is updated, an X lock is added to the secondary index.
conclusion
The lock condition of the read committed transaction isolation level will be introduced briefly. The lock condition of the repeatable read isolation level will be introduced at the beginning. Query information_schema. INNODB_TRX and information_schema. INNODB_LOCKS for information_schema. INNODB_LOCKS. I’ll show you how to use the show Engine Innodb status statement to see how SQL is locked.
[1] How does MySQL work