Trapping "Locked" error

JetScootr
JetScootr used Ask the Experts™
on
I using an Access table from VB 6.  I need to be able to support 6-8 users in the database at the same time.  (Note - the users will be using my app, NOT Access)  Problem - when running either SQL or DAO updates, I'm getting "Cannot update - locked by user Admin on machine BOX123".  The conflict is actually happening - two (or more) users are in same tables, but are updating different records and this happens.  I need a code workaround, options to set, etc to handle this gracefully and make sure the updates get into the database anyway.  Microsoft is really short on "theory of operation" docs, which is what this would fall under.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Switch to ADO, DAO is outdated. That will help get you started in the right direction.

Commented:
Not sure about DAO, but check your locking option on your recordset open call.  

If you don't have optimistic locking, you may get what you have described.

Commented:
DAO is still far faster for MDB tables, but I would agree with switching to ADO.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Certainly use ADO without question.  I can give you some code on this if you need it.

When using ADO and you open the recordset, try different combinations of table locking. Some of these will prevent other users from updating the table while another has the table locked.

Author

Commented:
Problem is that multiple users are actually simulatneously updating the same page, though not the same record.  What I'm saying is that it's not a coding error, or even an error in the technology.  This is a factor of the environment that the application must work with - that more than one user will be in the database at once.  It's not a big demand - a probable maximum of 4-8, no more, at once.  
Ultimately, I need a solution that allows the updates to happen in an orderly manner, rather than being discarded.  I guess I need a coding technique that goes something like this:

         ' Set option so that we can handle
         ' multi-user conflicts:
         rsMyTableOrUpdatableQuery.OptionX = ValueX

         rsMyTableOrUpdatableQuery.Edit
         '     (or .AddNew, or .Execute SQL)
         rsMyTableOrUpdatableQuery("FieldX") = etc....

         On Error Goto MagicConflictHandler
         rsMyTableOrUpdatableQuery.Update


MagicConflictHandler:
        ' What goes here?


if ADO automatically handles this, with conflict resolution, retries, timeouts, whatever, that would be great.  Speed alone will reduce the frequency but not eliminate the problem.
I'm experimenting with .LockEdits option.  The problem still occurs.

Thanx for the discussion, though...please keep it up:
edwinson:  What are ADO's combinations of table locking that might help this, and how would I use them?  I have little hope that MS's Docs will clearly explain.

Author

Commented:
Problem is that multiple users are actually simulatneously updating the same page, though not the same record.  What I'm saying is that it's not a coding error, or even an error in the technology.  This is a factor of the environment that the application must work with - that more than one user will be in the database at once.  It's not a big demand - a probable maximum of 4-8, no more, at once.  
Ultimately, I need a solution that allows the updates to happen in an orderly manner, rather than being discarded.  I guess I need a coding technique that goes something like this:

         ' Set option so that we can handle
         ' multi-user conflicts:
         rsMyTableOrUpdatableQuery.OptionX = ValueX

         rsMyTableOrUpdatableQuery.Edit
         '     (or .AddNew, or .Execute SQL)
         rsMyTableOrUpdatableQuery("FieldX") = etc....

         On Error Goto MagicConflictHandler
         rsMyTableOrUpdatableQuery.Update


MagicConflictHandler:
        ' What goes here?


if ADO automatically handles this, with conflict resolution, retries, timeouts, whatever, that would be great.  Speed alone will reduce the frequency but not eliminate the problem.
I'm experimenting with .LockEdits option.  The problem still occurs.

Thanx for the discussion, though...please keep it up:
edwinson:  What are ADO's combinations of table locking that might help this, and how would I use them?  I have little hope that MS's Docs will clearly explain.

Top Expert 2012

Commented:
As you have discovered locking in Access is done at a page level and so for the most part is useless.  Many years ago, I wrote an app that updated the row with the name of the user editing it and removed the name after updating.  This allowed you to say something like "User A is updating the row, are you sure you want to update?"  The one fly in the ointment with this approach is rows orphaned, but this can be remedied to some extent by clearing the users name on opening the app. This solution worked surprisingly well, although I have never had to use it again, there are better ways of skinning this cat.  

Anthony

Commented:
Interesting...

Author

