Parallel Query Execution while Sync in SQL Server -
i using microsoft sync framework sync data in n-tier client server architecture. synchronization works fine when there low volume of data synchronized. however, when there large volume of data synchronized, facing database locking issue. primary reason behavior seems long running transactions, causes other transactions multiple sync agents blocked. have tested 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, can issue causing behavior. believe behavior out of sync framework domain, sync framework opens transaction batch (i using batching in sync) , commits when batch done. however, cannot understand behavior when multiple database objects locked (long running transactions, @ point have noticed of table objects locked!),
sync fx not explicitly issue locks.
it's other database applications though, during sync, rows locked applies changes. whether locks few rows or entire table depends on sql server promoting or escalating locks row,page or table.
since, other database app, can use same techniques investigating locking issue (e.g., profiling, sp_who, etc...)
if sync groups/sync scopes big (many tables), may consider breaking them down smaller groups can commit faster , reduce potential concurrency issues. frequent syncs reduce potential locking issues if you're updating lesser number of rows.
Comments
Post a Comment