Mysql – When to use MySQL handler syntax

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

• Reasons for occasionally slow queries?
• Trouble deciphering a deadlock in an innodb status log
• Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

• Your SQL UPDATEs a single row but generates an error
• The UPDATE causes a rollback of the one row
• The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

Opinion

It's probably better to throw an exception during your applications input checking and not pass the buck to the database.

Workaround

There is a "workaround" but your mileage may vary: http://forge.mysql.com/worklog/task.php?id=3780

Brute Force?

You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the current problem. ...and of course that invites a host of other problems by using BLOBs instead of VARCHAR.

Upgrading

UTF32 may solve the problem and upgrading to 5.5.x isn't has hard as you'd think. Create a replicated slave (farm), sync up and promote it to master.