Remove blocking due to LCK_M_S and LCK_M

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Remove blocking due to LCK_M_S and LCK_M_U

Remove blocking due to LCK_M_S and LCK_M_U

Rate Topic

Display Mode

Topic Options

Author

Message

npranj

Posted Thursday, December 20, 2012 5:33 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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?

Post #1398923

GilaMonster

Posted Thursday, December 20, 2012 7:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 37,734, Visits: 29,999

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 2008, MVP
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

Post #1398958

npranj

Posted Thursday, December 20, 2012 10:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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?

Post #1399045

GilaMonster

Posted Thursday, December 20, 2012 10:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 37,734, Visits: 29,999

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 2008, MVP
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

Post #1399048

npranj

Posted Thursday, December 20, 2012 10:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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'

Post #1399058

GilaMonster

Posted Thursday, December 20, 2012 10:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 37,734, Visits: 29,999

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 2008, MVP
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

Post #1399062

npranj

Posted Thursday, December 20, 2012 12:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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

*/

Post #1399083

npranj

Posted Thursday, December 20, 2012 1:15 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

Clustered index on VersionID in dbo.VersionTable

Post #1399092

GilaMonster

Posted Friday, December 21, 2012 12:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 37,734, Visits: 29,999

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1399227

« Prev Topic | Next Topic »

Permissions