Home>Article>Database> Understand what a lock is and how to solve the phantom read problem in MySQL

Understand what a lock is and how to solve the phantom read problem in MySQL

coldplay.xixi forward: 2020-10-23 17:16:04 3129browse

MySQL Tutorialcolumn introduces how locks solve the phantom read problem.

Preface

Today I will introduce to you the knowledge related to locks in MySQL.

Unless otherwise stated, this article uses the default InnoDB engine. If other engines or databases are involved, they will be specifically pointed out.

What is a lock

A lock is a method used to ensure that each transaction can still read and modify data in a consistent manner in a concurrent scenario. After the data is locked, other transactions cannot modify it or can only block and wait for the lock to be released. Therefore, the granularity of the lock can affect the performance of accessing the database to a certain extent.

In terms of lock granularity, we can divide locks into table locks and row locks.

Table lock

As the name suggests, table lock is to directly lock the table. In the MyISAM engine, there is only table lock.

The locking method of table lock is:

LOCK TABLE 表名 READ;--锁定后表只读 UNLOCK TABLE; --解锁复制代码

Row lock

Row lock, from the name, is to lock a row of data. However, the actual implementation of row lock The algorithm will be relatively complex, and sometimes it does not just lock a certain piece of data. This will be expanded upon later.

The normal idea is: after locking a row of data, other transactions cannot access this data. Then we imagine that if transaction A accesses a piece of data, it just takes it out to read and does not want to modify it. , it happens that transaction B also comes to access this data, and it just wants to take it out and read it, and does not want to modify it. If it is blocked at this time, it will be a bit of a waste of performance. Therefore, in order to optimize this data reading scenario, we divide row locks into two major types:Shared locks and exclusive locks.

Shared lock

Shared lock, Shared Lock, also known as read lock, S lock, means that after a piece of data is added with an S lock, other transactions can also read the data. Share a lock.
We can add shared locks through the following statement:

select * from test where id=1 LOCK IN SHARE MODE;复制代码

After locking, the lock will be released until the locked transaction ends (commit or rollback).

Exclusive lock

Exclusive lock, Exclusive Lock, also known as write lock, X lock. That is to say, after an X lock is added to a piece of data, other transactions that want to access this data can only block and wait for the lock to be released, which is exclusive.

When we modify data, such as: insert, update, delete, MySQL will automatically add an exclusive lock. Similarly, we can manually add an exclusive lock through the following sql statement:

select * from test where id=1 for update;复制代码

In the InnoDB engine, row locks and table locks are allowed to coexist.

But there will be a problem. If transaction A locks one row of data in table t, and transaction B wants to lock table t at this time, what should we do at this time? How does transaction B know whether there is a row lock in table t? If full table traversal is used, when the data in the table is large, it will take half a day to lock, so MySQL introducesintention lock.

Intention lock

Intention lock is a table lock, which is divided into two types: Intention Shared Lock and Intention Exclusive Lock. The locks can be referred to as IS locks and IX locks respectively.

Intention locks are maintained by MySQL itself, and users cannot manually add intentions.

There are two major locking rules for intention locks:

When it is necessary to add an S lock to a row of data, MySQL will first add an IS lock to the table.
When it is necessary to add an X lock to a row of data, MySQL will first add an IX lock to the table.

In this case, the above problem will be easily solved. When you need to lock a table, you only need to check whether the table has a corresponding intention lock. There is no need to traverse the entire table. surface.

Compatibility of various locks

The picture below shows the compatibility of various locks. Please refer to the official website:

锁到底锁的是什么

建立以下两张表，并初始化5条数据，注意test表有2个索引而test2没有索引：

CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `NAME_INDEX` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test VALUE(1,'张1'); INSERT INTO test VALUE(5,'张5'); INSERT INTO test VALUE(8,'张8'); INSERT INTO test VALUE(10,'张10'); INSERT INTO test VALUE(20,'张20'); CREATE TABLE `test2` ( `id` varchar(32) NOT NULL, `name` varchar(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test2 VALUE(1,'张1'); INSERT INTO test2 VALUE(5,'张5'); INSERT INTO test2 VALUE(8,'张8'); INSERT INTO test2 VALUE(10,'张10'); INSERT INTO test2 VALUE(20,'张20');复制代码

举例猜测

在行锁中，假如我们对一行记录加锁，那么到底是把什么东西锁住了，我们来看下面两个例子：
举例1（操作test表）：

S IS

	X	IX	S	IS
X	Mutual Exclusion	Mutual Exclusion	Mutual Exclusion	Mutual Exclusion
IX	Mutual exclusion	Sharing	Conflict	##Sharing
	Mutual exclusion	Mutual Exclusion	Sharing	Sharing
	Mutual Exclusion	Sharing	Sharing	Sharing

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功
COMMIT; （释放锁）
	SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功

举例2（操作test2表）：

事务A	事务B
BEGIN;
SELECT * FROM test2 WHERE id=1 FOR UPDATE;
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功

从上面两个例子我们可以发现，test表好像确实是锁住了id=1这一行的记录，而test2表好像不仅仅是锁住了id=1这一行记录，实际上经过尝试我们就知道，test2表是被锁表了，所以其实MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表。

接下来再看一个场景：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

这个例子中我们是把name索引锁住了，然后我们在事务B中通过主键索引只查id，这样就用到name索引了，但是最后发现也被阻塞了。所以我们又可以得出下面的结论，MySQL索引不但锁住了辅助索引，还会把辅助索引对应的主键索引一起锁住。

到这里，可能有人会有怀疑，那就是我把辅助索引锁住了，但是假如加锁的时候，只用到了覆盖索引，然后我再去查主键会怎么样呢？

接下来让我们再验证一下：

事务A	事务B
BEGIN;
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

We can see that even if only auxiliary index locking is used, MySQL will still lock the primary key index, and the B tree leaf node of the primary key index stores the entire data, so any field queried will be locked. .

At this point, we can clearly draw a conclusion on what the lock is:

Conclusion

In the InnoDB engine, what is locked is the index:

If a table does not have an index, MySQL will lock the table (actually the primary key index of the hidden column ROWID is locked)
If we lock the auxiliary index, then the auxiliary index corresponds to The primary key index will also be locked
The primary key index is locked, which actually means that the entire record is locked (the primary key index leaf node stores the entire data)

Algorithm of row lock

We mentioned when introducing transactions in the previous article that MySQL prevents phantom reading by locking, but if the row lock only locks a row of records, it does not seem to prevent phantom reading. , so row lock locking a record is only one of the cases. In fact, there are three row lock algorithms: Record Lock, Gap Lock and Next-Key Lock. The reason why it can Preventing phantom reading is exactly what the key lock does.

Record Lock

The record lock is introduced above. When our query can hit a record, InnoDB will use the record lock to lock the hit row of records. .

Gap Lock

When our query does not hit the record, InnoDB will add a gap lock at this time.

##BEGIN; SELECT * FROM test WHERE id=1 FOR UPDATE; INSERT INTO test VALUE (2,'Zhang 2'); Blocking COMMIT; (release lock) ##From the above example, we can conclude:

TransactionA	TransactionB


	Blocking
##INSERT INTO test VALUE (3,'Zhang 3');
#SELECT * FROM test WHERE id=2 FOR UPDATE; Lock successful

There is no conflict between gap locks and gap locks, that is, transaction A adds gap locks, and transaction B adds gap locks. Gap locks can be added to the same gap.

(The reason why gap lock is used is when there is no data hit, so there is no need to block reading, and there is no need to block other transactions from locking the same gap)

Gap lock Mainly it will block the insertion operation
How is the gap determined

(-∞,1),(1,5),(5,8),(8,10),(10,20),(20, ∞)

If the primary key is not of int type, it will be converted into ASCII code and then the gap will be determined.

Next-Key Lock

Next-Key Lock is a combination of record lock and gap lock. When we perform a range query and not only hit one or more records, but also include gaps, temporary key locking will be used. Keyless locking is the default algorithm for row locks in InnoDB.

Note that this is only for the RR isolation level. For the RC isolation level, in addition to foreign key constraints and uniqueness constraints, gap locks will be added. Without gap locks, naturally there will be no temporary key locks, so The row locks added at the RC level are all record locks. If no record is hit, no locks will be locked. Therefore, the RC level does not solve the problem of phantom reading.

The temporary key lock will be downgraded to a gap lock or a record lock under the following two conditions:

When the query misses the task record, it will be downgraded to a gap lock.

When a record is hit using the primary key or unique index, it will be downgraded to a record lock.

TransactionA

TransactionB BEGIN; #INSERT INTO test VALUE (2,'Zhang 2') ; Blocking Blocking

SELECT * FROM test WHERE id>=2 AND id

##INSERT INTO test VALUE (6,'Zhang 6');

##INSERT INTO test VALUE (8,'Zhang 8');

Blocking

SELECT * FROM test WHERE id=8 FOR UPDATE;

Blocking

INSERT INTO test VALUE (9,'Zhang 9');

Insertion successful

##COMMIT;
(release lock)

上面这个例子，事务A加的锁跨越了(1,5)和(5,8)两个间隙，且同时命中了5，然后我们发现我们对id=8这条数据进行操作也阻塞了，但是9这条记录插入成功了。

临键锁加锁规则

临键锁的划分是按照左开右闭的区间来划分的，也就是我们可以把test表中的记录划分出如下区间：(-∞,1],(1,5],(5,8],(8,10],(10,20],(20,+∞)。

那么临键锁到底锁住了哪些范围呢？

**临键锁中锁住的是最后一个命中记录的 key 和其下一个左开右闭的区间**

那么上面的例子中其实锁住了(1,5]和(5,8]这两个区间。

临键锁为何能解决幻读问题

临键锁为什么要锁住命中记录的下一个左开右闭的区间？答案就是为了解决幻读。

我们想一想上面的查询范围id>=2且id

当然，其实如果我们执行的查询刚好是id>=2且id

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务持有锁的时候，会阻止其他的事务获取锁，这个时候会造成阻塞等待，那么假如事务一直等待下去，就会一直占用CPU资源，所以，锁等待会有一个超时时间，在InnoDB引擎中，可以通过参数：innodb_lock_wait_timeout查询：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';复制代码

默认超时时间是50s，超时后会自动释放锁回滚事务。但是我们想一下，假如事务A在等待事务B释放锁，而事务B又在等待事务A释放锁，这时候就会产生一个等待环路了，而这种情况是无论等待多久都不可能会获取锁成功的，所以是没有必要去等50s的，这种形成等待环路的现象又叫做死锁。

死锁(Dead Lock)

什么是死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=10 FOR UPDATE;
	BEGIN;
	SELECT * FROM test WHERE id=20 FOR UPDATE;
SELECT * FROM test WHERE id=20 FOR UPDATE;
	SELECT * FROM test WHERE id=10 FOR UPDATE;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查询出结果

We can see that after a deadlock occurs, it will be rolled back immediately, instead of aimlessly waiting for 50s to time out before rolling back the transaction. So how does MySQL know that a deadlock has occurred? Yes, how to detect the occurrence of deadlock?

Deadlock detection

Currently, most databases use the wait-for graph (wait graph) method to detect deadlocks. The InnoDB engine also uses this method to detect deadlocks. Two types of information will be recorded in the database:

Lock information list
Transaction waiting list
wait-for graph algorithm will build a graph based on these two pieces of information. When there is a loop in the figure, it proves that there is a deadlock:
In the figure below, there is a loop between t1 and t2, which proves that there is a deadlock between the transactions of t1 and t2

Avoidance of deadlock

Split long transactions into multiple small transactions as much as possible
Avoid queries without where conditional statements when querying, and use index queries as much as possible
If possible, try to use equivalent query

Lock information query

InnoDB provides 3 tables under the information_schema library for us to query and troubleshoot transaction and lock-related issues .

INNODB_TRX

Records information about each transaction currently executed in InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing (if any) ).

##trx_id In the InnoDD engine Unique ID of the transaction trx_state Transaction status: RUNNING, LOCK WAIT, ROLLING BACK,COMMITTING trx_started The start time of the transaction trx_requested_lock_id The lock ID of the waiting transaction, if trx_state is not LOCK WAIT, it is null trx_wait_started The time the transaction waits to start trx_weight The weight of the transaction reflects the rows modified and locked by a transaction Number, when a deadlock occurs, InnoDB will select the transaction with the smallest value to roll back trx_mysql_thread_id The thread ID in MySQL can be queried through SHOW PROCESSLIST trx_query SQL statement run by the transaction trx_operation_state The current operation state of the transaction, if not It is NULL trx_tables_in_use The number of tables used by the sql statement executed in the current transaction trx_tables_locked The number of locked tables (because row locks are used, although a table is shown to be locked, it may only be one or a few rows locked, so other rows can still be accessed by other transactions) trx_lock_structs The number of locks retained by the current transaction trx_lock_memory_bytes The index structure of the current transaction is in memory The size trx_rows_locked The approximate number of rows locked in the current transaction, including data that has been marked for deletion and is physically present but invisible to the current transaction trx_rows_modified The number of rows modified or inserted by the current transaction trx_concurrency_tickets The number of concurrency refers to It is the number of concurrencies that can still be executed before the current transaction ends. It can be set through the system variable innodb_concurrency_tickets trx_isolation_level Current transaction isolation level trx_unique_checks Whether the unique constraint is opened or closed for the current transaction: 0-no 1-yes ##trx_foreign_key_checks trx_last_foreign_key_error trx_adaptive_hash_latched trx_adaptive_hash_timeout trx_is_read_only trx_autocommit_non_locking INNODB_LOCKS

Column name	Meaning


















Whether it is the current transaction Turn on or off foreign key constraints: 0-no 1-yes
The last foreign key error message, if not, it will be empty
Whether the adaptive hash index is locked by the current transaction. When a partitioned adaptive hash index search system is used, a single transaction does not lock the entire adaptive hash index. Adaptive hash index partitioning is controlled by innodb_adaptive_hash_index_parts, which is set to 8 by default.
Whether to immediately abandon the search latch for the adaptive hash index, or to retain it across calls from MySQL. When there is no adaptive hash index contention, this value will remain zero and statements will hold latches until they complete. During contention, its count is reduced to zero and the statement releases the latch immediately after each row lookup. This value remains 0 when the adaptive hash index search system is partitioned (controlled by innodb_adaptive_hash_index_parts).
Whether the current transaction is read-only: 0-no 1-yes
A value of 1 means that this is a statement that does not include for update and lock in share model, and it is the only statement executed with autocommit enabled. When this column and TRX_IS_READ_ONLY are both 1, InnoDB will optimize the transaction To reduce the overhead associated with transactions that change table data.

Records information about each lock that a transaction requested but did not obtain, and each transaction that held a lock but was blocking another transaction. lock information.

Column name ##lock_id lock id (Although LOCK_ID currently contains TRX_ID, the data format in LOCK_ID may change at any time, do not write applications that parse LOCK_ID values) lock_trx_id Previous picture Transaction ID of the table lock_mode Lock mode: S, X, IS, IX, GAP, AUTO_INC,UNKNOWN lock_type Is the lock type a table lock or a row lock? lock_table The locked table lock_index The locked index, the table lock is NULL lock_space The space id of the lock record, the table lock is NULL The number of pages locked for NULL lock_page transactions and NULL lock_rec ## for table locks #The number of transaction locked rows, table lock is NULL The primary key value of transaction lock, table lock is NULL

Meaning

lock_data

INNODB_LOCK_WAITS

Records lock waiting information. Each blocked InnoDB transaction contains one or more rows representing the lock it requested and any locks that were blocking the request.

##lock_id lock id (although LOCK_ID currently contains TRX_ID, the data format in LOCK_ID may change at any time, do not write applications that parse LOCK_ID values) requesting_trx_id Requesting lock resources Transaction ID requested_lock_id ID of the requested lock blocking_trx_id Blocked transaction ID blocking_lock_id The ID of the blocking lock

Column name	Meaning

More related free learning Recommended:mysql tutorial(Video)

The above is the detailed content of Understand what a lock is and how to solve the phantom read problem in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement：

This article is reproduced at:juejin.im. If there is any infringement, please contact admin@php.cn delete

Previous article：How to start mysql Next article：How to start mysql

See more

Understand what a lock is and how to solve the phantom read problem in MySQL

MySQL Tutorialcolumn introduces how locks solve the phantom read problem.

Preface

What is a lock

Table lock

Row lock

Shared lock

Exclusive lock

Intention lock

Compatibility of various locks

锁到底锁的是什么

举例猜测

Conclusion

Algorithm of row lock

Record Lock

Gap Lock

临键锁加锁规则

临键锁为何能解决幻读问题

死锁(Dead Lock)

什么是死锁

Deadlock detection

Avoidance of deadlock

Lock information query

INNODB_TRX

Records information about each lock that a transaction requested but did not obtain, and each transaction that held a lock but was blocking another transaction. lock information.

INNODB_LOCK_WAITS

Related articles