LOCK A TABLE. ONLY ONE USER AT A TIME 3

[jadams0173]

I'm using VB6 and A2K with ADO. I'm trying to lock a table or a record in the table until the current user is done with it. I'm attempting to make an autonumber field. The table in question only has one record in it.

Would I be better off adding a locked check box and checking it that way?

I've read some FAQ's but the one i saw used DAO.

Here is what I've tried.

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs.Open "Select fixtureid from tblFixtureID", conn, adOpenKeyset, adLockPessimistic, adCmdText
'rs.EditMode
rs.Fields("fixtureid") = 2000


rs2.Open "Select fixtureid from tblFixtureID", conn, adOpenKeyset, adLockPessimistic, adCmdText
rs2.Fields("fixtureid") = 5000


Set rs = Nothing
Set rs2 = Nothing
conn.Close
Set conn = Nothing

Here is my connection string.

  With conn
  .CursorLocation = adUseClient
  
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=C:\MYDB.mdb;" & _
                      "Jet OLEDB:Database"

  .Open
  End With

I tried to have patience but it took to long! -DW

 

[BobRodes]

Perhaps you could also explain why what you've posted doesn't work.

 

[jadams0173]

Hey again BobRodes!

In my code above I was expecting an error to be raised indicating that the record was locked. I was going to trap this error and have a Sleep routine to wait until the record was free.

I was expecting the adLockPessimistic to lock the record and the second call to the same record I thought should have generated an error until the Update was called.

I tried to have patience but it took to long! -DW

 

[BobRodes]

Well, the most common reason for that is that you think you're editing the record when you aren't really.

In ADO, a record automatically goes into edit mode when someone actually does something that implies editing the record (for example typing in a text box that's bound to the recordset), and goes out of edit mode when someone does something that implies finishing the edit (calls the update or cancelupdate methods, for example). There isn't any Edit method as there was in DAO. If you're not using binding (I never do), the recordset doesn't know it's being edited. So, you're not going to automatically get pessimistic locking behavior if you're "binding by hand."

Use the EditMode property to test whether the record is actually being edited in the context that you think the record ought to be locked. Suppose you experiment with that and post back if you have further questions.

HTH

Bob

 

[jadams0173]

I will do that Bob and post back. Thanks again.

I tried to have patience but it took to long! -DW

 

[SBerthold]

You cannot lock the record like that. You need to use a Server Side cursor instead.

In fact, you are not even using adOpenKeyset or adLockPessimistic, but instead you are using adOpenStatic and adLockBatchOptimistic

 

[jadams0173]

In fact, you are not even using adOpenKeyset or adLockPessimistic, but instead you are using adOpenStatic and adLockBatchOptimistic

Because of the client side cursor?

I tried to have patience but it took to long! -DW

 

[SBerthold]

Yes.

 

[BobRodes]

<You cannot lock the record like that. You need to use a server side cursor instead.
Yes, well I was getting to that.

<In fact, you are not even using adOpenKeyset or adLockPessimistic, but instead you are using adOpenStatic and adLockBatchOptimistic
This isn't quite correct: while in fact setting the cursor location to client side coerces the cursor type to static, the default locktype for a client side cursor is adLockReadOnly, NOT adLockBatchOptimistic. If you wish to use a client side cursor for batch updates, you will have to make sure that you specifically set the locktype property to adLockBatchOptimistic.

Bob

 

[SBerthold]

BobRodes,

I never said it in that context.

While the lock type defaults to adLockReadOnly, when none have been assigned, if one is assigned and it cannot be used it will default to the next best one, which in this case adLockPessimistic usually defaults to adLockBatchOptimistic.

 

[jadams0173]

Hey BobRodes and SBerthold,

I've been reading the information in the thread. I've done away with the client side cursor.

Bob,
The EditMode seems to work for telling me when the field is being edited.

Is there a better way to do a self created autonumber field?

I tried to have patience but it took to long! -DW

 

[jadams0173]

SBerthold
After reading thread222-1359107 where you discuss cursors and say that you always use client side with ado I'm kinda scratching my head more than usual. I'm going to put the client side cursor back.



I tried to have patience but it took to long! -DW

 

[Andrzejek]

I don't know if this will be in any use for you, but the way I do locking in my table is:

I have a field in the Table called LOCKED_BY_USER which accepts a string.

When I hit the record, I check if this Field is NULL or has a value in it. If it is NULL, I update this field with user's login name, if already has something in it (another user's login name) it means it is locked by that user.

