Partitioned Data Manipulation Language

Partitioned Data Manipulation Language (partitioned DML) is designed for the following types of bulk updates and deletes:

• Periodic cleanup and garbage collection. Examples are deleting old rows or setting columns to NULL.
• Backfilling new columns with default values. An example is using an UPDATE statement to set a new column's value to False where it is currently NULL.

Partitioned DML isn't suitable for small-scale transaction processing. If you want to run a statement on a few rows, use transactional DMLs with identifiable primary keys. For more information, see Using DML.

If you need to commit a large number of blind writes, but don't require an atomic transaction, you can bulk modify your Spanner tables using batch write. For more information, see Modify data using batch writes.

You can get insights on active partitioned DML queries and their progress from statistics tables in your Spanner database. For more information, see Active partitioned DMLs statistics.

DML and partitioned DML

Spanner supports two execution modes for DML statements:

• DML, which is suitable for transaction processing. For more information, see Using DML.

• Partitioned DML, which enables large-scale, database-wide operations with minimal impact on concurrent transaction processing by partitioning the key space and running the statement over partitions in separate, smaller-scoped transactions. For more information, see Using partitioned DML.

The following table highlights some of the differences between the two execution modes.

Partitionable and idempotent

When a partitioned DML statement runs, rows in one partition don't have access to rows in other partitions, and you cannot choose how Spanner creates the partitions. Partitioning ensures scalability, but it also means that partitioned DML statements must be fully partitionable. That is, the partitioned DML statement must be expressible as the union of a set of statements, where each statement accesses a single row of the table and each statement accesses no other tables. For example, a DML statement that accesses multiple tables or performs a self-join is not partitionable. If the DML statement is not partitionable, Spanner returns the error BadUsage.

These DML statements are fully partitionable, because each statement can be applied to a single row in the table:

UPDATE Singers SET LastName = NULL WHERE LastName = '';

DELETE FROM Albums WHERE MarketingBudget > 10000;

This DML statement is not fully partitionable, because it accesses multiple tables:

# Not fully partitionable
DELETE FROM Singers WHERE
SingerId NOT IN (SELECT SingerId FROM Concerts);

Spanner might execute a partitioned DML statement multiple times against some partitions due to network-level retries. As a result, a statement might be executed more than once against a row. The statement must therefore be idempotent to yield consistent results. A statement is idempotent if executing it multiple times against a single row leads to the same result.

This DML statement is idempotent:

UPDATE Singers SET MarketingBudget = 1000 WHERE true;

This DML statement is not idempotent:

UPDATE Singers SET MarketingBudget = 1.5 * MarketingBudget WHERE true;

Delete rows from parent tables with indexed child tables

When you use a partitioned DML statement to delete rows in a parent table, the operation might fail with the error: The transaction contains too many mutations. This occurs if the parent table has interleaved child tables that contain a global index. Mutations to the child table rows themselves aren't counted against the transaction's mutation limit. However, the corresponding mutations to the index entries are counted. If a large number of child table index entries are affected, the transaction might exceed the mutation limit.

To avoid this error, delete the rows in two separate partitioned DML statements:

1. Run a partitioned delete on the child tables.
2. Run a partitioned delete on the parent table.

This two-step process helps keep the mutation count within the allowed limits for each transaction. Alternatively, you can drop the global index on the child table before deleting the parent rows.

Row locking

Spanner acquires a lock only if a row is a candidate for update or deletion. This behavior is different from DML execution, which might read-lock rows that don't match the WHERE clause.

Execution and transactions

Whether a DML statement is partitioned or not depends on the client library method that you choose for execution. Each client library provides separate methods for DML execution and Partitioned DML execution.

You can execute only one partitioned DML statement in a call to the client library method.

Spanner does not apply the partitioned DML statements atomically across the entire table. Spanner does, however, apply partitioned DML statements atomically across each partition.

Partitioned DML does not support commit or rollback. Spanner executes and applies the DML statement immediately.

• If you cancel the operation, Spanner cancels the executing partitions and doesn't start the remaining partitions. Spanner does not roll back any partitions that have already executed.
• If the execution of the statement causes an error, then execution stops across all partitions and Spanner returns that error for the entire operation. Some examples of errors are violations of data type constraints, violations of UNIQUE INDEX, and violations of ON DELETE NO ACTION. Depending on the point in time when the execution failed, the statement might have successfully run against some partitions, and might never have been run against other partitions.

If the partitioned DML statement succeeds, then Spanner ran the statement at least once against each partition of the key range.

Count of modified rows

A partitioned DML statement returns a lower bound on the number of modified rows. It might not be an exact count of the number of rows modified, because there is no guarantee that Spanner counts all the modified rows.

Transaction limits

Spanner creates the partitions and transactions that it needs to execute a partitioned DML statement. Transaction limits or per-transaction concurrency limits apply, but Spanner attempts to keep the transactions within the limits.

Spanner allows a maximum of 20,000 concurrent partitioned DML statements per database.

Unsupported features

Spanner does not support some features for partitioned DML: