I'd like to create a trigger on a SQL Server 2000 table that operates if the table is in a particular database, but not in others. How do I query for the database name?
Thanks.
DB_NAME() will give you the database name but since you create a trigger on tables I don't see how this is usefull?
when you create a trigger on table abc you have to be in a DB already so you already know the name on creation
Denis the SQL Menace
http://sqlservercode.blogspot.com/
I wanted to confirm that when using Database Mirroring that DDL Updates such as table and stored procedure updates on the principle server are replicated to the mirror server.
I thought I read that non-logged operations would not be replicated to the mirror server. What would be some examples of that. At the moment...you would think every entry in the the database would be saved into a table and that would be replicated.
...cordell...
Hi Cordell. Any logged operations are shipped to the mirror for replay, which includes standard database DDL (alter, create, etc.). For all intents and purposes, think of mirroring as being real-time log shipping...anything that can be recovered in a database/log restore of a backup will be replayed at the mirror.
The terem "non-logged operation" is a bit misleading really, because Sql always writes log records so changes are recoverable, it can just perform minimal-logged operations in appropriate scenarios. Without getting into that however, given that the recovery model for the principal database must be 'full' for mirroring to work, you won't be able to perform minimally-logged operations anyhow.
HTH,
|||http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx in addition to explanation above.I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.
I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?
Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.
DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email
EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'
I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.
I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?
Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.
DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email
EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'
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.
By the way, we don't escalate locks to page level. It is the query execution that decides the locking granularity.
Thanks|||I also would be interested in the plans for the queries inside the tigger, particularly the one causing the deadlock. Best thing is to run "SET STATISTICS PROFILE ON" before running the DML statement firing the trigger.
Thanks|||
Thanks for the inquiring back Sunil and Stefano.
Here's the log output that we get in SQL Server's Errorlog file with TF-1204 enabled:
>> First deadlock:
Deadlock encountered .... Printing deadlock information
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Wait-for graph
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Node:1
2005-11-09 08:15:34.68 spid4 KEY: 8:1251535542:1 (87008ebae3cf) CleanCnt:1 Mode: U Flags: 0x0
2005-11-09 08:15:34.68 spid4 Grant List 0::
2005-11-09 08:15:34.68 spid4 Owner:0x2baf32a0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:1010 ECID:0
2005-11-09 08:15:34.68 spid4 SPID: 1010 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-09 08:15:34.68 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.CYLMAS
SET cuser = 'SJW' , sup= 'OXY' , part = '249 ' ,loc = ' 1' , cstate = 'TK ' , sta
tus = 5 , csource = 2 , venno_fill = ' ' , c_lot = '052305 ' , truck = 122 , retest = 0 , s
end_vend =
2005-11-09 08:15:34.68 spid4 Requested By:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:961 ECID:0 Ec:(0x611C99B0) Value:0x57f9e560 Cost:(0/2E4)
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Node:2
2005-11-09 08:15:34.68 spid4 KEY: 8:1251535542:1 (87003aee4b12) CleanCnt:1 Mode: X Flags: 0x0
2005-11-09 08:15:34.68 spid4 Grant List 3::
2005-11-09 08:15:34.68 spid4 Owner:0x2baf2dc0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:961 ECID:0
2005-11-09 08:15:34.68 spid4 SPID: 961 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-09 08:15:34.68 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET cuser = 'TN ' , sup=
'ARG' , part = '336 ' ,loc = ' 1' , cstate = 'TK ' , status = 5 , csource = 2 , venno
_fill = ' ' , c_lot = '052202 ' , truck = 124 , retest = 0 , send_vend =
2005-11-09 08:15:34.68 spid4 Requested By:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1010 ECID:0 Ec:(0x676599B0) Value:0x6f5844c0 Cost:(0/498)
2005-11-09 08:15:34.68 spid4 Victim Resource Owner:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:961 ECID:0 Ec:(0x611C99B0) Value:0x57f9e560 Cost:(0/2E4)
>> Another deadlock:
Deadlock encountered .... Printing deadlock information
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Wait-for graph
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Node:1
2005-11-10 14:54:17.55 spid4 KEY: 8:1251535542:1 (830061471e7b) CleanCnt:1 Mode: X Flags: 0x0
2005-11-10 14:54:17.55 spid4 Grant List 3::
2005-11-10 14:54:17.55 spid4 Owner:0x6f386580 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:61 ECID:0
2005-11-10 14:54:17.55 spid4 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-10 14:54:17.55 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET mnfser = '1010 ' , mnfcod = ' ' , acqref = 0 , sup= 'OXY' , part = '124 ' , loc = ' 1' , retest = 0 , c_lot = ' ' , c_cufdec = 0 , c_vol = 0 , truck = 0 , cu
2005-11-10 14:54:17.55 spid4 Requested By:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:837 ECID:0 Ec:(0x29B4F9A8) Value:0x2b6edd80 Cost:(0/438)
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Node:2
2005-11-10 14:54:17.55 spid4 KEY: 8:1251535542:1 (87008ebae3cf) CleanCnt:1 Mode: U Flags: 0x0
2005-11-10 14:54:17.55 spid4 Grant List 1::
2005-11-10 14:54:17.55 spid4 Owner:0x58590ee0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:837 ECID:0
2005-11-10 14:54:17.55 spid4 SPID: 837 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-10 14:54:17.55 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET sup = 'OXY' , part = '249CO ' , loc = ' 1' , cusno = '03679' , cstate = 'CS ' , status = 3 , csource = 3 , cuser = '*H*' , truck = 0, cusno_ownd = CASE WHEN ownrshp = 1 AND cusno_ownd = ' ' THEN '03679
2005-11-10 14:54:17.55 spid4 Requested By:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x5A20B9B8) Value:0x6f386b20 Cost:(0/18B8)
2005-11-10 14:54:17.55 spid4 Victim Resource Owner:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:837 ECID:0 Ec:(0x29B4F9A8) Value:0x2b6edd80 Cost:(0/438)
In both cases, it seems that the deadlock is occurring on an update... and by the looks of the columns it is updating, this update is not the one defined within the trigger. Here is that trigger definition:
CREATE TRIGGER trig1
ON DATABASE31.OWNER.TABLE_1
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM DATABASE31.OWNER.TABLE_3 WHERE cylmas_trgr = 1) = 0
GOTO SKIP_TRGR
DECLARE
@.year decimal(4),
@.month decimal(2),
@.day decimal(2),
@.hour decimal(2),
@.minute decimal(2),
@.secs decimal(2)
SELECT
@.year =DATEPART(yyyy, getdate()),
@.month =DATEPART(mm, getdate()),
@.day =DATEPART(dd, getdate()),
@.hour =DATEPART(hh, getdate()),
@.minute =DATEPART(mi, getdate()),
@.secs =DATEPART(ss, getdate())
INSERT INTO DATABASE31.OWNER.TABLE_2
(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, plus, star, code, cus_po, cstate, ownrshp, seqno,
trnxn_time, csource, cusno_ownd, venno_ownd, venno_fill, send_vend,
recv_vend, cuser, contested, contested_dt, except_type, except_choice)
SELECT 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, D.c_pucd, D.c_mtfi, D.fill_dt, D.dot_number,
D.orig_manf_dt, D.plus, D.star, D.code, D.cus_po, D.cstate, D.ownrshp, D.seqno,
D.trnxn_time, D.csource, D.cusno_ownd, D.venno_ownd, D.venno_fill, D.send_vend,
D.recv_vend, D.cuser, D.contested, D.contested_dt, D.except_type, D.except_choice
FROM DELETED D
JOIN INSERTED I ON D.mnfser = I.mnfser AND D.mnfcod = I.mnfcod
WHERE 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 D.loc <> I.loc OR D.cusno <> I.cusno OR D.ownrshp <> I.ownrshp
OR D.cusno_ownd <> I.cusno_ownd OR D.venno_ownd <> I.venno_ownd
OR D.contested <> I.contested OR D.contested_dt <> I.contested_dt
OR D.csource <> I.csource
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
WHEN I.status <> 3 AND I.status <> 4
THEN ' '
ELSE I.cusno
END,
contested = CASE
WHEN (D.status = 3 OR D.status = 4)
AND (I.status <> 3 AND I.status <> 4)
THEN 0
ELSE I.contested
END
FROM DATABASE31.OWNER.TABLE_1 CYL
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
WHERE 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 I.loc <> D.loc OR I.cusno <> D.cusno OR I.ownrshp <> D.ownrshp
OR I.cusno_ownd <> D.cusno_ownd OR I.venno_ownd <> D.venno_ownd
OR I.contested <> D.contested OR I.contested_dt <> D.contested_dt
OR I.csource <> D.csource
SKIP_TRGR:
END
If you guys need anything else, just let me know, and thanks again for your assistance!
UPDATE TIMSDATA.OSC.CYLMAS SET mnfser = '1010 ' , mnfcod = ' ' , acqref = 0 , sup= 'OXY' , part = '124 ' , loc = ' 1' , retest = 0 , c_lot = ' ' , c_cufdec = 0 , c_vol = 0 , truck = 0 , cu...
You don't need to reproduce the deadlock - please just run the update so we can see the query plan both for the update itself and the statements inside the trigger.
Please also provide the table definition (both columns and indexes) for the TIMSDATA31.BOBE.CYLMAS table.
Since there are U locks involved in your deadlock graph, most likely one of your update query plans (either the one firing the trigger the trigger, or, most likely, the one inside the trigger) contains a spool. With the index definitions, it might be possible to tell why and see if we can get rid of it.
|||
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 | |--Inserted Scan(OBJECT:([TimsData31].[bobe].[CYLMAS] AS I)) 13 9 7 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.[VENNO_OWND], I.[CUSNO_OWND], I.[OWNRSHP], I.[LOC], I.[PART], I.[SUP], I.[MNFCOD], I.[MNFSER], I.[BARCOD], I.[CSTATE], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Scan(OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD] AS [CYL])) 13 73 6 Clustered Index Scan Clustered Index Scan OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD] AS [CYL]) [Bmk1000], [CYL].[MNFCOD], [CYL].[MNFSER] 215.0 4.7948871E-2 3.1500001E-4 343 0.04826387 [Bmk1000], [CYL].[MNFCOD], [CYL].[MNFSER] NULL PLAN_ROW 0 1.0
>> Table definition:
create table CYLMAS
(
BARCOD varchar(24) not null
,MNFSER varchar(24) not null
,MNFCOD char(3) not null
,ACQREF decimal(4) not null
,SUP char(3) not null
,PART varchar(25) not null
,LOC char(3) not null
,RETEST decimal(8) not null
,C_LOT varchar(25) not null
,C_CUFDEC decimal(1) not null
,C_VOL decimal(12,3) not null
,TRUCK decimal(5) not null
,CUSNO char(5) not null
,STATUS decimal(1) not null
,LSTDAT decimal(8) not null
,CYLBNK decimal(2) not null
,CYLCOM1 varchar(32) not null
,CYLCOM2 varchar(32) not null
,CPHYLOC varchar(10) not null
,C_PUCD decimal(1) not null
,C_MTFI decimal(1) not null
,FILL_DT decimal(8) not null
,DOT_NUMBER varchar(15) not null
,ORIG_MANF_DT decimal(6) not null
,PLUS decimal(1) not null
,STAR decimal(1) not null
,CODE char(1) not null
,CUS_PO varchar(22) not null
,CSTATE char(5) not null
,OWNRSHP decimal(1) not null
,SEQNO decimal(12) not null
,TRNXN_TIME decimal(6) not null
,CSOURCE decimal(3) not null
,CUSNO_OWND char(5) not null
,VENNO_OWND char(4) not null
,VENNO_FILL char(4) not null
,SEND_VEND decimal(8) not null
,RECV_VEND decimal(8) not null
,CUSER char(3) not null
,CONTESTED decimal(1) not null
,CONTESTED_DT decimal(8) not null
,cidentity decimal(18) identity
,rowversion timestamp
,EXCEPT_TYPE decimal(3) not null
,EXCEPT_CHOICE decimal(3) not null
,CONSTRAINT PK_CYLMAS PRIMARY KEY NONCLUSTERED (CIDENTITY)
,CONSTRAINT UQ_CYLMAS_BARCOD UNIQUE CLUSTERED (BARCOD)
);
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.|||I hope you did not post the exact names of the objects of your database in public. As we have known, this can be a threat to your database security.
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.
By the way, we don't escalate locks to page level. It is the query execution that decides the locking granularity.
Thanks|||I also would be interested in the plans for the queries inside the tigger, particularly the one causing the deadlock. Best thing is to run "SET STATISTICS PROFILE ON" before running the DML statement firing the trigger.
Thanks|||
Thanks for the inquiring back Sunil and Stefano.
Here's the log output that we get in SQL Server's Errorlog file with TF-1204 enabled:
>> First deadlock:
Deadlock encountered .... Printing deadlock information
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Wait-for graph
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Node:1
2005-11-09 08:15:34.68 spid4 KEY: 8:1251535542:1 (87008ebae3cf) CleanCnt:1 Mode: U Flags: 0x0
2005-11-09 08:15:34.68 spid4 Grant List 0::
2005-11-09 08:15:34.68 spid4 Owner:0x2baf32a0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:1010 ECID:0
2005-11-09 08:15:34.68 spid4 SPID: 1010 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-09 08:15:34.68 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.CYLMAS
SET cuser = 'SJW' , sup= 'OXY' , part = '249 ' ,loc = ' 1' , cstate = 'TK ' , sta
tus = 5 , csource = 2 , venno_fill = ' ' , c_lot = '052305 ' , truck = 122 , retest = 0 , s
end_vend =
2005-11-09 08:15:34.68 spid4 Requested By:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:961 ECID:0 Ec:(0x611C99B0) Value:0x57f9e560 Cost:(0/2E4)
2005-11-09 08:15:34.68 spid4
2005-11-09 08:15:34.68 spid4 Node:2
2005-11-09 08:15:34.68 spid4 KEY: 8:1251535542:1 (87003aee4b12) CleanCnt:1 Mode: X Flags: 0x0
2005-11-09 08:15:34.68 spid4 Grant List 3::
2005-11-09 08:15:34.68 spid4 Owner:0x2baf2dc0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:961 ECID:0
2005-11-09 08:15:34.68 spid4 SPID: 961 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-09 08:15:34.68 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET cuser = 'TN ' , sup=
'ARG' , part = '336 ' ,loc = ' 1' , cstate = 'TK ' , status = 5 , csource = 2 , venno
_fill = ' ' , c_lot = '052202 ' , truck = 124 , retest = 0 , send_vend =
2005-11-09 08:15:34.68 spid4 Requested By:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1010 ECID:0 Ec:(0x676599B0) Value:0x6f5844c0 Cost:(0/498)
2005-11-09 08:15:34.68 spid4 Victim Resource Owner:
2005-11-09 08:15:34.68 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:961 ECID:0 Ec:(0x611C99B0) Value:0x57f9e560 Cost:(0/2E4)
>> Another deadlock:
Deadlock encountered .... Printing deadlock information
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Wait-for graph
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Node:1
2005-11-10 14:54:17.55 spid4 KEY: 8:1251535542:1 (830061471e7b) CleanCnt:1 Mode: X Flags: 0x0
2005-11-10 14:54:17.55 spid4 Grant List 3::
2005-11-10 14:54:17.55 spid4 Owner:0x6f386580 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:61 ECID:0
2005-11-10 14:54:17.55 spid4 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-10 14:54:17.55 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET mnfser = '1010 ' , mnfcod = ' ' , acqref = 0 , sup= 'OXY' , part = '124 ' , loc = ' 1' , retest = 0 , c_lot = ' ' , c_cufdec = 0 , c_vol = 0 , truck = 0 , cu
2005-11-10 14:54:17.55 spid4 Requested By:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:837 ECID:0 Ec:(0x29B4F9A8) Value:0x2b6edd80 Cost:(0/438)
2005-11-10 14:54:17.55 spid4
2005-11-10 14:54:17.55 spid4 Node:2
2005-11-10 14:54:17.55 spid4 KEY: 8:1251535542:1 (87008ebae3cf) CleanCnt:1 Mode: U Flags: 0x0
2005-11-10 14:54:17.55 spid4 Grant List 1::
2005-11-10 14:54:17.55 spid4 Owner:0x58590ee0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:837 ECID:0
2005-11-10 14:54:17.55 spid4 SPID: 837 ECID: 0 Statement Type: UPDATE Line #: 132
2005-11-10 14:54:17.55 spid4 Input Buf: Language Event: UPDATE DATABASE.OWNER.TABLE_1 SET sup = 'OXY' , part = '249CO ' , loc = ' 1' , cusno = '03679' , cstate = 'CS ' , status = 3 , csource = 3 , cuser = '*H*' , truck = 0, cusno_ownd = CASE WHEN ownrshp = 1 AND cusno_ownd = ' ' THEN '03679
2005-11-10 14:54:17.55 spid4 Requested By:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x5A20B9B8) Value:0x6f386b20 Cost:(0/18B8)
2005-11-10 14:54:17.55 spid4 Victim Resource Owner:
2005-11-10 14:54:17.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:837 ECID:0 Ec:(0x29B4F9A8) Value:0x2b6edd80 Cost:(0/438)
In both cases, it seems that the deadlock is occurring on an update... and by the looks of the columns it is updating, this update is not the one defined within the trigger. Here is that trigger definition:
CREATE TRIGGER trig1
ON DATABASE31.OWNER.TABLE_1
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM DATABASE31.OWNER.TABLE_3 WHERE cylmas_trgr = 1) = 0
GOTO SKIP_TRGR
DECLARE
@.year decimal(4),
@.month decimal(2),
@.day decimal(2),
@.hour decimal(2),
@.minute decimal(2),
@.secs decimal(2)
SELECT
@.year =DATEPART(yyyy, getdate()),
@.month =DATEPART(mm, getdate()),
@.day =DATEPART(dd, getdate()),
@.hour =DATEPART(hh, getdate()),
@.minute =DATEPART(mi, getdate()),
@.secs =DATEPART(ss, getdate())
INSERT INTO DATABASE31.OWNER.TABLE_2
(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, plus, star, code, cus_po, cstate, ownrshp, seqno,
trnxn_time, csource, cusno_ownd, venno_ownd, venno_fill, send_vend,
recv_vend, cuser, contested, contested_dt, except_type, except_choice)
SELECT 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, D.c_pucd, D.c_mtfi, D.fill_dt, D.dot_number,
D.orig_manf_dt, D.plus, D.star, D.code, D.cus_po, D.cstate, D.ownrshp, D.seqno,
D.trnxn_time, D.csource, D.cusno_ownd, D.venno_ownd, D.venno_fill, D.send_vend,
D.recv_vend, D.cuser, D.contested, D.contested_dt, D.except_type, D.except_choice
FROM DELETED D
JOIN INSERTED I ON D.mnfser = I.mnfser AND D.mnfcod = I.mnfcod
WHERE 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 D.loc <> I.loc OR D.cusno <> I.cusno OR D.ownrshp <> I.ownrshp
OR D.cusno_ownd <> I.cusno_ownd OR D.venno_ownd <> I.venno_ownd
OR D.contested <> I.contested OR D.contested_dt <> I.contested_dt
OR D.csource <> I.csource
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
WHEN I.status <> 3 AND I.status <> 4
THEN ' '
ELSE I.cusno
END,
contested = CASE
WHEN (D.status = 3 OR D.status = 4)
AND (I.status <> 3 AND I.status <> 4)
THEN 0
ELSE I.contested
END
FROM DATABASE31.OWNER.TABLE_1 CYL
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
WHERE 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 I.loc <> D.loc OR I.cusno <> D.cusno OR I.ownrshp <> D.ownrshp
OR I.cusno_ownd <> D.cusno_ownd OR I.venno_ownd <> D.venno_ownd
OR I.contested <> D.contested OR I.contested_dt <> D.contested_dt
OR I.csource <> D.csource
SKIP_TRGR:
END
If you guys need anything else, just let me know, and thanks again for your assistance!
UPDATE TIMSDATA.OSC.CYLMAS SET mnfser = '1010 ' , mnfcod = ' ' , acqref = 0 , sup= 'OXY' , part = '124 ' , loc = ' 1' , retest = 0 , c_lot = ' ' , c_cufdec = 0 , c_vol = 0 , truck = 0 , cu...
You don't need to reproduce the deadlock - please just run the update so we can see the query plan both for the update itself and the statements inside the trigger.
Please also provide the table definition (both columns and indexes) for the TIMSDATA31.BOBE.CYLMAS table.
Since there are U locks involved in your deadlock graph, most likely one of your update query plans (either the one firing the trigger the trigger, or, most likely, the one inside the trigger) contains a spool. With the index definitions, it might be possible to tell why and see if we can get rid of it.|||
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 | |--Inserted Scan(OBJECT:([TimsData31].[bobe].[CYLMAS] AS I)) 13 9 7 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.[VENNO_OWND], I.[CUSNO_OWND], I.[OWNRSHP], I.[LOC], I.[PART], I.[SUP], I.[MNFCOD], I.[MNFSER], I.[BARCOD], I.[CSTATE], I.[CUSNO], I.[CONTESTED], I.[STATUS] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Scan(OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD] AS [CYL])) 13 73 6 Clustered Index Scan Clustered Index Scan OBJECT:([TimsData31].[bobe].[CYLMAS].[UQ_CYLMAS_BARCOD] AS [CYL]) [Bmk1000], [CYL].[MNFCOD], [CYL].[MNFSER] 215.0 4.7948871E-2 3.1500001E-4 343 0.04826387 [Bmk1000], [CYL].[MNFCOD], [CYL].[MNFSER] NULL PLAN_ROW 0 1.0
>> Table definition:
create table CYLMAS
(
BARCOD varchar(24) not null
,MNFSER varchar(24) not null
,MNFCOD char(3) not null
,ACQREF decimal(4) not null
,SUP char(3) not null
,PART varchar(25) not null
,LOC char(3) not null
,RETEST decimal(8) not null
,C_LOT varchar(25) not null
,C_CUFDEC decimal(1) not null
,C_VOL decimal(12,3) not null
,TRUCK decimal(5) not null
,CUSNO char(5) not null
,STATUS decimal(1) not null
,LSTDAT decimal(8) not null
,CYLBNK decimal(2) not null
,CYLCOM1 varchar(32) not null
,CYLCOM2 varchar(32) not null
,CPHYLOC varchar(10) not null
,C_PUCD decimal(1) not null
,C_MTFI decimal(1) not null
,FILL_DT decimal(8) not null
,DOT_NUMBER varchar(15) not null
,ORIG_MANF_DT decimal(6) not null
,PLUS decimal(1) not null
,STAR decimal(1) not null
,CODE char(1) not null
,CUS_PO varchar(22) not null
,CSTATE char(5) not null
,OWNRSHP decimal(1) not null
,SEQNO decimal(12) not null
,TRNXN_TIME decimal(6) not null
,CSOURCE decimal(3) not null
,CUSNO_OWND char(5) not null
,VENNO_OWND char(4) not null
,VENNO_FILL char(4) not null
,SEND_VEND decimal(8) not null
,RECV_VEND decimal(8) not null
,CUSER char(3) not null
,CONTESTED decimal(1) not null
,CONTESTED_DT decimal(8) not null
,cidentity decimal(18) identity
,rowversion timestamp
,EXCEPT_TYPE decimal(3) not null
,EXCEPT_CHOICE decimal(3) not null
,CONSTRAINT PK_CYLMAS PRIMARY KEY NONCLUSTERED (CIDENTITY)
,CONSTRAINT UQ_CYLMAS_BARCOD UNIQUE CLUSTERED (BARCOD)
);
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.|||I hope you did not post the exact names of the objects of your database in public. As we have known, this can be a threat to your database security.
