Invantive Data Replicator uses massive parallel processing to improve throughput. In several scenarios a single database table can be accessed by multiple parallel processes at the same time, reading and writing data. Invantive Data Replicator ensures that the access is logical correct.
However, SQL Server has inherited the concept of ‘lock escalation’ of table from Sybase. Depending on your set up, lock escalation can lead to SQL Server timeouts due to an exclusive lock when the table lock method is not configured correctly. Such a timeout takes one hour to appear after starting the replication process with the default configuration of Invantive Data Replicator.
This article explains how to disable lock escalation on SQL Server. Please consult your DBA or hire a SQL Server specialist before making changes when you consider yourself insufficiently skilled to overlook the consequences of disabling lock escalation.
Instructions to disable lock escalation
Execute the following steps to disable SQL Server lock escalation:
- Log on to the database as owner of the Invantive Data Replicator repository.
- Run the query listed below to generate SQL Server statements to change lock escalation mode.
- Execute the data replication process again.
SELECT 'alter table [' + t.name + '] set (lock_escalation=disable)' stmt
from sys.tables t
where t.lock_escalation_desc = 'TABLE'
and t.schema_id = schema_id()
order
by stmt