Commented:
acperkins; I am considering something along the lines of yer suggestion.  Set up a table called something like "AccessSux" with these columns:
Definition:   Tablename   UserName   RW     When    Expires
Sample data:  Journal     Scootr     Read   10:14:35   45
              SerialNums  Freddy     Write  12:34:56   15
etc

Have VB code query this table first for permission to update the high-traffic tables.  Write into "AccessSux" what/who's updating and then perform update to high-traffic tables.  Fields When and Expires are the time, and count of seconds, that an update is expected to occur.  If an update takes longer, expire and delete the record, assuming the user's box has puked or left the record hanging for some other reason.
Problem then moves from high-traffic tables to table "AccessSux".  Oh well.
Since I can't actually see a page identifier, I can't write my own "page update scheduler" to get around this.  
Still wondering what to do....
Dirk HaestProject manager

Commented:
Have you already tried the different lockings ?
recordset.open sql,connection, adopenkeyset,adlockoptimistic




Open Method (ADO Recordset)
     

Opens a cursor.

Syntax

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Parameters

Source   Optional. A Variant that evaluates to a valid Command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a persisted Recordset.

ActiveConnection   Optional. Either a Variant that evaluates to a valid Connection object variable name, or a String containing ConnectionString parameters.

CursorType   Optional. A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. Can be one of the following constants (see the CursorType property for definitions of these settings).

Constant Description
adOpenForwardOnly (Default) Opens a forward-onlytype cursor.
adOpenKeyset Opens a keyset-type cursor.
adOpenDynamic Opens a dynamic-type cursor.
adOpenStatic Opens a static-type cursor.


LockType   Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Can be one of the following constants (see the LockType property for more information).

Constant Description
adLockReadOnly (Default) Read-onlyyou cannot alter the data.
adLockPessimistic Pessimistic locking, record by recordthe provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic Optimistic locking, record by recordthe provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic Optimistic batch updatesrequired for batch update mode as opposed to immediate update mode.


Options   Optional. A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved. Can be one of the following constants (see the CommandType property for a more detailed explanation of the first five constants in this list).

Constant Description
adCmdText Indicates that the provider should evaluate Source as a textual definition of a command.
adCmdTable Indicates that ADO should generate an SQL query to return all rows from the table named in Source.
adCmdTableDirect Indicates that the provider should return all rows from the table named in Source.  
adCmdStoredProc Indicates that the provider should evaluate Source as a stored procedure.
adCmdUnknown Indicates that the type of command in the Source argument is not known.
adCmdFile Indicates that the persisted (saved) Recordset should be restored from the file named in Source.  
adAsyncExecute Indicates that the Source should be executed asynchronously.
adAsyncFetch Indicates that after the initial quantity specified in the Initial Fetch Size property is fetched, any remaining rows should be fetched asynchronously. If a row is required that has not been fetched, the main thread is blocked until the requested row becomes available.  
adAsyncFetchNonBlocking Indicates that the main thread never blocks while fetching. If the requested row has not been fetched, the current row automatically moves to the end of the file.


Remarks

Using the Open method on a Recordset object opens a cursor that represents records from a base table, the results of a query, or a previously saved Recordset.

Use the optional Source argument to specify a data source using one of the following: a Command object variable, an SQL statement, a stored procedure, a table name, or a complete file path name. If Source is a file path name, it can be a full path ("c:\dir\file.rst"), a relative path ("..\file.rst"), or a URL ("http://files/file.rst").

The ActiveConnection argument corresponds to the ActiveConnection property and specifies in which connection to open the Recordset object. If you pass a connection definition for this argument, ADO opens a new connection using the specified parameters. You can change the value of this property after opening the Recordset to send updates to another provider. Or, you can set this property to Nothing (in Microsoft Visual Basic) to disconnect the Recordset from any provider.

For the other arguments that correspond directly to properties of a Recordset object (Source, CursorType, and LockType), the relationship of the arguments to the properties is as follows:

The property is read/write before the Recordset object is opened.


The property settings are used unless you pass the corresponding arguments when executing the Open method. If you pass an argument, it overrides the corresponding property setting, and the property setting is updated with the argument value.


After you open the Recordset object, these properties become read-only.
Note   For Recordset objects whose Source property is set to a valid Command object, the ActiveConnection property is read-only, even if the Recordset object isn't open.

