SQL Server 2005 - Is there a way to lock a SELECT in a Transaction?

Question

Hi!

Is there a way to lock a SELECT in a Transaction? If a SELECT occurs, no more SELECTs are executed while the first one is not finished.

Thanks!

Answer 1

Might want to look at Isolation Level instead of a hint

Answer 2

You'll need an isolation level of SERIALIZABLE:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

The other SELECT queries will block until the transaction completes in this case. The default level is usually READ COMMITTED.

Answer 3

Not sure I understand your question correctly, but if you want to impose more rigorous locking than SQL Server's default, then you can either bump up the isolation level or use a locking hint. This can be useful if you first need to SELECT something and then later, based on the value SELECTed, do an UPDATE. To avoid a phantom UPDATE from another transaction (wherein the value you previously SELECTed was changed in b/w the SELECT and UPDATE), you can impose an update lock on your SELECT statement.

Eg:

 select * from mytable with (holdlock, xlock)

Notice that the SELECT statement above uses the more rigorous update lock & holds that lock for the duration of the transaction. You would also want to wrap your statements in an explicit transaction, as in:

begin transaction
select * from mytable with (holdlock, xlock) -- exclusive lock held for the entire transaction
-- more code here...
update mytable set col='whatever' where ...
commit transaction

Be wary, of course, for long-running transactions.

Answer 4

it seems you're looking for a pessimistic locking strategy but without acctually locking your data. look into Application locks in sql server.

Answer 5

Changing your isolation levels is generally your best choice.

But, if for whatever reason that's not an option for you - you could also do an exclusive table lock...

select * from MyTable with (tablockx)

That would prevent any other selects on the table until your transaction is finished.