How to solve mysql deadlock?

Mysql deadlock solution: 1. Wait until timeout (innodb_lock_wait_timeout=50s), automatically roll back the transaction; 2. Initiate deadlock detection, actively roll back a transaction, and let other transactions continue to execute ( innodb_deadlock_detect=on).

MySQL has two deadlock handling methods:

● Wait until timeout (innodb_lock_wait_timeout=50s), Automatically rollback transactions.

● Initiate deadlock detection, proactively roll back a transaction and allow other transactions to continue executing (innodb_deadlock_detect=on).

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

Deadlock detection

The principle of deadlock detection is to construct a directed graph with transactions as vertices and locks as edges, and determine whether there is a cycle in the directed graph. If there is a cycle, there is a deadlock.

Rollback

After detecting a deadlock, select the transaction with the smallest number of inserted, updated or deleted rows to roll back, based on the trx_weight field in the INFORMATION_SCHEMA.INNODB_TRX table.

The above is the detailed content of How to solve mysql deadlock?. For more information, please follow other related articles on the PHP Chinese website!