How can we help you today? How can we help you today?

Deadlock when synchronizing

When we are synchonizing two databases with a lot of changes, we will often see a deadlock error:

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

If we turn off the transaction option (check "Do not use transactions in synchronization scripts") then the deadlock doesn't happen. But we'd far prefer to run with transactions, just in case anything happens.

I find it hard to understand why running a simple (if long) DML script on a completely inactive database can be deadlocked. What could it be deadlocking with? I tried looking with the deadlock trace flags, but they don't seem to tell me about the other process.

Another clue is that this tends to happen a little over 30 secs into the sync process - seems like it might be a timeout. But it seems to happen the same place all the time.

what is causing this? and is there any workaround other than turning off transactions?
mkraley
0

Comments

5 comments

Sort by
  • peter.peart
    Hi there,

    Please can you confirm if you have any linked servers at all in this setup? If you can also confirm which version of SQL Compare you are experiencing this in that would be great.

    Many thanks!

    Pete
    peter.peart
    0
  • mkraley
    No linked servers
    SQL Compare 7.1.0.197
    mkraley
    0
  • peter.peart
    Hi Michael,

    I think we are best to continue this via a support call. We already have one open, I will e-mail you regarding it.

    Pete
    peter.peart
    0
  • jeremy.hallden
    Hi,

    We are experiencing this as well, but only when we synchronize certain stored procedures.

    "[1205] Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."


    Any ideas on how to solve our issues?
    jeremy.hallden
    0
  • jeremy.hallden
    Just read http://www.red-gate.com/messageboard/vi ... 0029#40029 and noticed that the stored procedures that fail are dependent on an UDT. So if we synchronize the UDT first, it all works fine.

    Any plans on when this issue will be fixed?
    jeremy.hallden
    0

Add comment

Please sign in to leave a comment.