Shulou(Shulou.com)06/01 Report--

1. Lock?

1.1 what is a lock

The meaning of lock in reality is: a closed object that is opened with a key or code. Locks in computers are generally used to manage concurrent access to shared resources, such as Lock,synchronized, which is familiar to our java classmates. Of course, there are locks in our database to control concurrent access to resources, which is one of the differences between a database and a file system.

1.2 Why should I know about database locks?

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

Xiao Ming is a Java development engineer who has just graduated and works in an Internet company. His usual job is to complete the requirements of PM. Of course, while completing the requirements, he certainly can not escape the framework of spring,springmvc,mybatis, so generally speaking, sql is still handwritten by himself. If you encounter a more complex sql, you will go to Baidu from the Internet. For some more important operations, such as transactions, Xiao Ming will use spring transactions to manage database transactions. Due to the relatively small amount of data, distributed transactions are not yet involved.

A few months ago, Xiaoming had a good time. I knew that one day, Xiaoming received a demand. The merchant had a configuration item, called preferential configuration item, and could configure buy one get one free, buy one get two free, and so on. Of course, these configurations are transmitted to the backend in batches, so there is a problem that each rule has to match whether he is deleted, added or modified, so the back-end logic is more troublesome. Smart Xiaoming came up with a way to delete the merchant's configuration directly and then add it all. Xiaoming immediately completed the development and successfully went online.

There is nothing wrong with starting to go online, but there are often some mysql-insert-deadlock exceptions in the log. As Xiaoming was relatively inexperienced, he met this type of problem for the first time, so he asked the veteran driver of his group, Da Hong, as soon as he saw the problem, and then after looking at his code, he output several commands, read several logs, immediately located the problem, and told Xiaoming: this is because delete will add gap locks, but gap locks can be compatible. However, when inserting new data, the insertion intention lock will be blocked by the gap lock, resulting in both parties being occupied by each other, resulting in deadlock. After hearing this, Xiaoming seemed to understand, because there are many things about Da Hong, it is inconvenient to trouble Da Hong all the time, so he decided to come down and think. After work, Xiao Ming recalled what Da Hong said, what is a gap lock and what is an insertion intention lock. It seems that as a developer, he should not only write SQL to the database, or he will not be able to solve some problems. After thinking about it, Xiao Ming embarked on the road of learning Mysql locks.

2.InnoDB

2.1mysql architecture

Xiaoming was in no hurry to learn about locking. He first learned about the Mysql architecture:

It can be found that Mysql consists of connection pool component, management service and tool component, sql interface component, query analyzer component, optimizer component, buffer component, plug-in storage engine, and physical files.

Xiaoming found that the storage engine is provided as a plug-in in mysql, and there are many storage engines in Mysql, each of which has its own characteristics. Then Xiao Ming typed on the command line:

Show engines\ G

It turns out that there are so many engines.

Type the following command to view the default engine of the current database:

Show variables like'% storage_engine%'

Xiaoming suddenly realized that his database was using InnoDB. He vaguely remembered that he had heard of an engine called MyIsAM when he was at school. Xiaoming thought, what's the difference between the two? I looked up the information immediately:

Compare whether InnoDBMyIsAM transactions support locks support MVCC row lock table locks foreign keys support storage space due to the need for cache, larger compressible scenarios have a certain amount of update and Insert a large number of select

Xiaoming probably understood the difference between InnoDB and MyIsAM. Because he used InnoDB, Xiaoming didn't struggle too much with this piece.

2.2 isolation of transactions

Before studying locks, Xiao Ming recalled the database transaction isolation that he had taught in school. In fact, one of the functions of locking in the database is to achieve transaction isolation. The isolation of transactions is actually used to solve problems such as dirty reading, unrepeatable reading and phantom reading.

2.2.1 dirty reading

One transaction reads uncommitted update data from another transaction. What do you mean?

Point-in-time transaction A transaction B1begin

2select * from user where id = 1 Benginben 3

Update user set namm = 'test' where id = 1th 4select * from user where id = 1

5 destroy commit

In transaction B, transaction A queries the id=1 data in user table at time point 2 and 4 respectively, but transaction B modifies it at time point 3, which leads to the query result of transaction An in 4 is actually modified by transaction B. Breaks the isolation in the database.

2.2.2 non-repeatable

In the same transaction, the result of reading the same data multiple times is different, unlike dirty reading, what is read here is committed.

Point-in-time transaction A transaction B1begin

