Analysis of database deadlock caused by Mysql Query statement using select .. forupdate _ MySQL

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. M has a business requirement 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 servers, 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 it is more intelligent, oracle supports for update skip locked to skip the lock area, so that the next 30 records can be queried immediately.

Here we still need to talk about 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.

After analysis, it is found that the SQL statement for update is a deadlock caused by another update statement.

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 above is the analysis of database deadlock caused by Mysql Query statement using select... for update _ MySQL content. for more information, please follow the PHP Chinese network (www.php1.cn )!