If you pass a Command object in the Source argument and also pass an ActiveConnection argument, an error occurs. The ActiveConnection property of the Command object must already be set to a valid Connection object or connection string.

If you pass something other than a Command object in the Source argument, you can use the Options argument to optimize evaluation of the Source argument. If the Options argument is not defined, you may experience diminished performance because ADO must make calls to the provider to determine if the argument is an SQL statement, a stored procedure, or a table name. If you know what Source type you're using, setting the Options argument instructs ADO to jump directly to the relevant code. If the Options argument does not match the Source type, an error occurs.

The default for the Options argument is adCmdFile if no connection is associated with the recordset. This will typically be the case for persisted Recordset objects.

If the data source returns no records, the provider sets both the BOF and EOF properties to True, and the current record position is undefined. You can still add new data to this empty Recordset object if the cursor type allows it.

When you have concluded your operations over an open Recordset object, use the Close method to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, set the object variable to Nothing.

Call Open with no operands, and before the ActiveConnection property is set, to create an instance of a Recordset created by appending fields to the Recordset Fields collection.

If you have set the CursorLocation property to adUseClient, you can retrieve rows asynchronously in one of two ways. The recommended method is to set Options to adAsyncFetch. Alternatively, you can use the Asynchronous Rowset Processing dynamic property in the Properties collection, but related retrieved events can be lost if you do not set the Options parameter to adAsyncFetch.

Note   Background fetching in the MSRemote provider is supported only through the Open method's Options parameter.

Author

Commented:
Dhaest:  I'm using DAO, not ADO.  App was written 4 years ago, is 45,000 lines, and boss is in hurry (1-2 months) to get it running multi-user.  MS docs are unhelpful (no need to cut&paste - we all got MSDN!).  How many of us could build a house correctly if we were given all of the power tools and instructions like "plug it in, pull the trigger, and point the spinning part at the wood"?
I'm hoping to find real-world experience with multi-user database that is NOT client-server (i.e., no ODBC connections involved).  The mdb file is on a file share on the server, and desktop apps read it.
I've experimented with dbPessimistic and dbOptimistic, was hoping for experienced explanation of actual operational impact of using each - or maybe something else entirely is what I need.  dbPessimistic seems to throw the "locked" error less often, but doesn't eliminate the problem.

VK

Commented:
Hello JetScootr !

Suggestion:

1. Create an Activex-Exe (OLE Automation Server). This app is only allowed to access the database.

2. All other apps who need access to the databse are sending their requests to the OLE Automation Server which are queued there and receiving their results from it.

3. But don't send the whole data via interface. Just return a readonly referece to that objects properties.

v.k.

Author

Commented:
Unfortunately, switching to true client-server (like an OLE automation server) requires a re-design of a 45000 line application.  Not something I can do before Valentine's day (the approximate deadline).
Or I think it does.
Is there an easy way to convert to ODBC connections from the classical "OpenDatabase","OpenTable", kind of app?

Commented:
JetScootr,

I certainly understand your situation. I first started Access programming using DAO and thought ADO was a beast that I did't want to have anything to do with. DAO really was not designed to be multi-user. ADO was developed because of the need to multi-user component and web environments.  I have an application now that runs in ADO with multiple users and very very rarely have any issues with multiple updates.

Now, ADO is without a doubt the best route.  ODBC connections are a piece of cake, and are so well documented that one site in particular will give you connection strings for nearly any combination you might need.

You also substantially improve the performance of your application.  4 years ago the best desktops were PII 350mhz with 64MB ram and at best 20GB drives.  Today a system like this is used as an anchor for my boat.

The reason you won't find too much to help your DAO problem is simply due to age and the advance of technology. I sincerely don't know if you will find a solution that will work consistently without making the change.  

You may just end up spending time over the next 1-2 months looking for a problem, then simply going back in again to make the conversion.

Author

Commented:
edwinson: Do you mean changing the app just enough to cover the syntax changes between DAO and ADO, or do you mean switching to ADO -and- set up ODBC connections?
If the latter, and if it's really simple enough, I could probably swing it with the boss.
but if setting up ODBC changes requires really significant changes, we'd probably not get a go-ahead.
Top Expert 2012

Commented:
Any change in a non-trivial app from DAO to ADO requires or should require a re-write <period>  Any attempt at doing a "literal translation" is doomed to disappointment and failure.  Just be warned.

Anthony
Before re-writing the app try the following:

1) Don't keep any database open.

When you load a record close the database, allow the operator to edit the fields. When they click on Save, reopen database any apply the updates in a transaction loop.

2) Transaction Loops:

open the database here
Started = Now
do

    do
        On Error Resume Next
        Err.Clear
        Workspaces(0).BeginTrans
        InTrans = True ' set a transaction indicator
       
        SQL="Update [xxx] Set .....etc."
        DB.Execute SQL, dbSeechanges
        If Err.Number<>0 Then Exit Do

        Do other updates etc.

        If Err.Number<>0 Then Exit Do
        ' the next statement may fail with record locks
        Workspaces(0).CommitTrans
        If Err.Number<>0 Then Exit Do
        InTrans = False
        Exit Do
    Loop
   
    If Err.Number<>0 Then Exit Do
   
    Workspaces(0).RollBack
    InTrans = False
    Try = Try + 1    
    If Try > 5 Then
       MsgBox "Could Not Save....etc
       Exit Sub
    end if
    DBEngile.Idle dbFreeLocks
    Sleep 3000    ' wait for other users to finish
loop


3) The DBEngine INI settings are important with DAO I will post an example later.

4) Recordsets

You must open as a snapshot unless you realy want to change data.

If you open a file which is not a snapshot and start reading records like this:

Do While Not Rs.Eof
   
   Some processing maybe the odd update
   As you start to read through the records thay can be locked but this depends on lock edits settings
   
   rs.MoveNext
   DoEvents

Loop

It is better to code it like this:

Do While Not Rs.Eof
   WorkSpcaes(0).BeginTrans
       
   Some processing ..... even some updates...

   Workspaces(0).CommitTrans
   
   rs.MoveNext
   DoEvents
Loop

5)  DataControl

If you are using the datacontrol, bad luck.....


Another way of handling updates, which works realy well, is to save all your SQL updates statements into a collection.  Then open class object in an ActiveX EXE located on the server where the data is stored. This will uses DCOM to update the data.

Like This:

Dim Col as New Collection
Dim WithEvents SQLHandle as "MyActivexEXE.MyClass"

SQl= "Insert Into fred (sdsddsd..etc"
Col.Add SQL, "1"

Set SQLHandle = CreateObject("MyActivexEXE.MyClass", "MyServer")

RaiseEventOnComplete = True ' or false
OK = SQLHandle.UpdateSQL(Col, RaiseEventOnComplete)

SQLHandle can either raise an event.

The database then thinks that it just has one user, making updates.

Hope this helps :~)
Woops, did not complete sentance:

SQLHandle can either raise an event when it completed, by working in an async. mode, or the update can force you to wait until the data update is done.

The difference is that to work in asyncmode the server queues the data into an array and then sets a timercontrol interval/  The timer then runs the update.  When non in async. mode the MyClass handles the update and returns success.

If you wan't to go this route I will help further.

p.s. it is easy to change your app to use DCOM, you just change:

DB.Execute SQL

into

Set SQLCol = New Collection
SQLCol.Add "Phase A/Options=RC;" + SQL, "Phase A"

Simple. Also only need for just 1 trans loop on the server. Saves loads in coding.  But in practice there some quite complex issues that need a little expansion.





Commented:
I tend to agree with the need for a rewrite, however, often times if the application has been well written to start with, the changes should be relatively easy.

the key phrase though is "well written".


Been there done that.
Here is a response which I gave to a different question.

1) Always open all record sets as read only.

2) Create new records using sql:

SQL = "Insert Into MyTable (Fld1,Fld2) Values (1234,5678):"

3) Update records using sql:

SQL = "Update Mytable Set Fld1=1234 Where (ID=54321);"

4) Always do updates in a tranaction loop which can restart. Similar in either ADO or DAO.

' Example simple transaction loop handling fallback recovery

DO

