Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the causes and solutions of mysql deadlock". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the causes and solutions of mysql deadlock".

Deadlocks occur when multiple transactions hold and request locks on the same resource at the same time, resulting in a deadlock. The solutions are: 1, use a lower isolation level; 2, access your tables and rows in a fixed order; 3, add carefully selected indexes to the table; 4, use fewer locks.

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

Deadlocks occur when multiple transactions hold and request locks on the same resource at the same time, resulting in circular dependencies. A deadlock occurs when a transaction attempts to lock resources in a different order. Take 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 execute the first statement and lock the resource in the process. Each transaction then tries to execute the second line of statements, only to find that it is locked. Two transactions will always wait for each other to complete, unless there is some other reason to break the deadlock.

In order to solve this problem, the database implements a variety of deadlock detection and timeout mechanisms. A complex storage engine like InnoDB prompts circular dependencies and returns an error immediately. Otherwise, the deadlock will cause the query to be very slow. Other bad practices are to wait for a timeout and then give up. The current way InnoDB handles deadlocks is to roll back transactions that hold at least exclusive row-level locks. (almost the simplest reference indicator for rollback)

The behavior of locks is sequentially determined by the storage engine. As a result, some storage engines may have deadlocks in a specific order of operations, while others may not. There are two kinds 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 complete rollback of one of the transactions can break the deadlock. Deadlock is an objective fact in a transaction system, and your design should consider dealing with deadlock. Some business systems can retry transactions from scratch.

How to deal with deadlocks

Deadlocks are a typical problem in transactional databases, but they are generally not dangerous unless they occur so frequently that you cannot run a transaction at all. Normally, you must write your applications so that they are always ready to reissue a transaction if it rolls back a transaction because of a deadlock.

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

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

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

Always be ready to reissue the transaction if it fails because of a deadlock. Deadlock is not dangerous. Try again.

Submit your affairs frequently. Small affairs tend to be less conflicting.

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

Access your tables and rows in a fixed order. Then the transaction forms a well-defined query and there is no deadlock.

Add a carefully selected index 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 thinks is most appropriate for your query.

Use fewer locks. If you can accept allowing a SELECT to return data from an old 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 continuous read in the same transaction is read from its own fresh snapshot.

If nothing else is helpful, use table-level locking to serialize your transactions. The correct way to use LOCK TABLES for 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 table T1 and read 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 makes your transactions well queued and deadlocks are avoided.

The way to get a serialized transaction is to create an auxiliary "semaphore" table that contains only a single row. Have each transaction update that row before accessing other tables. In this way, all transactions occur sequentially. Note that the InnoDB real-time deadlock detection algorithm can also be leased in this case because serialized locks are row-level locks. The timeout method, which uses 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 the InnoDB table lock.

Thank you for reading, the above is the content of "what is the cause and solution of mysql deadlock". After the study of this article, I believe you have a deeper understanding of the cause and solution of mysql deadlock, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.