In addition to this blog post there is an internal presentation that Yelp has provided slides for[1] covering MySQL's transaction isolation levels, replication, InnoDB locking and so on.<p>[1] - <a href="http://cloud.github.com/downloads/eklitzke/mysql-minutiae/presentation.pdf" rel="nofollow">http://cloud.github.com/downloads/eklitzke/mysql-minutiae/pr...</a>
Nice slides (linked from the article).<p>Since I'm MySQL ignorant, you say:<p>"the code wasn't sure if a row would already exist in the table, so it would issue a DELETE just in case followed by an INSERT with the correct data"<p>I come from a batch oriented background, so whenever I read something like this, my 'use a merge statement' light goes on.<p>I have literally no clue about the locking behavior of such an approach in MySQL, so I'm curious if (a) it was an option at all and (b) what locking behavior MySQL would throw into play with a merge (upsert) directly.
I like reading stuff like this. I deal with strange issues with MySQL all the time. It's nice to know others feel my pain and take the time to write about it. I don't usually see deadlocks anymore though thankfully.