Deadlocks on Clustered Index

Question

Deadlocks on Clustered Index

dopydb

SSCertifiable

Points: 6687
More actions
April 13, 2017 at 5:48 am

#322960

hi all
i have a very simple control table with 1 record, this record manages number increments for various ranges such as sales order, invoices no credit number etc, so its a very heavily accessed and updated table, which leads to it being a table where i get lots of deadlocks where various people are trying to update at the one time.
the table has one Clustered index, which has only 1 column - the company branch that everyone uses to update..
i cant add fields or create new tables, only index, and id rather not touch the clustered index so is there anything else i can do to lift the congestion here?
thanks
mal

Viewing 15 posts - 1 through 15 (of 38 total)

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

DesNorton SSC-Insane Points: 24278 More actions · Answer 1

dopydb - Thursday, April 13, 2017 5:48 AM
hi all
i have a very simple control table with 1 record, this record manages number increments for various ranges such as sales order, invoices no credit number etc, so its a very heavily accessed and updated table, which leads to it being a table where i get lots of deadlocks where various people are trying to update at the one time.
the table has one Clustered index, which has only 1 column - the company branch that everyone uses to update..
i cant add fields or create new tables, only index, and id rather not touch the clustered index so is there anything else i can do to lift the congestion here?
thanks
mal

If the updates are done within transactions, you could get better perf by ensuring that the transactions are as short as possible (no OPEN TRAN ... Loop ... COMMIT).

How to post data/code on a forum to get the best help.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

dopydb - Thursday, April 13, 2017 5:48 AM
hi all
i have a very simple control table with 1 record, this record manages number increments for various ranges such as sales order, invoices no credit number etc, so its a very heavily accessed and updated table, which leads to it being a table where i get lots of deadlocks where various people are trying to update at the one time.
the table has one Clustered index, which has only 1 column - the company branch that everyone uses to update..
i cant add fields or create new tables, only index, and id rather not touch the clustered index so is there anything else i can do to lift the congestion here?
thanks
mal

I take it upgrading to SQL2014 and making this table an in-memory table isn't an option...?

Thomas Rushton
blog: https://thelonedba.wordpress.com

dopydb SSCertifiable Points: 6687 More actions · Answer 3

i cant change the code Des (its core ERP)

and i wish Thomas 🙂 - it will be going at the end of next year, but thats a bit of pain until then!

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

Can you post the deadlock graph?

A deadlock has to be two sessions (or more) and two resources (or more), so there's something else involved.

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

dopydb SSCertifiable Points: 6687 More actions · Answer 5

dopydb

SSCertifiable

Points: 6687

April 14, 2017 at 3:36 am

#1938147

hi Gila, do you mean this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Not a picture of the deadlock graph. The graph itself, either saved as a file (.xdl) or paste the text of it.

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

dopydb SSCertifiable Points: 6687 More actions · Answer 7

hi Gail, i dont have a profile of it, this any use?

dopydb SSCertifiable Points: 6687 More actions · Answer 8

hold on maybe i have the .xdl 🙂 learning all the time!

dopydb SSCertifiable Points: 6687 More actions · Answer 9

wouldnt let me upload .xdl

but this is the txt

