MySQL Lock and deadlock analysis

In MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index.

In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values (set at update), the so-called Next-key locking.

Case Study 1:

The tab_test structure is as follows:

ID: primary key;

State: status;

Time:;

Index: idx_1 (state,time)

2 SQL statements with deadlock

UpdateTab_testSetState=1064, Time=Now ()whereState=1061  andTime<Date_sub (now (), INTERVAL -minute)UpdateTab_testSetState=1067, Time=Now ()whereIdinch(9921180)

Cause Analysis:

When "Update tab_test set State=1064,time=now () where state=1061 and Time < Date_sub (now (), INTERVAL minute)" executes, MySQL will make With the idx_1 index, the associated index record is locked first, because Idx_1 is a non-primary key index, and MySQL locks the primary key index for executing the statement.

Assuming that "update tab_test set State=1067,time=now () where ID in (9921180)" is executed almost simultaneously, this statement locks the primary key index first, and because it needs to update the value of state, it also needs to be locked idx_ Some index records for 1.

The first statement locks the idx_1 record, waits for the primary key index, and the second statement locks the primary key index record while waiting for the idx_1 record, so the deadlock occurs.

In the first statement before the primary key lock, the second statement has been to lock the primary key, so in high concurrency data operation, the deadlock situation is easy to produce

InnoDB automatically detects a transaction's deadlock and rolls back one or more transactions to prevent deadlocks. InnoDB will choose a less expensive transaction rollback, this transaction (1) unlocks and rolls back, and the statement (2) continues to run until the end of the transaction.

Solutions

Split the first SQL, first identify the eligible primary key values, and then update the records according to the primary key:

Select  from where state=1061and<  minute); Update tab_test state=1064, time=wherein (...);

Case Study 2

The table structure of the Teamuser table is as follows:
PRIMARY KEY (' uid ', ' Id '),
KEY ' K_id_titleweight_score ' (' id ', ' titleweight ', ' score '),
Engine=innodb

2 SQL statements with deadlock

 insert  into  teamuser_20110121 Span style= "color: #0000ff;" >select  *  from   Teamuser  delete  from  Teamuser Span style= "color: #0000ff;" >where  teamidand  Titleweight<  32768  and  jointime<   '  

Two statement lock condition
under InnoDB default transaction isolation level, normal Select does not require a row lock, but the lock in SHARE mode, the for The select in the update and high serialization levels is locked. With one exception, in this case, the statement (1) INSERT INTO teamuser_20110121 select * from Teamuser adds a table lock to the table teamuser_20110121 (engine= MyISAM). A shared lock is also added to the primary key index (that is, clustered index) for all rows of the Teamuser table. The primary key index is used by default.
and statement (2) DELETE from Teamuser WHERE teamid= $teamId and titleweight<32768 and jointime< ' $ Daysago_1week ' For a delete operation, an exclusive lock is added to the primary key index of the selected row. Since this statement also uses the prefix index of the non-clustered index key ' K_teamid_titleweight_score ' (' Teamid ', ' titleweight ', ' score '), the non-clustered index of the related row is also added to the exclusive lock.
lock conflict Generation
because a shared lock is mutually exclusive to an exclusive lock, when a party has an exclusive lock on a row of records, the other party cannot have a shared lock, and similarly, the other party cannot get its exclusive lock after the party has its shared lock. Therefore, when the statement (1), (2) runs concurrently, the equivalent of two transactions requests a lock resource for the same record row, resulting in a lock conflict. Because two transactions will request a primary key index, the lock conflict will only occur on the primary key index.

Ways to avoid deadlocks
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides non-lock reads in the SELECT statement. These features add to multi-user deployment and performance.
However, its locking mechanism also introduces the risk of deadlocks, which requires avoiding deadlocks when the application is designed. For implicit transactions consisting of a single SQL statement, the recommended method for avoiding deadlocks is as follows:
1. If you use the Insert...select statement to back up the table with a large amount of data, operate at a separate point in time, avoid contention with other SQL statements, or use SELECT INTO outfile plus load data infile instead of Insert...select. It's not just fast, and it doesn't require locking.
2. A transaction that locks a recordset should be as brief as possible to avoid consuming too many resources at a time and conflict with records from other transactions.
3. To update or delete tabular data, the Where condition of the SQL statement is either a primary key or an index, preventing the two cases from crossing and causing a deadlock. For cases where the WHERE clause is more complex, it is passed through SQL alone and then used in the UPDATE statement.
4. SQL statements do not have too many nested tables, can be split to split, to avoid the possession of resources while waiting for resources, resulting in conflict with other transactions.
5. To run a script on a fixed point, avoid running multiple scripts that read and write to the same table at the same point in time, paying special attention to statements that lock and manipulate large amounts of data.
6. The application increases the decision to deadlock, and if the transaction ends unexpectedly, rerun the transaction, reducing the impact on functionality.

viewing Deadlock Records

The show InnoDB STATUS \g statement views the last deadlock record in the MySQL client.

when you encounter a problemExecute show processlist to find the deadlock thread number. Then kill Processno, of course, will have to take a look at the specific operation. Deadlock show InnoDB status check engine status, you can see which statements produce deadlocks

MySQL Lock and deadlock analysis