Deadlock issue with 1204 results

I am consistently encountering deadlocks while running an app, with
replication running. My transactional replication insert proc always wins,
but my app fails. Below I have included the information from a 1204 trace,
but I don't really know how to read it. I believe that spid 63 has a page
level intent exclusive lock on my table, and a select statement spid 60
Share lock. I know these locks are incompatible, and this is the issue.
How do I find out more information on how to fix this?
What else should I look at?
How do I read the log results below?
2004-01-30 14:04:19.52 spid4 Wait-for graph
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:1
2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
Mode: IX Flags: 0x2
2004-01-30 14:04:19.52 spid4 Grant List 0::
2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:63 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type: INSERT
Line #: 8
2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
sp_MSins_transactions;1
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:2
2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
Mode: S Flags: 0x0
2004-01-30 14:04:19.52 spid4 Grant List 2::
2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S Flg:0x0
Ref:1 Life:00000001 SPID:60 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type: SELECT
INTO Line #: 44
2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto byacct--
/*
delete from exceptions where account_number = '32022238' and symbol = 'ADCT'
delete from matched_transaction where account_number = '32022238'and symbol
= 'ADCT'
delete from unmatched_transaction where account_number = '32022238'and
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:63 ECID:0 Ec:(0x6DE8F590) Value:0x2cccfee0 Cost:(0/514)
2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was deadlocked
on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..Captainkt
I assume you know what is a deadlock.
Do you have error handler in your applications with some delay which does
not give to user to wait until SQL Server would kill the victim?
Here are some tips on how to avoid deadlocking on your SQL Server:
Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the
transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is
to reduce the number of round trips between your application and SQL Server
by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make
sure you are not performing the same reads over and over again. If you do
need to read the same data more than once, cache it by storing it in a
variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at
the latest possible time, and then releases them at the very earliest time.
if appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked
is not modified often.
If appropriate, use as low of an isolation level as possible for the user
connection running the transaction.
Consider using bound connections.
"captainkt" <nothing@.fake.com> wrote in message
news:uPjQmma6DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am consistently encountering deadlocks while running an app, with
> replication running. My transactional replication insert proc always
wins,
> but my app fails. Below I have included the information from a 1204
trace,
> but I don't really know how to read it. I believe that spid 63 has a page
> level intent exclusive lock on my table, and a select statement spid 60
> Share lock. I know these locks are incompatible, and this is the issue.
> How do I find out more information on how to fix this?
> What else should I look at?
> How do I read the log results below?
> 2004-01-30 14:04:19.52 spid4 Wait-for graph
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:1
> 2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
> Mode: IX Flags: 0x2
> 2004-01-30 14:04:19.52 spid4 Grant List 0::
> 2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX
Flg:0x0
> Ref:1 Life:02000000 SPID:63 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type:
INSERT
> Line #: 8
> 2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
> sp_MSins_transactions;1
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:2
> 2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
> Mode: S Flags: 0x0
> 2004-01-30 14:04:19.52 spid4 Grant List 2::
> 2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S
Flg:0x0
> Ref:1 Life:00000001 SPID:60 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type:
SELECT
> INTO Line #: 44
> 2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto
byacct--
> /*
> delete from exceptions where account_number = '32022238' and symbol ='ADCT'
> delete from matched_transaction where account_number = '32022238'and
symbol
> = 'ADCT'
> delete from unmatched_transaction where account_number = '32022238'and
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:63 ECID:0 Ec:(0x6DE8F590) Value:0x2cccfee0 Cost:(0/514)
> 2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
> 2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
> 2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was
deadlocked
> on lock resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction..
>
>