Lock during compare?

PatrickMoore

Twice at work during complete DB data compares, we have run into issues that suggest to me that some tables have been locked.

Everything I have found in my searches for help on this says that SQL Data Compare doesn't put locks on any tables but it sure looks like that is exactly what is happening.

I am not trying to deploy any changes while doing this. I am strictly doing a compare to see the differences between the data in the tables of two systems that should match.

Any insight would be greatly appreciated.

Jessica R

Hi @PatrickMoore,

Thanks for your post!

A colleague has previously looked into this:

"That is a tough question - mainly because locking and the different types of locks is a broad topic - technically there is always a lock which could be optimistic, pessimistic, readonly and so forth.

I can't find anything in the documentation so I had a look in the source code and I can't find any references to locks that our programmers have intentionally specified, so I had a look at what the ADO .NET defaults are and basically it looks like ADO does a "pessimistic" lock on the table, so when a record gets updated (made "dirty") then SQL Server should not allow the read on those records until all changes in the transactions are committed... you can assume that only one table will be locked like this at any given time. During the compare phase, there should be a read/write lock so it should not affect anything at that point.

At any rate, SQL Data Compare does not allow you do configure this, so if it's causing a problem you may want to try to avoid it.

Hopefully this helps."

PatrickMoore

Thanks for the reply. It looks like it will be dangerous ground for me any time I have to do any comparisons since it is very dynamic production data that is needed constantly. I will just have to be careful in the future.

Thanks for the time spent researching it.

Jessica R

Hi @PatrickMoore,

No problem, please let us know if any other questions come up!