December 20, 2012 at 5:33 am
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?
December 20, 2012 at 7:14 am
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
December 20, 2012 at 10:11 am
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?
December 20, 2012 at 10:17 am
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
December 20, 2012 at 10:35 am
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'
December 20, 2012 at 10:41 am
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
December 20, 2012 at 12:00 pm
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
*/
December 20, 2012 at 1:15 pm
Clustered index on VersionID in dbo.VersionTable
December 21, 2012 at 12:39 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply