Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Transaction Failed to Block Process?

    I have a process that checks for new records in a table, determines the last batch number used in the table, increments that number by one, and updates all of the new records with that new batch number.

    I've simplified the code and it is essentially this:

    Code:
    begin transaction
    
    select	@Batch_ID=
            (
            select	max(Batch_ID)
            from	dbo.tblAlteredRecords with (NOLOCK)
            )
            +1
    
    update	tblAlteredRecords
    set     Batch_ID=@Batch_ID
    from	dbo.tblAlteredRecords
    where	Batch_ID=0
    
    commit transaction
    This process is automatically invoked as a SQL Server Agent Job every 15 minutes, but it can also be manually invoked, which happens about three or four times a day.

    I have evidence that yesterday, the automatic and manual process were running at the exact same time such that the two invocations were interlaced, executing like this:

    1) Manual invocation executes SELECT from above.
    2) Automatic invocation executes SELECT from above.
    3) Manual invocation executes UPDATE from above and updates X number of rows.
    4) Automatic invocation executes UPDATE from above and update zero rows.

    I am amazed that I am able to "see" two processes in such lockstep with each other because I was under the understanding that the transaction would have prevented more than one process executing this code at the same time.

    Why did the transaction not block the second process?

    What am I doing wrong?

    Or, is this just an example that, at a nanosecond level, things like this can occur.

    Thanks.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Review HOLDLOCK.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Possibly . . .

    But my reading of that is that the lock only applies to a single statement, not a group of statments.

    Unfortunately, because I need the value of the Batch_ID later in my code, I need to separate the Batch_ID statement from the UPDATE statement, resulting in two statements.

    I may be wrong, but it does not look like the HOLDLOCK hint applies when one is attempting to maintain a lock across more than one statement.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The select statement is selecting with the NOLOCK hint. This will not take out locks of its own, but it will not respect others' locks. See if you can remove that.

    Is there any way you can use a table of batches with an identity column on it to manage the BatchID?

  5. #5
    Join Date
    Sep 2010
    Posts
    15
    the transaction would have prevented more than one process executing this code at the same time.
    Not really.

    You use NOLOCK for select so:
    1) Manual invocation executes SELECT from above.
    2) Automatic invocation executes SELECT from above.
    3) Manual invocation executes UPDATE from above and updates X number of rows.
    4) Automatic invocation executes UPDATE from above and update zero rows.
    That is normal.

    If you want only 1 process can execute the sp at a time, using syschronized method in frontend application. With this, all processes that want to execute the sp have to wait until the current process finished his job. Clearly, that violates the principle of concurrency in DB world. So good or bad, that is up to you.

    If you want to solve the problem in DB, think about lock mode and isolation level.

    Look like your transaction has more than just 2 statements you provide, so with limit info, I have some suggestions ...
    - do not use NOLOCK in this case. As you say, you will use @Batch_ID for something later in that transaction. This is danger because with NOLOCK, @Batch_ID can be very wrong value. So you may use a wrong input for many processes later. It will mess your DB up.
    - HOLDLOCK is not for this case, I think. Moreover, using HOLDLOCK, deadlock will pop-up likes hell, you should be prepared.
    - using UPDLOCK in select statement. With this, you will not see 1,2,3,4 but 1,3,2,4

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You all have provided me with some good research paths . . .

    I consider myself pretty good at writing efficient, clean queries, but how a database deals with locking is a whole 'nother side of SQL Server that separates us pikers from the pros.

    Thanks for the ideas guys.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •