Database.Register-method resulting in deadlock
I'm working with two databases with the SQL Toolkit copying data from a source to a destination database.
Before the copying itself of data, I perform operations on the destination database and I start a transaction as the first thing I do. I do the following:
1) Start a transaction on the destination db.
2) Perform changes on destination db.
3) Invoke RedGate toolkit to copy data from souce to destination:
- Instantiate a RedGate.SQLCompare.Engine.Database on the source database. Runs the Register() method on it with db-connection informations.
- Instantiate a RedGate.SQLCompare.Engine.Database on the destination database. Runs the Register() method on it with db-connection informations * DEADLOCK HAPPENS HERE *
4) If RedGate-copy went well I commit the destination transaction. Otherwise it is rolled back.
When I inspect what happens with SQL Profiler. The Register method fires off a number of sql statements, and the crutial part starts when reading from the sysobjects table which is apparently locked:
SELECT sysusers.name as ownername, o.name, o.id FROM sysobjects o
LEFT JOIN sysusers WITH (NOLOCK) ON sysusers.uid=o.uid
WHERE o.xtype IN ('FN', 'IF', 'P', 'S', 'TR', 'TF', 'U', 'V', 'D', 'SN')
How should I perform this operation? I hope you can help me out on this.