Do

  Try = Try + 1
 
  On Error Resume Next

  Err.Clear
  CN.BeginTrans
  InTrans=True ' make a note you are in a transaction loop

  ' update table 1
  SQL = "Update xxxx where xxxx"
  CN.Execute SQL
  If Err.Number <> 0 The Exit Do

  ' update table 2
  SQL = "Update xxxx where xxxx"
  CN.Execute SQL
  If Err.Number <> 0 The Exit Do

  ' create some example records
  SQL = "Insert Into xxxxx"
  CN.Execute SQL
  If Err.Number <> 0 The Exit Do

  CN.CommitTrans
  Exit Do
 Loop

 If Err.Number = 0 Then exit Do ' all update ok
 
 CN.RollBackTrans
 
 If Try>5 Then
     MgsBox "Could Not Update Data"
     Exit Do
 End If

 blbStatus.Caption="Waiting for network congestion!"
 doevents
 Sleep 4000 '
Loop  

5) You can open a reordset for update but each time you read a record you will lock it, to avoid this:

Do While Not RS.Eof
  CN.BeginTrans
  if RS("TYPE")="X" Then
     RS.Edit ' this is only for DAO
     RS("TYPE")="Y"
     RS.Update"
  End If
  CN.Committrans ' releases lock on records that were not changed
Loop

6) Avoide keeping databases/connections open for more than a spilt second. (In otherwords dont use the VB intrinsic data control)


Others may have other views :~)

 

Author

Commented:
inthedark: thank you for all the thoughts - your effort is appreciated.
However, I'm trying to find a way, without redesigning into a client-server app, how to gracefully handle the conflicts and get everyone's update posted to the database anyway.  Ideally, I would never tell a user "could not update database" unless the database was actually inaccessible.
Your response seems to require a server app (OLE automation server), which in this case, means a complete redesign of the app.
Just for the record:  This is a business-process management database that also manages software Configuration Management paperwork and products.
about 70 tables, very well normalized.  About 400 SQL queries, about 250 of which are update/insert, the rest are select or various debug queries.  When fully populated, around 200 MB of data.  All queries are snapshots or specifically readonly unless an update is needed.  The app never opens a recordset for update and leaves it open more than needed to perform the update - I suspected this might happen when I designed it 5 years ago.
About 60 defined relationships.  No temporary tables (I build a temporary database on each user's local drive), very very few data controls - only for initial input of some selected low-traffic records. For the most part, the most complex updates are done with SQL, not .Edit/.Update.
almost no bound controls.  The MDB itself contains nothing but tables and queries - we use no Access Forms or Reports.
I'm a "code-bender" from the 80's - I trust my ability to code in VB and SQL, but I've seen too much flaky behavior from controls to implicitly trust them to update the database.  Hence the code-intensive design.  It's actually very stable - except for this multi-user problem.

What I'm probably gonna hafta do is this:
Write a "Try and if needed, wait and retry 10 times" sub for DAO updates (.Edit/.Update).
Write "Table Lockout" logic like I described above for the SQL, since SQL will probably update randomly selected pages throughout the MDB when running.
It's gonna be a lotta code tho...I'll update yall with the basic solution ideas once I get it running...may be a week or two, tho.
Thanx for all the input, folks!
Here's a good site that describes the techniques available to you.  To answer your question: The only way to handle the locking question that I know of is to wait for a short period of time and retry the operation.  You could implement a scheme where you queue these changes in your application, but I suspect this would be a large rewrite.  Alert the user that the condition happend and try using the sleep API to wait for that certain period of time.  Allow the loop to attempt 4 or 5 times consecutively:

Public Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)