2select * from user where id = 1 Benginben 3

Update user set namm = 'test' where id = 1 * * 4

Commit;5select * from user where id = 1

6commit

The operation committed in transaction B is before the second query of transaction A, but still reads the update result of transaction B and destroys the isolation of the transaction.

The operation committed in transaction B is before the second query of transaction A, but still reads the update result of transaction B and destroys the isolation of the transaction.

2.2.3 Phantom reading

One transaction reads insert data committed by another transaction.

Point-in-time transaction A transaction B1begin

2select * from user where id > 1 Benginben 3

Insert user select 2 Bing 4

Commit;5select * from user where id > 1

6commit

The data with id greater than 1 is queried twice in transaction A, and there is no data in the first query result with id greater than 1, but because transaction B inserts a piece of Id=2 data, transaction A can find the data inserted in transaction B in the second query.

Isolation in transactions:

Isolation level dirty read non-repeatable uncommitted read (RUC) NONONO committed read (RC) YESNONO repeatable read (RR) YESYESNO serializable YESYESYES

Xiaoming noticed that in the process of collecting data, some materials wrote that InnoDB is a little different from other databases. The repeatable reading of InnoDB can actually solve the illusion. Xiaoming thought: this InnoDB is quite powerful. I have to see how it works.

2.3 InnoDB lock type

Xiao Ming first takes a look at 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 seen as two read-write locks:

S-shared lock: also known as read lock, other transactions can continue to add shared lock, but cannot continue to add exclusive lock. X-exclusive lock: also known as a write lock, once a write lock is added, other transactions cannot be locked.

Compatibility: it means that after transaction An acquires a certain lock on a row, transaction B also attempts to acquire a certain lock on this line. If it can be acquired immediately, it is called lock compatibility, and vice versa.

The vertical axis represents the existing lock, and the horizontal axis represents the lock you are trying to acquire.

.XSX conflict S conflict compatibility

2.3.2 intention lock

An intention lock is a table-level lock in InnoDB, which, like its name, is used to express what a transaction wants to acquire. Intention locks are divided into:

Intention shared lock: indicates that a transaction wants to acquire a shared lock for several rows in a table. Intention exclusive lock: indicates that a transaction wants to acquire an exclusive lock for several rows in a table.

What is the use of this lock? Why do you need this lock? First of all, if we do not have this lock, if we want to add a table lock to the table, the general practice is to traverse each row to see if it has a row lock, which is too inefficient, and we intend to lock it. We just need to judge whether we intend to lock it or not. You don't have to scan row by line.

Because row-level locks are supported in InnoDB, the compatibility of InnboDB locks can be extended as follows:

.IXISXSIX compatibility conflict IS compatibility conflict compatibility conflict X conflict S conflict compatibility conflict compatibility

2.3.3 self-growing lock

Self-growing lock is a special table locking mechanism to improve the performance of concurrent insertion. There are several features for this lock:

The lock is released after sql execution, not at the end of the transaction. For Insert...select large data inserts can affect insert performance because it blocks the execution of another transaction. The self-increasing algorithm can be configured.

After the MySQL5.1.2 version, there are a lot of optimizations, and you can adjust the way you add locks according to different modes. After Xiao Ming saw that his MySQL was opened here and found that it was 5.7, 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.01sec)

There are three configuration modes of innodbautoinclock_mode in MySQL: 0, 1 and 2, corresponding to "traditional mode", "continuous mode" and "interlaced mode" respectively.

Traditional mode: that is, we use table locks at the top. Continuous mode: use mutexes for rows that can be determined when inserting, and table locks for those that cannot determine the number of rows. Staggered mode: all use mutex, why is it called interlaced mode? it is possible that self-increment is not continuous when inserting in batches, of course, generally speaking, if you do not value self-increment continuity, the performance is the best.

2.4InnoDB locking algorithm

Xiao Ming has learned what types of locks there are in InnoDB, but how to use them still depends on the locking algorithm.

2.4.1 record Lock (Record-Lock)

Record locks lock records, and what we want to say here is that index records are locked here, not our real data records.

If the lock is a non-primary key index, it will be locked on its own index and then locked on the primary key. If there is no index on the table (including no primary key), the hidden primary key index is used for locking. If the column to be locked does not have an index, the full table record is locked.

2.4.2 clearance lock

Gap lock as the name implies lock gap, do not lock the record. Lock gap means to lock a range, gap lock is also called gap lock, it will not block other gap locks, but will block the insertion of gap locks, which is also the key to prevent misreading.

