Insert, update, and delete data using mutations

This page describes how to insert, update, and delete data using mutations. A mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a database. Mutations are designed for writing data. They can't read data from your tables. Many update operations must read existing data before performing modifications. For these use cases, you must use a read-write transaction, which lets Spanner read rows and then apply mutations within the same atomic operation.

Although you can commit mutations by using gRPC or REST, it is more common to access the APIs through the client libraries.

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.

Insert new rows in a table

C++

You write data using the InsertMutationBuilder() function. Client::Commit() adds new rows to a table. All inserts in a single batch are applied atomically.

This code shows how to write the data:

voidInsertData(google::cloud::spanner::Clientclient){
namespacespanner=::google::cloud::spanner;
autoinsert_singers=spanner::InsertMutationBuilder(
"Singers",{"SingerId","FirstName","LastName"})
.EmplaceRow(1,"Marc","Richards")
.EmplaceRow(2,"Catalina","Smith")
.EmplaceRow(3,"Alice","Trentor")
.EmplaceRow(4,"Lea","Martin")
.EmplaceRow(5,"David","Lomond")
.Build();
autoinsert_albums=spanner::InsertMutationBuilder(
"Albums",{"SingerId","AlbumId","AlbumTitle"})
.EmplaceRow(1,1,"Total Junk")
.EmplaceRow(1,2,"Go, Go, Go")
.EmplaceRow(2,1,"Green")
.EmplaceRow(2,2,"Forever Hold Your Peace")
.EmplaceRow(2,3,"Terrified")
.Build();
autocommit_result=
client.Commit(spanner::Mutations{insert_singers,insert_albums});
if(!commit_result)throwstd::move(commit_result).status();
std::cout << "Insert was successful [spanner_insert_data]\n";
}

C#

You can insert data using the connection.CreateInsertCommand() method, which creates a new SpannerCommand to insert rows into a table. The SpannerCommand.ExecuteNonQueryAsync() method adds new rows to the table.

This code shows how to insert data:


usingGoogle.Cloud.Spanner.Data ;
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Threading.Tasks;
publicclassInsertDataAsyncSample
{
publicclassSinger
{
publicintSingerId{get;set;}
publicstringFirstName{get;set;}
publicstringLastName{get;set;}
}
publicclassAlbum
{
publicintSingerId{get;set;}
publicintAlbumId{get;set;}
publicstringAlbumTitle{get;set;}
}
publicasyncTaskInsertDataAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
List<Singer>singers=newList<Singer>
{
newSinger{SingerId=1,FirstName="Marc",LastName="Richards"},
newSinger{SingerId=2,FirstName="Catalina",LastName="Smith"},
newSinger{SingerId=3,FirstName="Alice",LastName="Trentor"},
newSinger{SingerId=4,FirstName="Lea",LastName="Martin"},
newSinger{SingerId=5,FirstName="David",LastName="Lomond"},
};
List<Album>albums=newList<Album>
{
newAlbum{SingerId=1,AlbumId=1,AlbumTitle="Total Junk"},
newAlbum{SingerId=1,AlbumId=2,AlbumTitle="Go, Go, Go"},
newAlbum{SingerId=2,AlbumId=1,AlbumTitle="Green"},
newAlbum{SingerId=2,AlbumId=2,AlbumTitle="Forever Hold your Peace"},
newAlbum{SingerId=2,AlbumId=3,AlbumTitle="Terrified"},
};
// Create connection to Cloud Spanner.
usingvarconnection=newSpannerConnection (connectionString);
awaitconnection.OpenAsync ();
awaitconnection.RunWithRetriableTransactionAsync(asynctransaction=>
{
awaitTask.WhenAll(singers.Select(singer=>
{
// Insert rows into the Singers table.
usingvarcmd=connection.CreateInsertCommand("Singers",newSpannerParameterCollection
{
{"SingerId",SpannerDbType.Int64,singer.SingerId},
{"FirstName",SpannerDbType.String,singer.FirstName},
{"LastName",SpannerDbType.String,singer.LastName}
});
cmd.Transaction=transaction;
returncmd.ExecuteNonQueryAsync();
}));
awaitTask.WhenAll(albums.Select(album=>
{
// Insert rows into the Albums table.
usingvarcmd=connection.CreateInsertCommand("Albums",newSpannerParameterCollection
{
{"SingerId",SpannerDbType.Int64,album.SingerId},
{"AlbumId",SpannerDbType.Int64,album.AlbumId},
{"AlbumTitle",SpannerDbType.String,album.AlbumTitle}
});
cmd.Transaction=transaction;
returncmd.ExecuteNonQueryAsync();
}));
});
Console.WriteLine("Data inserted.");
}
}

Go

You write data using a Mutation. A Mutation is a container for mutation operations. A Mutation represents a sequence of inserts, updates, or deletes that can be applied atomically to different rows and tables in a Spanner database.

Use Mutation.InsertOrUpdate() to construct an INSERT_OR_UPDATE mutation, which adds a new row or updates column values if the row already exists. Alternatively, use Mutation.Insert() method to construct an INSERT mutation, which adds a new row.

Client.Apply() applies mutations atomically to a database.

This code shows how to write the data:


