HOLDLOCK vs SERIALIZABLE locking hints

HOLDLOCK vs SERIALIZABLE locking hints

Post by Vikrant V Dalwale [M » Sat, 31 Aug 2002 12:08:23



Hello Penny,

If you consider a Single SELECT statement then  HOLDLOCK table hint  and
SERIALIZABLE ISOLATION LEVEL both  have the same effect. Both do the
Range-Locks.

However, if we are talking about multiple SELECT statements in a
Transaction then
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
begin tran
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
ISOLATION LEVEL
without HOLDLOCK hint and see the RangeS-S locks.

Thanks,

Vikrant Dalwale

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
>Mime-Version: 1.0
>Content-Type: text/plain; charset="us-ascii"
>Content-Transfer-Encoding: 7bit

>Newsgroups: microsoft.public.sqlServer.programming
>Date: Wed, 28 Aug 2002 11:10:22 -0700
>NNTP-Posting-Host: 216.17.146.37
>Lines: 1        
>Path: cpmsftngxa10!tkmsftngp01!tkmsftngp11
>Xref: cpmsftngxa10 microsoft.public.sqlserver.programming:288401
>X-Tomcat-NG: microsoft.public.sqlserver.programming

>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)?

>thanks
>p

>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

 
 
 

1. HOLDLOCK vs. SERIALIZABLE Locking Hints

Books Online says in several places that
    HOLDLOCK and SERIALIZABLE Locking Hints are eqivalent (interchangeable)
        "HOLDLOCK is equivalent to SERIALIZABLE"
        "SERIALIZABLE  is equivalent to HOLDLOCK"

In another place it says
        SERIALIZABLE:  "Perform a scan with the same locking semantics
            as a transaction running at the SERIALIZABLE isolation level. "

In yet another place it says
        "When you use the HOLDLOCK option, the isolation level is set
        to repeatable read. "

Yet, SERIALIZABLE and REPEATABLE READ isolation levels are not
equivalent.

Can someone please clarify?

Thank you,
Deac Lancaster

2. Database Admin tool in Java

3. Locking vs. serializable transactions

4. UNIX Timestamps Conversions

5. locking vs. serializable transactions

6. Where is ASE-11.0.3.3.rpm?

7. sp_IndexOption vs Lock Hints

8. OLE Object stored in long raw field

9. Difference between (HOLDLOCK) and (ROWLOCK, HOLDLOCK)

10. 7.0 vs 2000 lock hints

11. using ISOLATION LEVEL vs table LOCK HINTS

12. lock... holdlock

13. Locking Methods, HOLDLOCK?