Why developers must understand database locks in detail

1.Lock?

1.1 What is a lock?

The real meaning of a lock is: a closed object that can be opened with a key or code. Locks in computers are generally used to manage concurrent access to shared resources. For example, Lock and synchronized, which are familiar to our Java classmates, are common locks. Of course, there are also locks in our database to control concurrent access to resources, which is one of the differences between databases and file systems.

1.2 Why do you need to understand database locks?

Generally speaking, for general developers, it is enough to know some DQL (select) and DML (insert, update, delete) when using the database.

Xiao Ming is a Java development engineer who just graduated and works in an Internet company. His usual job is to complete the PM's requirements. Of course, while completing the requirements, he cannot escape the framework of spring, springmvc, and mybatis. So generally speaking, he still writes the SQL by hand. If he encounters a more complicated SQL, he will go to Baidu to search on the Internet. For some important operations, such as transactions, Xiao Ming will use spring transactions to manage database transactions. Due to the small amount of data, distributed transactions are not involved at present.

Xiao Ming had a smooth life in the past few months. Until one day, Xiao Ming received a demand. The merchant had a configuration item called discount configuration item, which could configure buy one get one free, buy one get two free and other rules. Of course, these configurations were transmitted to the backend in batches. This posed a problem because each rule had to be matched to determine whether it was deleted, added or modified. This made the backend logic more complicated. Smart Xiao Ming thought of a way to directly delete the merchant's configuration and then add all of them in. Xiao Ming completed the development immediately and launched it successfully.

There was nothing wrong with the initial launch, but some mysql-insert-deadlock exceptions often appeared in the logs. Since Xiao Ming had little experience and encountered this type of problem for the first time, he asked the veteran in their group, Dahong. When Dahong saw this problem and looked at his code, he output a few commands and read a few logs, and immediately located the problem. He told Xiao Ming: This is because a gap lock will be added during deletion, but gap locks are compatible with each other. However, when inserting new data, the insertion intention lock will be blocked by the gap lock, resulting in mutual occupation of resources on both sides, leading to deadlock. After listening to this, Xiao Ming seemed to understand, but since Dahong had a lot of things to do, it was inconvenient to bother her all the time, so he decided to think about it himself. After get off work, Xiao Ming thought about what Dahong said, what is a gap lock and what is an insert intention lock. It seems that as a developer, you should not only be able to write SQL for the database, otherwise you will not be able to solve some difficult problems. After thinking about it, Xiao Ming embarked on the road of no return to learn MySQL lock.

2. InnoDB

2.1MySQL Architecture

Xiao Ming was not in a hurry to unlock this knowledge. He first learned about the MySQL architecture:

It can be found that Mysql is composed of connection pool components, management services and tool components, sql interface components, query analyzer components, optimizer components, buffer components, plug-in storage engine, and physical files.

Xiao Ming discovered that the storage engine in MySQL is provided in the form of a plug-in. There are multiple storage engines in MySQL, and each storage engine has its own characteristics. Then Xiao Ming typed in the command line:

show engines \G;

It turns out there are so many types of engines.

Then type the following command to view the current database default engine:

show variables like '%storage_engine%'; 

Xiao Ming suddenly realized that his database was using InnoDB. He vaguely remembered that he had heard of an engine called MyISAM when he was in school. Xiao Ming wondered what the difference was between the two. He immediately looked up some information:

Xiao Ming roughly understood the difference between InnoDB and MyISAM. Since he used InnoDB, Xiao Ming did not worry too much about it.

2.2 Transaction Isolation

Before studying locks, Xiao Ming recalled the database transaction isolation he had learned in school. In fact, one of the functions of locks in the database is to achieve transaction isolation. The isolation of transactions is actually used to solve problems such as dirty reads, non-repeatable reads, and phantom reads.

2.2.1 Dirty Read

A transaction reads updated data that has not been committed by another transaction. What does it mean?

In transactions A and B, transaction A queried the data of id=1 in the user table at time points 2 and 4 respectively, but transaction B modified it at time point 3, resulting in the query result of transaction A in 4 being actually the result modified by transaction B. This breaks the isolation in the database.

2.2.2 Non-repeatable read

