Skip to main content
Logical data replication is only supported in CockroachDB self-hosted clusters.
Transactional mode is a logical data replication (LDR) mode that applies each replicated source transaction as a single transaction on the destination cluster. Where the default immediate mode applies row changes independently as they arrive, transactional mode applies the writes from each source transaction together. This page describes when to use transactional mode, how to start it, and how to manage conflicts, schema changes, and foreign key constraints on a transactional LDR stream.

When to use transactional mode

Use transactional mode when:
  • The replicating tables have foreign key constraints, which immediate mode does not support. Transactional mode detects the dependencies between the writes in a transaction and orders them so that constraints are satisfied as the transaction applies.
  • The replicating tables have unique constraints. immediate mode supports unique constraints, but it does not enforce an order between rows, so a write can hit a unique constraint violation and need to be retried. Transactional mode orders the writes automatically.
  • Your application requires atomicity across the rows a transaction touches. In immediate mode, a destination reader can observe a partially applied source transaction.
Transactional mode has a higher base latency than immediate mode, because the destination buffers and applies each source transaction as a unit. If the replicating tables have no constraints that span rows and your application tolerates row-at-a-time application, immediate mode offers lower replication lag. The following table summarizes the differences:

Before you begin

Start LDR in transactional mode

Transactional LDR uses two statements:

Seed the destination tables

From the destination cluster, which does not yet contain the tables, start a bidirectional transactional stream:
  • {database.public.table_name_...}: The fully qualified table names. The source and destination lists map positionally, so keep them in the same order.
  • {source_connection}: The external connection that points to the source cluster.
  • {destination_connection}: The external connection that points to the destination cluster, used for the reverse stream.
The statement creates the tables on the destination, runs the offline initial scan, brings the tables online, and starts replication in both directions in transactional mode.
Tables with user-defined types cannot use CREATE LOGICALLY REPLICATED. Seed those tables with backup and restore instead, then connect the clusters with CREATE LOGICAL REPLICATION STREAM.

Connect clusters whose tables already match

When the tables already exist and match on both clusters, start the stream from the destination cluster with cursor = 'now' to skip the backfill:
  • dlq = {schema}.{prefix}: Sets where the job creates its dead letter queue (DLQ) tables. You can also use the three-part form {database}.{schema}.{prefix}.
  • on_constraint_conflict, on_schema_conflict: The conflict policies for the stream.
For a bidirectional pair, run the statement on both clusters, reversing the source and destination. Set cursor to the same timestamp in both statements, rather than using cursor = 'now', so that both directions start from the same point in time and the clusters cannot diverge between the two starts.

Set the conflict policies

In transactional mode, whole transactions apply together, so conflicts that the workload itself does not cause are unexpected: a conflict usually indicates a problem with the setup, such as tables that did not match when the stream started. In a bidirectional stream, the workload can still cause conflicts, because each cluster accepts transactions that are valid on that cluster alone. For example, with an orders table that holds a foreign key to a customers table:
  1. On cluster A, a transaction inserts an order for customer 1.
  2. Before that transaction replicates, a transaction on cluster B deletes customer 1.
  3. Both transactions commit, because each is valid on its own cluster.
  4. When the insert replicates to cluster B, it references a customer that no longer exists and violates the foreign key constraint.
The stream requires you to opt in to how conflicts are handled, so a conflict policy is set either way; choose the policy based on whether your workload can cause conflicts like this:
  • on_constraint_conflict = 'pause' | 'dlq': Sets how constraint violations, such as foreign key or unique constraint violations, are handled. You must set this option to replicate tables with unique constraints or foreign keys.
  • on_schema_conflict = 'pause' | 'dlq': Sets how incompatible schemas are handled. You must set this option to run supported schema changes while the stream runs.
Choose 'pause' when you expect the workload to create no conflicts, so that any conflict stops replication for investigation. Choose 'dlq' when you expect occasional conflicts, so that replication continues while you handle the conflicting transactions from the DLQ tables. With 'dlq' set, the job also requires the dlq option, which sets where the job creates its dead letter queue (DLQ) tables. Source transactions that fail to apply with a DLQ-eligible error are recorded in the DLQ tables and replication continues past them. To review and retry DLQ entries, refer to Manage entries in the DLQ.

Resolve a paused job

With a conflict policy set to 'pause', the job applies every transaction before the conflicting transaction and then pauses. You can continue replication in one of the following ways:
  • To retry the transaction, correct the destination so that the transaction can apply, and then resume the job. For example, insert the missing parent row for a foreign key violation, or run the schema change on the destination.
  • To continue without applying the transaction, skip it and then resume the job:
    SKIP skips one transaction, and the destination does not receive the writes from that transaction. The clusters diverge for those rows until you reconcile them. If a later transaction also conflicts, the job pauses again.

Modify the conflict policies

To modify the conflict policies on a job, pause the job and then use one of the following statements:
  • {policy}: Either 'pause' or 'dlq'. You can set a policy to 'dlq' only if the job was created with a DLQ path.
After you modify the conflict policies, resume the job.

Seed with backup and restore

If you can stop write traffic during setup, you can seed the destination with backup and restore instead of CREATE LOGICALLY REPLICATED:
  1. Stop writes on the source cluster.
  2. Back up the replicating tables or their database from the source cluster.
  3. Restore the backup onto the destination cluster.
  4. After the restore completes, start the stream in each direction following Connect clusters whose tables already match.
Keep writes stopped until all restores complete. This guarantees that both sides are identical at the cursor timestamp, so the streams start with nothing to reconcile.

Extend to more than two clusters

The workflow generalizes to a full mesh of N clusters, where every cluster replicates with every other cluster. First create and seed the tables on every cluster, then create bidirectional streams between every remaining pair:
  1. Seed phase: Pick one populated cluster as the seed. For every other cluster, run CREATE LOGICALLY REPLICATED on that cluster to seed it from the seed cluster with a bidirectional transactional stream.
  2. Mesh-fill phase: For every remaining pair of seeded clusters, run CREATE LOGICAL REPLICATION STREAM in both directions, with cursor set to the same timestamp in both.

Schema changes in transactional mode

When LDR starts on a table, the job locks the schema. In transactional mode, the following schema changes are allowed on a replicating table while LDR runs, in addition to the allowlist schema changes available in both modes: LDR does not replicate schema changes. You run the statement on each cluster, and on_schema_conflict governs transactions that reference the new column while the schemas differ between the clusters. Other schema changes, including DROP COLUMN, still require you to coordinate the change across the clusters.

Example: add a column to a replicating table

The following example adds a loyalty_tier column to a customers table that is replicating bidirectionally in transactional mode between cluster A and cluster B.
  1. Confirm that every LDR job watching the table sets on_schema_conflict. To view the LDR jobs and their options on each cluster, run:
  2. Add the column as nullable, without a default value, on cluster A:
  3. Run the same statement on cluster B. The clusters can run the statement in either order:
    While only one cluster has the column, transactions that write to it are handled according to the on_schema_conflict policy of the jobs watching the table. Running the statement on both clusters promptly keeps this window short.
  4. Verify that writes to the new column replicate. On cluster A, update a row:
    On cluster B, read the row:
    The command returns the following:
  5. Update your application to write to the new column.
  6. (Optional) To add a NOT NULL constraint, first backfill the column on existing rows so that no NULL values remain, then run the following on both clusters:
The workflow is the same for unidirectional and bidirectional LDR.

Defer foreign key checks

This feature is in limited access and is only available to enrolled organizations. To enroll your organization, contact your Cockroach Labs account team. This feature is subject to change.
Transactional mode orders the rows within each replicated transaction so that foreign key dependencies are satisfied as the transaction applies. When rows within a transaction form a reference cycle, no valid order exists, so the transaction cannot apply. Reference cycles occur when:
  • Two tables hold foreign keys that reference each other, and one transaction writes matching rows to both.
  • A table holds a self-referential foreign key, and one transaction writes rows that reference each other. For example, a single transaction that inserts two employees rows whose manager_id values point at each other.
To replicate these transactions, defer the foreign key checks to the end of each applied transaction. Enable the cluster setting on the destination cluster:
The setting defers foreign key checks only in the transactions that the LDR stream applies; it does not change how foreign key checks run for other transactions on the cluster. In a bidirectional setup, enable the setting on both clusters. When deferred foreign key checks become generally available, transactional mode will defer foreign key checks unconditionally, and the cluster setting will no longer be required.

Monitor transactional LDR

Monitor a transactional stream the same way as any LDR job. Refer to Logical Data Replication Monitoring for the SQL shell, DB Console, Prometheus, and Datadog workflows. With the 'dlq' conflict policies set, replication continues past transactions that fail to apply, so monitor the DLQ tables and alert on growth. Refer to Manage entries in the DLQ.

See also