Lock Database in ADO

Question:

Has anyone done proper locking in a shared access database using ADO? What I am planning on doing is having a database with 2 records, only 2 fields.
one having:
SYSTYPE="Notebook" and a number
the other having
SYSTYPE="Workstation" and a number.
When a system pulls data from this table and will query on, I need to have it get the current number into a variable and increment the number by one. My concern is that I may have several systems requesting the information simultaneously and get the same number. As I understand the ADO locking, the system does not lock the record until an update happens, by which time another system may have read the number. Have been searching Google and some books but do not find a comforting example.

Any help would be appreciated.

Answer:

If you are going to rely on ADO, I would carefully read about both the resync() and requery() methods. If the transactions against the table require that each client grab a number then update that number for the next client - I would use semaphore locking, old but reliable. Simply add a logical field ( isbusy or islocked ). Then assume you have 3 clients going to hit the database within seconds of each other.

Client A gets there first, then B then C


tries = 0
numtries = 5
:update
RS.MoveFirst()
If ! RS.Fields("isBusy") Then RS.Fields("IsBusy").Value = 1
then depending upon if optimistic locking
RS.Update()

then proceeds to get the number and update the value, reset the semaphore to 0 and RS.Update().... ah, but along comes B and his code

If ! RS.Fields("isBusy") Then RS.Fields("IsBusy").Value = 1

proceeds to.....

ELSE

display(1,"Record Locked","Retrying...")

If tries < numtries
tries=tries+1
goto update
Else
goto some failure code
Endif

ENDIF

:rest of routine

literally B or C shouldn't even have to wait a second

Article ID:   W16569

File Created: 2005:02:18:12:21:30

Last Updated: 2005:02:18:12:21:30

Database Search

TechHome