Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GOHi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe

> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statement
s
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a crit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe

> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a crit :
>|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:

> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.