|
|
Posted by bobdurie@gmail.com on 07/20/07 15:00
On Jul 20, 8:57 am, Alex Kuznetsov <AK_TIREDOFS...@hotmail.COM> wrote:
> On Jul 19, 3:13 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > We have a Java application that runs against a variety of backends
> > including Oracle and MSSql 2000 and 2005. Our application has a
> > handful of active tables that are constantly being inserted into, and
> > some of which are being selected from on a regular basis.
>
> > We have a purge job to remove unneeded records that every hour "DELETE
> > FROM <table> WHERE <datafield> < <sometimestamp>". This is how we are
> > purging because we need 100% up time, so we do so every hour. For
> > some tables the timestamp is 2 weeks ago, others its 2 hours ago. The
> > date field is indexed in some cases, in others it is not... the
> > DELETE is always done off of a transaction (autoCommit on), but
> > experimentation has shown doing it on one doesn't help much.
>
> > This task normally functions fine, but every once in a while the
> > inserts or counts on this table fail with deadlocks during the purge
> > job. I'm looking for thoughts as to what we could do differently or
> > other experience doing this type of thing, some possibilities include:
> > - doing a select first, then deleting one by one. This is a
> > possibility, but its SLOW and may take over an hour to do this so we'd
> > be constantly churning deleting single records from the db.
> > - freezing access to these tables during the purge job... our app
> > cannot really afford to do this, but perhaps this is the only option.
> > - doing an update of an "OBSOLETE" flag on the record, then deleting
> > by that flag... i'm not sure we'd avoid issues doing this, but its'
> > an option.
>
> > The failures happen VERY infrequently on sql2005 and much more
> > frequently on sql2000. Any help or guidance would be most
> > appreciated, thanks!
>
> > Bob
>
> Read "Analyzing deadlocks with SQL Server Profiler", "How to resolve a
> deadlock", "Resolving Deadlocks in SQL Server 2000". Consider
> clustering your tables on your timestamp columns.
> BTW, unlike Erland, we set deadlock priority to high so that our
> purging interferes less with other activities.
>
> Alex Kuznetsov, SQL Server MVPhttp://sqlserver-tips.blogspot.com/
Thanks for the responses.
Alex, i will go back and re-read those articles, but we've implemented
as much as we can from them. As for the clustering of the tables
based on the timestamps, thats something i'm totally unfamiliar
with!!! Our schema is created straight from java with as much
database agnostic code as possible, could you perhaps hint at how to
do this for a noob like me?
I've included as much sql and deadlock graphs as i can below from a
test app used to reproduce the issues. Keep in mind to reproduce
these issues we need upwards of 40 select/count/insert threads running
concurrently, and one purge thread almost continuously purging. This
is "basically" how our app works when under heavy load, and we're
hoping we can keep it that way.
Heres the creation cmds:
CREATE TABLE loads (
LoadID BIGINT NOT NULL IDENTITY PRIMARY KEY,
Type INTEGER NOT NULL,
CreateDate DATETIME NOT NULL,
OtherDate DATETIME,
ObjectID INTEGER
)
CREATE INDEX loads_objectid ON loads (ObjectID ASC)
CREATE INDEX loads_type ON loads (Type ASC)
I'll state for the record i'm not sure what the jtds driver is doing
under the hood, so there might be accompanying statements for some of
the below, ie "exec sp_execute 1" and such.
The insert looks likes this:
exec sp_executesql N'INSERT INTO loads (Type, CreateDate, OtherDate,
ObjectID) VALUES ( @P0 , @P1 , @P2 , @P3 )',N'@P0 int,@P1
datetime,@P2 datetime,@P3 int',2,''2007-07-20
09:38:15:477'',NULL,10
Heres a select count:
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'',N'SELECT COUNT(*) FROM loads',1
select @p1
Here's a normal select:
declare @p1 int
set @p1=1073741825
declare @p5 int
set @p5=8
declare @p6 int
set @p6=1
exec sp_cursorprepare @p1 output,N'@P0 int',N'SELECT LoadID, Type,
CreateDate, OtherDate, ObjectID FROM loads WHERE Type >= @P0 ORDER
BY loads.LoadID DESC',1,@p5 output,@p6 output
select @p1, @p5, @p6
Heres a purge:
declare @p1 int
set @p1=2
exec sp_prepare @p1 output,N'',N'DELETE FROM loads WHERE CreateDate <
dateadd(SECOND, -40, CURRENT_TIMESTAMP)',1
select @p1
Theres 2 xml deadlock graph details below - the first is one where
many selects and purges are being done and they're both hung up on
each other's page locks. The second is a little more complex but only
involves selects and inserts.
DEADLOCK GRAPH 1:
<deadlock-list>
<deadlock victim="process8cd978">
<process-list>
<process id="process6d9b58" taskpriority="0" logused="8948600"
waitresource="PAGE: 6:1:10567" waittime="5000" ownerId="44602"
transactionname="DELETE" lasttranstarted="2007-07-20T10:17:15.337"
XDES="0xbf8e768" lockMode="IX" schedulerid="1" kpid="4864"
status="suspended" spid="92" sbid="0" ecid="0" priority="0"
transcount="2" lastbatchstarted="2007-07-20T10:17:14.617"
lastbatchcompleted="2007-07-20T10:17:14.617" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="44602" currentdb="6" lockTimeout="4294967295"
clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="4"
sqlhandle="0x020000003a58ce373592e196e136605d0abbd1342997e024">
DELETE FROM loads WHERE CreateDate < dateadd(SECOND, -40,
CURRENT_TIMESTAMP) </frame>
</executionStack>
<inputbuf>
()DELETE FROM loads WHERE CreateDate < dateadd(SECOND, -40,
CURRENT_TIMESTAMP) </inputbuf>
</process>
<process id="process8cd978" taskpriority="0" logused="0"
waitresource="PAGE: 6:1:10566" waittime="4906" ownerId="44192"
transactionname="DECLARE CURSOR"
lasttranstarted="2007-07-20T10:17:13.647" XDES="0x3c8b728"
lockMode="S" schedulerid="2" kpid="6180" status="suspended" spid="55"
sbid="0" ecid="0" priority="0" transcount="0"
lastbatchstarted="2007-07-20T10:17:13.647"
lastbatchcompleted="2007-07-20T10:17:13.633" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="44192" currentdb="6" lockTimeout="4294967295"
clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18"
sqlhandle="0x0200000035aa872d0616b9c3cde31b14d2698b1961a31bf1">
SELECT LoadID, Type, CreateDate, OtherDate, ObjectID FROM loads WHERE
Type >= @P0 ORDER BY loads.LoadID DESC </frame>
<frame procname="unknown" line="1"
sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SELECT 1 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="10567" dbid="6"
objectname="test.dbo.loads" id="lock4e44f40" mode="SIU"
associatedObjectId="72057594099728384">
<owner-list>
<owner id="process6d9b58" mode="IU"/>
<owner id="process8cd978" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process6d9b58" mode="IX" requestType="convert"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="10566" dbid="6"
objectname="test.dbo.loads" id="lock104c2a80" mode="IX"
associatedObjectId="72057594099728384">
<owner-list>
<owner id="process6d9b58" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process8cd978" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
DEADLOCK GRAPH 2:
<deadlock-list>
<deadlock victim="process6d8898">
<process-list>
<process id="process6d87a8" taskpriority="0" logused="0"
waitresource="PAGE: 6:1:1652" waittime="328" ownerId="5124"
transactionname="SELECT" lasttranstarted="2007-07-20T09:39:11.380"
XDES="0x3c8bad8" lockMode="S" schedulerid="1" kpid="1504"
status="suspended" spid="63" sbid="0" ecid="0" priority="0"
transcount="0" lastbatchstarted="2007-07-20T09:39:11.380"
lastbatchcompleted="2007-07-20T09:39:11.350" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="5124" currentdb="6" lockTimeout="4294967295"
clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18"
sqlhandle="0x020000008cf9c12603db8361e8d9c732563e92f02d4ef6a0">
SELECT COUNT(*) FROM loads WHERE Type >= @P0 </frame>
</executionStack>
<inputbuf>
(@P0 int)SELECT COUNT(*) FROM loads WHERE Type >= @P0 </
inputbuf>
</process>
<process id="process6d8898" taskpriority="0" logused="0"
waitresource="PAGE: 6:1:1697" waittime="390" ownerId="5131"
transactionname="SELECT" lasttranstarted="2007-07-20T09:39:11.410"
XDES="0x47fc9a8" lockMode="S" schedulerid="1" kpid="6856"
status="suspended" spid="60" sbid="0" ecid="0" priority="0"
transcount="0" lastbatchstarted="2007-07-20T09:39:11.410"
lastbatchcompleted="2007-07-20T09:39:11.397" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="5131" currentdb="6" lockTimeout="120000"
clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18"
sqlhandle="0x020000008cf9c12603db8361e8d9c732563e92f02d4ef6a0">
SELECT COUNT(*) FROM loads WHERE Type >= @P0 </frame>
</executionStack>
<inputbuf>
(@P0 int)SELECT COUNT(*) FROM loads WHERE Type >= @P0 </
inputbuf>
</process>
<process id="process8cd1f8" taskpriority="0" logused="5724"
waitresource="PAGE: 6:1:1697" waittime="375" ownerId="5117"
transactionname="implicit_transaction"
lasttranstarted="2007-07-20T09:39:11.363" XDES="0x62fc960"
lockMode="IX" schedulerid="2" kpid="3408" status="suspended" spid="57"
sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2007-07-20T09:39:11.363"
lastbatchcompleted="2007-07-20T09:39:09.800" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="5117" currentdb="6" lockTimeout="120000"
clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="86"
sqlhandle="0x020000006dd1c217ee7ebab08b2f41f4d5b634523d2edfc0">
INSERT INTO loads (Type, CreateDate, OtherDate, ObjectID) VALUES
( @P0 , @P1 , @P2 , @P3 ) </frame>
<frame procname="unknown" line="1"
sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 datetime,@P2 datetime,@P3 int)INSERT INTO loads (Type,
CreateDate, OtherDate, ObjectID) VALUES ( @P0 , @P1 , @P2 ,
@P3 ) </inputbuf>
</process>
<process id="process8cd2e8" taskpriority="0" logused="5936"
waitresource="PAGE: 6:1:1652" waittime="328" ownerId="5118"
transactionname="implicit_transaction"
lasttranstarted="2007-07-20T09:39:11.363" XDES="0x62fcf08"
lockMode="IX" schedulerid="2" kpid="7504" status="suspended" spid="62"
sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2007-07-20T09:39:11.363"
lastbatchcompleted="2007-07-20T09:39:09.800" clientapp="loadtest"
hostname="BDURIE" hostpid="123" loginname="sa" isolationlevel="read
committed (2)" xactid="5118" currentdb="6" lockTimeout="120000"
clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="86"
sqlhandle="0x020000006dd1c217ee7ebab08b2f41f4d5b634523d2edfc0">
INSERT INTO loads (Type, CreateDate, OtherDate, ObjectID) VALUES
( @P0 , @P1 , @P2 , @P3 ) </frame>
<frame procname="unknown" line="1"
sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 datetime,@P2 datetime,@P3 int)INSERT INTO loads (Type,
CreateDate, OtherDate, ObjectID) VALUES ( @P0 , @P1 , @P2 ,
@P3 ) </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1652" dbid="6"
objectname="test.dbo.loads" id="lock3b5c780" mode="IX"
associatedObjectId="72057594099269632">
<owner-list>
<owner id="process8cd1f8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process6d87a8" mode="S" requestType="wait"/>
<waiter id="process8cd2e8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1697" dbid="6"
objectname="test.dbo.loads" id="lock5bbd740" mode="IX"
associatedObjectId="72057594099269632">
<owner-list>
<owner id="process8cd2e8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process6d8898" mode="S" requestType="wait"/>
<waiter id="process8cd1f8" mode="IX" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
[Back to original message]
|