Lock wait timeout exceeded with ColdFusion & MySQL – Checked yer logs?

Sunday, March 31st, 2013

I just spent some time diagnosing an issue with inserting records into a MySQL database and I eventually tracked it down to a gotcha I think a lot of other people may run into.

The code in question does some fairly simple things – inside a <cftransaction> it’ll insert a record into a table, read the record back and insert a record into a second table with contents from the first record.

Easy, right? Except every time I tried it, I’d get a timeout with the following error:
Lock wait timeout exceeded; try restarting transaction
Inserting the records manually worked fine, but not from CF.

After searching through the MySQL logs, I noticed that a second connection was made half way through the transaction – the remaining query would then time out and the transaction would roll back.
What I discovered is this:

<cfquery datasource="yourDatasource">

is treated as a different connection to

<cfquery datasource="yourDatasource" username="" password="">

Generally I won’t include usernames and passwords in my cfquery statements, but I’m connecting to a library that does by default. I’ve found that leaving the authentication fields blank will connect fine using those specified in the datasource, however it seems that MySQL considers it a different connection which will cause a problem in a transaction.
I’m running Railo 4 – the behaviour may differ on ACF.

So there you have it. A great argument for encapsulation and a nicely configured datasource object.

Filed under: ColdFusion, Railo.