How do I fix this MySQL / Innodb Deadlocking issue in a non-blocking environment?
We are using MySQL with Innodb Engine repository. We have an "evented" environment that sends multiple parallel queries to a table. Basically, it works like this: We have a find_or_insert function that does this: - find () -> by result, if empty -> insert -> by find () result
We are using a non-blocking MySQL driver, so basically when we run this little algorithm at the same time, it runs all the sets before inserting the first result ... etc.
Unfortunately, we receive the following errors: "Deadlock detected while trying to lock, please try to restart transaction"
Anyone can help with this?
[EDIT]: Also, I really don't understand why MySQL has to lock the table to insert a new item here. Originally I though autoincrement was the culprit here, so I removed it ... bu I'm still getting the error. Is there a way to prevent MySQL from locking a table on inserts?
a source to share
Try mysql reference manual for diagnostics and resolution. It looks like you are doing a cache. A probable reason could be that many clients are entering the table at the same time trying to create the "first version" (for example, click "if empty insert"). Maybe you could add a pseudo-random delay or coordinate creators so you don't get many concurrent create calls in the db?
EDIT: Have you seen this page ? It looks like you need to set the my.cnf option to disable table locks using innodb. However, I assumed that your test might not be representable, as it might contain much higher percentage of authors than in the actual situation. If you start 100 threads with an empty table, they all instantly block on creation (maybe even for the same value). This is much worse than the average situation where you have better spreads across the keys, fewer misses, and a much higher reading percentage. If this is the expected behavior (i.e., you will have this behavior in real time), I would suggest adding a deferral strategy to the create statements.
a source to share
These requests need to be done one at a time, so maybe stop using this non-blocking driver. Or implement your own locking mechanism. Wait for you to finish before moving on to the next one.
You didn't say that it was unreasonable or for some reason you need these things to go so fast.
a source to share
I am changing my answer to understand that the question has been revised. Now it sounds like this is only a concurrent inserts issue, not a dependency on search behavior.
I understand that InnoDB does row locking on inserts and you cannot turn that off. However, you [edit] cannot use INSERT DELAYED. I just read that this is not available in InnoDB.
http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html
Perhaps explicit LOCK TABLES around all of your entries will override the default locking behavior. If there are no other operations on the table during the time interval when write events occur, this may work.
a source to share