In the same transaction, reading the same data multiple times may return different results. Unlike dirty reads, the data read here is the data that has been committed.

The operation submitted in transaction B is before the second query of transaction A, but the update result of transaction B is still read, which also destroys the isolation of the transaction.

2.2.3 Phantom Reads

One transaction reads the insert data that has been committed by another transaction.

In transaction A, two queries were made for id greater than 1. In the first query, there was no data in the result. However, because transaction B inserted a data with ID=2, transaction A was able to find the data inserted in transaction B in the second query.

Isolation in transactions:

Xiao Ming noticed that in the process of collecting information, some information said that InnoDB is a little different from other databases. InnoDB's repeatable read can actually solve phantom reads. Xiao Ming thought: This InnoDB is pretty awesome. I have to take a closer look at how it works.

2.3 InnoDB Lock Types

Xiao Ming first understands the common lock types in Mysql:

2.3.1 S or X

Two standard row-level locks are implemented in InnoDb, which can be simply viewed as two read-write locks:

• S-Shared lock: also called read lock. Other transactions can continue to add shared locks, but cannot continue to add exclusive locks.
• X-exclusive lock: also called write lock. Once a write lock is added, other transactions cannot lock it.

Compatibility: It means that after transaction A obtains a certain lock on a certain row, transaction B also tries to obtain a certain lock on the same row. If it can be obtained immediately, it is called lock compatibility, otherwise it is called a conflict.

The vertical axis represents the existing locks, and the horizontal axis represents the locks attempted to be acquired.

2.3.2 Intention Lock

Intention locks are table-level locks in InnoDB. As its name suggests, they are used to express what a transaction wants to obtain. Intention locks are divided into:

• Intentional shared lock: expresses that a transaction wants to obtain shared locks for certain rows in a table.
• Intentional exclusive lock: expresses that a transaction wants to obtain exclusive locks for certain rows in a table.

What is the use of this lock? Why do you need this lock? First of all, if there is no such lock, if you want to add a table lock to this table, the general practice is to traverse each row to see if it has a row lock. This is too inefficient. However, if we have an intentional lock, we only need to determine whether there is an intentional lock, and there is no need to scan each row one by one.

InnoDB supports row-level locks, so the compatibility of InnoDB locks can be extended as follows:

2.3.3 Auto-increment lock

The auto-increment lock is a special table lock mechanism that improves concurrent insert performance. There are several features for this lock:

• The lock is released when the SQL is executed, not when the transaction is executed.
• For Insert...select, inserting large amounts of data will affect the insertion performance because it will block the execution of another transaction.
• The auto-increment algorithm is configurable.

After MySQL version 5.1.2, there are many optimizations, and the way to increase the lock can be adjusted according to different modes. Xiao Ming saw this and opened his MySQL and found that it was version 5.7, so he entered the following statement to get the current lock mode:

mysql> show variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 2 |

+--------------------------+-------+

1 row in set (0.01 sec)

In MySQL, innodbautoinclock_mode has three configuration modes: 0, 1, and 2, corresponding to "traditional mode", "continuous mode", and "interleaved mode" respectively.

• Traditional mode: This is the table lock we use at the top.
• Continuous mode: Use mutexes when the number of rows can be determined during insertion, and use table locks when the number of rows cannot be determined.
• Interleaved mode: All use mutexes. Why is it called interleaved mode? It is possible that the auto-increment values ​​are not continuous during batch insertion. Of course, generally speaking, if you do not value the continuity of auto-increment values, you will generally choose this mode, which has the best performance.

2.4InnoDB Lock Algorithm

Xiao Ming has learned about the types of locks in InnoDB, but how to use these locks still depends on the lock algorithm.

2.4.1 Record-Lock

The record lock locks the record. What needs to be explained here is that what is locked here is the index record, not our actual data record.

• If the non-primary key index is locked, it will lock its own index and then lock the primary key.
• If there is no index on the table (including no primary key), the hidden primary key index will be used for locking.
• If the column to be locked has no index, all records in the table will be locked.

2.4.2 Gap Lock

As the name implies, gap locks lock the gaps but not the records. Locking the gap means locking a certain range. The gap lock is also called the gap lock. It will not block other gap locks, but it will block the insertion of the gap lock. This is also the key to preventing phantom reads.

