Im a bit at loss
This is what I want to do:
1. Open a transaction
2. Read 1 Row and locking it from further reads, but allowing reading other records on the same table
3. Update a value on it
4. Do some stuff with the data while still holding the transaction open
5. If everthing was ok: Update another value on the row
6. Commit the transaction allowing to access the row for all
This Is what i got so far
1. Open
SqlConnection
in c#
2. begin
SqlTransaction
with
IsolationLevel.ReadCommited
3. Execute the following script on the database
UPDATE [MyTable] SET Counter=Counter+1
OUTPUT inserted.Id, inserted.Value
WHERE Id = (SELECT TOP 1 Id FROM [MyTable] WITH (READPAST, ROWLOCK)
WHERE Flag=0 AND Counter < 5 ORDER BY Id ASC)
4. read the values from the
OUTPUT
clause and do some stuff
5. If everything from doing some stuff was ok: Update the value
Flag
to 1
6. Commit the transaction
basically a queue on the database.
My Problem: if two processes are doing this at the same time there is a probability of around 1% that the same record gets read and updated despite the transaction and locks. (I suppose the lock in select statement get released as soon as the select is finished?)
It's very important that only the rows get locked so that multiple instances can access the table at the same time.
If tried some variations of the SQL script above:
- Added
WITH (ROWLOCK)
clause to the update statement (did result in a few deadlocks for insert statements)
- Added
XLOCK
to the WITH clause in the select statement (resulted in Table lock, no erros on the other hand. However negates the readpast & rowlock hints)
How can I hold the lock on the read with statement without preventing other processes from accessing other records of the table?
Thank you for your help :)