处理和防止死锁的正确方法

Question

I've understood that deadlocks occur when an sql query tries to lock an already locked row and I'm currently experiencing deadlocks. here's my sql query below:

INSERT INTO transactions (product_id, category, C, amount, date) SELECT 'SomeProduct', 'SomeCategory', v.username, 10, '2016-3-31' FROM balance v WHERE v.username = 'SomeUsername' AND v.balance + 10 >= 0

balance is a virtual table that sums transactions to get user's balance.

This error usually is noticed when having a reasonable amount of users which makes it hard to test, any tips on how to avoid deadlocks or any possible solution because I'm inserting rows into the transaction table in a very numerous way and looking to solve it!

I've also tried tried to catch the exception, but I couldn't create a loop that would redo the query until it is finished.

Answer 1

General answer

Deadlocks can only occur when you have two or more resources, two or more processes, and the processes lock the resources in different order.

Say, process 1 wants to lock resource A, then B, then C. Process 2 wants to lock B, then A, then C.

This may lead to a dead lock if 1 gets A, then 2 gets B, then 1 waits for B and 2 waits for A - indefinitely.

The solution is, thankfully quite simple: anytime if a process needs to lock two or more resources, it must do so in a "sorted" fashion. In this example, if process 2 also gets A, then B, then C, a deadlock can never happen.

Specific answer

I your case, you seem to be locking different table rows within one transaction in more or less random order. Try to find out how to release locks with mysql and make sure you are only holding as many as you actually need. If you need to hold more than one at a time, try to order your requests in some way.

Hard to tell without knowing more about your code... the first Google hit for "mysql deadlock" shows some promising stuff though: https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks