Spanner Removes Cumulative Mutation Limits for DML Transactions

Why This Change Matters

A transaction often needs to update more than one table to keep application data consistent. An e-commerce checkout, for example, might reserve inventory, create an order, update payment status, and record an audit entry. These changes may need to succeed or fail together.

In Cloud Spanner, DML lets applications use familiar SQL statements such as INSERT, UPDATE, and DELETE inside read-write transactions. Before this change, developers could encounter the cumulative 80,000-mutation limit when multiple DML statements modified large amounts of data in one transaction.

The new behavior gives developers more flexibility: each DML statement is evaluated against the mutation limit independently. Several statements can collectively generate more than 80,000 mutations while remaining in one transaction, provided each statement stays within its limit and the transaction satisfies the other Spanner constraints.

This is useful when the business operation genuinely requires atomic updates across multiple sets of records.

Understanding Spanner Mutation Limits

A mutation represents a change to database data. The mutation count is not necessarily equal to the number of rows affected. Spanner also accounts for modified cells and relevant index updates.

For example, updating a row with several indexed columns can generate more mutation work than updating a single column without dependent indexes.

The important limits are:

The change does not remove every limit on transactions. It changes how the mutation limit is enforced for DML statements. The transaction-size limit and other operational constraints still apply.

Before and After the Change

Consider a transaction that performs three DML statements. Each statement generates 35,000 mutations.

Under the previous cumulative approach, the total of 105,000 mutations could exceed the transaction-level limit. Under the updated behavior, each statement is evaluated separately.

These figures are illustrative, not benchmark results. Actual mutation counts depend on the schema, modified columns, and indexes.

The distinction is important: a transaction can now exceed 80,000 mutations across multiple DML statements, but a single DML statement cannot simply generate an unlimited number of mutations.

A Practical SQL Example

Suppose an application needs to update product availability, mark old orders for review, and insert audit records as part of a single business operation.

The following GoogleSQL illustrates the individual statements. It assumes that the tables and columns exist and that the application supplies appropriate transaction boundaries.

UPDATE Inventory
SET Status = 'REVIEW'
WHERE WarehouseId = @warehouse_id
  AND Quantity < @minimum_quantity;

UPDATE Orders
SET ReviewRequired = TRUE
WHERE WarehouseId = @warehouse_id
  AND Status = 'PENDING';

INSERT INTO AuditLog (
    AuditId,
    EventType,
    WarehouseId
)
VALUES (
    @audit_id,
    'INVENTORY_REVIEW',
    @warehouse_id
);

These statements represent different parts of one logical operation. When executed inside a single read-write transaction, they can participate in the same atomic commit.

The updated mutation-limit behavior evaluates each DML statement independently. However, the transaction must still satisfy the 100 MiB size limit, and all statements must be valid for the database schema and constraints.

The example also highlights a design consideration: broad UPDATE statements can affect many rows. Before running them in production, inspect the expected row count, verify the filtering conditions, and confirm that the operation belongs in one atomic transaction.

Implementing the Transaction in Java

The change is compatible with existing Spanner client libraries, so applications do not need a special API to use the new limit behavior.

Here is a Java example using the Google Cloud Spanner client library. It runs two DML statements within a read-write transaction.

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.TransactionContext;
import com.google.cloud.spanner.TransactionRunner;

public class InventoryService {

    private final DatabaseClient databaseClient;

    public InventoryService(DatabaseClient databaseClient) {
        this.databaseClient = databaseClient;
    }

    public void markInventoryForReview(
            long warehouseId,
            long minimumQuantity) {

        databaseClient
            .readWriteTransaction()
            .run(transaction -> {

                Statement updateInventory = Statement.newBuilder(
                    "UPDATE Inventory " +
                    "SET Status = @status " +
                    "WHERE WarehouseId = @warehouseId " +
                    "AND Quantity < @minimumQuantity")
                    .bind("status").to("REVIEW")
                    .bind("warehouseId").to(warehouseId)
                    .bind("minimumQuantity").to(minimumQuantity)
                    .build();

                transaction.executeUpdate(updateInventory);

                Statement updateOrders = Statement.newBuilder(
                    "UPDATE Orders " +
                    "SET ReviewRequired = TRUE " +
                    "WHERE WarehouseId = @warehouseId " +
                    "AND Status = @orderStatus")
                    .bind("warehouseId").to(warehouseId)
                    .bind("orderStatus").to("PENDING")
                    .build();

                transaction.executeUpdate(updateOrders);

                return null;
            });
    }
}

