InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:23:24 GMT20http://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxPlease post the deadlock graph and the definition of all indexes on the involved tables.Fri, 21 Dec 2012 00:39:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxClustered index on VersionID in dbo.VersionTableThu, 20 Dec 2012 13:15:03 GMTnpranjhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxSome more information on that -I get -[b][u]LCK_M_S on the following[/u][/b]:Waiting on statement: IF ( SELECT TOP 1 1 FROM dbo.VersionTable WHERE StatusCd = 'New' ) [u][b]LCK_M_U on the following[/b][/u]:"/* (inserted by Ignite)Character Range: 739 to 893Waiting on statement: UPDATE MME.[VersionTable] SET [StatusCd] = 'Success' , UpdateDttm = GETUTCDATE() WHERE VersionId = @VersionId*/Thu, 20 Dec 2012 12:00:10 GMTnpranjhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxSwitch 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.[code]DBCC TRACEON(1222,-1)[/code]Please also post all index definitions.Thu, 20 Dec 2012 10:41:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxThank 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 OUTPUTASBEGINIF (SELECT TOP 1 1 FROM dbo.VersionTable WHERE StatusCd = 'New') UPDATE TOP (1) dbo.VersionTableSET 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 = 0SELECT @VersionIDNote - 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'Thu, 20 Dec 2012 10:35:41 GMTnpranjhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspx[quote][b]npranj (12/20/2012)[/b][hr]Would this help?WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms[/quote]As in, if you get a deadlock, ignore it and try again, repeat until successful? Sure, I wouldn't call it a solution though. [quote]And also if there are 6 threads spanning 70k records, will table partitioning be of help?[/quote]Maybe. Depends how you partition, how the queries are written, what data is needed, what granularity locks are taken, etcThu, 20 Dec 2012 10:17:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxWould this help?WAITFOR DELAY '00:00:00.05' -- Wait for 5 msAnd also if there are 6 threads spanning 70k records, will table partitioning be of help?Thu, 20 Dec 2012 10:11:46 GMTnpranjhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxTune the query (may include some rewriting)If that doesn't work, consider one of the snapshot isolation levelsThu, 20 Dec 2012 07:14:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398923-391-1.aspxHi,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 ENDNote - 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 RETRY2. Create table partitioning for the number of threads (6 threads) - but that will not remove the locks.Is there any other option?Thu, 20 Dec 2012 05:33:34 GMTnpranj