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

This article will explain in detail how mysql produces deadlocks. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Deadlock in mysql: refers to the phenomenon that two or more processes wait for each other because of competing for resources during execution. If there is no external force, they will not be able to push forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes. Table-level locks do not produce deadlocks. So solving deadlocks is mainly aimed at the most commonly used InnoDB.

The key to deadlocks is that two (or more) Session locks are not in the same order.

Then the corresponding key to solve the deadlock problem is to make different session locks in order.

Case

Demand: divide the investment money into several pieces and randomly distribute it to the borrower.

At first, the business program thought like this:

Investors invest, the amount will be randomly divided into several, and then randomly selected from the borrower table, and then through a select for update to update the balance in the borrower table and so on.

For example, if two users invest at the same time, the amount of user An is randomly divided into two parts and distributed to the borrower.

B the amount of user is randomly divided into two parts, which are distributed to the borrower.

Deadlocks occur very quickly, of course, because of the different order of locking.

The improvement to this problem is as simple as locking up all assigned borrowers at once.

Select * from xxx where id in (xx,xx,xx) for update

The list value mysql in in is automatically sorted from small to large, and locks are also added from small to large.

For example, (the following session id is the primary key): Session1: mysql > select * from T3 where id in (8pm 9) for update +-+ | id | course | name | ctime | +-+ | 8 | WA | f | 2016-03-02 11:36: 30 | | 9 | JX | f | 0-03-01 11:36:30 | +-+ rows in set (0.04 sec) Session2:select * from T3 where id in (2016) 5) for update Waiting for the lock. In fact, the record of id=10 was not locked at this time, but the record of id=5 was already locked. If you don't believe it here in id=8, please see Session3:mysql > select * from T3 where id=5 for update; waiting Session4:mysql > select * from T3 where id=10 for update. +-+ | id | course | name | ctime | +-+ | 10 | JB | g | 2016-03-10 11:45: 05 | +-+ row in set (0.00 sec) id=5 cannot be locked in other session But id=10 can be locked. On how to generate deadlocks on mysql to share here, I hope 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.