2.4.3 next-key lock

This lock is essentially a record lock plus a gap lock. At the RR isolation level (the InnoDB default), Innodb uses this algorithm for row scan locking, but if there is a unique index in the query scan, it degenerates to 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.

This also explains why Mysql can solve phantom reading at the RR level.

2.4.4 insert intention lock

Insert the intention lock the official explanation of Mysql:

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insertin 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 insertion intention lock is generated at the time of insertion, and 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 lock waiting will not occur. Suppose that a record index contains keys 4 and 7, and different transactions insert 5 and 6, respectively, and each transaction produces an insert intention lock added between 4 and 7, acquiring an exclusive lock on the inserted row, but will not be locked with each other because the data rows do not conflict.

The point here is that if there is a gap lock, the insertion intention lock will be blocked.

2.5 MVCC

MVCC, multi-version concurrency control technology. In InnoDB, add two hidden columns after each row of records, the record creation version number and the delete version number. Through version number and row lock, the concurrency performance of database system is improved.

In MVCC, there are two types of reads for read operations:

Snapshot read: read historical data, simple select statements, unlocked, MVCC implementation can be read repeatedly, using the MVCC mechanism to read committed data in undo. So its reading is non-blocking. Current read: statements that need to be locked, update,insert,delete,select...for update, etc., are current reads.

Snapshot reads under the RR isolation level do not take the time point at the start of the begin transaction as the time point for snapshot establishment, but the time point of the first select statement as the time point for snapshot establishment. Future select will read the snapshot value at the current point in time.

Each snapshot read under the RC isolation level creates a new snapshot.

The principle is that there are two hidden fields per row, one to record the current transaction and one to point to the Undolog to record the rollback. The previous snapshot can be read with undolog, without the need to open up a separate spatial record.

3. Lock analysis

Xiaoming has learned a lot about the basics of mysql locks here, so he decided to create his own table and do the next experiment. First, a simple user table is created:

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 several pieces of experimental data are inserted:

Insert user select 2033333310insert user select 2555555510insert user select 20999999

RR is selected for database transaction isolation

3.1 Lab 1

Xiaoming started two transactions and carried out experiment 1.

Point-in-time transaction A transaction B1begin

2select * from user where name = '555' for update;begin;3

Insert user select 31, "556", "556," 4.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Xiao Ming opened two transactions and entered the above statement, and found that transaction B actually timed out. Xiao Ming took a look at why he locked the line of name= 555. why I wanted to insert name=556 to block it for me. So Xiaoming opened the command line and entered:

Select * from information_schema.INNODB_LOCKS

It is found that the Next-key lock is added to transaction A, and the insertion intention lock is inserted first when transaction B is inserted, so the following conclusions are drawn:

You can see that transaction B is blocked due to a conflict between the gap lock and the insert intention lock.

3.2 Lab 2

Xiaoming found that the above query condition uses an ordinary non-unique index, so Xiaoming tried the primary key index:

Point-in-time transaction A transaction B1begin

2select * from user where id = 25 for update;begin;3

Insert user select 26, "666", "666,"4.

Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0

Unexpectedly found that transaction B did not block, ah, this is what is going on, Xiao Ming is a little confused, according to the routine of experiment 1 should be blocked ah, because there will be a gap lock between 25 and 30. So Xiaoming sacrificed the command line and found that only the X record lock had been added. It turns out that because the unique index degrades the record lock, the reason for this is that the non-unique index plus next-key lock cannot determine the exact number of rows. It is possible that other transactions may add the data of this index again in the course of your query, resulting in a breach of isolation, that is, illusory reading. Unique index because unique data rows are identified, there is no need to add gap locks to solve phantom reads.

3.3 Lab 3

Above tested the primary key index, non-unique index, here is a field that does not have an index, what happens if it is locked?

Point-in-time transaction A transaction B1begin

2select * from user where comment = '555' for update;begin;3

Insert user select 26, "666", "666,"4.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction5

Insert user select 31, "3131,"3131," 6.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction7

Insert user select 10, "100", "100", "8,"

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Xiaoming has a look, oh, I'll go, what's going on with this? it doesn't matter whether it's using the data in the non-gap lock range of experiment 1, or the data in the gap lock. Is it a watch lock?

Indeed, if you use data without an index, it will put a next-key lock on all clustered indexes.

Therefore, if you do not have an index on the query conditions, you must carry out consistent reading, that is, locking reading, which will lead to the addition of indexes to the whole table, block all other transactions, and the database will basically be unavailable.

