So, "What is a Deadlock?" you might ask. If you already know what a Deadlock is skip the next paragraph.
Imagine a bowl of soup with one spoon and two persons. Each person must hold the bowl and the spoon in order to eat the soup. One person grabs the bowl, the other person grabs the spoon. They both wait for each other for either the spoon or the bowl to take a "bite" on the soup. This situation represents a "Deadlock".
Earlier this week i've discovered a "Deadlock" situation on my MySQL 5.5.28 database. I've been noticing spurious "deadlocks" for a long time since the affected database became live. I've checked my software several times and i was certain that nothing i was doing could trigger a deadlock, not multithreading issues, not multiprocessing issues, not even multi-client issues since each client accessed exclusive ranges of data on the same table, so i thought how could this be?
After lots of research i've discovered a page that said that MySQL is not obliged to always return a result without a "deadlock" and that we should always "retry" to fetch the required information. Since i don't consider myself an expert in this area of expertise i decided to give it a go and developed a retry cycle to "attack" the deadlocking table.
After a month of normal operations on the live database another deadlock was found, this time in another table and on an insert operation. I went berzerk. How can this be? If the record does not exist on the database how can it be on deadlock!? If there is no record, no one else could be trying to access it!
I digged deeper on this second deadlock and discovered that the insert operation on that table had an "Insert Trigger" that requested an update operation on the previouly discovered deadlocking table (let's call it A). A forensic analysis to the MySQL database revealed that at the time of the Deadlock there was 2 clients trying to access Table A. Client 1 was accessing the table in a SELECT...FOR UPDATE and Client 2 in the INSERT operation to table B. Bear in mind that Client 1 is trying to select a range of data that is already mutually exclusive from the range of data that is going to be updated by the INSERT TRIGGER on Client 2, so conceptualy there are no reasons for deadlocking there.
So? What the hell is going on? I've read some foruns and here is my explanation:
- The first SELECT from Client 1 on table A is selecting a range of data that will be discarded. I'm sorry oracle, but trying to lock rows that i don't need doesn't seem very efficient to begin with.
- Although the INSERT operation on table B (that TRIGGERS an UPDATE on table A) is being executed as an atomic transaction the resources required for that transaction to happen are not being atomically requested, thus leaving a chance for this deadlock to happen.
Because of this two events Client 2 would hold a shared lock on Table A in order to INSERT on Table B while Client 1 would request the same row (to be rapidly discarded) on Table A which would then be needed by Client 2 to execute the UPDATE TRIGGER on Table A leaving the two requests on a deadlock.
What a mess!
I'm now using MySQL 5.5.30 and i didn't changed to 5.6.10 because that database version failed my system's database tests.
That is another mystery that i will try to solve. Stay tuned.
