Restricting a SELECT statement in SQL Server 2005

Question

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

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 2

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.