import(
"context"
"io"
"cloud.google.com/go/spanner"
)
funcwrite(wio.Writer,dbstring)error{
ctx:=context.Background()
client,err:=spanner.NewClient(ctx,db)
iferr!=nil{
returnerr
}
deferclient.Close()
singerColumns:=[]string{"SingerId","FirstName","LastName"}
albumColumns:=[]string{"SingerId","AlbumId","AlbumTitle"}
m:=[]*spanner.Mutation{
spanner.InsertOrUpdate ("Singers",singerColumns,[]interface{}{1,"Marc","Richards"}),
spanner.InsertOrUpdate ("Singers",singerColumns,[]interface{}{2,"Catalina","Smith"}),
spanner.InsertOrUpdate ("Singers",singerColumns,[]interface{}{3,"Alice","Trentor"}),
spanner.InsertOrUpdate ("Singers",singerColumns,[]interface{}{4,"Lea","Martin"}),
spanner.InsertOrUpdate ("Singers",singerColumns,[]interface{}{5,"David","Lomond"}),
spanner.InsertOrUpdate ("Albums",albumColumns,[]interface{}{1,1,"Total Junk"}),
spanner.InsertOrUpdate ("Albums",albumColumns,[]interface{}{1,2,"Go, Go, Go"}),
spanner.InsertOrUpdate ("Albums",albumColumns,[]interface{}{2,1,"Green"}),
spanner.InsertOrUpdate ("Albums",albumColumns,[]interface{}{2,2,"Forever Hold Your Peace"}),
spanner.InsertOrUpdate ("Albums",albumColumns,[]interface{}{2,3,"Terrified"}),
}
_,err=client.Apply (ctx,m)
returnerr
}

Java

You write data using a Mutation object. A Mutation object is a container for mutation operations. A Mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a Spanner database.

The newInsertBuilder() method in the Mutation class constructs an INSERT mutation, which inserts a new row in a table. If the row already exists, the write fails. Alternatively, you can use the newInsertOrUpdateBuilder method to construct an INSERT_OR_UPDATE mutation, which updates column values if the row already exists.

The write() method in the DatabaseClient class writes the mutations. All mutations in a single batch are applied atomically.

This code shows how to write the data:

staticfinalList<Singer>SINGERS=
Arrays.asList(
newSinger(1,"Marc","Richards"),
newSinger(2,"Catalina","Smith"),
newSinger(3,"Alice","Trentor"),
newSinger(4,"Lea","Martin"),
newSinger(5,"David","Lomond"));
staticfinalList<Album>ALBUMS=
Arrays.asList(
newAlbum(1,1,"Total Junk"),
newAlbum(1,2,"Go, Go, Go"),
newAlbum(2,1,"Green"),
newAlbum(2,2,"Forever Hold Your Peace"),
newAlbum(2,3,"Terrified"));
staticvoidwriteExampleData(DatabaseClientdbClient){
List<Mutation>mutations=newArrayList<>();
for(Singersinger:SINGERS){
mutations.add(
Mutation.newInsertBuilder("Singers")
.set("SingerId")
.to(singer.singerId)
.set("FirstName")
.to(singer.firstName)
.set("LastName")
.to(singer.lastName)
.build());
}
for(Albumalbum:ALBUMS){
mutations.add(
Mutation.newInsertBuilder("Albums")
.set("SingerId")
.to(album.singerId)
.set("AlbumId")
.to(album.albumId)
.set("AlbumTitle")
.to(album.albumTitle)
.build());
}
dbClient.write(mutations);
}

Node.js

You write data using a Table object. The Table.insert() method adds new rows to the table. All inserts in a single batch are applied atomically.

This code shows how to write the data:

// Imports the Google Cloud client library
const{Spanner}=require('@google-cloud/spanner');
/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
constspanner=newSpanner ({
projectId:projectId,
});
// Gets a reference to a Cloud Spanner instance and database
constinstance=spanner.instance(instanceId);
constdatabase=instance.database(databaseId);
// Instantiate Spanner table objects
constsingersTable=database.table('Singers');
constalbumsTable=database.table('Albums');
// Inserts rows into the Singers table
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
// they must be converted to strings before being inserted as INT64s
try{
awaitsingersTable.insert([
{SingerId:'1',FirstName:'Marc',LastName:'Richards'},
{SingerId:'2',FirstName:'Catalina',LastName:'Smith'},
{SingerId:'3',FirstName:'Alice',LastName:'Trentor'},
{SingerId:'4',FirstName:'Lea',LastName:'Martin'},
{SingerId:'5',FirstName:'David',LastName:'Lomond'},
]);
awaitalbumsTable.insert([
{SingerId:'1',AlbumId:'1',AlbumTitle:'Total Junk'},
{SingerId:'1',AlbumId:'2',AlbumTitle:'Go, Go, Go'},
{SingerId:'2',AlbumId:'1',AlbumTitle:'Green'},
{SingerId:'2',AlbumId:'2',AlbumTitle:'Forever Hold your Peace'},
{SingerId:'2',AlbumId:'3',AlbumTitle:'Terrified'},
]);
console.log('Inserted data.');
}catch(err){
console.error('ERROR:',err);
}finally{
awaitdatabase.close();
}

PHP

You write data using the Database::insertBatch method. insertBatch adds new rows to a table. All inserts in a single batch are applied atomically.

This code shows how to write the data:

