CODEWITHSUNDEEP
mysqldatabase-deadlocksjooqbatch-insert
sanity
sanity

Reputation: 35772

How do I avoid MySql deadlocks?

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?

Upvotes: 2

Views: 1702

Answers (1)

tadman
tadman

Reputation: 211580

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.

Upvotes: 2

Related Questions