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

Editor to share with you how MySQL deals with deadlocks. I hope you will get something after reading this article. Let's discuss it together.

What is a deadlock

The official definition is as follows: both transactions hold the lock needed by the other and are waiting for the other to release, and neither party will release its own lock.

This is like you have a hostage, the other side has a hostage, you two go to negotiate a replacement. You let the opposite side release the person, and the opposite side asks you to let the person go.

Second, why deadlocks are formed

Seeing here, you may have the question that transactions are different from negotiations, why can't transactions be released immediately after using the lock? I can't believe you still have to hold the lock after the operation? This involves concurrency control of MySQL.

There are two ways to control the concurrency of MySQL, one is MVCC, the other is two-phase locking protocol. So why concurrency control? This is because when multiple users operate MySQL at the same time, in order to improve concurrency performance and require the same as the serial execution after the request of multiple users (serializable scheduling). The specific concurrency control is no longer expanded here. Let's move on to an in-depth discussion of the two-phase locking protocol.

Two-phase locking protocol (2PL)

Official definition:

The two-phase locking protocol means that all transactions must lock and unlock the data in two phases. Before reading or writing to any data, the transaction must first obtain a lock on the data; after releasing a lock, the transaction will no longer apply for and obtain any other blockade.

There are two phases corresponding to MySQL:

Extension phase (after transaction starts, before commit): acquire lock

Contraction phase (after commit): release the lock

That is to say, serializable scheduling can only be achieved by following the two-stage locking protocol.

However, the two-phase locking protocol does not require transactions to lock all the data they need at once, and there is no order requirement in the locking phase, so this concurrency control method will form a deadlock.

3. How does MySQL handle deadlocks?

MySQL has two ways to handle deadlocks:

Wait until the innodb_lock_wait_timeout=50s.

Initiate deadlock detection, actively roll back one transaction, and let other transactions continue to execute (innodb_deadlock_detect=on).

Due to performance reasons, deadlock detection is generally used to deal with deadlocks.

Deadlock detection

The principle of deadlock detection is to construct a directed graph with transaction as vertex and lock as edge to judge whether there is a loop in the directed graph.

Roll back

After a deadlock is detected, select the transaction rollback with the least number of rows inserted, updated or deleted, based on the trx_weight field in the INFORMATION_SCHEMA.INNODB_TRX table.

How to avoid deadlock

Collect deadlock information:

Use the command SHOW ENGINE INNODB STATUS to see the cause of the deadlock.

Innodb_print_all_deadlocks is opened during debugging and all deadlock logs are collected.

Reduce deadlocks:

Use transactions instead of lock tables.

Make sure there is no long-term business.

Commit the transaction immediately after the operation, especially on the interactive command line.

If you are using (SELECT... FOR UPDATE or SELECT... LOCK IN SHARE MODE), try to lower the isolation level.

When you modify multiple tables or rows, keep the order of changes consistent.

By creating an index, fewer locks can be created.

It is best not to use (SELECT... FOR UPDATE or SELECT... LOCK IN SHARE MODE).

If none of the above solves the problem, try locking multiple tables using lock tables T1, T2, T3

After reading this article, I believe you have a certain understanding of "how MySQL handles deadlocks". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.