Mysql Database Deadlock Process Analysis (select for update) _mysql

Recently there is a business needs, multiple machines need to query data from a MySQL table at the same time and do follow-up business logic, in order to prevent more than one machine to get the same data, each machine needs to be in the acquisition of data to lock the data section, to ensure that many machines do not get the same data.

Our MySQL storage engine is InnoDB and supports row locks. There are many ways to solve the data at the same time, in order to be simpler, do not add other tables and services, we consider the use of select ... for update, so that x locks the data section of the query, the other data in the table is not locked, other business logic can still operate.

Such a server, such as SELECT. For update limit 0, 30 o'clock, other servers executing the same SQL statement will automatically wait for the release of the lock, and the server can query the next 30 data after the previous server lock is released. If more intelligence is required, Oracle supports for update skip locked the lock area, so that it does not wait to query for the next 30 records that are not locked.

The following is the deadlock caused by the MySQL for update.

After analysis, MySQL's InnoDB storage Engine practice Lock is a lock line, but it is locked internally, depending on whether the value of the Where condition and select is only primary KEY or Non-key key index to determine how the lock, such as only the primary key, the lock primary key index, if only a Non-key key, then the lock is not primary key index, If the primary key is not 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 condition and result order are consistent, not logically cause a lock primary key index, waiting for the lock is not primary key index, another lock Non-key index, waiting for the primary key index caused by deadlock.

Finally, after analysis, we found the SQL statement for update and a deadlock caused by another SQL statement that updates non-select data.

For example, there are 60 data, select ... For update query 第31-60条 data, update updated 1-10 data, according to the InnoDB storage engine row lock principle, should not lead to different rows of locks caused by waiting for each other. Start thinking it's a row lock locks a block of data in larger amounts of data. caused a segment of data to be locked, but after a lot of data testing, found that the feeling of the whole table locked, but actually not.

Here are a few examples to illustrate:

The data begins with the ID =400000 data, issuccess and GetTime fields are 0, and now if 400000 data is issuccess to 1. Execute the following two SQL.

--1:
set autocommit=0;
Begin;
SELECT * FROM table1 where GetTime < 1 and issuccess=0 the ORDER by ID ASC limit 0,30 for update;
commit;
--2:

If the first SQL statement is not commit, the second SQL statement will only wait, so the second SQL statement modifies issuccess to 0,issuccess the index data with a value of 0, and the second SQL statement will not be able to update the data to the locked row.

Then execute the following SQL statement

--1:
set autocommit=0;
Begin;
SELECT * FROM table1 where GetTime < 1 and issuccess=0 the ORDER by ID ASC limit 0,30 for update;
commit;
--2:

The second SQL statement can be executed. Because the index segment of the issuccess=2 is not locked.

The above example knows the lock index paragraph is also easier to understand, the following is a wonderful thing:

First modify the gettime of id = 400000 data to 1,issuccess=0, and then execute SQL once:

--1:
set autocommit=0;
Begin;
Update Ctripticketchangeresultdata a set Issuccess=1 where ID =400000;
commit;
--2:

The 1th SQL first does not commit, according to the reason will only lock 40000 this line record, the second SQL execution, according to the reason can only query from 400001 Records 30 records, but the second SQL statement blocks the wait.

The reason is that the first SQL statement has no commit and no rollback, so it locks the primary key index, then locks the issuccess index, and the second SQL statement, because the value of the Issuccess field is judged in the Where, Since the previous issuccess of 400000 is 0, updating to 1 is uncertain and may be rolled back, so SQL2 needs to wait to determine if 400000 of the data issuccess is modified. SQL2 SQL statement because the gettime<1 is judged, the actual 400000 record is not satisfied, but according to the principle of the lock index, so the SQL2 statement will be blocked.

Therefore, if according to the business scene, you can put the SQL2 statement issuccess conditions, and here gettime query conditions from gettime<1 modified to gettime=0, so you can not block direct query out.

GetTime the lock effect caused by the range query is analyzed, it is not a gap lock problem, the feeling should be to use the scope as a condition, all the search range starting from line No. 0 will be locked. For example, update 400000 will be blocked, but update 400031 will not be blocked.

Our project has a deadlock, this is the principle, an SQL statement locks the primary key index, then locks the Non-key key index, and another SQL statement locks the Non-key index and then locks the primary key index. Although two SQL statements expect lock data rows to be different, the two SQL statements query or update conditions or result fields that have the same column may cause each other to wait for each other's lock, and 2 SQL statements cause a deadlock.

A personal summary of the analysis of locks under the InnoDB storage engine may be problematic:

1, update or query for update, will start in the where conditions for each field to determine whether there is a lock, if there is a lock will wait, because if there is a lock, the value of this field is uncertain, can only wait for the lock commit or rollback after the data is determined after the query.

2, in addition, and order by has a relationship, because the previous data may be locked, but query from the back of a range can be queried.

3, the other limit also have a relationship, such as limit 20,30 from the 20th record to take 30 rows of data, but the first row of data if locked, because of uncertain rollback or commit, will lock wait.

Ps:mysql uses the KILL command to resolve a deadlock problem, killing an executing SQL statement

When you run some statements using MySQL, the data volume is too large to cause deadlocks and is not reflected. At this point, you need to kill a query that is consuming resources, and the syntax format for the KILL command is as follows:

Copy Code code as follows:

KILL [CONNECTION | QUERY] thread_id

Each connection to the mysqld runs in a separate thread, and you can use the show Processlist statement to see which threads are running and terminate a thread with the kill thread_id statement.

Kill allows you to connection or query modifiers of your choice: Kill connection is the same as a kill without a modifier: it terminates the connection associated with a given thread_id. Kill query Terminates the connection to the currently executing statement, but maintains the status of the connection.

If you have process permissions, you can view all threads. If you have super Administrator privileges, you can terminate all threads and statements. Otherwise, you can only view and terminate your own threads and statements. You can also use the Mysqladmin processlist and mysqladmin kill commands to check and terminate threads.

First login MySQL, then use: Show Processlist; View the status of each thread in the current MySQL.

Mysql> show Processlist;
+------+------+----------------------+----------------+---------+-------+-----------+--------------------- 
| Id  | User | Host         | db       | Command | Time | State   | Info
+------+------+----------------------+----------------+---------+-------+-----------+----------------- ----| 
7028 | root | ucap-devgroup:53396 | platform    | Sleep  | 19553 |      | NULL
| 8352 | root | ucap-devgroup:54794 | platform    | Sleep  | 4245 |      | NULL
| 8353 | root | ucap-devgroup:54795 | platform    | Sleep  |   3 |      | NULL
| 8358 | root | ucap-devgroup:62605 | platform    | query  | 4156 | updating | update T_SHOP Set |

The above shows a list of currently executing SQL statements, identifying the corresponding ID of the statement that consumes the most resources.

Then run the KILL command with the following command format:

Kill ID;
--Example:
Kill 8358

Kill it.