Options
Deadlock when syncing
tcan
Posts: 3
I'm running up against the same issue described in this post:
http://www.red-gate.com/messageboard/vi ... 0255e72a40
We haven't seen it between databases (we don't actually do a lot of that) because we usually compare between scripts on disk and a database. As in the post, though, it would be preferable to be able to run our changes in a transaction. In order to do this while avoiding the deadlock we make our changes in stages — starting with users/roles, then to types, then tables, etc., for example.
Has anyone else run into this? What are your solutions?
My environments:
SQL Server 2008 Developer Edition (no linked servers)
Windows 7, Vista, Server 2008
SQL Compare 8.2 with latest cumulative patch
Thank you,
Tyler
http://www.red-gate.com/messageboard/vi ... 0255e72a40
We haven't seen it between databases (we don't actually do a lot of that) because we usually compare between scripts on disk and a database. As in the post, though, it would be preferable to be able to run our changes in a transaction. In order to do this while avoiding the deadlock we make our changes in stages — starting with users/roles, then to types, then tables, etc., for example.
Has anyone else run into this? What are your solutions?
My environments:
SQL Server 2008 Developer Edition (no linked servers)
Windows 7, Vista, Server 2008
SQL Compare 8.2 with latest cumulative patch
Thank you,
Tyler
Comments
It works with a live database as we base the dependency on the order they're created, and the UDDT has to be created before the UDTT that references it.
With scripts, we're not so lucky as each type has it's own file, and the files are evaluated in alphabetical order.
We will be able fix this, but it isn't something we can patch and will need to wait for a full release.
I think the only workaround will be to make sure you sync the UDDT first, and then everything else.