What are the causes and solutions of mysql deadlock?

When multiple transactions hold and request locks on the same resource at the same time, resulting in circular dependencies, a deadlock occurs. The solution is: 1. Use a lower isolation level; 2. Use a fixed Access your tables and rows sequentially; 3. Add carefully selected indexes to tables; 4. Use fewer locks.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Deadlock occurs when multiple transactions hold and request locks on the same resource at the same time, resulting in circular dependencies. Deadlock occurs when a transaction attempts to lock resources in a different order. Take the two transactions on 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 be executed after the first statement and lock resources during the process. Each transaction then tried to execute the second line of statements, only to find that it was locked. The two transactions will wait forever for the other to complete, unless there are other reasons to break the deadlock.

In order to solve this problem, the database implements various deadlock detection and timeout mechanisms. Complex storage engines like InnoDB will indicate circular dependencies and return errors immediately. Otherwise deadlock will cause the query to be very slow. Some other bad practice is to wait for a timeout and then give up. The current way InnoDB handles deadlocks is to roll back the transaction holding the least exclusive row-level lock. (Almost the simplest reference indicator for rollback)

The order of lock behavior is determined by the storage engine. Therefore, some storage engines may deadlock under a specific sequence of operations, others may not. There are two types of deadlocks: some are unavoidable due to actual data conflicts, and some are caused by the way the storage engine works.

Only a partial or complete rollback of one of the transactions can break the deadlock. Deadlock is an objective fact in transaction systems, and your design must consider handling deadlocks. Some business systems can retry transactions from the beginning.

How to deal with deadlocks
Deadlocks are a typical problem in transactional databases, but unless they occur so frequently that you cannot run a transaction at all, they are generally not dangerous. of. Normally, you must write your applications so that they are always prepared to reissue a transaction if it is rolled back due to a deadlock.

InnoDB uses automatic row-level locking. Even in the case of a transaction that only inserts or deletes a single row, you can encounter a deadlock. This is because these operations are not truly "tiny" and they automatically set a lock on (possibly several) index records of the inserted or deleted row.

You can use the following techniques to deal with deadlocks and reduce the likelihood of them occurring:

Use SHOW INNODB STATUS to determine the cause of the last deadlock. This can help you tune your application to avoid deadlocks.

Always be prepared to reissue a transaction if it fails due to deadlock. Deadlock is not dangerous, try again.

Commit your transactions often. Small matters are less prone to conflict.

If you are using locked reads (SELECT ... FOR UPDATE or ... LOCK IN SHARE MODE), try using a lower isolation level, such as READ COMMITTED.

Access your tables and rows in a fixed order. Then the transactions form well-defined queries and there are no deadlocks.

Add carefully selected indexes to your tables. Then your query will need to scan fewer index records and therefore set fewer locks. Use EXPLAIN SELECT to determine which index MySQL thinks is most appropriate for your query.

Use less locking. If you can accept allowing a SELECT to return data from an older snapshot, do not add a FOR UPDATE or LOCK IN SHARE MODE clause to it. It is better to use the READ COMMITTED isolation level here because each sustained read within the same transaction reads from its own fresh snapshot.

If nothing else helps, serialize your transactions using table-level locking. The correct way to use LOCK TABLES on transactional tables (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 to table t1 and read from table t, you can do it as follows:

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 transactions to be queued nicely, and deadlocks are avoided.

The way to get a serialized transaction is to create an auxiliary "semaphore" table, which contains only a single row. Let each transaction update that row before accessing other tables. In this way, all transactions occur in a sequential manner. Note that InnoDB's on-the-fly deadlock detection algorithm also works in this case because serialized locking is row-level locking. Timeout methods, with MySQL table-level locking, must be used to resolve deadlocks.

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

Related recommendations: "mysql tutorial"

The above is the detailed content of What are the causes and solutions of mysql deadlock?. For more information, please follow other related articles on the PHP Chinese website!