SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove blocking due to LCK_M_S and LCK_M_U


Remove blocking due to LCK_M_S and LCK_M_U

Author
Message
npranj
npranj
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 323
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90419 Visits: 45284
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
npranj
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 323
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90419 Visits: 45284
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
npranj
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 323
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'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90419 Visits: 45284
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
npranj
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 323
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
npranj
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 323
Clustered index on VersionID in dbo.VersionTable
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90419 Visits: 45284
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search