The MySQL query statement uses select: Database deadlock analysis caused by for update

Recently there is a business requirement, multiple machines need to query data from a MySQL table at the same time and make subsequent business logic, in order to prevent multiple machines to get the same data at the same time, each machine needs to lock in the acquisition data segment, to ensure that multiple machines do not get the same data.

Our MySQL storage engine is InnoDB and supports row locks. There are many ways to get data at the same time, in order to be simpler, without adding other tables and servers, we consider the use of the Select ... for Update method so that the X lock locks the data segment of the query, the other data in the table is not locked, and the other business logic can be manipulated.

Such a server such as select. For update limit 0, 30 o'clock, the other server executes the same SQL statement automatically waiting for the release lock, waiting for the previous server lock to be released, the server can query the next 30 data. If smarter, Oracle supports the for update skip locked skip lock area so that it does not wait for the next 30 records to be queried immediately.

There is still a need to say that the MySQL for update caused the deadlock.

After analysis, MySQL's InnoDB storage Engine practice Lock is a lock line, but it is the lock index, according to where conditions and the value of select whether only primary key or non-primary key index to determine how to lock, such as only the primary key, the lock primary key index, if only non-primary key, lock non-primary key index, If the primary key does not have a primary key, the interior is locked in order. But the same select. Why is the FOR UPDATE statement dead locked? The same SQL statement query conditions and result sequence are consistent, supposedly does not cause a lock primary key index, waiting for the lock non-primary key index, another lock is a non-primary key index, waiting for the primary key index caused by deadlock.

Finally, it was analyzed to find the SQL statement for update and the deadlock caused by another update statement.

For example, there are 60 data, select. For update query 第31-60条 data, update updates 1-10 data, according to the InnoDB storage engine's Row lock principle, should not cause the lock of different rows to wait for each other. Starting to think that a row lock locks a block of data in a large amount of data. Results in a block of data being locked, but after a lot of data testing, it was found that the whole table was locked, but not really.

Home computer No environment, another day with the company computer to describe the specific scene.

The MySQL query statement uses select: Database deadlock analysis caused by for update