4. Back to the accident.

4.1 deadlock

After finishing the experiment, Xiaoming finally understood some basic routines of locking, but what is the deadlock on the front line?

Deadlock: refers to two or more transactions in the execution process, due to competition for resources caused by a phenomenon of waiting for each other. It indicates that there will be a deadlock only if there is a wait, and deadlocks can be solved by removing the wait, such as rolling back the transaction.

There are two ways to resolve deadlocks:

Wait timeout: when a transaction rolls back the transaction after the wait timeout, another transaction can be executed, but it is inefficient and there will be waiting time. Another problem is that if the transaction has a large weight, a lot of data has been updated, but it has been rolled back, which will lead to a waste of resources. Wait diagram (wait-for-graph): the wait diagram is used to describe the wait relationship between transactions, when the diagram appears if the loop is as follows:

When a rollback occurs, InnoDB usually chooses to roll back transactions with less weight, that is, transactions with less undo.

4.2 online problems

When Xiao Ming came here, he had all the basic skills he needed, so he began to reproduce this problem on his own local surface:

Point-in-time transaction A transaction B1beginbot from user where name = '777transactions / from user where name =' 666transactions / 3insert user select 27 Duplicates: 777pr / insert user select 26pr / 666 / 666 / 666 / 4ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionQuery OK, 1 row affected (14.32 sec) Records: 1 Duplicates: 0 Warnings: 0

You can see that transaction An appears to be rolled back and transaction B executes successfully. What happened at each point in time?

Time 2: transaction A deletes the data of name = '777' and needs to add a next-Key lock to the index 777, but it does not exist, so it only adds a gap lock between 555 and 999, and the same transaction B also adds a gap lock between 555 and 999. Clearance locks are compatible.

Time point 3: transaction A, performing the Insert operation, inserts the intention lock first, but there is a gap lock between 55599 and 999. transaction A blocks due to the insertion intention lock and gap lock conflict, waiting for transaction B to release the gap lock. Transaction B is the same, waiting for transaction A to release the gap lock. So there is A-> BM B-> A loop waiting.

Time 4: the transaction manager chooses to roll back transaction A, and the transaction B insert operation executes successfully.

4.3 repair BUG

This problem was finally found by Xiao Ming, because of the gap lock, now we need to solve this problem, the reason for this problem is that there is a gap lock, so let's get rid of him:

Solution 1: the isolation level is downgraded to RC, and no gap lock will be added at the RC level, so there will be no problem, but there will be phantom reading at the RC level, and both submittable reads will break the isolation, so this solution will not work. Solution 2: the isolation level is upgraded to serializable. Xiaoming found that this problem will not occur after testing, but at the serializable level, the performance will be lower and there will be more lock waiting, which will not be considered either. Plan 3: modify the code logic, do not delete directly, change each data to judge which is updated, which is deleted, and which is added by the business logic. This workload is a little larger. Xiao Ming wrote the logic of direct deletion in order not to do these complex things, so this plan will not be considered. Plan 4: less modification code logic, before deletion, you can query through the snapshot (without locking), if the query has no results, then insert directly, if deleted through the primary key, in the previous third section of experiment 2, through a unique index will be degraded to a record lock, so there is no gap lock.

After considering that Xiao Ming chose the fourth kind, repaired it immediately, and then went online to observe and verify, it was found that this Bug would not appear now, and now Xiao Ming was finally able to get a good night's sleep.

4.4 how to prevent deadlock

Xiaoming summed up the following points through basic learning and ordinary experience:

Access tables and rows in a fixed order. Cross-access is more likely to cause transaction waiting loops. Try to avoid large transactions, the more resource locks you occupy, the more likely it is to have deadlocks. It is recommended that it be broken down into small affairs. Lower the quarantine level. If the business allows it (as analyzed above, some businesses do not), lowering the isolation level is also a better choice, such as changing the isolation level from RR to RC, which can avoid many deadlocks caused by gap locks. Add a reasonable index to the table. Prevent the occurrence of table locks without indexes, and the probability of deadlocks will increase suddenly.

Last

Due to the limited space, many things can not be introduced if interested students can read "Mysql Technology Insider-InnoDB engine" Chapter 6 and Master he's MySQL locking processing analysis. The author's own level is limited, if there are any mistakes, please correct them.

Well, the above is the whole content of this article. I hope the content of this article has a certain reference and learning value for your study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.