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.

Related posts

  1. Integrate WordPress into your ColdFusion appIntegrate a Wordpress blog into your CFML app, with functions...
  2. Find the closest in a list of numbersFor a while now I’ve been working on a core...
  3. Cache and display multiple Twitter feeds in ColdFusion – CFCCFC to easily pull in and cache multiple Twitter feeds....

Filed under: ColdFusion, Railo.

Leave a Comment

About

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

Contact

Gary Stanton

Email:

Tel:
01273 775522

Twitter

No public Twitter messages.

Delicious Feed

Website Design & Development