13 Replies Latest reply on Nov 13, 2007 11:48 AM by gerryf

    Microsoft Access database randomly locks itself??!

    happysailingdude Level 1
      Hi there

      Our Access database is doing something weird. Every so often (I think when we've had a busy day in terms of traffic) a .ldb file randomly appears in the directory where our database file resides. Whilst this .ldb file is in existance the CF application can't read (or write) to the database and thus eventually CF times out and returns an error (or the user's browser gives up and displays a 404 error).

      Sometimes this .ldb file dissapears of it's own accord after a second or so, but at other times it doesn't go away again and we have to restart the server.

      - not ideal!

      does anyone know why this occurs?

      I've heard that running a deliberatly bad query (ie selecting from a non-existant table) can 'clear' this .ldb file - in fact we went as so far as to run such a query every 10 mintues as a scheduled task as a preventative measure - and although it 'feels' like this may have helped, we do still get these random .ldb files appearing every now and then and thus we have to restart the server :(

      our site is getting busier (450,000 page impressions last month) so maybe we need to migrate to a new database (mySQL perhaps?)

      however we don't want to go through all the pain of migrating to another database (is that hard to do?) only to find that wasn't the cause of our problem.

      any input would be very gratefully received.

      thank you very much.
        • 1. Re: Microsoft Access database randomly locks itself??!
          jdeline Level 1
          Here's a link to the Microsoft KB that discusses .ldb files. http://support.microsoft.com/kb/208778

          Are you attempting to use a CFLOCK around the CFQUERY?
          • 2. Re: Microsoft Access database randomly locks itself??!
            tclaremont Level 2
            I used to have this problem until someone gave me the following code:

            <!--- This line disables the connection--->
            <cfset cfusion_disable_dbconnections("CURRENT.DSN",1)>

            <!--- This line reenables the connection--->
            <cfset cfusion_disable_dbconnections("CURRENT.DSN",0)>


            Notice that it disables database connections and then re-enables them. This usually prevents you from having to restart the server.

            Make sure to replace the CURRENT.DSN with your DSN.

            If you want to automate this, you might consider working with your error handling routine. When you encounter a particular error, run this code.
            • 3. Re: Microsoft Access database randomly locks itself??!
              jdeline Level 1
              I believe it should be
              <cfset rc =cfusion_disable_dbconnections("CURR ENT.DSN",1)>
              This worked in CF v.5, but I understand it is not supported in MX. Does running a bogus query (SELECT * FROM nonExistantTable) technique still work?
              • 4. Re: Microsoft Access database randomly locks itself??!
                tclaremont Level 2
                I think you might be right about the versioning. The site that I needed this for is STILL on CF5.

                Note that it works as I typed it (I copied and pasted it). I did not need to add rc=

                I readilly admit that it does not appear to be correct syntax.
                • 5. Re: Microsoft Access database randomly locks itself??!
                  happysailingdude Level 1
                  thanks very much for your replies guys.

                  In answer to jdeline's question "Are you attempting to use a CFLOCK around the CFQUERY?" - yes we used to do that alot but i recently removed all of the cflock's (as it turns out they weren't required) in the hope that this might cure the issue, but alas it hasn't we still get the ldb files..

                  We're running CF 7 so it seems that tclaremont's suggestion won't apply to us unfortunately.

                  So I don't think we're any the wiser - if anyone can help I'd be very grateful indeed.

                  thanks very much
                  • 6. Microsoft Access database randomly locks itself??!
                    jdeline Level 1
                    Try this. In Application.cfm, do a SELECT * FROM foo, where foo is a table that does not exist. That will throw an error and may unlock the MDB.

                    Use CFTRY/CFCATCH to catch the error. Then proceed with the remainder of your Application.cfm.

                    This trick was reported to work on earlier versions of MS Access; I can't say whether it is still effective.
                    • 7. Re: Microsoft Access database randomly locks itself??!
                      tclaremont Level 2
                      That solution does not sound overly resource intensive either. It should not slow down the page much since it does not take long to determine that the table does not exist.

                      It is still a workaround to a problem that you have not identified, though. It would be nice to solve the problem instead of putting a band aid on it.
                      • 8. Re: Microsoft Access database randomly locks itself??!
                        happysailingdude Level 1
                        Thanks very much for your replies guys - in the end we just upgraded to MySQL and we haven't had any problems in the 2 weeks since.

                        We didn't change anything else (other than the odd query to ensure the application woked with mysql) so i think it's fair to assume that the Access database was simply 'overloaded'.
                        • 9. Microsoft Access database randomly locks itself??!
                          tclaremont Level 2
                          There are two kinds of Access people. Those that love it and never had a problem, and those that have used a real database and can now recognize how horrible Access is.
                          • 10. Re: Microsoft Access database randomly locks itself??!
                            Level 7
                            tclaremont wrote:
                            > There are two kinds of Access people. Those that love it and never haven't had a problem, and those that have used a real database and can now recognize how horrible Access is.

                            Access is not horrible when used what it is designed for. A small, easy
                            to use desktop database. Of course as a multi-user/network/web based
                            database it is way over it's head, and if one is not careful one will
                            soon be in trouble.

                            • 11. Microsoft Access database randomly locks itself??!
                              gerryf Level 1
                              First, we HAVE seen a Microsoft Access DB locking up using MX7, and as happysailingdude saw, occasionally the problem-lock clears up quickly, and we do not have to reboot the machine (or restart the server).

                              tclaremont noted that a trick involving disabling and then immediately reenabling connections to the offending DB was used to clear up the problem, but that the workaround was only known for CF5 and earlier. With MX7 you use administrative CFCs (part of MX7) to get to system-y things like datasource definitions. An MX7 demo-implementation of the disable/enable trick is shown below. Before running the code, hand-create an Access DB with a single trivial table. The code assumes FORUM_EXAMPLE.mdb is the name of the DB file, and it's assumed to be in D:\ForumExample.

                              <!--- ForumExample-ClearDBLock.cfm, using CF MX7 --->
                              <!--- The first time through here the datasource with name FORUM_EXAMPLE is created. Use the CF Administrator to
                              verify that the DS got created, and use Windows Explorer to verify that a lockfile (.ldb) exists.

                              On the second time through, the "disable connections" flag is toggled, resulting in the disappearance of the
                              lockfile.

                              To run the test multiple times, manually delete the datasource in the CF Administrator. --->
                              <cftry>
                              <cfset MyDSName = "FORUM_EXAMPLE"> <!--- name of DB and DS name for this example --->
                              <cfset MyDBName = 'D:\ForumExample\' & MyDSName & '.mdb'>

                              <cfset admin_obj = CreateObject ("component", "cfide.adminapi.administrator")>
                              <cfset admin_obj.login ("cfadminpw")> <!--- log in as CF administrator --->
                              <cfset ds_obj = CreateObject ("component", "cfide.adminapi.datasource")>
                              <cfset ssources = ds_obj.getDatasources ()> <!--- get current list of datasources --->
                              <cfdump var="#ssources#"> <!--- list existing datasources --->

                              <cfset s = StructNew ()>
                              <cfset s.name = MyDSName>
                              <cfset s.databasefile = MyDBName>
                              <cfif NOT StructKeyExists (ssources, MyDSName)>
                              <cfset ds_obj.setMSAccess (argumentCollection=s)>
                              <cfset success = ds_obj.verifyDSN (MyDSName)>
                              <cfif success>
                              <cfoutput>INFO: Successfully created datasource #MyDSName# for DB #MyDBName#.<br></cfoutput>
                              <cfoutput>LEFT TO DO: <br /> 1. Verify DS is listed in CF Administrator<br /> 2. Verify existence of lockfile<br /> 3. Run this page again<br></cfoutput>
                              <cfelse>
                              <cfoutput>ERROR: Unable to create datasource #MyDSName# for DB #MyDBName#.<br></cfoutput>
                              <cfthrow type="EarlyExit">
                              </cfif>
                              <cfelse>
                              <cfset s.disable = True> <!--- get rid of the lockfile by toggling "disable" property --->
                              <cfset ds_obj.setMSAccess (argumentCollection=s)>
                              <cfset s.disable = False>
                              <cfset ds_obj.setMSAccess (argumentCollection=s)>
                              <cfoutput>LEFT TO DO: Verify that lockfile has been deleted for datasource #MyDSName#.<br></cfoutput>
                              </cfif>
                              <cfcatch type="EarlyExit">
                              </cfcatch>
                              </cftry>
                              • 12. Microsoft Access database randomly locks itself??!
                                SafariTECH Level 1
                                FYI: Access should never be used for a production site - it is a desktop database, not a server database. Even Adobe documents that you should not. it has nothing to do with people like it or hate it ... it has to do with the fact that it is being used in a capcity that it was never designed for.

                                LDB files are lock files to keep the DB from being accessed while an action is taking place on the file. You can only have 1 concurrent write request to an Access database and the rest are queued. If the requests all run through before the queue times out you will be fine, but it may seem slow and if you get a lot of traffic your site will keep timing out.

                                The LDB file comes and goes as people are using the database. If you manually interrupt a connection, then you will be cutting off someone accessing the DB and potentially causing lost data to you or the user.

                                The only way you should ever interrupt the database is by closing the website while you need access to the DB so people will simply finish their activity and then will not be able to start anything else. If our site is busy, manually killing an LDB will likely be replaced by a new one very quickly when the next queued request comes through.

                                If you want to avoid these types of issues then you need to use an enterprise level DBMS and not a desktop application.

                                • 13. Microsoft Access database randomly locks itself??!
                                  gerryf Level 1
                                  On 11/8 SafariTECH responded: "Access should never be used for a production site - it is a desktop database, not a server database." It IS a "desktop" database and NOT a "server" database, but that doesn't disqualify it from being used for a production site.

                                  At our company we have extensive (multiple-year) experience with Access on the back-end, and if you take the time to understand its limitations and account for them in your code, it can be used successfully. When we choose between Access or SQL Server on the back-end, it is based on expected traffic, ease of maintenance, footprint, and so on. In most cases we DO end up using SQL Server, but there are circumstances where Access -- even as a "desktop" database -- has proved a better choice.

                                  In a recent system we built, access to the web server and ColdFusion had to be extremely limited upon system deployment. No VPN, no modem, and a special arrangement with a third party to gain physical access. To minimize the system footprint while designing for completely-hands-off, 24x7 operation, we needed a pair of servers with shadowing and automatic failover if the primary system died. If we had chosen SQL Server, it would have required its own server box, and we would have had to double these up as well. This costs more money and adds yet more complexity.

                                  Given what we knew would be the traffic through the dedicated, private site with a handful of users at any one time, we decided that a server-class DB was unnecessary, and in any case, would force us to add hardware that itself could be a source of system failure. This was the deciding point in favor of Access.

                                  All aspects of the system that directly relate to Access have been running successfully, except the issue reported initially by happysailingguide. We experienced the same issue, which appears to be a ColdFusion-induced error, and not something inherent to Access or the fact that it's a desktop database. (Based on what evidence? We use Access in many automated telephone applications which are written in Perl instead of ColdFusion, and this problem has NEVER been seen in the 5+ years of production usage.) In our case it appears there are NO requests queued to Access at the time of the lockup, and the lock can last forever unless we take programmatic intervention.

                                  As anybody who has built and deployed many production sites knows, you must simply accept the fact that no hardware or software is error-free (yours or anybody else's). The described Access lockup is just one of these errors (and is probably pretty low on Adobe's list of things to fix). With a different DB there wouldn't be the same problem, but there would be another to take its place, perhaps increased system failures because of the increased complexity of the system, or the downside of greatly increased costs when replicating the system.

                                  When I was trying to decide on the Access vs SQL Server question a couple years ago, I read the forums and web discussions about how Access should never be used for a production site. There were many scary statements about Access that nearly prevented me from using it. But at the advice of a colleague who has used Access for several years in a telephony app with 100 or more simultaneous callers, I stared a little more at the timestamps on the statements, and found they were from a long time ago (in computer-years). Microsoft has made MANY improvements to Access over time. For anybody facing the same decision, you will find it worthwhile to consider Access if cost, complexity, and footprint are important factors.