Shulou(Shulou.com)05/31 Report--

Editor to share with you how to deal with MySQL deadlock problem, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The lock compatibility list in MySQL is generally like this. If you look at it for the first time, you will feel a little dizzy and feel like you can't get to the point.

First of all, InnoDB implements two similar row locks, namely S (shared lock) and X (exclusive lock), while the table-level intention locks at the InnoDB level are IS (intention sharing lock) and IX9 intention exclusive lock), and the intention locks are compatible with each other. This sentence is very important, according to this train of thought, half of the content is clear. The other part is the compatibility of S and X. Combinations with S locks and X locks are mutually exclusive, with the exception of one type of scenario, that is, both S locks are compatible. So according to this idea, this picture can be basically understood without having to remember it.

It seems that the combination of S locks is very soft, from this scenario to maintain compatibility, then the probability of deadlock should be very low, in fact, under the RR,RC isolation level, we can gradually expand and follow suit.

If the combination of S locks is compatible in two sessions, then the next combination of X locks is mutually exclusive.

In a scenario where two sessions are concurrent, the steps for deadlock are as follows:

Mysql > create table dt1 (id int unique

Query OK, 0 rows affected (0.03 sec)

Session 1:

Begin

Select * from dt1 lock in share mode;-explicit shared lock

Session 2:

Begin

Select * from dt1 lock in share mode;-explicit shared lock

Session 1:

Insert into dt1 values (1);-blocking

Session 2:

Insert into dt1 values (2);-- trigger deadlock

So the characteristics of the above sentence are obvious, the inserted data are 1 and 2 respectively, it seems that the complementary conflict is not good.

We are a little faster, and we may rarely see a way to declare share mode directly, but it is often triggered by other scenarios, one of the main reasons is that the check of duplicate data opens the S lock. This is a relatively special point, which requires attention.

By expanding at this point, you can easily expand to three sessions.

Session 1 is only responsible for inserting a piece of data, and session 2 and 3 are followed by a record (session 2 and session 3 automatically commit), but because of the check of the uniqueness index, both session 2 and session 3 open the S lock, because it is compatible, so it's not affected yet. If session 1 commits normally, the check of session 2Magazine 3 will take effect, resulting in data cannot be inserted, violating the uniqueness constraint, but if we do the opposite, we can use a rollback to release the lock, and then both session 2 and session 3 will get the S lock successfully, followed by the X lock, not to mention the detailed algorithm. At this time, they block each other, resulting in a deadlock in session 3, and the data insertion of session 2 will still be successful.

Session 1:

Begin

Insert into dt1 values (1)

Session 2:

Insert into dt1 values (1)

Session 3:

Insert into dt1 values (1)

Session 1:

Rollback

It looks like an elaborate quiz, but it contains this big truth, for example, according to this idea, if the next two statements are delete, it will also trigger a deadlock. Sometimes we can look at the legend head on, or reason through the deadlock log. One of the inspirations to me is Tai Chi.

It would be much better to understand it from the perspective of the lock.

A less vivid picture shows that the left part is the insert operation in session 1, and the right part is in session 2 and session 3, both holding S locks, and then their S locks will be upgraded to X locks for the same reason after the transaction is rolled back, resulting in deadlocks.

Following this line of thinking, we can continue to expand a few scenarios. Like the delete way.

According to this idea, a lot of deadlock scenarios can be constructed.

The above is all the content of the article "how to deal with MySQL deadlock". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.