Strange TABLE LOCK behavior...

[*Vince]

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!!!

[RIVES]

Maybe you could set the isolation level in your database so that any query to the table would still be allowed.

[*Vince]

The thing is that I don't want any other query to be able to select the table. I want the querys to jam on the statement in my code and continue when the lock is free or timeout append... Still with me here ?