2.4.3 Next-key lock

This lock is essentially a record lock plus a gap lock. At the RR isolation level (InnoDB default), Innodb uses this algorithm for row scan locks, but if there is a unique index in the query scan, it will degenerate into using only record locks. Why? Because the unique index can determine the number of rows, while other indexes cannot determine the number of rows. It is possible that the data of this index will be added again in other transactions, which will cause phantom reads.

This also explains why MySQL can solve phantom reads at the RR level.

2.4.4 Insert Intention Lock

Insert intention lock Mysql official explanation of it:

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values ​​of 4 and 7. Separate transactions that attempt to insert values ​​of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

It can be seen that the insert intention lock is generated during insertion. When multiple transactions write different data to the same index gap at the same time, there is no need to wait for other transactions to complete, and no lock wait occurs. Suppose there is a record index containing key values ​​4 and 7, and different transactions insert 5 and 6 respectively. Each transaction will generate an insert intention lock added between 4-7 and obtain an exclusive lock on the inserted row, but they will not be locked with each other because the data rows do not conflict.

It should be noted here that if there is a gap lock, the insertion of the intention lock will be blocked.

2.5 MVCC

MVCC, multi-version concurrency control technology. In InnoDB, two hidden columns are added after each row of records to record the creation version number and the deletion version number. Through version numbers and row locks, the concurrent performance of the database system is improved.

In MVCC, read operations can be divided into two types:

• Snapshot read: reads historical data, simple select statements, no locks, MVCC to achieve repeatable read, using the MVCC mechanism to read the committed data in undo. So its reading is non-blocking.
• Current read: Statements that require locking, such as update, insert, delete, select...for update, etc., are all current reads.

For snapshot reads at the RR isolation level, the snapshot creation time is not the time point when the begin transaction starts, but the time point when the first select statement is used as the time point when the snapshot is created. Subsequent selects will read the snapshot value at the current time point.

At the RC isolation level, each snapshot read creates a new snapshot.

The specific principle is that each row has two hidden fields, one for recording the current transaction and the other for recording the rollback pointing to Undolog. The previous snapshot can be read by using undolog without having to allocate space for recording.

3. Lock analysis

Xiao Ming has learned a lot of basic knowledge about MySQL locks, so he decided to create a table to do an experiment. First, create a simple user table:

CREATE TABLE `user` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL,

`comment` varchar(11) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Then inserted several experimental data:

insert user select 20,333,333;

insert user select 25,555,555;

insert user select 20,999,999;

Database transaction isolation selects RR

3.1 Experiment 1

Xiao Ming started two transactions and conducted experiment 1.

Xiao Ming started two transactions and entered the above statements, and found that transaction B actually timed out. Xiao Ming checked and found that he had clearly locked the row name = 555, so why was I blocked when I wanted to insert name = 556? So Xiao Ming opened the command line and entered:

select * from information_schema.INNODB_LOCKS

It is found that a Next-key lock is added to 555 in transaction A. When transaction B inserts, the insertion intention lock is inserted first, so the following conclusion is drawn:

It can be seen that transaction B is blocked due to the conflict between the gap lock and the insert intention lock.

3.2 Experiment 2

Xiao Ming found that the above query condition used a normal non-unique index, so Xiao Ming tried the primary key index:

It is found that transaction B is not blocked. What is going on? Xiao Ming is a little confused. According to the routine of Experiment 1, it should be blocked because there will be a gap lock between 25-30. So Xiao Ming used the command line again and found that only the X record lock was added. It turns out that the unique index will downgrade the record lock. The reason for this is: since the non-unique index plus the next-key lock cannot determine the exact number of rows, it is possible that other transactions will add the data of this index again during your query, resulting in the destruction of isolation, which is phantom read. Since the unique index specifies the only data row, there is no need to add gap locks to resolve phantom reads.

3.3 Experiment 3

The primary key index and non-unique index are tested above. There is another field that has no index. What will happen if it is locked?

Indeed, if you use unindexed data, it will add next-key locks to all clustered indexes.