When I move from one record to another, if I’ve locked the record – I move NULL to it. If it is not mine, I just leave it alone.

You just have to remember to un-lock the record when you leave for good, like when you exit your app. I also have a small stored procedure to unlock all records at night when nobody is logged in. I am also able to detect if user is on the locked record but the record is locked by that user - if for some reason he/she crashed on that record and never had a chance to un-locked it.

My users like this way because I am able to display who has the record locked – since I have their login name.


Have fun.

---- Andy

 

[jadams0173]

OK. After reading closer I realize that I'm thinking of the client side cursor in my connection string. The other thread above is talking about a client side cursor in a recordset.

I tried to have patience but it took to long! -DW

 

[SBerthold]

If not specified, the recordset cursor location will default to that of the connection's.

Yes, I use client side cursors. But I never said whether I use pessimmistic locks or not, as you apparently want to use.
Pessimmistic locks lock the record, or page, at the source the moment you edit a field or call AddNew, until it is updated back to the source.

When optimistic batch locking, and using .UpdateBatch, then record is locked for just the moment the record is actually updated. If there are conflicting record updates, you can either ignore them or use the rs.Filter=adFilterConflictingRecords to see the records which conflict.
Or, what I prefer, is using an UPDATE statement for a single record at a time, and TimeStamp validation in the Where clause.

If the record depends on a field like a unique "order number", then there are better ways of handling this through the dbms directly.

So, you also need to read up on LockTypes and how to use each of them under ADO.

 

[SBerthold]

>will default to that of the connection's.

That is actually the purpose of this property of the connection object: As a default cursor location for recordsets created using that connection. Therefore the property is Read/Write on an opened connection, and can be changed on the connection object at anytime.

 

[jadams0173]

Thanks for clearing that up SBerthold.

If the record depends on a field like a unique "order number", then there are better ways of handling this through the dbms directly.

I do need a unique number that I will put a suffix and prefix on. The number will not start out at 1 which is why I want to generate it myself. The unique number with the prefix and suffix attached will be the PK in the main table and be used as FK in a couple others.


I tried to have patience but it took to long! -DW

 

[BobRodes]

Now I'm really down a rabbit hole. I'm going to do some more testing and get back. So far, I've been able to successfully update a static recordset on an SQL Server connection without difficulty.

 

[SBerthold]

Are you sure it is sticking to adOpenStatic?

Depending on the LockType with a Server-Side cursor, the Cursor type may change.

Usually, if you define any lock type other than ReadOnly with a Static or ForwardOnly Server-side cursor, you will get back a KeySet cursor, which is updatable.

First, the CursorLocation has priority, then the LockType, and lastly the CursorType.

With Server Side cursors, NEVER rely on the default cursor and lock type properties, when manually setting one of the cursor or lock type properties, unless you are aware of all the different affects the possible combinations of settings, manually and defaults, will have, even specific to the dbms being used.

[COLOR=blue]
.CursorLocation = adUseServer
.CursorType = adOpenStatic [/color]'Or .CursorType = adOpenForwardOnly[COLOR=blue]
.LockType = adLockReadOnly[/color]
[COLOR=green]'[b]NOTE: Defining any other LockType may change the CursorType to adOpenKeyset[/b][/color][COLOR=blue]

.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
[/color]'OR:
'.LockType = adLockBatchOptimistic
'.LockType = adLockReadOnly
'.LockType = adLockPessimistic
[COLOR=green]'With the LockType "adLockBatchOptimistic", it will depend on the dbms whether it really is
'just a plain adLockOptimistic lock or not[/color]

[COLOR=blue]
.CursorLocation = adUseClient
.CursorType = adOpenStatic [/color][COLOR=green]'The only Type possible[/color][COLOR=blue]
.LockType = adLockOptimistic
[/color]'OR:
'.LockType = adLockBatchOptimistic
'.LockType = adLockReadOnly
[COLOR=green]'NOTE: Using a adLockPessimistic lock type may default to adLockBatchOptimistic, and not adLockOptimistic
[/color]

 

[JerryKlmns]

Well, I think SBerthold do deserves a star, for all that informative, nicely said and typed!