How do I fix this MySQL/Innodb Deadlocking issue in a non-blocking environment?

Question

Hey,

We're using MySQL with Innodb Engine storage. We have an "evented" environment that sends multiple concurrent requests on a table. Basically, it works like this: We have a find_or_insert function that does that: - find() -> on result, if empty -> insert -> on result find()

We're using a non-blocking MySQL driver, so basically, when we start this small algorithm more than once at the same time, it runs all the finds before inserting the first result... etc.

Unfortunately, we get these errors : "Deadlock found when trying to get lock; try restarting transaction"

Anyone can help with that?

[EDIT] : Also, I actually do not understand why would MySQL need to lock the table just to insert a new element here. Initially I though the auto-increment was the culprit here, so I removed it... bu I still get the error. Is there a way to prevent MySQL to lock the table on inserts?

Answer 1

Try the mysql reference manual for diagnosing and resolving. It sounds like you are making a cache. A probable reason could be that a lot of clients concurrently hit the table trying to create the 'first version' (i.e hit the 'if empty insert'). Maybe you could add a pseudo-random delay or coordinate the creators so that you don't get lots of concurrent create calls to the db?

EDIT: Have you seen this page? Seems like you need to set a my.cnf setting to disable per-table locks with innodb. What I mainly was suggesting though, was that your test might not be representable since it may contain a much higher percentage writers than in a realistic situation. If you start 100 threads with an empty table they would all instantly block on create (even for the same value perhaps). This is much worse than in an average situation where you have a better spread over the keys, fewer misses and a much higher percentage of reads. If this is expected behaviour (i.e you would have this behaviour live), I would suggest adding a backoff strategy in the create statements.

Answer 2

You need these requests to happen one at a time, so maybe stop using that non-blocking driver. Or implement your own blocking mechanism. Wait for one to finish before moving on to the next one.

You havent said that this is unreasonable, or that you for some reason need these things to move along so quickly.

Answer 3

I'm changing my answer to reflect that the question is revised. It now sounds like this is only an issue with the concurrent inserts, not a dependency on the find behavior.

It's my understanding that InnoDB does row locking on inserts and you cannot turn this off. However you [edit] are not able to use INSERT DELAYED. I just read this is not available on InnoDB.

http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html

Perhaps an explicit LOCK TABLES around all your writes collectively will override the default locking behavior. If there are no other operations on the table during the interval when the write events occur, this might work.

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html