Access DB and CFLOCK

[Kendo]

Hi all,

I'm using Access on what will potentially be a very busy site. I have previously had problems with using Access on a busy site and the DB locking up.

I've looked all over and I'm yet to see any discussion of this, even though a lot of people know of this common Access problem.

Am I able to use CFLOCK around the CFQUERYs in order to get around this problem? What happens if someone has requested a CFM page, it's currently in the CFLOCK, and someone else requests the same page? Does CF queue the request from the second user?

I appreciate that using CFLOCK in this manner may slow the site down considerably, but it'll be worth it if Access can keep up with the site.

Many thanks!

 

[MorganGreylock]

Kendo,

On a side note:
If you are anticipating a lot of traffic on your site, you
might want to reconsider using Access. Access was designed
as more of an end-user personal DB system, not a scalable
enterprise-ish system. There is a relative upper limit on the number of records you have (I've had DBs crash after 20k
records before), and it doesn't do very well at keeping its
size down unless you do regular compacts (which can be
time consuming). Basically, its not designed to be a large
scale database.

Depending on your OS and webserver, I would suggest SQL
Server. I'm not particularly fond of MS, but at least SQL
Server can be scaled.

BUT, If you continue to use access, I'll say this about CFLOCK: It is mainly used for application variables. Application variables are stored in the server's memory, and using CFLOCK prevents two different requests from accessing or modifying a single variable at the same time, which can cause memory corruption (and eventually other problems if left unchecked) CFLOCK forces the server into single-threaded mode, and every time you do that it has a negative impact on performance (which again is not Access's strong point in the first place)

But, to answer your question, yes, it does queue the second request, although there is a timeout. You can set the THROWONTIMEOUT attribute as to whether or not an exception will be thrown if the timeout expires.

I hope that helps, and wasn't too verbose
MG

 

[Kendo]

Thank you for your response, MG.

As with all ColdFusion programmers who are stuck using Access, it's always frustrating. But that's the problem - I don't have the budget to go for anything else.

Thanks for your answer on the queueing front...at least I know that if I go for that option I shouldn't find the DB locking up again.

Cheers!

 

[SidianConsulting]

You might want to check out MSDE and see if it might be a good choice in your situation:

http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

-Tek

 

[Kendo]

Yeah - I'd love to have MSDE or SQL Server. Or MySQL.

But really, my only option is Access.

Thanks!