Re: HOLDLOCK?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 08:34:39 -0700

Hi Greg

You have to be in a transaction for HOLDLOCK to hold anything, but you can
explicitly create a transaction with BEGIN TRAN.

BEGIN TRAN

SELECT ... FROM tab with (HOLDLOCK)

...

SELECT ... FROM tab

COMMIT TRAN

this will guarantee that the data you read in the first select will not be
changed until the COMMIT TRAN. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Greg Sullivan" <saucerattack@hotmail.com> wrote in message 
news:7bfe64e8.0410220704.26224b70@posting.google.com...
>I guess the software engineers here don't have much better to do than
> debate the extreme limits of database concurrency.  So, the question
> has come up about how to ensure data has not changed between two
> select statements within a stored procedure.  Take the following as an
> example:
>
> create procedure exampleProcedure
> as
> begin
>   select fieldOne
>   from   tableOne
>
>   select columnOne
>   from   tableTwo
>
> end
> go
>
> How can I ensure that no one has come along and updated tableTwo
> between the time the procedure acquires a lock on tableOne and it
> acquires a lock on tableTwo?  My first thought is to use a HOLDLOCK,
> but everything that I've read indicates that a HOLDLOCK persists the
> lock the duration of a transaction.  And in this case, I have no
> isert/update/delete statements so I am not technically in a
> transaction.
>
> Is this issue just academic?  Personally, I think they are making a
> mountain out of a molehill, but I'm not able to quote scripture (BOL)
> at them.
>
> Any ideas?

Relevant Pages

  • Re: SELECT locking: EXACTLY HOW?
    ... Shared locks are compatable with one another so it is perfectly fine for 2 concurrent sessions to hold shared locks on the same resource. ... There is no primary key or other indexes in your example script so a table level lock is needed. ... then I have something like these in a transaction (isolation level = ... SELECT v FROM XT WITH (HOLDLOCK) WHERE v = n ...
    (comp.databases.ms-sqlserver)
  • Re: Table Locking
    ... When one procedure starts a transaction, then calls another sp which does ... Make sure EACH proc which does a begin tran also does either a rollack tran ... SQL Server will do appropriate locking on all resources, ... to avoid using lock hints... ...
    (microsoft.public.sqlserver.programming)
  • Re: Row Locking and Transaction
    ... Yes an update lock may be a possibility depending on what you are doing. ... >> for the duration of the transaction. ... >> without the Holdlock. ...
    (microsoft.public.sqlserver.programming)
  • Re: Row Locking and Transaction
    ... for the duration of the transaction. ... rollback tran ... What type of lock ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting a lock or killing a process
    ... I feel that u are not commiting the transaction. ... after a Begin TRAN. ... > We are currently having a problem with our SQL server. ... > we can't update because the lock is still there. ...
    (microsoft.public.sqlserver.server)