Deadlocks on clustered PK

Last Post 20 Feb 2008 11:35 PM by tom27. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
19 Feb 2008 10:21 AM


Hi guys,

I have this script that runs every Sunday which supposed to delete records from 3 tables.
Usually it runs fine, but sometimes it fails with a deadlock on the primary key for table3.
The primary key for table3 is clustered.

The problem is that we have another process that is running constantly from another server and updates table3.
Updates also accessing table3 by the primary key.

Here is the deadlock information (I checked and 1977058079 indicates it's a table3)

Node:1
KEY: 13:1977058079:1 (5500a672cf12) CleanCnt:2 Mode: X Flags: 0x0
Owner:0x46bba5a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:97 ECID:0
SPID: 97 ECID: 0 Statement Type: UPDATE Line #: 1

Node:2
KEY: 13:1977058079:1 (82003ea25fa7) CleanCnt:2 Mode: U Flags: 0x0
Owner:0x63fd2d20 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:74 ECID:0
SPID: 74 ECID: 0 Statement Type: DELETE Line #: 51


The problem is that I can't modify attached script to delete records using different field to separate index usage and wondering what can be done to prevent deadlocks in this case?

Thanks


Here is the script that runs once a week:

DECLARE @Table Table (ID int PRIMARY KEY)

DECLARE @rowcount int,
@datemax DATETIME

SET NOCOUNT ON

SELECT @datemax = GETDATE() - 5,
@rowcountmax = 1000

SET @rowcount = @rowcountmax

WHILE @rowcount = @rowcountmax
BEGIN

SET ROWCOUNT @rowcountmax
INSERT INTO @Table
SELECT ID FROM table3 with (NOLOCK)
WHERE date < @datemax
SET ROWCOUNT 0

DELETE a FROM table1 a INNER JOIN @Table x on a.ID = x.ID
SELECT @rowcount = @@ROWCOUNT


DELETE a FROM table2 a INNER JOIN @Table x on a.ID = x.ID
SELECT @rowcount = @@ROWCOUNT


DELETE a FROM table3 a INNER JOIN @Table x on a.ID = x.ID
SELECT @rowcount = @@ROWCOUNT


DELETE FROM @Table

END

SQLUSA
New Member
New Member

--
19 Feb 2008 01:18 PM
Don't delete records real-time.

Just marked them >inactive< (assume you have a flag, if not you add one).

Periodically, you run a scheduled job to do the batch delete of all inactives.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
trans53
New Member
New Member

--
19 Feb 2008 04:53 PM
Thank you so much, i will try that.
tom27
New Member
New Member

--
19 Feb 2008 08:46 PM
Post your update statement and index structure of object_name(1977058079) also.
trans53
New Member
New Member

--
20 Feb 2008 01:13 PM
This is how we update:

UPDATE table3 SET Column1 = ..., Column1 = ... WHERE ID = ...


ALTER TABLE [dbo].[table3] ADD CONSTRAINT [SALEID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [FILEGROUP]
trans53
New Member
New Member

--
20 Feb 2008 03:16 PM
Nope, there is no update for ID in the set statements and no explicit transactions at all. Also each table updated in sequence one at the time.
tom27
New Member
New Member

--
20 Feb 2008 11:35 PM
As Gunney said we have to have the clean history of all the statements of what is happening in the server that time.

One thing you can experiment is disable all the foreign key references to table 3, since delete statement on a master table can place an IX lock on the child tables, and see if deadlocks are still coming. You can enable it after.
You are not authorized to post a reply.

Acceptable Use Policy