Deadlock when synchronizing
mkraley
Posts: 6
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?
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?
Comments
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
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
SQL Compare 7.1.0.197
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
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
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?
Any plans on when this issue will be fixed?