Mysql deadlock test and resolve

Deadlock test

Deadlocks, which occur when two transactions require a set of conflicting locks and cannot continue the transaction for another time. Command Line window 1:

BEGIN; UPDATE ty SET b =1 WHERE a = 2; UPDATE ty SET b =1 WHERE a = 3; // COMMIT (a = 3);Copy the code

Command line window 2:

BEGIN; UPDATE ty SET b =1 WHERE a =1; UPDATE ty SET b =1 WHERE a = 2; Transaction 1 and transaction 2 wait for each other's lock. If a deadlock occurs, mysql will COMMIT the current transaction.Copy the code

Solutions:

It is caused by a BUG in the program. There is no other way to adjust the logic of the program. Close analysis of the program logic for multi-line operation of the database, processed in the same order as far as possible, try to avoid locking two resources at the same time, such as operating A and B two rows, always press B after A first order processing, must lock two resources at the same time, to ensure that at any time should be to lock in the same order