Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Deadlock (1205) error, please help!

2 views
Skip to first unread message

Jay Douglas

unread,
Jul 10, 2009, 1:45:54 PM7/10/09
to

I have a very unusual database schema that has been migrated to SQL Server
2005 from Oracle. My forward advisement is the actual schema can not be
changed at all. The app is a high usage OLTP system where a transaction
could run 1 - 20 minutes.

The primary crux of the schema has a primary table with ~240 foreign key
tables referencing back to it.

Example:

Table Animal
AnimalId
Name

Table Dog
DogId
AnimalId
FavoriteBall

Table Cat
CatId
AnimalId
FavoriteToy

Table Fish
FishId
AnimalId
IsSaltWater

(You get the idea)


My problem is when I attempt to delete two different dogs I'm getting
deadlocks in the Animal table. Example sequence:

Data Load:
INSERT INTO Animal (AnimalId, Name) VALUES (1, 'Fido');
INSERT INTO Dog (DogId, AnimalId, FavoriteBall) VALUES (1, 1, 'Tennis Ball');

INSERT INTO Animal (AnimalId, Name) VALUES (2, 'Clifford');
INSERT INTO Dog (DogId, AnimalId, FavoriteBall) VALUES (2, 2, 'Soccer Ball');

COMMIT

Transaction 1:
DELETE FROM Dog WHERE DogId = 1;

Transaction 2:
DELETE FROM Dog WHERE DogID = 2;

These two different queries cause a deadlock (error 1205).

The actual lock is an IX on the Animal table, not the primary key or actual
row.

Things we've tried:
1, Allow page lock off on Dog.AnimalId index
2, Snapshot isolation
3, row lock hints
4, no lock hits on other queries
5, countless other things which I can't remember

I would appreciate any suggestions the community may have to offer.
Unfortunately, the schema can not be changed and we must figure out some
sort of path to get this issue resolved.

Thanks in advance!

Output from deadlock graph (I had to change some of the details to match my
really scaled down example)

<EVENT_INSTANCE><EventType>DEADLOCK_GRAPH</EventType><PostTime>2009-07-10T00:07:22.277</PostTime><SPID>12</SPID><TextData><deadlock-list><deadlock
victim="process313d1798"><process-list><process id="process252ad108"
taskpriority="0" logused="145344" waitresource="PAGE: 29:1:871716"
waittime="3734" ownerId="1396238850" transactionname="user_transaction"
lasttranstarted="2009-07-10T00:07:10.870" XDES="0x3f23f260" lockMode="S"
schedulerid="3" kpid="5212" status="suspended" spid="140" sbid="0" ecid="0"
priority="0" transcount="2" lastbatchstarted="2009-07-10T00:07:10.870"
lastbatchcompleted="2009-07-10T00:07:10.870" clientapp=".Net SqlClient Data
Provider" hostname="Machine2" hostpid="5036" loginname="user1"
isolationlevel="read committed (2)" xactid="1396238850" currentdb="29"
lockTimeout="4294967295" clientoption1="671088672"
clientoption2="128056"><executionStack><frame
procname="SampleDb.dbo.SP_Delete_Dog" line="979" stmtstart="76252"
stmtend="76606"
sqlhandle="0x03001d007b24af08d0b5fb00419c00000100000000000000">
DELETE FROM Dog WHERE DogId = 1;


</frame><frame procname="SampleDb.dbo.SP_Delete_Dog" line="198"
stmtstart="13942" stmtend="14516"
sqlhandle="0x03001d00eeb22c135832f3003e9c00000100000000000000">
</frame></executionStack><inputbuf>
Proc [Database Id = 29 Object Id = 321696494]
</inputbuf></process><process id="process313d1798" taskpriority="0"
logused="97508" waitresource="PAGE: 29:1:871716" waittime="1890"
ownerId="1396278577" transactionname="user_transaction"
lasttranstarted="2009-07-10T00:07:13.073" XDES="0x4a1514f0" lockMode="S"
schedulerid="4" kpid="4268" status="suspended" spid="151" sbid="0" ecid="0"
priority="0" transcount="2" lastbatchstarted="2009-07-10T00:07:13.073"
lastbatchcompleted="2009-07-10T00:07:13.073" clientapp=".Net SqlClient Data
Provider" hostname="Machine1" hostpid="4784" loginname="podshistory"
isolationlevel="read committed (2)" xactid="1396278577" currentdb="29"
lockTimeout="4294967295" clientoption1="671088672"
clientoption2="128056"><executionStack><frame
procname="SampleDb.dbo.SP_Delete_Dog" line="979" stmtstart="76252"
stmtend="76606"
sqlhandle="0x03001d007b24af08d0b5fb00419c00000100000000000000">
DELETE FROM Dog WHERE DogId = 2; </frame><frame
procname="SampleDb.dbo.UP_Remove_Dog" line="198" stmtstart="13942"
stmtend="14516"
sqlhandle="0x03001d00eeb22c135832f3003e9c00000100000000000000">
</frame></executionStack><inputbuf>
Proc [Database Id = 29 Object Id = 321696494]
</inputbuf></process></process-list><resource-list><pagelock fileid="1"
pageid="871716" dbid="29" objectname="SampleDb.dbo.Animal" id="lock9936980"
mode="IX" associatedObjectId="72057595834138624"><owner-list><owner
id="process252ad108" mode="IX" /><owner id="process313d1798" mode="IX"
/></owner-list><waiter-list><waiter id="process252ad108" mode="S"
requestType="convert" /><waiter id="process313d1798" mode="S"
requestType="convert"
/></waiter-list></pagelock></resource-list></deadlock></deadlock-list></TextData><TransactionID
/><LoginName>sa</LoginName><StartTime>2009-07-10T00:07:22.277</StartTime><ServerName>db1</ServerName><LoginSid>AQ==</LoginSid><EventSequence>94648365</EventSequence><IsSystem>1</IsSystem><SessionLoginName
/></EVENT_INSTANCE>


Russell Fields

unread,
Jul 10, 2009, 2:26:46 PM7/10/09
to
Jay,

Well, I am surprised that a delete of Dog results in an IX lock on Animal.
However, I note that you are not simply issuing "DELETE FROM Dog WHERE DogID
= 1'', but that this is a line in the stored procedure.

So, what else is the stored procedure doing that might produce this lock?
Are there any constraints between Dog and Animal (such as a cascading
delete)? What are the indexes in these tables?

RLF

"Jay Douglas" <j...@jaydouglasREMOVETHIS.com> wrote in message
news:eyatGZYA...@TK2MSFTNGP03.phx.gbl...

Jay Douglas

unread,
Jul 10, 2009, 2:44:36 PM7/10/09
to

Russell,

Thanks for your reply. There is an FK between Dog and Animal with no
delete. Items within the Animal have already been deleted.

Animal has a PK index and Dog has a PK index and another on AnimalId.

Any ideas?

Jay


"Russell Fields" <russel...@nomail.com> wrote in message
news:%23HSd8vY...@TK2MSFTNGP03.phx.gbl...

TheSQLGuru

unread,
Jul 10, 2009, 5:18:31 PM7/10/09
to
Try issuing an exclusive type of lock with a select statement on the 'other'
item that is part of the deadlock (not the one you are actually deleting
from) PRIOR to issuing the delete statement. If you cannot first acquire
that lock (updlock, holdlock maybe?) then you will be blocked and wait for
the already-in-process delete to complete. Then you get your select
exclusive lock and can do your delete - and you should block other potential
deleters from hitting the data you have select/delete locks on until the
delete is complete.

Hope that made sense - it is clear as a bell to me. :-))

Note that your concurrency is gonna be for shit with that type of schema -
but aint no way around that.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jay Douglas" <j...@jaydouglasREMOVETHIS.com> wrote in message
news:eyatGZYA...@TK2MSFTNGP03.phx.gbl...
>

Erland Sommarskog

unread,
Jul 10, 2009, 5:35:33 PM7/10/09
to
Jay Douglas (j...@jaydouglasREMOVETHIS.com) writes:
> Thanks for your reply. There is an FK between Dog and Animal with no
> delete. Items within the Animal have already been deleted.

How would that be possible? You should not be able to delete a row in
Animal if Dog has an FK constraint referring to it.

Did you confuse the tables when changed the names?

But as far as I can tell from the deadlock trace, there is a user
transaction in progress. That is, it is likely to matter what happened
previously in that transaction.

Here is an example that is similar to yours, but in one detail:

CREATE TABLE paren (a int NOT NULL PRIMARY KEY, b char(23) NULL)
CREATE TABLE child (a int NOT NULL PRIMARY KEY,
b int NOT NULL REFERENCES paren(a))

-- CREATE INDEX paren_ix ON child(b)

go
INSERT paren (a) VALUES (1)
INSERT paren (a) VALUES (2)
go
INSERT child (a, b) VALUES(1, 1)
INSERT child (a, b) VALUES(2, 2)

In one window run:

BEGIN TRANSACTION
DELETE child WHERE a = 1

In a second window run:

BEGIN TRANSACTION
DELETE child WHERE a = 2

In the first window, now run:

DELETE paren WHERE a = 1

This will hang. In the second window run:

DELETE paren WHERE a = 2

One process will be deadlocked.

If the index is present, there is no deadlock.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Jay Douglas

unread,
Jul 16, 2009, 5:05:28 PM7/16/09
to
The fix was adjusting a query earlier in the transaction that had an inner
join that referenced a table that was already in the FROM statement ... The
inner join to itself was locking ~50% rows in the database. Fixed the
query, fixed the deadlocks.

Thanks everybody for their help.

JD

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C44EF37...@127.0.0.1...

0 new messages