DML and Mutations - a tale of two data altering techniques in Cloud Spanner

Mayur kale
Jun 27, 2020 · 5 min read

Data Manipulation Language (DML) and Mutations are two APIs in Cloud Spanner that you can use to modify data. You may find yourself asking, “Why two alternative APIs?” or “When should I choose one over the other?”. Let’s demystify each and explore their similarities and differences, so you can answer those questions.

The code samples in this post refer to a fictitious music industry database with two tables Singers and Albums with the following schema. Albums is an interleaved table of Singers, which stores the albums list of each Singer.

CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
BirthDate DATE,
LastUpdated TIMESTAMP,
) PRIMARY KEY(SingerId);
CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(1024),
MarketingBudget INT64,
) PRIMARY KEY(SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

DML

The Cloud Spanner DML enables you to INSERT, UPDATE, and DELETE data in Spanner tables. You can run DML statements using the client libraries, the Google Cloud Console, and the gcloud command-line tool.

Spanner offers two implementations of DML execution, each with different properties. At a high level, there is:

  • Standard DML: suitable for OLTP (Online Transaction Processing) workloads. The following Java sample code creates a read-write transaction and calls executeUpdate() to insert 4 records into the Singers table. To see examples in other supported languages, see Modifying data using DML in our official Spanner documentation.
  • Partitioned DML: enables large-scale, database-wide operations with minimal impact on concurrent OLTP operations by partitioning the keyspace and running the statement over the partitions in separate, smaller scoped transactions. Partitioned DML is designed for bulk updates and deletes: 1) Periodic cleanup and garbage collection. Examples are deleting old rows or setting columns to NULL. 2) 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.

Mutations

Besides DML, users can use Mutations to write data to Cloud Spanner. A Mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a Spanner database.

You write data using a Mutation object. A Mutation object is a container for mutation operations to update, delete and insert data. You can include operations that apply to different rows, or different tables, in a mutation. After you define one or more mutations that contain one or more writes, you must apply the mutation to commit the write(s). Each change is applied in the order in which they were added to the Mutation.

Here’s an example, again in Java, of inserting 4 rows into Singers using the Mutation API. For each row, we add an insert mutation and then call write() to execute the mutations. To see examples in other supported languages, see Modifying data using mutations in our official Spanner documentation.

Comparing DML and Mutations

Here are the high level similarities and differences between DML and Mutations.

Let’s take a deeper look at the differences.

  • Read Your Writes (reading uncommitted results within an active transaction) semantics: changes you make using DML statements are visible to subsequent statements in the same transaction. This is different from using mutations, where changes are not visible in any reads (including reads done in the same transaction) until the transaction commits. This is because mutations in a transaction are buffered client-side (locally) and sent to the server 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.
  • Upsert: DML does not support a native upsert, an operation that inserts rows into a database table if they do not already exist, or updates them if they do. However, there is an insert_or_update operation in the mutations world. Applications using DML can work around this limitation by reading the row first, and then using the appropriate DML statement.
  • Constraint Checking: Spanner checks the constraints after every DML statement. This is different from using mutations, where Spanner buffers mutations in the client until commit and checks constraints at commit time. Evaluating the constraints after each statement allows Spanner to guarantee that the data that a DML statement returns is consistent with the schema.
  • SQL Syntax: DML provides a conventional way to manipulate data, you can reuse SQL skills to alter the data using the DML API.

Avoid using DML and Mutations in the same transaction

If a transaction contains both DML statements and mutations in the commit request, 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.

Here is an example that illustrates potentially surprising behavior. The code inserts two rows into Albums using the Mutation API. The snippet, then calls executeUpdate() to update the newly inserted rows and calls executeQuery() to read updated albums.

If you were to execute this code, you’d see 0 records updated. This happens because the changes we made using Mutations are not visible to subsequent statements until the transaction commits. Ideally, we should have buffered writes only at the very end of the transaction.

Summary

As we’ve seen, DML and Mutations support similar data manipulation features. Choosing which approach to use will depend on whether some of the richer features such as “Read Your Writes”, “Upsert” or support for SQL syntax are important to your use case.

Google Cloud - Community

Google Cloud community articles and blogs

Mayur kale

Written by

Software Engineer at Google

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Mayur kale

Written by

Software Engineer at Google

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store