locked
Dead Lock Problem RRS feed

  • Question

  • Hi Everyone,

    I have a problem with a stored procedure that when it's called from multiple clients at once it keeps causing deadlocks or not performing the way it's supposed.

    I have the table below. The requirment is to insert a row in the table and increment the TrackingNumber by 1. The tracking number has to be sequential for each TrackingAsset and cannot skip number. The requirement at out of my control so I can't change them just have to find a way to correct them.

    Table Defention

    CREATE TABLE [dbo].TrackingTable(
    	TrackingID [uniqueidentifier] NOT NULL,
    	TrackingNumber [bigint] NOT NULL,
    	TrackingAsset [uniqueidentifier] NOT NULL
    ) ON [PRIMARY]

    The statement of the stored procedure that keeps dead locking is:

    DECLARE @TrackingID UNIQUEIDENTIFIER  = NEWID()
    DECLARE @TracckingAsset UNIQUEIDENTIFIER = '01B8EBA6-64BE-48F6-841E-EF1988BFC9F7'
    INSERT INTO 
    	TrackingTable
    	( 
    		TrackingID, 
    		TrackingNumber,
    		TrackingAsset)
    		VALUES ( 
    		@TrackingID,
    		COALESCE(( SELECT MAX ( TrackingNumber ) From TrackingTable  WHERE TrackingAsset= @TrackingAsset*/),0) + 1,
    		@TrackingAsset)

    All the column names and table names are changed so don't mind spelling mistakes and names that don't make sense.

    The Coalesce is there because the TrackingAsset may not exist so it'll return a null.

    I've tired all (and I mean all) Transaction Isolation level to no avail. Depending on the isolation level I'll either get deadlocks or the TrackingNumber will not be Unique and Sequential.

    I've also tried creating a table to hold the TrackingAsset and TrackingNumber and getting the number from there. It still caused the same issues. 

    Does anyone see a way around this?

    Tuesday, February 18, 2014 3:34 AM

Answers

  • An horrifying number of bad advice in this thread. NOLOCK? Yeah, that is really good if you want avoid duplicates. IDENTITY or SEQUENCE? Please read the question before you make such suggestions. They don't fly if you need numbers to be contiguous. And not really if you want number per tracking asset.

    Anyway the solution is not difficult at all:

    BEGIN TRANSACTION

    SELECT @TrackingNumber = coalesce(MAX(TrackingNumber), 0) + 1
    FROM   TrackingTable WITH (UPDLOCK)
    WHERE  TrackingAsset = @TrackingAsset

    INSERT INTO TrackingTable    
            TrackingID,
            TrackingNumber,
            TrackingAsset)
            VALUES (
            NewID, @TrackingNumber, @TrackingAsset)

    COMMIT TRANSACTION

    There is one problem in this solution: if two process attempt to add the first row at the same time for the same tracking asset, one of them will get a PK violation. This could be avoided by adding SERIALIZABLE to the table hint above, but serializable is very prone to deadlocks in itself. If this is a real concern, I think it is better to use TRY-CATCH and perform a retry in this case.

    Why two separate statements? I seem to recall that my MVP colleague Tony ROgerson found that if you do it in a single statement, without a transaction, the UPDLOCK is released before the INSERT itself starts working.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Tuesday, February 18, 2014 12:48 PM
    • Marked as answer by Pantelis44999 Tuesday, February 18, 2014 2:06 PM
    Tuesday, February 18, 2014 9:29 AM

All replies