Data Manipulation Language best practices

This page describes best practices for using Data Manipulation Language (DML) and Partitioned DML.

Use a WHERE clause to reduce the number of locked rows

You execute DML statements inside read-write transactions. Cloud Spanner acquires shared read locks on all rows that it reads, including rows that might not match the filter condition of the WHERE clause. Cloud Spanner acquires exclusive locks on rows that it modifies. The result is that Cloud Spanner might lock a large number of row ranges or an entire table, even if the transaction only modifies a few of the rows it reads. To lock fewer rows, scope the WHERE clause for UPDATE and DELETE statements to small primary key ranges by including the primary key columns in the WHERE clause.

Don't use DML statements and mutations in the same transaction

Cloud Spanner buffers insertions, updates, and deletions performed using DML statements on the server-side, and the results are visible to subsequent SQL and DML statements within the same transaction. This behavior is different from the Mutation API, where Cloud Spanner buffers the mutations on the client-side and sends the mutations server-side as part of the commit operation. As a result, mutations in the commit request are not visible to SQL or DML statements within the same transaction.

You might want to combine DML statements and mutations in the same transaction, because some operations are only supported in the Mutation API. An example is insert_or_update. If a transaction contains both DML statements and mutations in the commit request, Cloud Spanner executes the DML statements before the mutations. To avoid having to account for the order of execution in your client library code, you should use either DML statements or the mutations in a single transaction, but not both. If you use both, you should buffer writes only at the very end of the transaction.

Use the PENDING_COMMIT_TIMESTAMP function to write commit timestamps

You use the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp in a DML statement. Cloud Spanner selects the commit timestamp when the transaction commits.

Partitioned DML and date and timestamp functions

Partitioned DML uses one or more transactions that might run and commit at different times. If you use the date or timestamp functions, the modified rows might contain different values.

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud Spanner Documentation