Skip to main content
W
weezerboy
Inspiring
August 21, 2009
Answered

Access DB heavy usage problem

  • August 21, 2009
  • 4 replies
  • 3142 views

I have a website that people vote for the coolest helmets here

http://www.vhsl-reference.com/helmet_1.cfm

When they click the vote button at the above page they go to a 2nd page that does an insert into an access db and then redirects them back to the voting page to vote again.

Its easy for users to vote numerous times quickly and during the day I have gotten up to 10,000 + votes and I know I will get more.

The problem is when there is heavy usage(voting) the access db locks up, the site becomes unavailable, and usually I get the dreaded .ldb file and then I need to call my host and get them to reboot the server.

My question is, is there any code that I can put around my INSERT statement code that will make the database to wait(hesitate or hold)  until one INSERT is done before doing another?

OR

Is there any other strategy to deal with this issue. I have thought of inserting into multiple data tables or databases based on the time of the vote to allay the problem.

But ideally I would like to somehow code the insert page to hesitate until the database is not in use.

CFLOCK maybe??

This topic has been closed for replies.
Correct answer BKBK

A named lock would indeed do what you want, something like

<cflock name="insertQuery" timeout="5" throwOnTimeout="no" type="exclusive">

<cfquery>

<!--- insert --->

<cfquery>

</cflock>

The attributes timeout(in seconds) and throwOnTimeout determine how Coldfusion manages time in the lock. I would set throwOnTimeout to no. That prevents Coldfusion from generating an exception and stopping when a request exceeds the time out. Execution simply continues.

4 replies

A
Adam Cameron.
Inspiring
August 26, 2009

Its easy for users to vote numerous times quickly and during the day I have gotten up to 10,000 + votes and I know I will get more.at all

This is not an issue that should be addressed with code; it should be addressed by using the right tool for the job.  And MS Access is not the right tool for the job here.  You should migrate your DB to SQL Server Express or something like that.  MS Access is a desktop application, and should not be used in a server-based, multi-user environment.

I'm not just being glib here: this is not something you should be attempting to remedy with code changes.

--

Adam

tclaremont
tclaremont
Inspiring
August 31, 2009

While I agree that Access is a very poor choice here, I have had situations where I had no other choice. With the dreaded lock error I could unlock the table without the need for a db server reboot, or a call to the webhost.

It has been a while, but I believe I recall the solution as intentionally running an impossible query agains the database. In other words, SELECT * from tblFoobar when you KNOW that tblFoobar does not exist. For some reason this unlocks the database.

So, if this issue cannot be resolved any other way, you might try running the impossible query at the end of EVERY database update page. Of course, if you really want to pursue this, you will need error handling in place so the end user does not get an error message associated with the dud query.

If I am misremembering this, I am sure another poster will chime in.

ALSO, run a compact and repair process on your database from time to time. This should reduce the amount of time that the database is "open" duing an update, and therefore reduce the liklihood of multiple attempts at the same time.

A
Adam Cameron.
Inspiring
August 31, 2009

While I agree that Access is a very poor choice here, I have had situations where I had no other choice.

Bear in mind that when one encounters these "no other choices" situations these days, CF ships with Derby embedded.  Derby is a better solution than MS Access.

I have to say I have never been in a situation in which I have had "no other choice" in my eight years of doing CF.  That's not to say that other work patterns might lend themselves to these situations more often, but I think almost always there is a choice.  And any choice other than Access is the correct one.

--

Adam

T
TLC-IT
Inspiring
August 25, 2009

You are definitely over-exerting Microsoft Access's design limits.

However, the <cflock> strategy "just might be a work-around" because it will cause ColdFusion to serialize that block of code:  no matter how many servers you've got, the actions will take place one at a time.

Within that block, you probably also want to start a database transaction against the Access database.  This should make it clear to MS-Access that the operations must be fully committed to disk before processing continues.

This might "slow down" the posting operations but it should restore reliability.  And I don't think it will slow things down much.

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
August 25, 2009

A named lock would indeed do what you want, something like

<cflock name="insertQuery" timeout="5" throwOnTimeout="no" type="exclusive">

<cfquery>

<!--- insert --->

<cfquery>

</cflock>

The attributes timeout(in seconds) and throwOnTimeout determine how Coldfusion manages time in the lock. I would set throwOnTimeout to no. That prevents Coldfusion from generating an exception and stopping when a request exceeds the time out. Execution simply continues.

D
Dan_Bracuk
Inspiring
August 22, 2009

You are trying to use MSAccess for something it wasn't designed for.  Use something better.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices