Gaspar
3/7/2007 2:49:00 PM
I need to x-lock a specific record, so as to prevent other transactions
from reading or writing. This is what I'm doing:
BEGIN TRANS
SELECT Value FROM MyTable WITH (ROWLOCK, XLOCK) WHERE Id = 1000
...
...
...
...
...
...
COMMIT
I try this in SQL Server Management studio with two different connections:
CONNECTION_1:
(The above statement)
CONNECTION_2:
SELECT Value FROM MyTable WHERE Id = 1000
Of course, CONNECTION_2 is blocked until CONNECTION_1 ends.
The problem comes then executing the statement in my application through
ADO: although the X-LOCK seems to be granted (I check the Activity
Monitor in SSMS), CONNECTION_2 can execute the SELECT statement!!! (i.e.
it does not wait until my application commits the transaction).
This is the code:
MyADOConnection.BeginTrans;
MyADOCmd.CommandText := 'SELECT Value FROM MyTable WITH (ROWLOCK, XLOCK)
WHERE Id = 1000';
MyADOCmd.Execute;
....
....
.... (do some operations)
...
... (at this point, for example, Connection_2
... can perform the SELECT! why???)
...
MyADOConnection.CommitTrans;
Thanks a lot for help,
Gaspar