CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2microsoft-sync-framework
Saeed
Saeed

Reputation: 71

Parallel Query Execution while Sync in SQL Server

I am using Microsoft Sync Framework to sync data in N-Tier Client Server architecture. Synchronization works fine when there is low volume of data to be synchronized. However, when there is large volume of data to be synchronized, I am facing database locking issue. The primary reason for this behavior seems to be long running transactions, which causes other transactions from multiple sync agents to be blocked. I have tested this for following:
Sql Server 2008 --> Physical Machine/ VM - Failed, having locking issue
Sql Server 2008 R2 --> Physical Machine/ VM - Succeeded on Physical, Failed on VM

Kindly suggest, what can be the issue which is causing this behavior. I believe this behavior is out of the Sync Framework domain, as sync framework opens a transaction for a batch (I am using batching in sync) and commits when the batch is done. However, I cannot understand the behavior when multiple database objects are locked (long running transactions, even at a point I have noticed almost all of the table objects were locked!),

Upvotes: 0

Views: 1071

Answers (1)

JuneT
JuneT

Reputation: 7860

Sync Fx does not explicitly issue locks.

It's just like any other database applications though, during sync, some rows will be locked as it applies changes. whether it locks only few rows or the entire table depends on SQL Server promoting or escalating locks from row,page or table.

since, its just like any other database app, you can use the same techniques for investigating the locking issue (e.g., profiling, sp_who, etc...)

if your sync groups/sync scopes are big (many tables), you may consider breaking them down into smaller groups so they can commit faster and reduce the potential concurrency issues. frequent syncs will also help reduce the potential for locking issues if you're updating lesser number of rows.

Upvotes: 1

Related Questions