|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Deadlock problem with insert trigger
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 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 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. Show quoteHide quote "Gabe Moothart" <g***@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 Show quoteHide quote > > 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 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. > 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 : Show quoteHide quote > 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 -- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, 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 Show quoteHide quote > 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 > > 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: Show quoteHide quote > 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.  
Other interesting topics
|
|||||||||||||||||||||||
