Click here to Skip to main content
15,119,877 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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
SQL
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 :)
Posted
Updated 14-May-13 5:15am
v2
Comments
CHill60 14-May-13 10:49am
   
Investigate the use of UPDLOCK - see example here Thread safe sql transactions[^].
A common opinion seems to be to leave locking to the DBMS though. I've also seen several sugggestions around using an IsAvailableForUpdate flag (a sort of manual extension to locking) which is what I thought your [Flag] column was attempting
Nicholas Marty 15-May-13 9:33am
   
Yeah. After lot of testing this seems to do the trick. But I had to rewrite the query (Only a select statement without any update) so basically a SELECT WITH (UPDLOCK READPAST ROWLOCK).

1 solution

Posting solution for tidy up.

I suggested ... Investigate the use of UPDLOCK - see example here http://stackoverflow.com/questions/1921091/thread-safe-sql-transaction-how-to-lock-a-specific-row-during-a-transaction[^]
A common opinion seems to be to leave locking to the DBMS though. I've also seen several sugggestions around using an IsAvailableForUpdate flag (a sort of manual extension to locking) which is what I thought your [Flag] column was attempting

OP confirmed ...
Quote:
Yeah. After lot of testing this seems to do the trick. But I had to rewrite the query (Only a select statement without any update) so basically a SELECT WITH (UPDLOCK READPAST ROWLOCK).
   
Comments
Nicholas Marty 16-May-13 5:34am
   
Thanks for hinting me in the right direction ;)
CHill60 16-May-13 6:02am
   
Did some further reading ... sounds like quite a lot of people have had similar problems and similar pain getting over their individual issues. Doesn't seem to work quite the way we'd expect it to :-( Good Luck!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900