Mysql database deadlock Process Analysis (select for update ),

Mysql database deadlock Process Analysis (select for update ),

There is a business demand recently. Multiple machines need to query data from a Mysql table at the same time and perform subsequent business logic. To prevent multiple machines from getting the same data at the same time, each machine needs to lock the data segment for obtaining data to ensure that multiple machines do not obtain the same data.

Our Mysql storage engine is innodb and supports row locks. There are many ways to get data at the same time. For the sake of simplicity and without adding other tables and services, we consider using select... in this way, the X lock locks the queried data segment. Other data in the table is not locked, and other business logic can still be operated.

Such a server, such as select .. for update limit, when other servers execute the same SQL statement, they will automatically wait for the release lock. After the previous server lock is released, the server will be able to query the next 30 data records. If more intelligence is required, oracle supports for update skip locked to skip the lock area, so that the next 30 records that are not locked can be queried immediately without waiting.

The following describes the deadlock caused by mysql for update.

After analysis, although mysql's innodb Storage engine practice locks rows, it locks indexes internally, determine how to lock a primary key index based on the where condition and select value. For example, if there is only a primary key, lock the primary key index. if there is only a non-primary key, lock the non-primary key index, if both primary keys and non-primary keys exist, internal locks are performed in order. But how can the same select... for update statement be deadlocked? The query conditions and result sequence of the same SQL statement are consistent. It does not cause a primary key index to be locked. Wait for the non-primary key index to be locked, and the other locks the non-primary key index, wait for the deadlock caused by the primary key index.

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

For example, if there are 60 data records, select .. for update queries the 31-60 pieces of data, and update updates the 1-10 pieces of data. According to the row lock principle of the innodb Storage engine, there should be no mutual waiting caused by different row locks. In the beginning, the row lock locks data blocks when the data volume is large. As a result, the data in a segment is locked. However, after a large amount of data tests, it is found that the entire table is locked, but not actually.

The following are examples:

The data starts from the data with id = 400000. The IsSuccess and GetTime fields are both 0. Now, if the IsSuccess of 400000 data is 1. Run the following two SQL statements.

-- 1:set autocommit=0;begin;select * from table1 where getTime < 1 and IsSuccess=0 order by id asc limit 0,30 for update;commit;-- 2:update table1 a set IsSuccess=0 where id =400000; 

If the first SQL statement does not commit, the second SQL statement can only wait. Therefore, the second SQL statement changes IsSuccess to 0, and the IsSuccess non-primary key index locks the index data whose value is 0, the second SQL statement cannot 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 order by id asc limit 0,30 for update;commit;-- 2:update table1 a set IsSuccess=2 where id =400000; 

In this way, the second SQL statement can be executed. Because the IsSuccess = 2 Index segment is not locked.

The above example shows that it is easier to understand the lock index segment. Here is a wonderful thing:

First, change the GetTime of the id = 400000 data to 1, and IsSuccess = 0, and then execute the SQL statement once:

-- 1:set autocommit=0;begin;update ctripticketchangeresultdata a set issuccess=1 where id =400000;commit;-- 2:select * from table1 where getTime < 1 and IsSuccess=0 order by id asc limit 0,30 for update; 

1st SQL statements do not commit first. In principle, only 40000 rows of records will be locked. In the second SQL statement, only 30 records from 400001 records can be queried, however, the second SQL statement will block the wait.

The reason is that the first SQL statement has no commit or rollback, so it first locks the primary key index, and then locks the non-primary key index of IsSuccess, in the second SQL statement, the value of the IsSuccess field should be determined in the where statement. Since the previous IsSuccess value of 400000 is 0, the update to 1 is still unknown and may be rolled back, therefore, sql2 needs to wait to determine whether the IsSuccess of the 400000 data is modified. Because the SQL statement of sql2 judges GetTime <1, the actual 400000 record is no longer satisfied, but according to the locking index principle, sql2 statements will be blocked.

Therefore, you can cancel the IsSuccess condition of the SQL 2 statement according to the business scenario. Here, the GetTime query condition is changed from GetTime <1 to GetTime = 0, so that the query can be directly performed without blocking.

The impact of locks caused by GetTime using range queries has been analyzed. It is not a problem of gap locks. It seems that the range should be used as the condition, and all search ranges starting from row 1 will be locked. For example, update 400000 is blocked, but update 400031 is not blocked.

This is the principle of deadlock in our project. An SQL statement first locks the primary key index and then the non-primary key index. Another SQL statement first locks the non-primary key index and then the primary key index. Although the two SQL statements expect different data rows to be locked, if the two SQL statements query or update conditions or result fields have the same columns, they may wait for each other to lock, two SQL statements cause a deadlock.

Summary:

1. When updating or querying for update, the where condition starts to judge whether there is a lock for each field. If there is a lock, it will wait, because if there is a lock, the value of this field is unknown, you can only wait for the lock commit or rollback to confirm the data before querying.

2. It is also related to order by, because the previous data may have a lock, but the query can be performed from the following range.

3. There is also a relationship between limit and limit. For example, limit 20 and 30 fetch 30 rows of data from 20th records. However, if the first row of data is locked, it will also wait for the lock because it is not sure whether to roll back or submit the data.

Ps: mysql uses the kill command to solve the deadlock problem and kill an SQL statement being executed.

When using mysql to run certain statements, the deadlock is caused by a large amount of data, which is not reflected. In this case, you need to kill a query statement that is consuming resources. The syntax format of the kill command is as follows:

Copy codeThe Code is as follows:
KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld runs in an independent thread. You can use the show processlist statement to check which threads are running and use the KILL thread_id statement to terminate a thread.

KILL allows the optional CONNECTION or QUERY modifier: kill connection is the same as KILL without the modifier: It terminates the CONNECTION related to the given thread_id. Kill query terminates the statement currently being executed, but keeps the connection intact.

If you have the PROCESS permission, you can view all threads. If you have super administrator permissions, 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.

Log on to mysql first, and then use: show processlist; to view the status of each thread in 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 preceding figure shows the list of SQL statements currently being executed. Find the id of the statement with the largest resource consumption.

Run the kill command. The command format is as follows:

Kill id;
-- Example:
Killed 8358

Kill.

Articles you may be interested in:

• Analysis and Induction of deadlock logs caused by MySQL Innodb tables
• MySQL deadlock analysis and solution example
• How to find the deadlock ID in the MySQL thread