Detailed information on the deadlock in Mysql and how to handle the deadlock _mysql

A deadlock occurs when multiple transactions hold and request locks on the same resource at the same time as a result of cyclic dependency. Deadlocks occur when a transaction attempts to lock a resource in a different order. Take the two transactions on the StockPrice table for example:

Business 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 unlucky, each transaction can execute the first statement and lock the resource in the process. Then every transaction tries to execute the second line of statements, but it is found to be locked. Two transactions will always wait for each other to complete unless there are other reasons to interrupt the deadlock.

To solve this problem, the database implements various deadlock detection and timeout mechanisms. A complex storage engine such as InnoDB prompts for a circular dependency and returns an error immediately. Otherwise the deadlock will cause the query to be very slow. Some other bad practice is to wait for the timeout and then give up. The current InnoDB handles deadlocks by rolling back transactions that hold the least exclusive row-level locks. (Reference metric for almost the simplest rollback)

The behavior of the lock is in the order that the storage engine determines. As a result, some storage engines may be given a life lock in a particular sequence of operations, others may not. There are two types of deadlocks: some are unavoidable because of actual data conflicts, and some are caused by the way the storage engine works.

Only a partial or full rollback of one of the transactions could break the deadlock. Deadlock is an objective reality in a transactional system, and your design must be designed to deal with deadlocks. Some business systems can retry transactions from scratch.

How to handle deadlocks
deadlocks are a typical problem with transactional databases, but they are generally not dangerous unless they occur so frequently that you are less able to run a transaction. Normally, you must write your application so that they are always prepared to issue a transaction again if a transaction is rolled back because of a deadlock.

InnoDB uses automatic row-level locking. You can encounter deadlocks even if you are only inserting or deleting transactions for a single row. This is because these operations are not really "minimal", and they automatically set locks on the rows that are inserted or deleted (possibly several) indexed records.

You can use the following techniques to deal with deadlocks to reduce the likelihood that they will occur:

Use using show INNODB status to determine the cause of the last deadlock. This will help you adjust your application to avoid deadlocks.

Always ready to issue the transaction again if it fails because of a deadlock. Deadlock is not dangerous, try again.

Always submit your affairs. Small transactions tend to be less prone to conflict.

If you are using lock 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. The transaction forms a well defined query and does not have a deadlock.

Add a carefully selected index to your table. Your query needs to scan for fewer index records and thus set fewer locks. Use the explain select to determine which index MySQL considers to be the most appropriate for your query.

Use fewer locks. If you can accept that a select is allowed to return data from an old snapshot, do not add a for update or lock in SHARE mode clause. It is better to use the Read Committed isolation level here, as each continuous reading in the same transaction is read from its own fresh snapshot.

If nothing else helps, use table-level locking to serialize your transactions. The correct way to use lock tables for a transactional table, such as InnoDB, is to set autocommit = 0 and not call unlock tables until you explicitly commit the transaction. For example, if you need to write a table T1 and read from the 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 allows your business to queue up well and deadlock is avoided.

The way to get a serialized transaction is to create a secondary "semaphore" table that contains only one single row. Let each transaction update that row before accessing the other tables. In this way, all transactions occur in a sequential fashion. Note that the InnoDB instant deadlock detection algorithm can also be leased in this case because serialization locking is a row-level lock. Timeout method, with MySQL table-level locking, must be used to resolve deadlocks.

Use the Lock tables command in your application if Autocommit=1,mysql does not set the InnoDB table lock.