http://www.microsoft.com/Accessdev/articles/bapp97/chapters/ba10_4.htm
This is how I did it (and it sounds like one of your last comments:

I have a few functions: LockRecord, UpdateRecord, AddNewRecord. These basically do rs.Edit, rs.Update and rs.AddNew - but they have error traps and timeout loops. The function is something like:

while trys<5
on error resume next
rs.edit
if err=0 then
  LockRecord = true
wait 1 sec
loop
LockRecord = false


And to use it:

if LockRecord(rs) then
  rs!Name="wibble"
  if UpdateRecord(rs) then
    db.committrans
else
  db.RollBack
end


Each set of operations starts with a starttrans and any failing functions rollback and report an error.

Also and this is very important... you must make sure you lock records for the minimum amount of time, never, ever, wait for a user with any records locked.

I have found that using these routine and rules makes Access fairly good for upto 8 users.

A few others words..... I have a multiuser app that was first written with VB4 and has gone through several DAO/Access incarnations. The latest DAO3.5 and above seem unstable and we get some database corruption. Microsoft have effectively told us that Access is not designed for mission critical data and to use the MSDE. Personally I think that sucks...but anyway...

ADO: Do not underestimate the time required to port your DAO code to ADO (like I did). A cursor glance make them look very similar but there are many subtle differences that are hard fought! It took me an order of magnitude longer to port my app to ADO.
So, are you going to close this question out and the other question that you posted, or is this still open?

Commented:
I agree with inthedark. You have to mix Recordset with actual SQL to solve your problem. Instead of intimating the user after 5 times run it as an infinite loop but display a message that it might take a little longer to update due to network traffic. And by the way if you mix Recordset and SQL together there wont be any congestion problem at all becuase all Updates and Inserts would be acrried by Access itself and no recordset would lock the rows or tables as they will be read only recordsets.
Also there is another tool you can use:

Before you start the update open a file with lock read write acess, if the open fails you have to wait.

Furthermore, I place all of my code into a class module, so that in a global you can declare:

Global DAO as New clsDAOFunctions

So in you subroutines within you large app you can get quick access to your update management features. Because the class handles its onw variables it is quick to add to your sub, just paste it in.  In the following code GainAccess opens a file for No Share and issues  DBEngine.Idle dbFreeLocks so make sure you are not holding anu records another users needs. ShowBusy will display a form, if not already on screen, and then Sleep 1000. ReleaseAccess will remove the screen if already on display and release the file lock and also issue a DBEngine.Idle dbFreeLocks.

Example:

Dim Busy As New AccessControl
Do While Not Busy.GainAccess ' open control file
    Busy.ShowBusy ' display a network busy form and wait 1 second
Loop

Now do your update:

Busy.ReleaseAccess ' close form if it had to be open

It will only take you a few seconds to add this to each subroutine. Say 1 minute per sub, this means that you can go and play golf by late afternoon.

Best of luck with the golf   :~)

Author

Commented:
twalgrave: Not gonna close it yet - you guys are really giving me a lotta good stuff...and you've collectively given me an idea that I think will have a chance of success.
When I do close this, I'll give the 483 to whoever gave the most, but I'll give out about 500 more points to others have made significant contributions.  I really appreciate all the input.  I have over 4000 question points right now, I can afford to be generous.  In any case, it appears that the solution will be a synthesis of a buncha people's efforts - so points should be shared.
My idea:
I'm gonna elminate DAO record updates entirely and convert them all (All .Edit, .AddNews) to SQL.  There's 80-100 such updates, but SQL is easy - just a lotta parameters to feed a query.
I can easily add a delay loop and a retry counter.  Plus, SQL is inherently in a transaction, so all updates made by all users should dovetail a little better.  I'm still thinking about multi-table updates (I have a few to deal with), but if nothing else, I can manage that with code to uncreate the "one" if the "many" don't work.

Thanx for API call, twalgrave - I had forgotten about sleep (in more ways than one!) and was gonna put in a call to OSTimeGetTime and a counter loop.  Counter loops are ugly - they lock up the user's PC.  Sleep is much nicer to the user.

inthedark: Cool, I mean kewl, idea for managing my own locking scheme.  There were two problems I was stuck on, and you fixed one.  
One: How to make sure the locked resource becomes unlocked when the user hits CTRL-ALT-DEL (which happened just this last Monday during a "long" 20-second query).  Inthedark's use of external file locking handles this nicely: It isn't stored data in the database - which would also have the conflict problem; and the "lock" disappears if the APP dies or the PC disconnects from the network.
Two: How to prevent multi-user gridlock when each needs a resource the other has locked:  UserA and UserB both need tables "ABC" and "XYZ".  But by chance, UserA locks ABC and UserB locks XYZ - then both sit and wait for the other resource then both fail on retries.

Inthedark's scheme may help here also.  
First: You should use randomly generated timewaits, always, on any retry scheme.  This prevents synchronous retries and randomly lets one or the other "win" first access.
Second: On the third, sixth, ninth retry, unlock everything using inthedark's external file scheme, wait a random bit, then re-attempt to relock everything.
I'll have to keep working on this - but I think it'll cause UserA to win, then UserB wins when UserA is done.

