Restricting a SELECT statement in SQL Server 2005

Question

Hi,

I have a situation where before doing a particular task I have to check whether a particular flag is set in DB and if it is not set then rest of the processing is done and the same flag is set. Now, in case of concurrent access from 2 different transactions, if first transaction check the flag and being not set it proceeds further. At the same time, I want to restrict the 2nd transaction from checking the flag i.e. I want to restrict that transaction from executing a SELECT query and it can execute the same once the 1st transaction completes its processing and set the flag.

I wanted to implement it at the DB level with locks/hints. But no hint restrict SELECT queries and I cannot go for Isolation level restrictions.

Answer 1

I believe that SQL Server 2005 does this natively by not permitting a dirty read. That is, as I understand it, as long as the update / insert occurs before the second user tries to do the select to check the flag, the db will wait for the update / insert to be committed before processing the select.

Here are some common locks that may assist you as well, if you'd like more granularity.

edit : XLOCK may also be of some help. And, wrapping the SQL in a transaction may help as well.

Answer 2

You can create an Application Lock to protect your flag, so the second transaction will not perform SELECT or access the flag if it cannot acquire the Application Lock

Answer 3

You could try an stored procedure which does both tasks, or as an entry point for 2 distinct stored procedures which does different tasks (something like a proxy).

Stored procedures are monitors in SQL Server, so are artifacts to manage concurrency (what is you want to do).

Answer 4

You just need to simply start a transaction in your SP / code then update the flag. That will block any other user from reading it (unless they are reading uncommitted).

If they are reading uncommitted, set an exclusive lock on your update transaction.