Strange TABLE LOCK behavior

[*Vince]

( ALREADY POSTED IN DATABASE Q & A )

Hi all,

I have a table in SQL SERVER 7 that I want to lock when read and free when update is done or cursor closed.

I use RDOs (resultsets) in VB to do the SELECT part and I specifiy that I want an rdOpenKeyset (updatable) set of rows LOCKED as soon as the SELECT is launched (rdConcurLock) or if you prefer, PESSIMISTIC locking.

PROBLEM :

- User 1 locks the table...
- User 2 wants to read the table so the SELECT wait for the lock to be free.
- User 1 free the lock...
- User 2 SHOULD return with the row set BUT...
-> The row set is EMPTY, I have to requery my SELECT in order to have data in the row set.


Anyone ever had the same experience before ?
What did I do wrong ?

Please help me!!!

[michelle]

Hello Vince,

Please send your source.

Nice regards,

Michelle.

[*Vince]

Source

Dim rsNextId As rdoResultset


Set rsNextId = DomtarDB.OpenResultset("SELECT * FROM Contractuel_Seq", rdOpenKeyset, rdConcurLock)

' Nobody can use the same value as I have retreived now... rsNextId.Edit

rsNextId![No_Contractuel_Seq]=(rsNextId![No_Contractuel_Seq] + 1)

rsNextId.Update

rsNextId.Close
Set rsNextId = Nothing

' Lock should now be free


CONTEXT :

This SELECT is used to get the next available Employe Number for INSERTs. I want to be sure nobody can read the same number as I do so that when I free the table, I've updated the next number + 1.

PROBLEM :

When I trace this in multi-user, one of the X users gets the LOCK on the table, the others are waiting for the LOCK to disapear. When the lock is free, one of the waiting other gets the LOCK and should be able to edit the rows. It can't, it's empty !!! It didn't return any DATA...

[ingrid]

Hello Vince

I am very sorry but I can't help you.

Nice regards,

Michelle.