SQL Server Performance Forum – Threads Archive

Deadlock and replication

Hi all,
I am using peer to peer transaction replication but after this i am getting deadlocks.Actually before this replication there was no deadlock and other thing deadlocks are comming on only that database from where i created this p2p replication i mean always on the first node of this replication system. Please help.
Ankur
Thanx

---

Check what kind of operation are happening during this DEADLOCK occurence, also check the concurrency pattern.
To troubleshooting, you may want to turn on trace flag 1204 and 1222 so you can know what actually cause the deadlock. Also make sure you have proper index to address those resource intensive queries.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi, After lot of analysis finally i got those stored procedure making deadlocks.See and tell me is there anything which i can do Select a,b,c from table1 with(nolock) where d=’xxxx’ if(a is not null) update table1 with(rowlock) set e=e-iVal Now in this above code if i remove Select statement there is no deadlock but with that they are. Second this is happeneing only with replication

---

You could try adding UPDLOCK to the select instead of nolock. This will exclusively lock the row from the select onwards and may help prevent deadlocking.

---

Hi, why don’t u try If Exists. For : Select a,b,c from table1 with(nolock) where d=’xxxx’
if(a is not null)
update table1 with(rowlock) set e=e-iVal Read : If Exists(Select 1 from table1 with(nolock) where d=’xxxx’)
Begin
update table1 with(rowlock) set e=e-iVal
End

---

]]>