Hi JetScootr

I was interested in your comment that "...Sleep is much nicer to the user...". I am not sure where you get that information since both methods put your program into a "do nothing" loop for the duration.

MSDN: Many programmers use the Sleep API function to pause the execution of their programs. One of the disadvantages of using Sleep is that it pauses the thread that the application is running in, and that any window that the application has open will not repaint properly. This may present an unattractive user interface to the user. An alternative to Sleep is to use SetWaitableTimer, which will allow the screen to repaint, receive DDE messages, and so forth.

Good post btw
EB
JetScootr,
<twalgrave: Not gonna close it yet >

I didn't mean to imply that you should.  I just saw that you had the problem solved and was wondering if you wanted more feedback/ideas.  I see now which it is.

Author

Commented:
ElijahBailey:  Sleep is preferable to a simple counter loop, which hogs the CPU and effectively freezes EVERY thread by what amounts to a CPU DoS attack.
However - what you suggest sounds even better.  I'm using ApiViewer, which shows this:

Declare Function SetWaitableTimer Lib "kernel32.dll" ( _
      ByVal hTimer As Long, _
      ByRef lpDueTime As LARGE_INTEGER, _
      ByVal lPeriod As Long, _
      ByRef pfnCompletionRoutine As PTIMERAPCROUTINE, _
      lpArgToCompletionRoutine As Any, _
      ByVal fResume As Long) As Long

Long Integer I can guess is a VB6.0 "Long", but I have no reference for PTIMERAPCROUTINE.  What it is and how is it declared?

Here's what I have on SetWaitableTimer:

Public Type FILETIME
        dwLowDateTime As Long
        dwHighDateTime As Long
End Type

Declare Function SetWaitableTimer Lib "kernel32" (ByVal hTimer As Long, lpDueTime As FILETIME, ByVal lPeriod As Long, ByVal pfnCompletionRoutine As Long, ByVal lpArgToCompletionRoutine As Long, ByVal fResume As Long) As Long

Author

Commented:
I coded it as follows, and get "can't find entry point in kernel32.dll":


' Cut and paste twalgrave's:
Public Type FILETIME
       dwLowDateTime As Long
       dwHighDateTime As Long
End Type

Declare Function SetWaitableTimer Lib "kernel32.dll" ( _
    ByVal hTimer As Long, _
    lpDueTime As FILETIME, _
    ByVal lPeriod As Long, _
    ByVal pfnCompletionRoutine As Long, _
    ByVal lpArgToCompletionRoutine As Long, _
    ByVal fResume As Long) As Long
'
' ------ end cut and paste
'
Public Sub MakeItWait(Milliseconds As Long)
Dim hTimer As Long,   lpDueTime As FILETIME
Dim lPeriod As Long,    pfnCompletionRoutine As Long
Dim lpArgToCompletionRoutine As Long
DIM fResume As Long
Dim Result As Long

hTimer = Milliseconds
lpDueTime.dwHighDateTime = Now + 10 ' Not sure units
lpDueTime.dwLowDateTime = Now + 5   ' or rep here
lPeriod = 0
pfnCompletionRoutine = 0
lpArgToCompletionRoutine = 0
fResume = 0
   
Result = SetWaitableTimer(hTimer, lpDueTime, lPeriod, pfnCompletionRoutine, _
    lpArgToCompletionRoutine, fResume)

   
Any ideas?  I'm on W95, SP2 (I think).
End Sub

SetWaitableTimer is a WinNT based function.  If you are running this on Win95, won't work.

A little background on the error mentioned:

