Remove blocking due to LCK_M_S and LCK_M_U

  • npranj

    SSCrazy

    Points: 2323

    Hi,

    I am trying to remove a blocking in one stored procedure.

    We are encountering deadlock because -

    1. This SP is being called through SSIS in parallel threads.

    2. There is SELECT as well as UPDATE happening on the same table.

    -

    -

    -

    EXECUTE @RC = [dbo].[GetVersionID]

    @Threadid

    ,@VersionID OUTPUT

    -

    -

    WHILE @VersionID <> 0

    BEGIN

    UPDATE dbo.[VersionTable]

    SET [StatusCd] = 'S'

    ,UpdateDttm = GETUTCDATE()

    WHERE VersionId = @VersionId

    EXECUTE @RC = [dbo].[GetVersionID] @Threadid,@VersionID OUTPUT

    END

    Note - GetVersionID proc does two tasks-

    1. Picks the top 1 record that has StatusCd='New' for that passed ThreadId.

    2. Changes the status of that to StatusCd = 'In Progress' and returns the VersionID.

    Proposed solution: (your comments please).

    1. Create a Lable (Eg. RETRY) before TRY. CATCH the error and when the ERROR_NUMBER() = 1205,

    then WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

    GOTO RETRY -- Go to Label RETRY

    2. Create table partitioning for the number of threads (6 threads) - but that will not remove the locks.

    Is there any other option?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Tune the query (may include some rewriting)

    If that doesn't work, consider one of the snapshot isolation levels

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • npranj

    SSCrazy

    Points: 2323

    Would this help?

    WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

    And also if there are 6 threads spanning 70k records, will table partitioning be of help?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    npranj (12/20/2012)


    Would this help?

    WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms

    As in, if you get a deadlock, ignore it and try again, repeat until successful? Sure, I wouldn't call it a solution though.

    And also if there are 6 threads spanning 70k records, will table partitioning be of help?

    Maybe. Depends how you partition, how the queries are written, what data is needed, what granularity locks are taken, etc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • npranj

    SSCrazy

    Points: 2323

    Thank you for your inputs. Would need one more direction here on tuning. All indexes are in place.

    This is the only piece of code -

    WHILE @VersionID <> 0

    BEGIN

    UPDATE dbo.[VersionTable]

    SET [StatusCd] = 'Success' ,UpdateDttm = GETUTCDATE()

    WHERE VersionId = @VersionId

    EXECUTE @RC = [dbo].[GetVersionID] @Threadid,@VersionID OUTPUT

    END

    ------------------------------------------

    Procedure: dbo.GetVersionID

    ------------------------------------------

    CREATE PROC dbo.GetVersionID

    @Threadid int,

    @VersionId int OUTPUT

    AS

    BEGIN

    IF

    (SELECT TOP 1 1

    FROM dbo.VersionTable WHERE StatusCd = 'New')

    UPDATE TOP (1) dbo.VersionTable

    SET StatusCd = 'In Progress' ,ThreadId = @ThreadId,UpdateDttm = GETUTCDATE()

    WHERE StatusCd = 'New'

    IF (SELECT COUNT(1) FROM dbo.VersionTable

    WHERE ThreadId = @ThreadId AND StatusCd = 'In Progress') = 1

    SELECT @Versionid = VersionId

    FROM dbo.VersionTable

    WHERE ThreadId = @ThreadId

    AND StatusCd = 'In Progress'

    ELSE

    SET @VersionId = 0

    SELECT @VersionID

    Note - GetVersionID proc does two tasks-

    1. Picks the top 1 record that has StatusCd='New' for that passed ThreadId.

    2. Changes the status of that to StatusCd = 'In Progress' and returns the VersionID.

    ===============

    Table script

    =================

    CREATE TABLE [dbo].[VersionTable](

    [VersionID] [int] NOT NULL,

    [ThreadID] [int] NOT NULL,

    [UpdateDttm] [datetime] NOT NULL,

    [StatusCd] [varchar](10) NOT NULL,

    CONSTRAINT [PK_VersionTable] PRIMARY KEY CLUSTERED

    (

    [VersionID] ASC

    )

    ) ON [PRIMARY]

    ==========

    Data

    ===========

    INSERT INTO [dbo].[VersionTable]

    ([VersionID]

    ,[ThreadID]

    ,[UpdateDttm]

    ,[StatusCd])

    SELECT 1,1,GETUTCDATE(),'New'

    UNION ALL

    SELECT 2,1,GETUTCDATE(),'New'

    UNION ALL

    SELECT 3,1,GETUTCDATE(),'Success'

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    Please also post all index definitions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • npranj

    SSCrazy

    Points: 2323

    Some more information on that -

    I get -

    LCK_M_S on the following:

    Waiting on statement:

    IF

    (

    SELECT TOP 1 1

    FROM dbo.VersionTable WHERE StatusCd = 'New'

    )

    LCK_M_U on the following:

    "/* (inserted by Ignite)

    Character Range: 739 to 893

    Waiting on statement:

    UPDATE MME.[VersionTable]

    SET [StatusCd] = 'Success' ,

    UpdateDttm = GETUTCDATE()

    WHERE VersionId = @VersionId

    */

  • npranj

    SSCrazy

    Points: 2323

    Clustered index on VersionID in dbo.VersionTable

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please post the deadlock graph and the definition of all indexes on the involved tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply