Database Commit Modes

The replication environment supports three ways of committing data to the relational database:

Auto Commit Mode

In auto commit mode the SQL Server connection is switched into "auto commit" mode and every individual change to the database is automatically committed by SQL Server its self. No start transaction, commit or rollback instructions are ever issued by any code in the replication environment.

This mode can be selected via a command line option:

-commitmode=AUTOMATIC

Or by setting an environment variable:

REPLICATOR_COMMIT_MODE=AUTOMATIC

Batch Commit Mode (default)

In batch commit mode the replicator process attempts to optimize the number of transactions by grouping multiple changes to the database into a lower number of transactions. The replicator will continue doing this until one of three events take place:

1. A predetermined batch row count is reached.
2. The replicator sleeps because the replication queue has become empty.
3. A non-routine request (creating or deleting a table, etc.) is encountered.

In each of these cases the current transaction is committed to the database and a new transaction is started.

Batch commit mode mode is the default behavior, but may be explicitly selected via a command line option, as can the batch row count:

-commitmode=BATCH -commitbatch=1000

Or by setting environment variables:

REPLICATOR_COMMIT_MODE=BATCH
REPLICATOR_COMMIT_BATCH=1000

Manual Commit Mode

In manual commit mode, code in each specific operation explicitly starts a transaction, alters the database and then commits the transaction. This is the most "expensive" approach, but remains for compatibility with earlier code bases when this was the only mechanism available.

This mode can be selected via a command line option:

-commitmode=MANUAL

Or by setting an environment variable:

REPLICATOR_COMMIT_MODE=MANUAL