Therefore, when you are developing normally, if there is no index for the query conditions, you must perform a consistent read, that is, a locked read, which will result in the addition of an index to the entire table, causing all other transactions to be blocked, and the database will basically be in an unavailable state.

4. Back to the accident

4.1 Deadlock

After Xiao Ming finished the experiment, he finally understood some basic locking routines, but what was the deadlock that appeared online before?

Deadlock: refers to a phenomenon in which two or more transactions wait for each other due to competition for resources during execution. This means that deadlock occurs only when there is waiting. The deadlock can be solved by removing the waiting, such as rolling back the transaction.

There are two ways to resolve deadlock:

• Waiting timeout: When a transaction is rolled back after waiting for a timeout, another transaction can be executed. However, this is inefficient and will result in waiting time. Another problem is that if the transaction has a large weight and has updated a lot of data, but is rolled back, it will lead to a waste of resources.
• Wait-for-graph: The wait-for-graph is used to describe the waiting relationship between transactions. If a loop appears in this graph, it is as follows:

If a rollback occurs, InnoDB usually chooses to roll back transactions with smaller weights, that is, transactions with smaller undo values.

4.2 Online Issues

Xiao Ming has all the basic skills he needs, so he starts to reproduce this problem in his local table:

You can see that transaction A is rolled back, while transaction B is successfully executed. What happened at each point in time?

Time point 2: Transaction A deletes the data with name = '777'. It needs to add a next-Key lock to the index 777, but it does not exist. Therefore, only a gap lock is added between 555 and 999. Similarly, transaction B also adds a gap lock between 555 and 999. Gap locks are compatible with each other.

Time point 3: Transaction A performs an Insert operation and first inserts an intention lock. However, there is a gap lock between 555-999. Due to the conflict between the insertion intention lock and the gap lock, transaction A is blocked and waits for transaction B to release the gap lock. Transaction B is similar and waits for transaction A to release the gap lock. So there is a A->B, B->A loop waiting.

Time point 4: The transaction manager chooses to roll back transaction A, and the insert operation of transaction B is executed successfully.

4.3 Bug Fixes

Xiao Ming finally found this problem, which is due to the gap lock. Now we need to solve this problem. The reason for this problem is the gap lock, so let's get rid of it:

• Solution 1: Downgrade the isolation level to RC. At the RC level, no gap locks will be added, so there will be no problems. However, at the RC level, phantom reads will occur, and committed reads will destroy the isolation problem, so this solution is not feasible.
• Solution 2: The isolation level is upgraded to serializable. After testing, Xiao Ming found that this problem would not occur. However, at the serializable level, the performance will be lower and there will be more lock waits, which is also not considered.
• Solution three: Modify the code logic. Do not delete directly. Instead, let the business logic determine which data are updated, deleted, and added. This workload is a bit large. Xiao Ming wrote this direct deletion logic in order to avoid doing these complicated things, so this solution is not considered for now.
• Solution 4: Less modification of code logic. Before deletion, you can query through snapshot (without locking). If the query has no results, insert directly. If there is deletion through primary key, in the previous Experiment 2 in Section 3, the unique index will be downgraded to record lock, so there is no gap lock.

After consideration, Xiao Ming chose the fourth option, immediately made a repair, and then went online for observation and verification. He found that the Bug would no longer occur. Now Xiao Ming can finally sleep well.

4.4 How to Prevent Deadlock

Xiao Ming summarized the following points through basic learning and daily experience:

• Tables and rows are accessed in a fixed order. Cross-access is more likely to cause transaction wait loops.
• Try to avoid large transactions. The more resource locks you occupy, the more likely deadlocks will occur. It is recommended to break it down into small tasks.
• Reduce the isolation level. If the business allows (as analyzed in 4.3 above, some businesses do not allow this), lowering the isolation level is also a good choice. For example, adjusting the isolation level from RR to RC can avoid many deadlocks caused by gap locks.
• Add appropriate indexes to the table. Prevent table locks from occurring when there is no index, as the probability of deadlocks will increase suddenly.

at last

Due to limited space, many things cannot be fully introduced. If you are interested, you can read Chapter 6 of "MySQL Technology Insider - InnoDB Engine" and Master He's MySQL Lock Processing Analysis. The author's level is limited, if there are any mistakes, please point them out.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.