This page describes how to insert, update, and delete Cloud Spanner data using
Data Manipulation Language (DML) statements. You can run DML statements
using the client libraries, the
Google Cloud console, and the gcloud
command-line tool. You can run Partitioned DML
statements using the client libraries and the gcloud
command-line tool.
For the complete DML syntax reference, see Data Manipulation Language syntax for Google Standard SQL-dialect databases or PostgreSQL data manipulation language for PostgreSQL-dialect databases
Use DML
DML supports INSERT
, UPDATE
, and DELETE
statements in the
console, Google Cloud CLI, and client libraries.
Locking
You execute DML statements inside read-write transactions. When Cloud Spanner reads data, it
acquires shared read locks on limited portions of the row ranges that you read. Specifically, it
acquires these locks only on the columns you access. The locks can include data that does not
match the filter condition of the WHERE
clause.
When Cloud Spanner modifies data using DML statements, it acquires exclusive locks on the specific data that you are modifying. In addition, it acquires shared locks in the same way as when you read data. If your request includes large row ranges, or an entire table, the shared locks might prevent other transactions from completing in parallel.
To modify data as efficiently as possible, use a WHERE
clause that enables
Cloud Spanner to read only the necessary rows. You can achieve this goal with a filter on the
primary key, or on the key of a secondary index. The WHERE
clause limits the scope of
the shared locks and enables Cloud Spanner to process the update more efficiently.
For example, suppose that one of the musicians in the Singers
table changes their
first name, and you need to update the name in your database. You could execute the following DML
statement, but it forces Cloud Spanner to scan the entire table and acquires shared locks that
cover the entire table. As a result, Cloud Spanner must read more data than necessary, and
concurrent transactions cannot modify the data in parallel:
-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE
UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";
To make the update more efficient, include the SingerId
column in the
WHERE
clause. The SingerId
column is the only primary key column for
the Singers
table:
-- RECOMMENDED: INCLUDING THE PRIMARY KEY COLUMN IN THE WHERE CLAUSE
UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards" AND SingerId = 1;
Concurrency
Cloud Spanner sequentially executes all the SQL statements (SELECT
,
INSERT
, UPDATE
, and DELETE
) within a transaction. They are not executed
concurrently. The only exception is that Cloud Spanner might execute multiple
SELECT
statements concurrently, because they are read-only operations.
Transaction limits
A transaction that includes DML statements has the same limits as any other transaction. If you have large-scale changes, consider using Partitioned DML.
If the DML statements in a transaction result in more than 20,000 mutations, the DML statement that pushes the transaction over the limit returns a
BadUsage
error with a message about too many mutations.If the DML statements in a transaction result in a transaction that is larger than 100 MB, the DML statement that pushes the transaction over the limit returns a
BadUsage
error with a message about the transaction exceeding the size limit.
Mutations performed using DML are not returned to the client. They are merged into the commit request when it is committed, and they count towards the maximum size limits. Even if the size of the commit request that you send is small, the transaction might still exceed the allowed size limit.
Run statements in the console
Use the following steps to execute a DML statement in the console.
Go to the Cloud Spanner Instances page.
Select your project in the drop-down list in the toolbar.
Click the name of the instance that contains your database to go to the Instance details page.
In the Overview tab, click the name of your database. The Database details page appears.
Click Query.
Enter a DML statement. For example, the following statement adds a new row to the
Singers
table.INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')
Click Run query. The console displays the result.
Execute statements with the Google Cloud CLI
To execute DML statements, use the gcloud spanner databases execute-sql
command. The following example adds a new row to the Singers
table.
gcloud spanner databases execute-sql example-db --instance=test-instance \ --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"
Modify data using the client library
To execute DML statements using the client library:
- Create a read-write transaction.
- Call the client library method for DML execution and pass in the DML statement.
- Use the return value of the DML execution method to get the number of rows inserted, updated, or deleted.
The following code example inserts a new row into the Singers
table.
C++
You use the ExecuteDml()
function to execute a DML statement.
C#
You use the ExecuteNonQueryAsync()
method to execute a DML statement.
Go
You use the Update()
method to execute a DML statement.
Java
You use the executeUpdate()
method to execute a DML statement.
Node.js
You use the runUpdate()
method to execute a DML statement.
PHP
You use the executeUpdate()
method to execute a DML statement.
Python
You use the execute_update()
method to execute a DML statement.
Ruby
You use the execute_update()
method to execute a DML statement.
The following code example updates the MarketingBudget
column of the Albums
table based on a WHERE
clause.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
The following code example deletes all the rows in the Singers
table where
the FirstName
column is Alice
.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
The following example, for Google Standard SQL-dialect databases only, uses a
STRUCT
with bound parameters
to update the LastName
in rows filtered by FirstName
and LastName
.
Google Standard SQL
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Read data written in the same transaction
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 until the transaction commits.
Cloud Spanner checks the constraints after every DML statement. This is different from using mutations, where Cloud Spanner buffers mutations in the client until commit and checks constraints at commit time. Evaluating the constraints after each statement allows Cloud Spanner to guarantee that the data that a DML statement returns is consistent with the schema.
The following example updates a row in the Singers
table, then executes a
SELECT
statement to print the new values.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Get the query plan
You can retrieve a query plan
using the console, the client libraries, and the gcloud
command-line tool.
Use Partitioned DML
Partitioned DML is designed for bulk updates and deletes, particularly periodic cleanup and backfilling.
Execute statements with the Google Cloud CLI
To execute a Partitioned DML statement, use the
gcloud spanner databases execute-sql
command with the
--enable-partitioned-dml
option. The following example updates rows in the
Albums
table.
gcloud spanner databases execute-sql example-db \ --instance=test-instance --enable-partitioned-dml \ --sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'
Modify data using the client library
The following code example updates the MarketingBudget
column of the Albums
table.
C++
You use the ExecutePartitionedDml()
function to execute a Partitioned DML statement.
C#
You use the ExecutePartitionedUpdateAsync()
method to execute a Partitioned DML statement.
Go
You use the PartitionedUpdate()
method to execute a Partitioned DML statement.
Java
You use the executePartitionedUpdate()
method to execute a Partitioned DML statement.
Node.js
You use the runPartitionedUpdate()
method to execute a Partitioned DML statement.
PHP
You use the executePartitionedUpdate()
method to execute a Partitioned DML statement.
Python
You use the execute_partitioned_dml()
method to execute a Partitioned DML statement.
Ruby
You use the execute_partitioned_update()
method to execute a Partitioned DML statement.
The following code example deletes rows from the Singers
table, based on the
SingerId
column.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Use batch DML
If you need to avoid the extra latency incurred from multiple serial requests,
use batch DML to send multiple INSERT
, UPDATE
, or DELETE
statements in a
single transaction:
C++
Use the ExecuteBatchDml()
function to execute a list of DML statements.
C#
Use the connection.CreateBatchDmlCommand()
method to create your batch
command, use the Add
method to add DML statements, and execute the
statements with the ExecuteNonQueryAsync()
method.
Go
Use the BatchUpdate()
method to execute an array of DML Statement
objects.
Java
Use the transaction.batchUpdate()
method to execute an ArrayList
of
multiple DML Statement
objects.
Node.js
Use transaction.batchUpdate()
to execute a list of DML statements.
PHP
Use executeUpdateBatch()
to create a list of DML statements, then use
commit()
to execute the statements.
Python
Use transaction.batch_update()
to execute multiple DML statement strings.
Ruby
Use transaction.batch_update
to execute multiple DML statement strings.