"can't find entry point in xxx.dll" means that the function specified in the "Declare Function XXX " cannot be found in the DLL. Each DLL has a numeric assignment for each of the functions it supports.  The Declare function tries to find the numeric number of the function that you specified.  If it doesn't find it, it won't find the entry point (memory location) for that function (It's like the equivalent to the AddressOf operator in VB).


Your best bet is to perform something like sleep.  It's available on Win95 and up.  Preform a Doevents right before the Sleep. I know it locks the thread, but on Win95, there's not much more you can do.
Further help.

When I convberted a major app to ADO. I created a class that emulated a recordset, only in so far as collecting the data.

I used the default property so that all of the data was stacked into the class. Example:

' RS.AddNew redundant
RS("SL Invoice") = "$$2$$"
RS("SL Date") = Date

I then gave the class a method to create the SQL.

SQL = RS.SQL(True) ' True = Insert Into, False = Update

I prefixed some SQL statements with tags to tell the global database update process to perform additional actions, like get Counter/autonumber field and replace a token like "$$1$$" to the sql statements that followed.

SQL = "$/GETCOUNTER:[SL Entry] /CODE:$$1$$;" + SQL

I then used another instance of the same class to store the SQL:

SQLStack(SQLStack).NexItem) =  SQL

I created my own version of a collection object, you can create the same thing too. Here is an example based on the collection object. (Although I created my own collection onject to overcome some of the problems with the MS Collection, I also gave my control the abbility to recreate itself.)  

Example:

Dim T As New zHolder

T("My Var") = "Any Data"
T("My List") = T.NewHolder
T("My List")("Item1") = "More Stuff"

Here is the code for a basic magic collection.

---------------------zMagicCollection.CLS

Option Explicit

' Magic Collection Class by: Nick Young of VIP InterSoft
' nyoung@vipintersoft.com

' MUST READ THESE NOTES:
'You can create a Magic collection by setting
'a default property within your object class
'(Tools-Procedure Attributes-Select procedure name-Advanced-Then
'set Procedure Id to Default.

'In the following sample code procedure Item must be
'set to the default property:

' this code can be placed into a class or a user control

Public ArrayData As Variant
Public Values As New Collection ' Hold your data
Public Names As New Collection
Dim myCount As Long


Public Property Get Item(VarName)
Dim Element
If IsNumeric(VarName) Then
   Element = VarName + 1 ' fix VB bug with collection numbering
Else
   Element = CStr(VarName)
End If
On Error Resume Next
If IsObject(Values(Element)) Then
   If Err.Number <> 0 Then
       Item = Empty
   Else
       Set Item = Values(Element)
   End If

Else
   Err.Clear
   Item = Values(Element)
   If Err.Number <> 0 Then
      Item = Empty
   End If
End If

End Property

Public Property Let Item(VarName, Value)
Dim MyVar

Dim Obj As Boolean
On Error Resume Next
Obj = IsObject(Values(VarName))
If Err.Number <> 5 Then
   On Error GoTo 0
   Values.Remove CStr(VarName)
   Values.Add Value, CStr(VarName)
Else
   On Error GoTo 0
   myCount = myCount + 1
   Values.Add Value, CStr(VarName)
   ' save the name so you can create a list of the
   ' names & values that have been added.
   Names.Add CStr(VarName), CStr(myCount)
End If

' You can call onChange event handling here

End Property

Public Property Get Count()
Count = Values.Count
End Property

Public Property Get NewEnum() As IUnknown
   'this property allows you to enumerate
   'this collection with the For...Each syntax
   Set NewEnum = Values.[_NewEnum]
End Property


Public Sub ReDimPreserveArray(NewSize As Long)
ReDim Preserve ArrayData(NewSize)
End Sub

Function GetElement(Element As Long)
   If IsObject(ArrayData(Element)) Then
       Set GetElement = ArrayData(Element)
   Else
       GetElement = ArrayData(Element)
   End If
End Function
Function PutElement(Element As Long, Value)
   If IsObject(Value) Then
       Set ArrayData(Element) = Value
   Else
       ArrayData(Element) = Value
   End If
End Function

Author

Commented:
Effort expended to help me was far above & beyond the call of duty...thanx much.  Will post another comment once I get it fixed with details of how I fixed it.  Seems the problem is twofold:  I need to respect locks more, and code retries, etc.  Switching to ADO and an ODBC connection is probably best, long term, but for now, DAO and smart coding will have to do.
Second:  "Opportunistic Locking" (OpLocks) on the server side is also causing me problems.  The IT folks won't turn it off.  Oh boy.  In the battle between App code and OS code, the App seldom wins - and I must try now to find a way to defeat this "feature" of Windows NT/2000 server.
(MS KB article 300216, 129202, etc)
Thanx again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial