SQL Server Performance

Deadlock and replication

Discussion in 'SQL Server 2005 Replication' started by ankurtechno, Nov 7, 2006.

  1. ankurtechno New Member

    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
  2. satya Moderator

    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.
  3. ankurtechno New Member

    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



  4. thomas New Member

    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.
  5. madhuottapalam New Member

    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

Share This Page