use Google\Cloud\Spanner\SpannerClient;
/**
 * Inserts sample data into the given database.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_data(string $instanceId, string $databaseId): void
{
 $spanner = new SpannerClient();
 $instance = $spanner->instance($instanceId);
 $database = $instance->database($databaseId);
 $operation = $database->transaction(['singleUse' => true])
 ->insertBatch('Singers', [
 ['SingerId' => 1, 'FirstName' => 'Marc', 'LastName' => 'Richards'],
 ['SingerId' => 2, 'FirstName' => 'Catalina', 'LastName' => 'Smith'],
 ['SingerId' => 3, 'FirstName' => 'Alice', 'LastName' => 'Trentor'],
 ['SingerId' => 4, 'FirstName' => 'Lea', 'LastName' => 'Martin'],
 ['SingerId' => 5, 'FirstName' => 'David', 'LastName' => 'Lomond'],
 ])
 ->insertBatch('Albums', [
 ['SingerId' => 1, 'AlbumId' => 1, 'AlbumTitle' => 'Total Junk'],
 ['SingerId' => 1, 'AlbumId' => 2, 'AlbumTitle' => 'Go, Go, Go'],
 ['SingerId' => 2, 'AlbumId' => 1, 'AlbumTitle' => 'Green'],
 ['SingerId' => 2, 'AlbumId' => 2, 'AlbumTitle' => 'Forever Hold Your Peace'],
 ['SingerId' => 2, 'AlbumId' => 3, 'AlbumTitle' => 'Terrified']
 ])
 ->commit();
 print('Inserted data.' . PHP_EOL);
}

Python

You write data using a Batch object. A Batch object is a container for mutation operations. A mutation represents a sequence of inserts, updates, or deletes that can be applied atomically to different rows and tables in a Spanner database.

The insert() method in the Batch class is used to add one or more insert mutations to the batch. All mutations in a single batch are applied atomically.

This code shows how to write the data:

definsert_data(instance_id, database_id):
"""Inserts sample data into the given database.
 The database and table must already exist and can be created using
 `create_database`.
 """
 spanner_client = spanner.Client()
 instance = spanner_client.instance(instance_id)
 database = instance.database(database_id)
 with database.batch() as batch:
 batch.insert(
 table="Singers",
 columns=("SingerId", "FirstName", "LastName"),
 values=[
 (1, "Marc", "Richards"),
 (2, "Catalina", "Smith"),
 (3, "Alice", "Trentor"),
 (4, "Lea", "Martin"),
 (5, "David", "Lomond"),
 ],
 )
 batch.insert(
 table="Albums",
 columns=("SingerId", "AlbumId", "AlbumTitle"),
 values=[
 (1, 1, "Total Junk"),
 (1, 2, "Go, Go, Go"),
 (2, 1, "Green"),
 (2, 2, "Forever Hold Your Peace"),
 (2, 3, "Terrified"),
 ],
 )
 print("Inserted data.")

Ruby

You write data using a Client object. The Client#commit method creates and commits a transaction for writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.

This code shows how to write the data:

# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require"google/cloud/spanner"
spanner=Google::Cloud::Spanner.newproject:project_id
client=spanner.clientinstance_id,database_id
client.commitdo|c|
c.insert"Singers",[
{SingerId:1,FirstName:"Marc",LastName:"Richards"},
{SingerId:2,FirstName:"Catalina",LastName:"Smith"},
{SingerId:3,FirstName:"Alice",LastName:"Trentor"},
{SingerId:4,FirstName:"Lea",LastName:"Martin"},
{SingerId:5,FirstName:"David",LastName:"Lomond"}
]
c.insert"Albums",[
{SingerId:1,AlbumId:1,AlbumTitle:"Total Junk"},
{SingerId:1,AlbumId:2,AlbumTitle:"Go, Go, Go"},
{SingerId:2,AlbumId:1,AlbumTitle:"Green"},
{SingerId:2,AlbumId:2,AlbumTitle:"Forever Hold Your Peace"},
{SingerId:2,AlbumId:3,AlbumTitle:"Terrified"}
]
end
puts"Inserted data"

Update rows in a table

Suppose that sales of Albums(1, 1) are lower than expected. As a result, you want to move 200,000ドル from the marketing budget of Albums(2, 2) to Albums(1, 1), but only if the money is available in the budget of Albums(2, 2).

C++

Use the Transaction() function to run a transaction for a client.

Here's the code to run the transaction:

voidReadWriteTransaction(google::cloud::spanner::Clientclient){
namespacespanner=::google::cloud::spanner;
using::google::cloud::StatusOr;
// A helper to read a single album MarketingBudget.
autoget_current_budget=
[](spanner::Clientclient,spanner::Transactiontxn,
std::int64_tsinger_id,
std::int64_talbum_id)->StatusOr<std::int64_t>{
autokey=spanner::KeySet().AddKey(spanner::MakeKey(singer_id,album_id));
autorows=client.Read(std::move(txn),"Albums",std::move(key),
{"MarketingBudget"});
usingRowType=std::tuple<std::int64_t>;
autorow=spanner::GetSingularRow(spanner::StreamOf<RowType>(rows));
if(!row)returnstd::move(row).status();
returnstd::get<0>(*std::move(row));
};
autocommit=client.Commit(
[&client,&get_current_budget](
spanner::Transactionconst&txn)->StatusOr<spanner::Mutations>{
autob1=get_current_budget(client,txn,1,1);
if(!b1)returnstd::move(b1).status();
autob2=get_current_budget(client,txn,2,2);
if(!b2)returnstd::move(b2).status();
std::int64_ttransfer_amount=200000;
returnspanner::Mutations{
spanner::UpdateMutationBuilder(
"Albums",{"SingerId","AlbumId","MarketingBudget"})
.EmplaceRow(1,1,*b1+transfer_amount)
.EmplaceRow(2,2,*b2-transfer_amount)
.Build()};
});
if(!commit)throwstd::move(commit).status();
std::cout << "Transfer was successful [spanner_read_write_transaction]\n";
}

C#

For .NET Standard 2.0 (or .NET 4.5) and newer, you can use the .NET framework's TransactionScope() to run a transaction. For all supported versions of .NET, you can create a transaction by setting the result of SpannerConnection.BeginTransactionAsync as the Transaction property of SpannerCommand.

Here are the two ways to run the transaction:

.NET Standard 2.0


usingGoogle.Cloud.Spanner.Data ;
usingSystem;
usingSystem.Threading.Tasks;
usingSystem.Transactions;
publicclassReadWriteWithTransactionAsyncSample
{
publicasyncTask<int>ReadWriteWithTransactionAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the Add Column and Write Data To New Column samples first,
// in that order.
stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
usingTransactionScopescope=newTransactionScope(TransactionScopeAsyncFlowOption.Enabled);
decimaltransferAmount=200000;
decimalsecondBudget=0;
decimalfirstBudget=0;
usingvarconnection=newSpannerConnection (connectionString);
usingvarcmdLookup1=connection.CreateSelectCommand ("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
using(varreader=awaitcmdLookup1.ExecuteReaderAsync ())
{
while(awaitreader.ReadAsync ())
{
// Read the second album's budget.
secondBudget=reader.GetFieldValue<decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if(secondBudget < transferAmount)
{
thrownewException($"The second album's budget {secondBudget} is less than the amount to transfer.");
}
}
}
// Read the first album's budget.
usingvarcmdLookup2=connection.CreateSelectCommand ("SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
using(varreader=awaitcmdLookup2.ExecuteReaderAsync ())
{
while(awaitreader.ReadAsync ())
{
firstBudget=reader.GetFieldValue<decimal>("MarketingBudget");
}
}
// Specify update command parameters.
usingvarcmdUpdate=connection.CreateUpdateCommand ("Albums",newSpannerParameterCollection
{
{"SingerId",SpannerDbType .Int64 },
{"AlbumId",SpannerDbType .Int64 },
{"MarketingBudget",SpannerDbType .Int64 },
});
// Update second album to remove the transfer amount.
secondBudget-=transferAmount;
cmdUpdate.Parameters ["SingerId"].Value=2;
cmdUpdate.Parameters ["AlbumId"].Value=2;
cmdUpdate.Parameters ["MarketingBudget"].Value=secondBudget;
varrowCount=awaitcmdUpdate.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget+=transferAmount;
cmdUpdate.Parameters ["SingerId"].Value=1;
cmdUpdate.Parameters ["AlbumId"].Value=1;
cmdUpdate.Parameters ["MarketingBudget"].Value=firstBudget;
rowCount+=awaitcmdUpdate.ExecuteNonQueryAsync();
scope.Complete();
Console.WriteLine("Transaction complete.");
returnrowCount;
}
}

.NET Standard 1.5


usingGoogle.Cloud.Spanner.Data ;
usingSystem;
usingSystem.Threading.Tasks;
publicclassReadWriteWithTransactionCoreAsyncSample
{
publicasyncTask<int>ReadWriteWithTransactionCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the Add Column and Write Data To New Column samples first,
// in that order.
stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
decimaltransferAmount=200000;
decimalsecondBudget=0;
decimalfirstBudget=0;
usingvarconnection=newSpannerConnection (connectionString);
awaitconnection.OpenAsync ();
usingvartransaction=awaitconnection.BeginTransactionAsync ();
usingvarcmdLookup1=connection.CreateSelectCommand ("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup1.Transaction =transaction;
using(varreader=awaitcmdLookup1.ExecuteReaderAsync ())
{
while(awaitreader.ReadAsync ())
{
// Read the second album's budget.
secondBudget=reader.GetFieldValue<decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if(secondBudget < transferAmount)
{
thrownewException($"The second album's budget {secondBudget} contains less than the amount to transfer.");
}
}
}
// Read the first album's budget.
usingvarcmdLookup2=connection.CreateSelectCommand ("SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
cmdLookup2.Transaction =transaction;
using(varreader=awaitcmdLookup2.ExecuteReaderAsync ())
{
while(awaitreader.ReadAsync ())
{
firstBudget=reader.GetFieldValue<decimal>("MarketingBudget");
}
}
// Specify update command parameters.
usingvarcmdUpdate=connection.CreateUpdateCommand ("Albums",newSpannerParameterCollection
{
{"SingerId",SpannerDbType .Int64 },
{"AlbumId",SpannerDbType .Int64 },
{"MarketingBudget",SpannerDbType .Int64 },
});
cmdUpdate.Transaction =transaction;
// Update second album to remove the transfer amount.
secondBudget-=transferAmount;
cmdUpdate.Parameters ["SingerId"].Value=2;
cmdUpdate.Parameters ["AlbumId"].Value=2;
cmdUpdate.Parameters ["MarketingBudget"].Value=secondBudget;
varrowCount=awaitcmdUpdate.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget+=transferAmount;
cmdUpdate.Parameters ["SingerId"].Value=1;
cmdUpdate.Parameters ["AlbumId"].Value=1;
cmdUpdate.Parameters ["MarketingBudget"].Value=firstBudget;
rowCount+=awaitcmdUpdate.ExecuteNonQueryAsync();
awaittransaction.CommitAsync();
Console.WriteLine("Transaction complete.");
returnrowCount;
}
}

Go

Use the ReadWriteTransaction type for executing a body of work in the context of a read-write transaction. Client.ReadWriteTransaction() returns a ReadWriteTransaction object.

The sample uses ReadWriteTransaction.ReadRow() to retrieve a row of data.

The sample also uses ReadWriteTransaction.BufferWrite(), which adds a list of mutations to the set of updates that will be applied when the transaction is committed.

The sample also uses the Key type, which represents a row key in a Spanner table or index.


import(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
funcwriteWithTransaction(wio.Writer,dbstring)error{
ctx:=context.Background()
client,err:=spanner.NewClient(ctx,db)
iferr!=nil{
returnerr
}
deferclient.Close()
_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{
getBudget:=func(keyspanner.Key )(int64,error){
row,err:=txn.ReadRow(ctx,"Albums",key,[]string{"MarketingBudget"})
iferr!=nil{
return0,err
}
varbudgetint64
iferr:=row.Column (0,&budget);err!=nil{
return0,err
}
returnbudget,nil
}
album2Budget,err:=getBudget(spanner.Key {2,2})
iferr!=nil{
returnerr
}
consttransferAmt=200000
ifalbum2Budget>=transferAmt{
album1Budget,err:=getBudget(spanner.Key{1,1})
iferr!=nil{
returnerr
}
album1Budget+=transferAmt
album2Budget-=transferAmt
cols:=[]string{"SingerId","AlbumId","MarketingBudget"}
txn.BufferWrite([]*spanner.Mutation{
spanner.Update("Albums",cols,[]interface{}{1,1,album1Budget}),
spanner.Update("Albums",cols,[]interface{}{2,2,album2Budget}),
})
fmt.Fprintf(w,"Moved %d from Album2's MarketingBudget to Album1's.",transferAmt)
}
returnnil
})
returnerr
}

Java

Use the TransactionRunnerinterface for executing a body of work in the context of a read-write transaction. This interface contains the method run(), which is used to execute a read- write transaction, with retries as necessary. The readWriteTransaction method of the DatabaseClient class returns a TransactionRunner object for executing a single logical transaction.

The TransactionRunner.TransactionCallable class contains a run() method for performing a single attempt of a transaction. run() takes a TransactionContext object, which is a context for a transaction.

The sample uses the Struct class, which is handy for storing the results of the readRow() calls. The sample also uses the Key class, which represents a row key in a Spanner table or index.

Here's the code to run the transaction:

staticvoidwriteWithTransaction(DatabaseClientdbClient){
dbClient
.readWriteTransaction()
.run(transaction->{
// Transfer marketing budget from one album to another. We do it in a transaction to
// ensure that the transfer is atomic.
Structrow=
transaction.readRow("Albums",Key.of(2,2),Arrays.asList("MarketingBudget"));
longalbum2Budget=row.getLong(0);
// Transaction will only be committed if this condition still holds at the time of
// commit. Otherwise it will be aborted and the callable will be rerun by the
// client library.
longtransfer=200000;
if(album2Budget>=transfer){
longalbum1Budget=
transaction
.readRow("Albums",Key.of(1,1),Arrays.asList("MarketingBudget"))
.getLong(0);
album1Budget+=transfer;
album2Budget-=transfer;
transaction.buffer(
Mutation.newUpdateBuilder("Albums")
.set("SingerId")
.to(1)
.set("AlbumId")
.to(1)
.set("MarketingBudget")
.to(album1Budget)
.build());
transaction.buffer(
Mutation.newUpdateBuilder("Albums")
.set("SingerId")
.to(2)
.set("AlbumId")
.to(2)
.set("MarketingBudget")
.to(album2Budget)
.build());
}
returnnull;
});
}

Node.js

Use Database.runTransaction() to run a transaction.

Here's the code to run the transaction:

// This sample transfers 200,000 from the MarketingBudget field
// of the second Album to the first Album, as long as the second
// Album has enough money in its budget. Make sure to run the
// addColumn and updateData samples first (in that order).
// Imports the Google Cloud client library
const{Spanner}=require('@google-cloud/spanner');
/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
constspanner=newSpanner ({
projectId:projectId,
});
// Gets a reference to a Cloud Spanner instance and database
constinstance=spanner.instance(instanceId);
constdatabase=instance.database(databaseId);
consttransferAmount=200000;
// Note: the `runTransaction()` method is non blocking and returns "void".
// For sequential execution of the transaction use `runTransactionAsync()` method which returns a promise.
// For example: await database.runTransactionAsync(async (err, transaction) => { ... })
database.runTransaction (async(err,transaction)=>{
if(err){
console.error(err);
return;
}
letfirstBudget,secondBudget;
constqueryOne={
columns:['MarketingBudget'],
keys:[[2,2]],// SingerId: 2, AlbumId: 2
};
constqueryTwo={
columns:['MarketingBudget'],
keys:[[1,1]],// SingerId: 1, AlbumId: 1
};
Promise.all([
// Reads the second album's budget
transaction.read('Albums',queryOne).then(results=>{
// Gets second album's budget
constrows=results[0].map(row=>row.toJSON());
secondBudget=rows[0].MarketingBudget;
console.log(`The second album's marketing budget: ${secondBudget}`);
// Makes sure the second album's budget is large enough
if(secondBudget < transferAmount){
thrownewError(
`The second album's budget (${secondBudget}) is less than the transfer amount (${transferAmount}).`,
);
}
}),
// Reads the first album's budget
transaction.read('Albums',queryTwo).then(results=>{
// Gets first album's budget
constrows=results[0].map(row=>row.toJSON());
firstBudget=rows[0].MarketingBudget;
console.log(`The first album's marketing budget: ${firstBudget}`);
}),
])
.then(()=>{
console.log(firstBudget,secondBudget);
// Transfers the budgets between the albums
firstBudget+=transferAmount;
secondBudget-=transferAmount;
console.log(firstBudget,secondBudget);
// Updates the database
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted (back) to strings before being inserted as INT64s.
transaction.update('Albums',[
{
SingerId:'1',
AlbumId:'1',
MarketingBudget:firstBudget.toString(),
},
{
SingerId:'2',
AlbumId:'2',
MarketingBudget:secondBudget.toString(),
},
]);
})
.then(()=>{
// Commits the transaction and send the changes to the database
returntransaction.commit();
})
.then(()=>{
console.log(
`Successfully executed read-write transaction to transfer ${transferAmount} from Album 2 to Album 1.`,
);
})
.catch(err=>{
console.error('ERROR:',err);
})
.then(()=>{
transaction.end();
// Closes the database when finished
returndatabase.close();
});
});

PHP

Use Database::runTransaction to run a transaction.

Here's the code to run the transaction:

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
use UnexpectedValueException;
/**
 * Performs a read-write transaction to update two sample records in the
 * database.
 *
 * This will transfer 200,000 from the `MarketingBudget` field for the second
 * Album to the first Album. If the `MarketingBudget` for the second Album is
 * too low, it will raise an exception.
 *
 * Before running this sample, you will need to run the `update_data` sample
 * to populate the fields.
 * Example:
 * ```
 * read_write_transaction($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_write_transaction(string $instanceId, string $databaseId): void
{
 $spanner = new SpannerClient();
 $instance = $spanner->instance($instanceId);
 $database = $instance->database($databaseId);
 $database->runTransaction(function (Transaction $t) use ($spanner) {
 $transferAmount = 200000;
 // Read the second album's budget.
 $secondAlbumKey = [2, 2];
 $secondAlbumKeySet = $spanner->keySet(['keys' => [$secondAlbumKey]]);
 $secondAlbumResult = $t->read(
 'Albums',
 $secondAlbumKeySet,
 ['MarketingBudget'],
 ['limit' => 1]
 );
 $firstRow = $secondAlbumResult->rows()->current();
 $secondAlbumBudget = $firstRow['MarketingBudget'];
 if ($secondAlbumBudget < $transferAmount) {
 // Throwing an exception will automatically roll back the transaction.
 throw new UnexpectedValueException(
 'The second album\'s budget is lower than the transfer amount: ' . $transferAmount
 );
 }
 $firstAlbumKey = [1, 1];
 $firstAlbumKeySet = $spanner->keySet(['keys' => [$firstAlbumKey]]);
 $firstAlbumResult = $t->read(
 'Albums',
 $firstAlbumKeySet,
 ['MarketingBudget'],
 ['limit' => 1]
 );
 // Read the first album's budget.
 $firstRow = $firstAlbumResult->rows()->current();
 $firstAlbumBudget = $firstRow['MarketingBudget'];
 // Update the budgets.
 $secondAlbumBudget -= $transferAmount;
 $firstAlbumBudget += $transferAmount;
 printf('Setting first album\'s budget to %s and the second album\'s ' .
 'budget to %s.' . PHP_EOL, $firstAlbumBudget, $secondAlbumBudget);
 // Update the rows.
 $t->updateBatch('Albums', [
 ['SingerId' => 1, 'AlbumId' => 1, 'MarketingBudget' => $firstAlbumBudget],
 ['SingerId' => 2, 'AlbumId' => 2, 'MarketingBudget' => $secondAlbumBudget],
 ]);
 // Commit the transaction!
 $t->commit();
 print('Transaction complete.' . PHP_EOL);
 });
}

Python

Use the run_in_transaction() method of the Database class to run a transaction.

Here's the code to run the transaction:

defread_write_transaction(instance_id, database_id):
"""Performs a read-write transaction to update two sample records in the
 database.
 This will transfer 200,000 from the `MarketingBudget` field for the second
 Album to the first Album. If the `MarketingBudget` is too low, it will
 raise an exception.
 Before running this sample, you will need to run the `update_data` sample
 to populate the fields.
 """
 spanner_client = spanner.Client()
 instance = spanner_client.instance(instance_id)
 database = instance.database(database_id)
 defupdate_albums(transaction):
 # Read the second album budget.
 second_album_keyset = spanner.KeySet(keys=[(2, 2)])
 second_album_result = transaction.read(
 table="Albums",
 columns=("MarketingBudget",),
 keyset=second_album_keyset,
 limit=1,
 )
 second_album_row = list(second_album_result)[0]
 second_album_budget = second_album_row[0]
 transfer_amount = 200000
 if second_album_budget < transfer_amount:
 # Raising an exception will automatically roll back the
 # transaction.
 raise ValueError("The second album doesn't have enough funds to transfer")
 # Read the first album's budget.
 first_album_keyset = spanner.KeySet(keys=[(1, 1)])
 first_album_result = transaction.read(
 table="Albums",
 columns=("MarketingBudget",),
 keyset=first_album_keyset,
 limit=1,
 )
 first_album_row = list(first_album_result)[0]
 first_album_budget = first_album_row[0]
 # Update the budgets.
 second_album_budget -= transfer_amount
 first_album_budget += transfer_amount
 print(
 "Setting first album's budget to {} and the second album's "
 "budget to {}.".format(first_album_budget, second_album_budget)
 )
 # Update the rows.
 transaction.update(
 table="Albums",
 columns=("SingerId", "AlbumId", "MarketingBudget"),
 values=[(1, 1, first_album_budget), (2, 2, second_album_budget)],
 )
 database.run_in_transaction(update_albums)
 print("Transaction complete.")

Ruby

Use the transaction method of the Client class to run a transaction.

Here's the code to run the transaction:

# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require"google/cloud/spanner"
spanner=Google::Cloud::Spanner.newproject:project_id
client=spanner.clientinstance_id,database_id
transfer_amount=200_000
client.transactiondo|transaction|
first_album=transaction.read("Albums",[:MarketingBudget],keys:[[1,1]]).rows.first
second_album=transaction.read("Albums",[:MarketingBudget],keys:[[2,2]]).rows.first
raise"The second album does not have enough funds to transfer"ifsecond_album[:MarketingBudget] < transfer_amount
new_first_album_budget=first_album[:MarketingBudget]+transfer_amount
new_second_album_budget=second_album[:MarketingBudget]-transfer_amount
transaction.update"Albums",[
{SingerId:1,AlbumId:1,MarketingBudget:new_first_album_budget},
{SingerId:2,AlbumId:2,MarketingBudget:new_second_album_budget}
]
end
puts"Transaction complete"

Delete rows in a table

Each client library provides multiple ways to delete rows:

  • Delete all the rows in a table.
  • Delete a single row by specifying the key column values for the row.
  • Delete a group of rows by creating a key range.
  • Delete rows in an interleaved table by deleting the parent rows, if the interleaved table includes ON DELETE CASCADE in its schema definition.

C++

Delete rows using the DeleteMutationBuilder() function for a client.

This code shows how to delete the data:

voidDeleteData(google::cloud::spanner::Clientclient){
namespacespanner=::google::cloud::spanner;
// Delete the albums with key (2,1) and (2,3).
autodelete_albums=spanner::DeleteMutationBuilder(
"Albums",spanner::KeySet()
.AddKey(spanner::MakeKey(2,1))
.AddKey(spanner::MakeKey(2,3)))
.Build();
// Delete some singers using the keys in the range [3, 5]
autodelete_singers_range=
spanner::DeleteMutationBuilder(
"Singers",spanner::KeySet().AddRange(spanner::MakeKeyBoundClosed(3),
spanner::MakeKeyBoundOpen(5)))
.Build();
// Deletes remaining rows from the Singers table and the Albums table, because
// the Albums table is defined with ON DELETE CASCADE.
autodelete_singers_all=
spanner::MakeDeleteMutation("Singers",spanner::KeySet::All());
autocommit_result=client.Commit(spanner::Mutations{
delete_albums,delete_singers_range,delete_singers_all});
if(!commit_result)throwstd::move(commit_result).status();
std::cout << "Delete was successful [spanner_delete_data]\n";
}

C#

Delete rows using the connection.CreateDeleteCommand() method, which creates a new SpannerCommand to delete rows. The SpannerCommand.ExecuteNonQueryAsync() method deletes the rows from the table.

This example deletes the rows in the Singers table individually. The rows in the Albums table are deleted because the Albums table is interleaved in the Singers table and is defined with ON DELETE CASCADE.


usingGoogle.Cloud.Spanner.Data ;
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Threading.Tasks;
publicclassDeleteDataAsyncSample
{
publicclassAlbum
{
publicintSingerId{get;set;}
publicintAlbumId{get;set;}
publicstringAlbumTitle{get;set;}
}
publicasyncTask<int>DeleteDataAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
varalbums=newList<Album>
{
newAlbum{SingerId=2,AlbumId=1,AlbumTitle="Green"},
newAlbum{SingerId=2,AlbumId=3,AlbumTitle="Terrified"},
};
introwCount=0;
using(varconnection=newSpannerConnection (connectionString))
{
awaitconnection.OpenAsync ();
// Delete individual rows from the Albums table.
awaitTask.WhenAll(albums.Select(asyncalbum=>
{
varcmd=connection.CreateDeleteCommand("Albums",newSpannerParameterCollection
{
{"SingerId",SpannerDbType.Int64,album.SingerId},
{"AlbumId",SpannerDbType.Int64,album.AlbumId}
});
rowCount+=awaitcmd.ExecuteNonQueryAsync();
}));
Console.WriteLine("Deleted individual rows in Albums.");
// Delete a range of rows from the Singers table where the column key is >=3 and <5.
varcmd=connection.CreateDmlCommand ("DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5");
rowCount+=awaitcmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
// Delete remaining Singers rows, which will also delete the remaining
// Albums rows since it was defined with ON DELETE CASCADE.
cmd=connection.CreateDmlCommand ("DELETE FROM Singers WHERE true");
rowCount+=awaitcmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
}
returnrowCount;
}
}

Go

Delete rows using a Mutation. Use the Mutation.Delete() method to construct a DELETE mutation, which deletes a row. The Client.Apply() method applies mutations atomically to the database.

This example deletes the rows in the Albums table individually, and then deletes all the rows in the Singers table using a KeyRange.


import(
"context"
"io"
"cloud.google.com/go/spanner"
)
funcdelete(wio.Writer,dbstring)error{
ctx:=context.Background()
client,err:=spanner.NewClient(ctx,db)
iferr!=nil{
returnerr
}
deferclient.Close()
m:=[]*spanner.Mutation{
// spanner.Key can be used to delete a specific set of rows.
// Delete the Albums with the key values (2,1) and (2,3).
spanner.Delete("Albums",spanner.Key {2,1}),
spanner.Delete("Albums",spanner.Key {2,3}),
// spanner.KeyRange can be used to delete rows with a key in a specific range.
// Delete a range of rows where the column key is >=3 and <5
spanner.Delete("Singers",spanner.KeyRange{Start:spanner.Key {3},End:spanner.Key {5},Kind:spanner.ClosedOpen }),
// spanner.AllKeys can be used to delete all the rows in a table.
// Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
// defined with ON DELETE CASCADE.
spanner.Delete("Singers",spanner.AllKeys ()),
}
_,err=client.Apply (ctx,m)
returnerr
}

Java

Delete rows using the Mutation.delete() method.

This examples uses the KeySet.all() method to delete all the rows in the Albums table. After deleting the rows in the Albums table, the example deletes the rows in the Singers table individually using keys created with the KeySet.singleKey() method.

staticvoiddeleteExampleData(DatabaseClientdbClient){
List<Mutation>mutations=newArrayList<>();
// KeySet.Builder can be used to delete a specific set of rows.
// Delete the Albums with the key values (2,1) and (2,3).
mutations.add(
Mutation.delete(
"Albums",KeySet.newBuilder().addKey(Key.of(2,1)).addKey(Key.of(2,3)).build()));
// KeyRange can be used to delete rows with a key in a specific range.
// Delete a range of rows where the column key is >=3 and <5
mutations.add(
Mutation.delete("Singers",KeySet.range(KeyRange.closedOpen(Key.of(3),Key.of(5)))));
// KeySet.all() can be used to delete all the rows in a table.
// Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
// defined with ON DELETE CASCADE.
mutations.add(Mutation.delete("Singers",KeySet.all()));
dbClient.write(mutations);
System.out.printf("Records deleted.\n");
}

Node.js

Delete rows using the table.deleteRows() method.

This example uses the table.deleteRows() method to delete all the rows from the Singers table. The rows in the Albums table are deleted because the Albums table is interleaved in Singers table and is defined with ON DELETE CASCADE.

// Imports the Google Cloud client library
const{Spanner}=require('@google-cloud/spanner');
/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
constspanner=newSpanner ({
projectId:projectId,
});
// Gets a reference to a Cloud Spanner instance and database
constinstance=spanner.instance(instanceId);
constdatabase=instance.database(databaseId);
// Instantiate Spanner table object
constalbumsTable=database.table('Albums');
// Deletes individual rows from the Albums table.
try{
constkeys=[
[2,1],
[2,3],
];
awaitalbumsTable.deleteRows(keys);
console.log('Deleted individual rows in Albums.');
}catch(err){
console.error('ERROR:',err);
}
// Delete a range of rows where the column key is >=3 and <5
database.runTransaction (async(err,transaction)=>{
if(err){
console.error(err);
return;
}
try{
const[rowCount]=awaittransaction.runUpdate ({
sql:'DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5',
});
console.log(`${rowCount} records deleted from Singers.`);
}catch(err){
console.error('ERROR:',err);
}
// Deletes remaining rows from the Singers table and the Albums table,
// because Albums table is defined with ON DELETE CASCADE.
try{
// The WHERE clause is required for DELETE statements to prevent
// accidentally deleting all rows in a table.
// https://cloud.google.com/spanner/docs/dml-syntax#where_clause
const[rowCount]=awaittransaction.runUpdate ({
sql:'DELETE FROM Singers WHERE true',
});
console.log(`${rowCount} records deleted from Singers.`);
awaittransaction.commit();
}catch(err){
console.error('ERROR:',err);
}finally{
// Close the database when finished.
awaitdatabase.close();
}
});

PHP

Delete rows using the Database::delete() method. The Database::delete() method page includes an example.

Python

Delete rows using the Batch.delete() method.

This example deletes all the rows in the Albums and Singers tables individually using a KeySet object.

defdelete_data(instance_id, database_id):
"""Deletes sample data from the given database.
 The database, table, and data must already exist and can be created using
 `create_database` and `insert_data`.
 """
 spanner_client = spanner.Client()
 instance = spanner_client.instance(instance_id)
 database = instance.database(database_id)
 # Delete individual rows
 albums_to_delete = spanner.KeySet(keys=[[2, 1], [2, 3]])
 # Delete a range of rows where the column key is >=3 and <5
 singers_range = spanner.KeyRange(start_closed=[3], end_open=[5])
 singers_to_delete = spanner.KeySet(ranges=[singers_range])
 # Delete remaining Singers rows, which will also delete the remaining
 # Albums rows because Albums was defined with ON DELETE CASCADE
 remaining_singers = spanner.KeySet(all_=True)
 with database.batch() as batch:
 batch.delete("Albums", albums_to_delete)
 batch.delete("Singers", singers_to_delete)
 batch.delete("Singers", remaining_singers)
 print("Deleted data.")

Ruby

Delete rows using the Client#delete method. The Client#delete page includes an example.

# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require"google/cloud/spanner"
spanner=Google::Cloud::Spanner.newproject:project_id
client=spanner.clientinstance_id,database_id
# Delete individual rows
client.delete"Albums",[[2,1],[2,3]]
# Delete a range of rows where the column key is >=3 and <5
key_range=client.range3,5,exclude_end:true
client.delete"Singers",key_range
# Delete remaining Singers rows, which will also delete the remaining
# Albums rows because Albums was defined with ON DELETE CASCADE
client.delete"Singers"

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年10月14日 UTC.