If you consider a Single SELECT statement then HOLDLOCK table hint and
SERIALIZABLE ISOLATION LEVEL both have the same effect. Both do the
However, if we are talking about multiple SELECT statements in a
All the tables in all the SELECT statemets have to have HOLDLOCK Hint in
order to be equivalent to the
SERIALIZABLE ISOLATION LEVEL
You test this quickly by running two QAnalyser connections to SQL Server.
Run, ( use northwind database) following cmd in one connection
set transaction isolation level READ COMMITTED
select * from orders(HOLDLOCK) where OrderID <=10260
-- Don't commit the tran yet..
Run sp_lock in another connection and you can see the RangeS-S locks on
the Orders Table.
Then commit the above transaction and repeat this for SERIALIZABLE
without HOLDLOCK hint and see the RangeS-S locks.
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure ! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
>X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
>Subject: HOLDLOCK vs SERIALIZABLE locking hints
>Content-Type: text/plain; charset="us-ascii"
>Date: Wed, 28 Aug 2002 11:10:22 -0700
>Xref: cpmsftngxa10 microsoft.public.sqlserver.programming:288401
>BOL says that HOLDLOCK and SERIALIZABLE are equivalent locking hints,
>but is this really true? It makes sense that they'd both hold locks
>through the end of the transaction, but do they both acquire key range
>locks when necessary? It seems that HOLDLOCK would really be equivalent
>to REPEATABLEREAD. Does anyone know for sure (I'm proceeding with
>experiments in the meantime)?
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!