Could not update; currently locked by user 'admin' on machine...

Folks, have you ever run into this error:

Could not update; currently locked by user 'admin' on machine 'machinename'

It occasionally happens with an Access database, in my case when someone's trying to do an insert. I may have found an explanation. Now please, keep the suggestions about moving off of Access to yourselves. I KNOW that it's not intended for production use, yadda, yadda. There are people who otherwise find it perfectly adequate.

Before I offer the explanation, to those who might persist with wanting to say that the solution is to move off of Access, to quote Dr. Evil, talking to his son in Austin Powers:

Evil: "Alright, zip it!"
Scott: "You know you can't even.."
Evil:"..zip it! Zip!"
Scott: "Look all I.."
Evil: "..Ladies and Gentlemen of the jury, ex-zip it A!"
Scott: "Number Two would you please back me up.."
Evil: "Look I'm Zippy Longstockings!"
Scott: "I can't.."
Evil: "..You must zip it! (whip sound) zip it good!"

All I want to discuss is this particular problem. ;-)

The error message, and nearly every reference that I've ever found relating to it on the net, has suggested that the problem is that someone else "has the file open". That may make sense in a local network environment, but I just knew that couldn't be the case here. It's on a commercial host. And the DB is NOT in a web-accessible path, so it's not a matter of someone else downloading it or opening it over the web. And the client doesn't ever download or upload new copies via FTP. It's ONLY updated on the net.

So it's really just a strange error that occurs once in a while (in my case, it could be monthly). I'm betting that quite a few others have experienced this problem and just not been able to resolve it.

After all that lead up, what's the solution? Well, in my case, when I looked at the code in which the error was occurring, I noticed something that just had never caught my eye before. It was doing a CFQUERY inserting against one table, and then right after that doing a CFTRANSACTION with another insert (into a different table but the same datasource) followed by another query doing a SELECT MAX to get the autonumber key that was just created. (Again, let's not debate the veracity and wisdom of using autonumber primary keys, but I will note that there are better ways to get the last inserted primary key, even in Access. See my previous blog entry on this.). FWIW, that CFTRANSACTION was using an ISOLATION value of READ_COMMITTED.

I don't know why it was doing the first query outside the CFTRANSACTION, but it struck me that there may be a conflict where the first CFQUERY might still be being processed when the CFTRANSACTION tried to obtain its needed lock on the DB. That would maybe cause this error.

(Indeed, one might wonder if another scenario could cause the problem. Imagine a template simply doing a CFTRANSACTION (perhaps again important that it's doing a READ_COMMITTED), which simply happens to try to run at the same time that another CFQUERY against that datasource ran at the same time, on another thread. It may be that what we're seeing here is simply Access's version of a lock pending timeout. I mean, the error was occurring on the INSERT inside the transaction (the first query inside it), so while we might expect any such DB lock timeout to happen on the CFTRANSACTION itself, maybe the way CF passes the ODBC SQL commands to the Jet Engine, the error can't be reported on the CFTRANSACTION and therefore it happens on the CFQUERY.)

Anyone have thoughts? Does this sound useful? I've changed the code in this one template to see if the problem will never show up again. I'm not in a position to run some load testing right now so I can't see if I could recreate and prove if this solves it. If anyone can, please do report your findings.