Details about deadlocks in MySQL and how to handle deadlocks.

Details about deadlocks in MySQL and how to handle deadlocks.

When multiple transactions hold and request locks on the same resource at the same time and generate a circular dependency, a deadlock occurs. A deadlock occurs when a transaction attempts to lock resources in different sequence. Take two transactions in the StockPrice table as an example:

Transaction 1

START TRANSACTION;UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';COMMIT;

Transaction #2

START TRANSACTION;UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';UPDATE StockPrice SET;COMMIT;

If you are unlucky, each transaction can execute the first statement and lock the resource during the process. Then every transaction tries to execute the second line of statement, but finds it locked. The two transactions will always wait for the other party to complete, unless there are other reasons to interrupt the deadlock.

To solve this problem, the database implements various Deadlock Detection and timeout mechanisms. Complex storage engines such as InnoDB will prompt circular dependencies and immediately return errors. Otherwise, the deadlock will slow the query. Some other bad practices are to wait for timeout and give up. Currently, InnoDB processes deadlocks by rolling back transactions that hold at least exclusive locks. (The simplest reference for rollback)

The locking behavior is determined by the storage engine in sequence. Therefore, some storage engines may experience deadlocks in specific operation sequence, and others may not. There are two types of deadlocks: some are unavoidable due to actual data conflicts, and some are generated by the storage engine's working method.

Only partial or full rollback of one of these transactions can break the deadlock. Deadlock is an objective fact in the transaction system. You must consider deadlocks in design. Some business systems can retry transactions from the beginning.

How to handle deadlocks
Deadlocks are typical problems in transactional databases, but they are generally not dangerous unless they occur so frequently that you cannot run a transaction. Normally, you must write your applications so that they are always prepared to roll back a transaction due to a deadlock and then re-issue a transaction.

InnoDB uses automatic row-level locking. Even if you only insert or delete a single row of transactions, you can encounter a deadlock. This is because these operations are not really "extremely small" and they automatically lock the index records of the inserted or deleted rows (possibly several.

You can use the following techniques to deal with deadlocks to reduce their likelihood:

Use show innodb status to determine the cause of the last deadlock. This helps you adjust applications to avoid deadlocks.

Always prepare to re-issue the transaction if it fails due to a deadlock. Deadlock is not dangerous. Try again.

Submit your transactions frequently. Small transactions are less prone to conflicts.

If you are using locked READ, (SELECT... for update or... lock in share mode), try to use a lower isolation level, such as read committed.

Access your tables and rows in a fixed order. Transactions form well-defined queries without deadlocks.

Add carefully selected indexes to your table. Then, your query needs to scan fewer index records and therefore set fewer locks. Use explain select to determine which index MySQL considers the most appropriate for your query.

Use fewer locks. If you can accept that a SELECT statement can return data from an old snapshot, do not add a for update or lock in share mode clause to it. Here, it is better to use the read committed isolation level, because each continuous READ in the same transaction reads from its own snapshot.

If nothing else is helpful, use table-level locking to serialize your transactions. The correct way to use lock tables for transaction TABLES (such as InnoDB) is to set AUTOCOMMIT = 0 and do not call unlock tables until you explicitly commit the transaction. For example, if you want to write table t1 and read it from table t, you can do the following:

SET AUTOCOMMIT=0;LOCK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and t2 here];COMMIT;UNLOCK TABLES;

Table-level locking ensures that your transactions are well queued and deadlocks are avoided.

A serialized transaction is created by creating an auxiliary "semaphore" table, which contains only one single row. Let each transaction update the row before accessing other tables. In this way, all transactions occur in sequence. Note that the InnoDB instant Deadlock Detection Algorithm can also be rented in this case, because serialization locks are row-level locks. Timeout method. It is locked at the MySQL table level and must be used to solve the deadlock.

Use the lock tables command in the application. If AUTOCOMMIT = 1, MySQL does not set InnoDB table LOCK.

Articles you may be interested in:

• MySQL deadlock caused by the load data statement
• Mysql database deadlock causes and solutions
• Mysql database deadlock Process Analysis (select for update)
• How to find the deadlock ID in the MySQL thread
• MySQL deadlock analysis and solution example
• Analysis and Induction of deadlock logs caused by MySQL Innodb tables