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

This article will explain in detail whether deadlocks will occur in update statements in the MySQL database, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Recently, the production of the MySQL database, is not just a DeadLock, in which I did troubleshooting, yesterday made the relevant upgrade, resulting in yesterday very busy, many netizens added my friends, did not respond in time, until the end of the upgrade in the evening, I made the relevant explanation in the group!

The error log message is as follows:

The update statements involved are as follows:

Isn't it amazing that there is a deadlock when executing a update sql?

The specific deadlock advance is that the order_id field in the i_pay_record table has an index.

Let's illustrate the problem by creating a new table, xttblog.

When we execute the following update statement, a deadlock may occur!

Mysql's transaction support is related to the storage engine, MyISAM does not support transactions, INNODB supports transactions, and updates may use row-level locks or table-level locks. We use INNODB as the storage engine here, which means that the update statement will be processed as a transaction. I mentioned in the previous article that row-level locks must be based on indexes, and the above update statement uses index idx_1, so row-level locks must be added here.

Row-level locks do not lock records directly, but lock indexes (as explained in previous articles).

If a SQL statement uses a primary key index, mysql locks the primary key index; if a statement operates on a non-primary key index, mysql locks the non-primary key index first and then locks the primary key index.

This update statement performs the following steps:

Because non-primary key indexes are used, you first need to acquire row-level locks on idx_1

It is then updated according to the primary key, so you need to acquire the row-level lock on the primary key

When the update is complete, commit and release all locks

If you suddenly insert a statement between steps 1 and 2: update xttblog … .. where id=? And user_id=? With this statement, the primary key index is locked first, and then the idx_1.

At this time, the tragedy happened!

One statement acquires a lock on idx_1 and waits for a lock on the primary key index; another statement acquires a lock on the primary key and waits for a lock on idx_1, resulting in a deadlock.

It's amazing, but it's not surprising at all, as long as you understand some of the underlying design principles of MySQL!

So is there a solution to this problem?

Of course I do. Why else would I write this article?

The stupidest and most reliable solution is to first get the primary key of the record that needs to be updated, and then update it one by one!

This will solve the problem, but this solution is different from the previous update statement, which updates all records in one transaction, but not in the same transaction after the circular update, so there is another transaction outside the for loop.

In MySQL with INNODB, row-level locks are added by default, and row-level locks are based on indexes. Before analyzing deadlocks, you need to query the execution plan of mysql to see whether indexes are used and which indexes are used. Table-level locks are used for operations that do not use indexes. If the primary key index is used in the operation, the primary key index will be locked first, and then on other indexes, otherwise the locking order will be reversed. In applications with high concurrency, batch updates must take the recorded primary key and give priority to obtaining the lock on the primary key, which can reduce the occurrence of deadlock.

It's not that update doesn't have deadlocks, it's just that your program doesn't encounter high concurrency!

On the MySQL database update statements will not occur deadlock sharing here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.