SQlL 2012 - stored proc - Deadlock issue

Question

SQlL 2012 - stored proc - Deadlock issue

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

matthew.green 36969 (6/12/2015)
fyi deadlock graph attached
the 2 conflicting statments in this instance are:
303: MarkAsCompleted 'C0239484'
192: MarkAsKO 'C0239494'
As such seems to be conflicting with another proc - im reviewing now

Thanks for the info. Next time, please post the deadlock XML and not a picture of the deadlock graph.

-- Gianluca Sartori

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 2

not sure where my deadlock post has gone with the graph attached (prehaps some sort of review)

anyway - graph showed it was conflicting with a similar proc (markascomplete) used the same code but with different variables - i have updated that proc using the same methodology as the mark KO - ffingers crossed

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 3

hmm - seems to still deadlock - normally against itself:

Deadlock graph150823671sa0X0116

2015-06-12 11:31:09.347<deadlock-list>

<process-list>

UPDATE tbl_main

SET inprogress = 0,

caseworked = 1,

success = 0,

Exception = 1,

ExceptionReason = @reason,

DateWorked = getdate(),

LastURL = @LastURL,

@RecordID = RecordID

WHERE lockedby = @Username; </frame>

MarkAsKO 'C0239486', 'KO - Perform IDV link didnt show expected page, most likely invalid data input error','http://sabb1.santanderuk.gs.corp/ACTRAB_ENS/BtoChannelDriver.ssobto?dse_operationName=OP_FlowAccountTransfActivationP2at&dse_parentContextName=&dse_processorState=initial&dse_nextEventName=start&taskId=lnxipe60p|SPP21ATR|ab258491|R|13031606|48490291|lnxipe60p|FULLFILM|0|22&navName=NAV1' </frame>

</executionStack>

MarkAsKO 'C0239486', 'KO - Perform IDV link didnt show expected page, most likely invalid data input error','urlt&taskId=lnxipe60p|SPP21ATR|ab258491|R|13031606|48490291|lnxipe60p|FULLFILM|0|22&navName=NAV1' </inputbuf>

</process>

UPDATE tbl_main

SET inprogress = 0,

caseworked = 1,

success = 0,

Exception = 1,

ExceptionReason = @reason,

DateWorked = getdate(),

LastURL = @LastURL,

@RecordID = RecordID

WHERE lockedby = @Username; </frame>

MarkAsKO 'C0239477', 'KO - Perform IDV link didnt show expected page, most likely invalid data input error','url&taskId=lnxipe60p|SPP21ATR|ab258512|R|13122709|48490615|lnxipe60p|FULLFILM|0|22&navName=NAV1' </frame>

</executionStack>

MarkAsKO 'C0239477', 'KO - Perform IDV link didnt show expected page, most likely invalid data input error','url&taskId=lnxipe60p|SPP21ATR|ab258512|R|13122709|48490615|lnxipe60p|FULLFILM|0|22&navName=NAV1' </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Table and index definitions please.

Do you have a unique index on LockedBy?

Why does the table not have a clustered index?

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Looks like you have no unique index on lockedby.

-- Gianluca Sartori

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 6

tbl is for a temp quick and dirty process I am running - max 90k records - default value for user id is '-'

as such no unique index, just added a clustered index (non unique) on username as will probably save a few seconds

fyi:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Tbl_main](

[RecordID] [bigint] IDENTITY(1,1) NOT NULL,

[Sortcode] [nchar](6) NOT NULL,

[AccountNumber] [nchar](8) NOT NULL,

[ProductType] [nchar](55) NOT NULL,

[DateImported] [datetime] NOT NULL,

[InProgress] [bit] NOT NULL,

[CaseWorked] [bit] NOT NULL,

[Success] [bit] NOT NULL,

[Exception] [bit] NOT NULL,

[ExceptionReason] [nvarchar](1024) NULL,

[DateWorked] [datetime] NULL,

[Locked] [bit] NOT NULL,

[LockedBy] [nvarchar](25) NOT NULL,

[LockedAt] [datetime] NULL,

[LastURL] [nvarchar](1024) NOT NULL,

[Initial_Prod] [nvarchar](255) NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DateImported] DEFAULT (getdate()) FOR [DateImported]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_InProgress] DEFAULT ((0)) FOR [InProgress]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_CaseWorked] DEFAULT ((0)) FOR [CaseWorked]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_Success] DEFAULT ((0)) FOR [Success]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_Exception] DEFAULT ((0)) FOR [Exception]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_ExceptionReason] DEFAULT (N'-') FOR [ExceptionReason]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Table_1_IsLocked] DEFAULT ((0)) FOR [Locked]

GO

ALTER TABLE [dbo].[Tbl_main] ADD CONSTRAINT [DF_Tbl_main_LockedBy] DEFAULT (N'-') FOR [LockedBy]

GO

ALTER TABLE [dbo].[Tbl_main] ADD DEFAULT ('-') FOR [LastURL]

GO

ALTER TABLE [dbo].[Tbl_main] ADD DEFAULT ('-') FOR [Initial_Prod]

GO

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 7

the uniqueness of that column is handled from my app - as you can see from deadlocks - it is always using different id's

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Put a unique nonclustered index on the LockedBy column.

It can be a filtered index since not all rows will be locked, and the default for a row that isn't locked should be NULL, not -

Firstly to help with the deadlocks, second because 'it's enforced by the app' is followed by 'why is my data wrong' in just about every case I've seen.

LockedBy is a very poor choice for a clustered index. It's not narrow, it's not ever-increasing and it's not unchanging. Almost any other place will likely be better.

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

GilaMonster (6/12/2015)
'it's enforced by the app' is followed by 'why is my data wrong' in just about every case I've seen.

If I had $0.01 for every time this happened to me, I would be answering SSC questions from my beach manor at the Maldives 😀

-- Gianluca Sartori

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 10

ya i got to that - going to rebuild using recordid as key as will be quicker than trying to work out what the hells happening here as am really scratching my head!!

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 11

rebuilt properly (using record id for all selects/updates/inserts) - and been running for a while with no deadlocks.... still dont understand what was causing them though as the ids were definetly unique, and had ran fine for last few days!....

thanks for the info guys, much appreciated

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

You knew the IDs were unique, SQL didn't so it had to take wider locks than it would have if you'd had a unique index there in the first place.

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

matthew.green 36969 SSC Veteran Points: 208 More actions · Answer 13

ta for the update - this is still running without issue