Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove blocking due to LCK_M_S and LCK_M_U Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:51 AM
Points: 48, Visits: 297
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
Posted Thursday, December 20, 2012 7:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 40,206, Visits: 36,613
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
Posted Thursday, December 20, 2012 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:51 AM
Points: 48, Visits: 297
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
Posted Thursday, December 20, 2012 10:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 40,206, Visits: 36,613
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
Posted Thursday, December 20, 2012 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:51 AM
Points: 48, Visits: 297
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
Posted Thursday, December 20, 2012 10:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 40,206, Visits: 36,613
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
Posted Thursday, December 20, 2012 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:51 AM
Points: 48, Visits: 297
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
Posted Thursday, December 20, 2012 1:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:51 AM
Points: 48, Visits: 297
Clustered index on VersionID in dbo.VersionTable
Post #1399092
Posted Friday, December 21, 2012 12:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 40,206, Visits: 36,613
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 »

Add to briefcase

Permissions Expand / Collapse