The example focuses on transaction structure rather than demonstrating a particular mutation count. It assumes the database client has already been initialized and that the schema matches the SQL statements.

Each call to executeUpdate executes a separate DML statement. Under the updated behavior, the 80,000-mutation limit is checked for each statement rather than cumulatively across the DML statements in the transaction.

Spanner can retry a read-write transaction when contention occurs. Keep transaction callbacks safe to retry: avoid sending external notifications, charging a payment provider, or performing other irreversible side effects directly inside the callback. Use an appropriate post-commit workflow for those operations.

DML Versus the Mutation API

One of the most important details is that DML and the Mutation API have different mutation-limit behavior.

DML uses SQL statements that execute within the transaction. Changes made through DML are visible to subsequent SQL and DML statements in that transaction.

The Mutation API, by contrast, lets an application construct inserts, updates, and deletes that are applied as part of the commit. Its 80,000-mutation limit remains cumulative across the mutations in that commit.

Google Cloud recommends avoiding a mixture of DML and Mutation API writes in the same transaction unless the application specifically requires it. Their execution and visibility behavior differs, which can lead to unexpected results if the ordering is not understood.

The updated DML limit does not increase the Mutation API limit. If a workload depends on the Mutation API, developers should review whether DML, batch writes, or another supported approach better fits the operation.

When Should You Use DML or Partitioned DML?

Removing the cumulative DML mutation limit does not mean that every large database operation belongs in a single transaction.

Use regular DML when a business operation requires atomicity across several statements. Examples include order processing, related inventory updates, and coordinated changes to records that must remain consistent.

Partitioned DML is designed for large-scale operations that can be divided into smaller transactions, such as backfilling a column or cleaning up old records. Spanner processes work across partitions, which can reduce the need for a single large transaction.

Partitioned DML has its own restrictions, including requirements around idempotency and supported statements. It should not be substituted for an atomic transaction when partial completion would violate business rules.

Best Practices for Production Workloads

Keep Transactions Focused

Although a transaction can contain more DML mutations overall, putting too much unrelated work into one transaction is still a poor design choice.

Longer transactions can hold locks for longer periods, increasing contention and the likelihood of transaction aborts. Group statements according to business requirements rather than combining unrelated operations simply because the limit permits it.

Monitor Mutation Counts

Spanner provides commit statistics that include the total mutation count for a committed transaction. This count can exceed 80,000 when a transaction contains multiple DML statements under the new rule.

Monitoring mutation counts helps identify growing workloads before they become operational problems. Also monitor transaction latency, aborts, lock contention, and application retry rates.

Account for Secondary Indexes

Indexes can increase the mutation count associated with a statement. A query that modifies fewer rows than expected can still produce substantial mutation work if several indexed columns or dependent indexes are involved.

When a statement approaches the limit, examine the schema and indexing requirements rather than estimating its mutation count from affected rows alone.

Test the Entire Transaction

Testing each DML statement independently is not enough. Validate the combined transaction against realistic data volumes, constraints, concurrent traffic, and failure conditions.

Check both the per-statement mutation limit and the overall transaction-size limit. A transaction that stays under the mutation limit can still fail for other reasons.

Common Mistakes to Avoid

• Assuming the 80,000-mutation limit has been removed entirely.

• Applying the new DML rule to the Mutation API.

• Treating the number of affected rows as the exact mutation count.

• Ignoring the 100 MiB transaction-size limit.

• Combining unrelated work into long-running transactions.

• Mixing DML and buffered mutations without understanding their execution order.

• Assuming that a transaction will never be retried or aborted.

• Using one large transaction for a backfill that would be better handled by Partitioned DML.

Conclusion

Cloud Spanner's updated DML mutation-limit behavior gives developers more flexibility when several SQL statements must participate in one atomic transaction. Each DML statement is evaluated independently against the 80,000-mutation limit, allowing the combined total across multiple statements to exceed 80,000.

The change is compatible with existing client libraries, but it does not eliminate transaction-size constraints, locking concerns, or the separate Mutation API limit.

For production applications, the right approach remains the same: keep transactions focused, account for index changes, monitor commit statistics, and select DML, Partitioned DML, or the Mutation API according to the consistency requirements of the workload.