Access Database - Table/Record Locking with ADO
We have an MS-Access database that is accessed through the Jet 4.0 Engine and VB/ADO. There is a transaction table in the database (it is the only table in the database.. don't ask) It can be hit against from any one of 5 - 10 computer operators at the same time. These operators may add or delete transactions from the table. These transactions then get posted to the appropriate accounts at a time of the computer operators choosing.
At any rate, when two or more operators attempt to access the table at once the whole thing comes to a halt and VB throws an error. (Well the program doesn't stop due to error handling, but only the transaction of the first operator to get to the database is recorded and sometimes even stranger things happen...)
So what we did was, open the database/table adLockReadOnly and then catch the ADO error (which says cannot connect because the file is locked) and then we spin attempting to connect to the database again and again for a certain number of tries, if after this number of tries has expired we display a message to the user that they need to try submitting the transaction again and/or their network connection is down.
This whole process seems to work ok. It gets the job done, but it seems rather sloppy. We have tried numerous combinations of the connection flags and some don't even seem to be implemented.
I know Access is a file-shared database and I have read the MS Document on Jet Engine Record Locking, but that doesn't really help me figure out a better scheme of doing this. We have to use Access for this application as a server database is out of the question, not even MSDE.
So my question is, has anyone successfully implemented a clean table/record locking scheme using ADO or DAO and Access? If so how did you do it and could you point me in the right direction?
Saturday, May 31, 2003
Jet (at least, the last time I looked) doesn't lock at the record level - it locks at the 2K page level. So adjacent records are likely to "interfere" with one another in this way. So try padding the record out with a fixed length field so that the record is >1K -- therefore Jet won't store two records on the same page.
Sunday, June 1, 2003
You set record level locking among the start up options.
To the best of my knowledge this came in with Access 2000.
Sunday, June 1, 2003
>two or more operators attempt to access the table at once the whole thing comes to a halt
It seems to me that the above problem was not fully addressed. Your solution to lock up the whole table and prevent multiple users seems to be a bit strong of a fix. Some additional research as to why this was problem might be in order. Perhaps a full table lock was the only reasonable solution, but I don’t know. Perhaps you threw in the towel too soon here?
While it is true that locking the whole table as you do now is a possible solution, I don’t think that is the best way. Also, that “loop” that spins waiting for table lock should have some “wait” put in to reduce network load (you don’t want to swap the network with a very large hit in network activity). I am guessing that your “loop” most likely does have time wait now to “slow” down the requests, but I don't want you to swamp the network.
Having said the above, you can use record locking of JET. Record locking only occurs when you use the DAO “edit”, or ADO modifies the record.
I have rolled my own locking routines in JET. I did this some years ago since before access 2000/jet4 there was no record locking in JET, but only page locking. To avoid problems I wrote my own locking routines.
Funny, but when I stared using Pick systems, we also witnessed the change from page locking to record locking. These changes to the database system no doubt occurred as a result of customer feed back. However, by the time the vendors got around to fixing this problem, we developers had usually found workaround. It seems this always happens to me!
A base functional spec for my JET locking routines was:
** Locking records in the lock table MUST be re-cycled. A weak spot in JET is temp tables, and temp records are not given back to the pool of space until a compact. So, any designs must make real special efforts to avoid adding, then deleting records. Avoiding add and deletes means you reduce maintenance. The result is no file bloat, and the need to compact the file is reduced. The design must recycle the locking records. I can run some applications for 1 year or more without my locking scheme adding bloat. A general high volume of users editing existing data WILL NOT cause a file increase in size do to locking records in the lock table. Users MUST be free to edit lots of existing data, and no file bloat shall occur due to the locking scheme used.
** I wanted only one locking table to manage all table locks in the application. The design should allow multiple users to work with multiple tables locked. The code and the lock table should work for all tables in the application.
** By abstracting the locking code to a set of routines, then I can change the back end file from JET to sql server to mysql, and the rest of the application works. This was a unexpected bonus.
** Since no real locks are ever put on the data tables, then any process is free to read records, and never actually hit a wait state to read/edit data. This can increase multi-user performance in a Jet environment by a good deal since none of the data tables actually have pessimistic locks on them! (pessimistic means that a "edit" will casue a trapable error).
** Often, a user needs a prompt that a particular record is locked. The locking routines should include a standard “in use by such a user’ dialog box. All code shall be written and designed such that the user can ALWAYS safely bail and exit a wait for a lock prompt.
** Since software can hang, or freeze, then a user ability to clear the locks file has to be exposed to the user.
** Eventually, the locking routines MUST rely on some code that will prevent two users from grabbing the same resource at the same time. We don’t want have to deal with some special “system” time issues were two people can get the same resource at the same time. Hence, we will accomplish this by using key violation errors in JET. We will use ability of JET to prevent two users from adding the same unique key value, since the engineers who wrote JET had to come up some way of preventing this in a multi user environment. We will thus hang off the coattail’s of this primary key fact. Hence, we don’t ever use a table lock, but only a key violation. This again reduces the “contention” and thus a table is NEVER locked out from a process to read it.
** I am using ms-access, so the locking routines MUST be able to integrate into the forms design that ms-access uses with MINIMAL code. If the user tries to load a resource in use, they get a wait prompt. If that prompt is canceled, then the form load DOES NOT occur. Thus, I add code to the on-open, and on-close event
** Since I am using JET 3.x, then by writing my own locking routines, I never suffer a page lock problem. Page locks can be problem when small detail records are being adding by more then one user. This is was perhaps my #1 reason for writing the locking routines.
** There is no provision in the locking routines to lock a whole table, but one can certainly lock a process (semaphore).
So, the above points is a very basic functional spec for my criteria for record locking. And yes, I had set the above criteria BEFORE one line of code was written.
Further, the application was then designed with the above in mind. I can only stress that coming up with HOW your locking strategy is going to work BEFORE the application is designed will result in a much better software application.
So, the base idea here is a key violation in a lock table. The lock table design is simply:
LockItem NetWorkLogon ComputerName
LockItem is our primary key (text field). NetworkLogon is the windows logon name. Computer name is the actual work station name.
Notice how we don’t have a “table” name, and key id. I decided to forgo having two fields (one for table name, and then keyid).
Why did I not use two fields here?
Well, one field for the key request is far more simple. I don’t have to try and add two values (table name, and keyid) at the same time.
Also, since the “value” I can add to the table is anything I want, then the locking scheme is very good for semaphore type stuff (ie: you can use it for process locks real easy also). So, a simple routine to key lock a string value is all I have here. Kiss is the word!
To get a request a record lock, I use:
LngId = key id of reocrd
If askforLock(“tblBook*” & lngId) = True then
‘ you got the record lock
Notice how you the programmer has to make up the key used for the request. In the above, we simply use the table name followed by asterisk followed by the keyid. This was a coding standard adopted throughout the application.
So, you can also use the above for a process lock (semaphore). Hence, lets just make up a routine called “MainUpdate”, and we only want ONE user to be able to run that update. We simply go:
If askforLock(“MainUpdate”) = True then
.... code goes here to updat....
' now unlock...
I will not bore you with the actual code in askforLock, but it grabs the network logon, computer logon, and then attempts to add the above key. If one can add the key, then you got the lock.
The actual lock code eventually resolves gets to the following DAO code:
tblLockRecords!lockitem = strKeyLoc
tblLockRecords!NetWorklogon = UserLogonName
tblLockRecords!ComputerName = UserMachineName
mylock = True 'lock is successful
If a key violation occurs, then the “update” above will fail, and the on error goto does:
' this error assumes that the key was already in the table, and thus the lock
mylock = False
tblLockRecords.Requery ' we are still in a edit state, Requery will flush/release index lock(s)
That in nut shell my design for a locking. It works very well.
Now that we have record locking in JET, you kind of don't need the above, but I still use above since I don't have to figure out a new way to do somthing...
Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal
Monday, June 2, 2003
Thanks for the very thorough reply Albert. You are correct there is a wait state in the loop. We are using ADO from VB6 and it seems to work fine.
Your scheme seems pretty nifty also and is cleaner, which is what I am looking for. I think I need to find a good book on using JET with ADO and multiuser issues.
I guess I still don't understand how record level locking would prevent multiple simultaneous table inserts from colliding?
Monday, June 2, 2003
I will assume that the users all have full create and delete rights to the shared folder? Jet needs to create a ldb file, and if users don’t have full rights to the shared dir, then that can be a problem.
Other then that, I can’t see why using record locking would tie up all users.
It could be something silly like creating a reocrdset, and locking the first record of the table.
For the rest, it seems like you have taken care not to swap the network etc. My post was a bit long, but I did want to give my reasons as to why I rolled my own locking stuff.
I don’t really see the need to make your own routines..but it did work well for me.
And, the only “guts” of the code is the above sample “update” that will cause a on-error for the key violation. That is really how it works.
I still don’t think you don’t have to roll your own locking, but the above is a fall back position.
Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal
Monday, June 2, 2003
I found a sample on MSDN that is similar to what we've implemented. It loops (resumes) on the error conditions caused my multiple simultaneous users.
This is exactly what we've done except we used ADO and the error numbers are different.
Monday, June 2, 2003
I have encountered the same problem (it's inevitable in a multi-user environment, I guess). I tried to keep things as simple as possible.
I add an extra field to every table that would require locking. The way I have the UI set up is that the user opens a separate form to edit 1 record (i.e. no editing via Datasheets). Upon opening the "Edit Form" for a particular record, first check if the "IsLocked" field of the table is NULL. If it's NULL, it means no one else has it locked, so UPDATE that field to have the CurrentUser() name and their Machine Name -- need both in case 2 people have logged in with the same name (see AccessWeb for function that returns the Machine Name).
IF, however, when trying to edit a record, the "IsLocked" field is NOT NULL, then you know someone else is currently editing it. AND since the "IsLocked" field contains the aforementioned User/Machine Name, it's easy to tell the user who has it locked.
I found that no bloat is added since, as Albert mentioned in his approach, no new records are INSERTed or DELETEd (just UPDATEd).
This may be too-simplistic a solution, depending on your situation, but I think as long as you're restrincting users to editing one record at a time, you're ok.
Tuesday, June 3, 2003
when we talking about locking, we must remember about 'rush condition'. I think, locking mechanism like John Lev suggestion will have 'rush condition' risk in the time you read (SELECT) and write (UPDATE) the "isLocked " column. We must do this operation is ATOMIC way!
Thursday, April 8, 2004
Locking approaches that use an "islocked" field in the real database fail the "user went to lunch" test, and require giving users a way to request a lock override -- messy.
Better: use a separate table. I call mine "checkout" (based on library book checkout model). It contains fields for the user's Session.SessionID, tablename, table record#, and expiresAt. The latter = Current date/time + Session.Timeout. All references occur thru SessionID, which is invariant for a given current user. Timeout works because if the user hasn't saved by then, they're going to get a session timed out message anyway. (I even have my edit routine put a "save by" countdown timer on the edit screen based on timeout.)
Whenever user wants a lock, the lock function first deletes any records in which expiresAt are < current date/time. That handles "gone to lunch." This scheme also lets various programs in the application (or different applications in a meta-data system) have differing Session.Timeout periods.
Next the lock function searches for tablename/record#. If you don't already have record# in a popup list or some such, read the record as read-only to get the #. If the function finds the table/record # in the table, it issues a "record is in use by <userid>" message. If not, it saves the checkout record for the user's SessionID an returns to the edit screen-building routine.
After the user saves the update, an Unlock function finds the tablename/record# record for the user's SessionID and deletes it.
If you worry about constantly deleting records, and have a small enough user base, you could switch to one-record-per-logonID, instead of SessionID, and not delete the records -- just empty out the tablename /record# fields. This only works if there is an iron-clad guarantee that same account can't be used by more than one person.
Friday, April 9, 2004
We are currently faced with a similair problem basically with ADO/Jet4.
When one user begins a transaction and adds a record to the table, any other user trying to add a record is locked out until the first user ends the transaction or rollsback.
Amazingly, after weeks of trails to figure why this was happening, we discovered that if a table has no index fields, more than one user can add records within a transaction.
We have submitted this to Microsoft support and as of yet it is still being researched.
Hope this helps. If anyone is interested in how to recreate this case let me know and I will post a copy of what was sent to Microsoft
Monday, April 26, 2004
Fog Creek Home