<deadlock victim="process26033d38"><process-list><process id="process26033d38" taskpriority="0" logused="0" waitresource="KEY: 17:72057655109550080 (61001edaa25e)" waittime="156" ownerId="1733094732" transactionname="implicit_transaction" lasttranstarted="2017-04-13T15:34:45.533" XDES="0x210e38d0" lockMode="X" schedulerid="1" kpid="11544" status="suspended" spid="165" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2017-04-13T15:34:47.097" lastbatchcompleted="2017-04-13T15:34:47.097" clientapp="SYSPRO" hostname="GRNAPP001" hostpid="7420" loginname="SYSPRO" isolationlevel="read committed (2)" xactid="1733094732" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058" databaseName="SysproCompanyW"><executionStack><frame procname="adhoc" line="1" stmtstart="766" sqlhandle="0x02000000ed67481a666568bf2e641fb42f06a7c79493d93b">
UPDATE SysproCompanyW..SalBranch SET Branch = @P1,Description = @P2,BranchAddr1 = @P3,BranchAddr2 = @P4,BranchAddr3 = @P5,FrtCosAcc = @P6,SalesCtlAcc = @P7,CosCtlAcc = @P8,FrtSalesAcc = @P9,FinChargeAcc = @P10,RoundingAcc = @P11,SalesTaxAcc = @P12,FstAcc = @P13,DepositAcc = @P14,PrefixNextInv = @P15,NextInvoice = @P16,NextCreditNote = @P17,NextDebitNote = @P18,PrefixNextSo = @P19,NextSalesOrder = @P20,NextSoCrNote = @P21,NextSoDbNote = @P22,NextDelNote = @P23,PrefixNextRma = @P24,NextRma = @P25,Nationality = @P26 WHERE Branch = '10 ' </frame><frame procname="mssqlsystemresource.sys.sp_execute" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000">
sp_execute </frame></executionStack><inputbuf>
(@P1 char(2),@P2 char(30),@P3 char(30),@P4 char(20),@P5 char(20),@P6 char(15),@P7 char(15),@P8 char(15),@P9 char(15),@P10 char(15),@P11 char(15),@P12 char(15),@P13 char(15),@P14 char(15),@P15 char(1),@P16 numeric(6,0),@P17 numeric(6,0),@P18 numeric(6,0),@P19 char(1),@P20 numeric(6,0),@P21 numeric(6,0),@P22 numeric(6,0),@P23 numeric(6,0),@P24 char(1),@P25 numeric(8,0),@P26 char(3))UPDATE SysproCompanyW..SalBranch SET Branch = @P1,Description = @P2,BranchAddr1 = @P3,BranchAddr2 = @P4,BranchAddr3 = @P5,FrtCosAcc = @P6,SalesCtlAcc = @P7,CosCtlAcc = @P8,FrtSalesAcc = @P9,FinChargeAcc = @P10,RoundingAcc = @P11,SalesTaxAcc = @P12,FstAcc = @P13,DepositAcc = @P14,PrefixNextInv = @P15,NextInvoice = @P16,NextCreditNote = @P17,NextDebitNote = @P18,PrefixNextSo = @P19,NextSalesOrder = @P20,NextSoCrNote = @P21,NextSoDbNote = @P22,NextDelNote = @P23,PrefixNextRma = @P24,NextRma = @P25,Nationality = @P26 WHERE Branch = '10 ' </inputbuf></process><process id="process2a0d5108" taskpriority="0" logused="10000" waitresource="KEY: 17:72057655109550080 (61001edaa25e)" waittime="8171" schedulerid="4" kpid="13332" status="suspended" spid="309" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2017-04-13T15:35:00.940" lastbatchcompleted="2017-04-13T15:35:00.940" clientapp="SYSPRO" hostname="GRNAPP001" hostpid="4972" loginname="SYSPRO" isolationlevel="read committed (2)" xactid="1733203993" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058" databaseName="SysproCompanyW"><executionStack><frame procname="adhoc" line="1" sqlhandle="0x02000000cb1f513bb01353d98c5bda57f29abb38e53a3498">
FETCH API_CURSOR0000000000AE436E </frame><frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
FETCH API_CURSOR0000000000AE436E </inputbuf></process></process-list><resource-list><keylock hobtid="72057655109550080" dbid="17" objectname="SysproCompanyW.dbo.SalBranch" indexname="SalBranchKey" id="lock5b55c240" mode="U" associatedObjectId="72057655109550080"><owner-list><owner id="process26033d38" mode="U" /><owner id="process2a0d5108" mode="S" /></owner-list><waiter-list><waiter id="process26033d38" mode="X" requestType="convert" /><waiter id="process2a0d5108" mode="U" requestType="wait" /></waiter-list></keylock></resource-list></deadlock>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Can you please post the CREATE TABLE for SalBranch, and the CREATE INDEX statement for each index on it.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Just a dumb question, but could this be a result of the Branch column being updated when it is also the column used in the WHERE clause? Also, didn't the OP state that there is only one row of data in the table?

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

Lynn Pettis - Friday, April 14, 2017 2:56 PM
Just a dumb question, but could this be a result of the Branch column being updated when it is also the column used in the WHERE clause?

No, that won't cause a deadlock alone.

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

dopydb SSCertifiable Points: 6687 More actions · Answer 13

GilaMonster - Friday, April 14, 2017 1:45 PM
Can you please post the CREATE TABLE for SalBranch, and the CREATE INDEX statement for each index on it.

USE [SysproCompanyW]
GO

/****** Object: Table [dbo].[SalBranch] Script Date: 17/04/2017 09:40:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SalBranch](
    [Branch] [char](2) NOT NULL,
    [Description] [char](30) NULL,
    [BranchAddr1] [char](30) NULL,
    [BranchAddr2] [char](20) NULL,
    [BranchAddr3] [char](20) NULL,
    [FrtCosAcc] [char](15) NULL,
    [SalesCtlAcc] [char](15) NULL,
    [CosCtlAcc] [char](15) NULL,
    [FrtSalesAcc] [char](15) NULL,
    [FinChargeAcc] [char](15) NULL,
    [RoundingAcc] [char](15) NULL,
    [SalesTaxAcc] [char](15) NULL,
    [FstAcc] [char](15) NULL,
    [DepositAcc] [char](15) NULL,
    [PrefixNextInv] [char](1) NULL,
    [NextInvoice] [decimal](6, 0) NULL,
    [NextCreditNote] [decimal](6, 0) NULL,
    [NextDebitNote] [decimal](6, 0) NULL,
    [PrefixNextSo] [char](1) NULL,
    [NextSalesOrder] [decimal](6, 0) NULL,
    [NextSoCrNote] [decimal](6, 0) NULL,
    [NextSoDbNote] [decimal](6, 0) NULL,
    [NextDelNote] [decimal](6, 0) NULL,
    [PrefixNextRma] [char](1) NULL,
    [NextRma] [decimal](8, 0) NULL,
    [TimeStamp] [timestamp] NULL,
    [Nationality] [char](3) NULL,
CONSTRAINT [SalBranchKey] PRIMARY KEY CLUSTERED
(
    [Branch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

dopydb SSCertifiable Points: 6687 More actions · Answer 14

USE [SysproCompanyW]
GO

/****** Object: Index [SalBranchKey] Script Date: 17/04/2017 09:41:33 ******/
ALTER TABLE [dbo].[SalBranch] ADD CONSTRAINT [SalBranchKey] PRIMARY KEY CLUSTERED
(
[Branch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO