We have a table with an update trigger that we seem to be having unintended deadlock issues. The trigger, among other things, updates the same row that was updated to spawn the trigger. In our examples where we are encountering the deadlocks we are always doing single row updates on a unique clustered index.
Originally, we seemed to be having a lot of unnecessary lock escalation occurring to the page and table level. We added a lock hint to the table to prevent page locks (sp_indexoption 'osc_play.cylmas.uq_cylmas_barcod', 'disallowpagelocks',TRUE ). This has helped minimize the deadlocks on that table considerably.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Database locking issue
Hot Topic
- Delete data viewer error
- Bug or Feature?
- 32bit Admin tools when the only media you have is 64 bit
- Custom Component development questions
- About Login Account
- GetIsClustered() returns FALSE for Veritas Cluster Server
- Upgrading databases from beta 2 to rtm?
- Getting Error 1935 when trying to install SQLXML 3 SP3
- UNION with the second set ordered
- Data Source Views on top of AS400
SQL Server
- The SUM issue
- CLR trigger in another schema
- Report Service failed to start
- Create change scripts like Microsoft SQL Server Managment Studio
- Error 404 when opening reportserver
- Creating Columns as an array
- CLR Trigger to write file.......
- Subscription and email issues with Browser role.
- Visual Basic application return connection timed out
- Concurrent snapshot still locking tables for a long time
Database locking issue
Steve Pontello
Is there any reason why you are joining the inserted and deleted tables inside the trigger with the target table, CYLMAS, on two columns (mnfser AND mnfcod) that are neither indexed nor unique
JOIN DELETED D ON CYL.mnfser = D.mnfser AND CYL.mnfcod = D.mnfcod
JOIN INSERTED I ON D.mnfser = I.mnfser AND D.mnfcod = I.mnfcod
This results in a scan (rather than seek) of the CYLMAS table for the update inside the trigger, which in turn results in more U locks being acquired than necessary, which in turn leads to deadlocking.
Would it be feasible to join these tables based on the CIDENTITY identity column
JOIN DELETED D ON CYL.CIDENTITY = D.CIDENTITY
JOIN INSERTED I ON D.CIDENTITY = I.CIDENTITY
This would yield seeks instead of scans, hence improving performances, and also make the deadlocks disappear.
Thomas089
Thanks
rmicro1
Here is the info you requested to see Stefano... I do see there is a Table Spool within the update that the trigger executes; not sure what to make of it though (let me know if there is a different way to post it so it is more easily readable ). The table defintion follows after that... thanks for your rapid response!
(1 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------- -------- ------------------------------ -------- ------------------------
1 1 UPDATE [bobe].[cylmas] SET [mnfcod]=@1 WHERE [barcod]=@2 10 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 1.3755676E-2 NULL NULL UPDATE 0 NULL
1 1 |--Clustered Index Update(OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SET:([CYLMAS].[rowVersion]=[Expr1005], [CYLMAS].[MNFCOD]=RaiseIfNull([Expr1004]))) 10 2 1 Clustered Index Update Update OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SET:([CYLMAS].[rowVersion]=[Expr1005], [CYLMAS].[MNFCOD]=RaiseIfNull([Expr1004])) NULL 1.0 1.0471402E-2 0.000001 4 1.3755676E-2 NULL NULL PLAN_ROW 0 1.0
1 1 |--Top(1) 10 3 2 Top Top NULL NULL 1.0 0.0 0.0000001 47 3.2832751E-3 [Bmk1000], [Expr1004], [Expr1005] NULL PLAN_ROW 0 1.0
1 1 |--Compute Scalar(DEFINE:([Expr1004]=Convert([@1]), [Expr1005]=gettimestamp(29))) 10 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1004]=Convert([@1]), [Expr1005]=gettimestamp(29)) [Expr1004]=Convert([@1]), [Expr1005]=gettimestamp(29) 1.0 0.0 0.0000001 47 3.2831749E-3 [Bmk1000], [Expr1004], [Expr1005] NULL PLAN_ROW 0 1.0
1 1 |--Clustered Index Seek(OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SEEK:([CYLMAS].[BARCOD]=[@2]) ORDERED FORWARD) 10 5 4 Clustered Index Seek Clustered Index Seek OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SEEK:([CYLMAS].[BARCOD]=[@2]) ORDERED FORWARD [Bmk1000] 1.0 3.2034749E-3 7.9600002E-5 36 3.2830751E-3 [Bmk1000] NULL PLAN_ROW 0 1.0
(5 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ --------------------------- -------- ------------------------------ -------- ------------------------
1 1 IF (SELECT COUNT(*) FROM TIMSDATA31.BOBE.CYL_CONFIG WHERE cylmas_trgr = 1) = 0 11 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 3.7664268E-2 NULL NULL COND 0 NULL
1 1 |--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1002]=0) then 1 else 0)) 11 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1004]=If ([Expr1002]=0) then 1 else 0) [Expr1004]=If ([Expr1002]=0) then 1 else 0 1.0 0.0 0.0000001 11 3.7664268E-2 [Expr1004] NULL PLAN_ROW 0 1.0
1 1 |--Nested Loops(Inner Join) 11 3 2 Nested Loops Inner Join NULL NULL 1.0 0.0 4.1799999E-6 11 3.7664168E-2 [Expr1002] NULL PLAN_ROW 0 1.0
1 1 |--Constant Scan 11 4 3 Constant Scan Constant Scan NULL NULL 1.0 0.0 1.157E-6 4 1.157E-6 NULL NULL PLAN_ROW 0 1.0
1 1 |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1011]))) 11 5 3 Compute Scalar Compute Scalar DEFINE:([Expr1002]=Convert([Expr1011])) [Expr1002]=Convert([Expr1011]) 1.0 0.0 0.00000025 11 3.7658829E-2 [Expr1002] NULL PLAN_ROW 0 1.0
1 1 |--Stream Aggregate(DEFINE:([Expr1011]=Count(*))) 11 6 5 Stream Aggregate Aggregate NULL [Expr1011]=Count(*) 1.0 0.0 0.00000025 11 3.7658829E-2 [Expr1011] NULL PLAN_ROW 0 1.0
1 1 |--Clustered Index Scan(OBJECT:([TimsData31].[bobe].[CYL_CONFIG].[PK_CYL_CONFIG]), WHERE:([CYL_CONFIG].[CYLMAS_TRGR]=1)) 11 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([TimsData31].[bobe].[CYL_CONFIG].[PK_CYL_CONFIG]), WHERE:([CYL_CONFIG].[CYLMAS_TRGR]=1) [CYL_CONFIG].[CYLMAS_TRGR] 1.0 3.7578501E-2 7.9600002E-5 32 3.7658099E-2 [CYL_CONFIG].[CYLMAS_TRGR] NULL PLAN_ROW 0 1.0
(7 row(s) affected)
(7 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------
0 1 INSERT INTO TIMSDATA31.BOBE.CYLHSTRY
(barcod, mnfser, mnfcod, acqref, sup, part, loc, retest,
c_lot, c_cufdec, c_vol, truck, cusno, status, lstdat, cylbnk,
cylcom1, cylcom2, cphyloc, c_pucd, c_mtfi, fill_dt, dot_number,
orig_manf_dt, 12 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.1066002 NULL NULL INSERT 0 NULL
0 1 |--Clustered Index Insert(OBJECT:([TimsData31].[bobe].[CYLHSTRY].[PK_cylhstry]), SET:([CYLHSTRY].[VENNO_FILL]=D.[VENNO_FILL], [CYLHSTRY].[VENNO_OWND]=D.[VENNO_OWND], [CYLHSTRY].[CUSNO_OWND]=D.[CUSNO_OWND], [CYLHSTRY].[SEQNO]=D.[SEQNO], [CYLHSTRY] 12 2 1 Clustered Index Insert Insert OBJECT:([TimsData31].[bobe].[CYLHSTRY].[PK_cylhstry]), SET:([CYLHSTRY].[VENNO_FILL]=D.[VENNO_FILL], [CYLHSTRY].[VENNO_OWND]=D.[VENNO_OWND], [CYLHSTRY].[CUSNO_OWND]=D.[CUSNO_OWND], [CYLHSTRY].[SEQNO]=D.[SEQNO], [CYLHSTRY].[CSTATE]=D.[CSTATE], [CYL NULL 1.0 1.0532976E-2 0.000001 27 0.1066002 NULL NULL PLAN_ROW 0 1.0
0 1 |--Compute Scalar(DEFINE:([Expr1002]=getidentity(1221579390, 29, NULL), [Expr1003]=gettimestamp(29))) 12 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1002]=getidentity(1221579390, 29, NULL), [Expr1003]=gettimestamp(29)) [Expr1002]=getidentity(1221579390, 29, NULL), [Expr1003]=gettimestamp(29) 1.0 0.0 0.0000001 313 9.6066222E-2 D.[BARCOD], D.[MNFSER], D.[MNFCOD], D.[ACQREF], D.[SUP], D.[PART], D.[LOC], D.[RETEST], D.[C_LOT], D.[C_CUFDEC], D.[C_VOL], D.[TRUCK], D.[CUSNO], D.[STATUS], D.[LSTDAT], D.[CYLBNK], D.[CYLCOM1], D.[CYLCOM2], D.[CPHYLOC NULL PLAN_ROW 0 1.0
0 1 |--Nested Loops(Inner Join, WHERE:((I.[MNFSER]=D.[MNFSER] AND I.[MNFCOD]=D.[MNFCOD]) AND (((((((((((((D.[CSTATE]<>I.[CSTATE] OR D.[BARCOD]<>I.[BARCOD]) OR D.[MNFSER]<>I.[MNFSER]) OR D.[MNFCOD]<>I.[MNFCOD]) OR D.[SUP]<> 12 4 3 Nested Loops Inner Join WHERE:((I.[MNFSER]=D.[MNFSER] AND I.[MNFCOD]=D.[MNFCOD]) AND (((((((((((((D.[CSTATE]<>I.[CSTATE] OR D.[BARCOD]<>I.[BARCOD]) OR D.[MNFSER]<>I.[MNFSER]) OR D.[MNFCOD]<>I.[MNFCOD]) OR D.[SUP]<>I.[SUP]) OR D.[PART]<>I.[PART]) OR NULL 1.0 0.0 4.1799999E-6 470 9.6066117E-2 D.[BARCOD], D.[MNFSER], D.[MNFCOD], D.[ACQREF], D.[SUP], D.[PART], D.[LOC], D.[RETEST], D.[C_LOT], D.[C_CUFDEC], D.[C_VOL], D.[TRUCK], D.[CUSNO], D.[STATUS], D.[LSTDAT], D.[CYLBNK], D.[CYLCOM1], D.[CYLCOM2], D.[CPHYLOC NULL PLAN_ROW 0 1.0
1 1 |--Deleted Scan 12 5 4 Deleted Scan Deleted Scan OBJECT:([TimsData31].[bobe].[CYLMAS] AS D) NULL 1.0 4.7948871E-2 7.9600002E-5 305 4.8028469E-2 D.[BARCOD], D.[MNFSER], D.[MNFCOD], D.[ACQREF], D.[SUP], D.[PART], D.[LOC], D.[RETEST], D.[C_LOT], D.[C_CUFDEC], D.[C_VOL], D.[TRUCK], D.[CUSNO], D.[STATUS], D.[LSTDAT], D.[CYLBNK], D.[CYLCOM1], D.[CYLCOM2], D.[CPHYLOC NULL PLAN_ROW 0 1.0
1 1 |--Inserted Scan(OBJECT:([TimsData31].[bobe].[CYLMAS] AS I)) 12 6 4 Inserted Scan Inserted Scan OBJECT:([TimsData31].[bobe].[CYLMAS] AS I) NULL 1.0 4.7948871E-2 7.9600002E-5 174 4.8028469E-2 I.[CSOURCE], I.[CONTESTED_DT], I.[CONTESTED], I.[VENNO_OWND], I.[CUSNO_OWND], I.[OWNRSHP], I.[CUSNO], I.[LOC], I.[PART], I.[SUP], I.[MNFCOD], I.[MNFSER], I.[BARCOD], I.[CSTATE] NULL PLAN_ROW 0 1.0
(6 row(s) affected)
(6 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------
0 1 UPDATE CYL
SET seqno = D.seqno + 1,
lstdat = @year * 10000 + (@month*100) + @day,
trnxn_time = @hour * 10000 + (@minute*100) + @secs,
except_type = 0,
except_choice = 0,
cusno = CASE
13 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.17960025 NULL NULL UPDATE 0 NULL
0 1 |--Clustered Index Update(OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SET:([CYLMAS].[EXCEPT_CHOICE]=RaiseIfNull(0), [CYLMAS].[EXCEPT_TYPE]=RaiseIfNull(0), [CYLMAS].[rowVersion]=[Expr1012], [CYLMAS].[SEQNO]=RaiseIfNull([Expr1005]), [CYLMAS]. 13 2 1 Clustered Index Update Update OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD]), SET:([CYLMAS].[EXCEPT_CHOICE]=RaiseIfNull(0), [CYLMAS].[EXCEPT_TYPE]=RaiseIfNull(0), [CYLMAS].[rowVersion]=[Expr1012], [CYLMAS].[SEQNO]=RaiseIfNull([Expr1005]), [CYLMAS].[CONTESTED]=RaiseIfNull([Exp NULL 1.0 1.0471402E-2 0.000001 63 0.17960025 NULL NULL PLAN_ROW 0 1.0
0 1 |--Compute Scalar(DEFINE:([Expr1005]=Convert(D.[SEQNO]+1), [ConstExpr1017]=Convert([@year]*10000+[@month]*100+[@day]), [ConstExpr1018]=Convert([@hour]*10000+[@minute]*100+[@secs]), [Expr1010]=If (I.[STATUS]<>3 AND I.[STATUS]<>4) then ' ' e 13 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1005]=Convert(D.[SEQNO]+1), [ConstExpr1017]=Convert([@year]*10000+[@month]*100+[@day]), [ConstExpr1018]=Convert([@hour]*10000+[@minute]*100+[@secs]), [Expr1010]=If (I.[STATUS]<>3 AND I.[STATUS]<>4) then ' ' else I.[CUSNO], [Expr101 [Expr1005]=Convert(D.[SEQNO]+1), [ConstExpr1017]=Convert([@year]*10000+[@month]*100+[@day]), [ConstExpr1018]=Convert([@hour]*10000+[@minute]*100+[@secs]), [Expr1010]=If (I.[STATUS]<>3 AND I.[STATUS]<>4) then ' ' else I.[CUSNO], [Expr1011]=If (( 1.0 0.0 0.0000001 84 0.16912785 [Bmk1000], [Expr1005], [ConstExpr1017], [ConstExpr1018], [Expr1008], [Expr1009], [Expr1010], [Expr1011], [Expr1012] NULL PLAN_ROW 0 1.0
0 1 |--Table Spool 13 4 3 Table Spool Eager Spool NULL NULL 1.0 2.3747498E-2 9.5999997E-7 65 0.16912775 [Bmk1000], D.[SEQNO], D.[STATUS], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
0 1 |--Top(ROWCOUNT est 0) 13 5 4 Top Top NULL NULL 1.0 0.0 0.0000001 674 0.14537929 [Bmk1000], D.[SEQNO], D.[STATUS], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
0 1 |--Nested Loops(Inner Join, WHERE:([CYL].[MNFSER]=I.[MNFSER] AND [CYL].[MNFCOD]=I.[MNFCOD])) 13 6 5 Nested Loops Inner Join WHERE:([CYL].[MNFSER]=I.[MNFSER] AND [CYL].[MNFCOD]=I.[MNFCOD]) NULL 1.0 0.0 8.9869997E-4 674 0.14537919 [Bmk1000], D.[SEQNO], D.[STATUS], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
0 1 |--Nested Loops(Inner Join, WHERE:((D.[MNFSER]=I.[MNFSER] AND D.[MNFCOD]=I.[MNFCOD]) AND (((((((((((((I.[CSTATE]<>D.[CSTATE] OR I.[BARCOD]<>D.[BARCOD]) OR I.[MNFSER]<>D.[MNFSER]) OR I.[MNFCOD]<>D.[MNFCOD]) 13 7 6 Nested Loops Inner Join WHERE:((D.[MNFSER]=I.[MNFSER] AND D.[MNFCOD]=I.[MNFCOD]) AND (((((((((((((I.[CSTATE]<>D.[CSTATE] OR I.[BARCOD]<>D.[BARCOD]) OR I.[MNFSER]<>D.[MNFSER]) OR I.[MNFCOD]<>D.[MNFCOD]) OR I.[SUP]<>D.[SUP]) OR I.[PART]<>D.[PART]) OR NULL 1.0 0.0 4.1799999E-6 339 9.6066117E-2 D.[SEQNO], D.[STATUS], I.[MNFCOD], I.[MNFSER], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
1 1 | |--Deleted Scan 13 8 7 Deleted Scan Deleted Scan OBJECT:([TimsData31].[bobe].[CYLMAS] AS D) NULL 1.0 4.7948871E-2 7.9600002E-5 174 4.8028469E-2 D.[CSOURCE], D.[CONTESTED_DT], D.[CONTESTED], D.[VENNO_OWND], D.[CUSNO_OWND], D.[OWNRSHP], D.[CUSNO], D.[LOC], D.[PART], D.[SUP], D.[MNFCOD], D.[MNFSER], D.[BARCOD], D.[CSTATE], D.[SEQNO], D.[STATUS] NULL PLAN_ROW 0 1.0
1 1 &nb