How do I avoid MySql deadlocks?

Question

I'm talking to a MySql database using the jOOQ database abstraction layer.

I keep getting the following error:

SQL [null]; Deadlock found when trying to get lock; try restarting transaction

This is during a bulk insert of about 500 rows into a table. It is likely that more than one of these bulk inserts will be attempted at a time from different threads.

What is causing the deadlock, and how can I avoid it?

Without more info impossible to answer. Also possible duplicate for http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans — Eddy, Aug 18 '12 at 19:41

score 2 · Accepted Answer · answered Aug 18 '12 at 19:32

2

A traditional deadlock is when a transaction is trying to lock A and then B where another is trying to lock B and then A, leading to a situation where neither can complete. MySQL produces another sort of deadlock when there are too many pending locks on a particular resource.

You should check SHOW PROCESSLIST to see how many "waiting for lock" processes you have. It could be that the ones that fail are simply out of luck because there's too many in line.

answered Aug 18 '12 at 19:32

tadman

208,517
23
234
262

1

"show processlist" lists 15 processes right now, what is the limit? – sanity Aug 18 '12 at 19:36
If they're all locking the same table, then you've probably found the limit. I'm not sure it's documented or adjustable. You can adjust the `table_lock_wait_timeout` or `innodb_lock_wait_timeout` server variables to make the locks more patient, too. – tadman Aug 18 '12 at 19:45

How do I avoid MySql deadlocks?

1 Answers1