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

Editor to share with you how to troubleshoot the deadlock problem caused by concurrent insertion in mysql, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

VPN was used to troubleshoot the problem, but no cause was found at the time.

After going to work, I found that it was a bug on the client side. A request should have been sent, but a large number of requests were sent, and there was no idempotent design at the application level, so all requests fell to the database level.

Database is a process

Environment

MySQL 5.6.14

Transaction isolation level read commit

The logic that causes the problem is roughly as follows:

CREATE TABLE T1 (I INT, PRIMARY KEY (I)) ENGINE = InnoDB

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;INSERT INTO T1 VALUES (1)

Session 2:

START TRANSACTION;INSERT INTO T1 VALUES (1)

Session 3:

START TRANSACTION;INSERT INTO T1 VALUES (1)

Session 1:

ROLLBACK

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order:

Session 1:

START TRANSACTION;DELETE FROM T1 WHERE I = 1

Session 2:

START TRANSACTION;INSERT INTO T1 VALUES (1)

Session 3:

START TRANSACTION;INSERT INTO T1 VALUES (1)

Session 1:

COMMIT

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 commits, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

As you can see, when Insert, the exclusive lock and insert intention lock on the record.

In the case of concurrency, if the record is already on an exclusive lock, an attempt is made to put a shared lock on the record.

If there are more than three concurrent threads

The first thread has an exclusive lock, and the second and third threads see that the record has an exclusive lock, then try to put a shared lock on the record.

Once the first thread rolls back, both the second and third threads apply for exclusive locks while they own the shared lock. At this time there is a deadlock.

It is important to note that if the first thread commits, the second and third thread will report an error repeating the primary key, but at this time, the second and third thread still owns the shared lock of the record. Second, the third thread must be rolled back. Otherwise, the shared lock they have will not be released.

Go back to the original question.

Three threads insert award_free_firecracker_watch_ the Common table at the same time, one thread successfully acquires the exclusive lock, and the other two threads share the lock.

When the thread that acquires the exclusive lock commits, the two threads that share the lock, and the last step, the update award_free_firecracker_watch_common table, produces a deadlock.

They all apply for exclusive locks while acquiring the shared lock.

These are all the contents of this article entitled "how to troubleshoot deadlocks caused by concurrent insertions in mysql". 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.