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.

Comments are closed.


Simian Enterprises is the trading name of Gary Stanton, a freelance web developer working by the sea in Brighton, UK. Gary's been creating websites since 1996 and still loves it. Read more


Gary Stanton


01273 775522


No public Twitter messages.

Delicious Feed

Website Design & Development