Transactions overview

Note: The examples in this document are intended for GoogleSQL-dialect databases. However,the concepts and semantics apply to both GoogleSQL-dialect andPostgreSQL-dialect databases.

This page describes transactions in Spanner and introducesSpanner's read-write, read-only, and partitioned DML transactioninterfaces.

A transaction in Spanner is a set of reads and writes. Alloperations in a transaction are atomic, meaning either they all succeed or theyall fail.

A session is used to perform transactions in a Spanner database.A session represents a logical communication channel with theSpanner database service. Sessions can execute a single ormultiple transactions at a time. For more information, seeSessions.

Transaction types

Spanner supports the following transaction types, each designedfor specific data interaction patterns:

  • Read-write: These transactions are used for read and write operations,followed by a commit. They might acquire locks. If they fail, they'llrequire retries. While they're confined to a single database, they canmodify data across multiple tables within that database.

  • Read-only: These transactions guarantee data consistency across multipleread operations, but don't permit data modifications. They execute at asystem-determined timestamp for consistency, or at a user-configured pasttimestamp. Unlike read-write transactions, they don't require a commitoperation or locks. However, they might pause to wait for ongoing writeoperations to conclude.

  • Partitioned DML: This transaction type executes DML statements aspartitioned DML operations. It's optimizedfor executing DML statements at scale but with restrictions to ensure thestatement is idempotent and partitionable in a way that lets it executeindependently of other partitions. For numerous writes that don't need anatomic transaction, consider using batch writes. For more information, seeModify data using batch writes.

Note: Because read-only transactions can execute on any replica, use them forread operations unless a read-write transaction is explicitly required. Only uselocking read-write transactions when required.

Read-write transactions

A read-write transaction consists of zero or more reads or query statementsfollowed by a commit request. At any time before the commit request, the clientcan send a rollback request to abort the transaction.

Serializable isolation

Using the default serializable isolation level, read-write transactionsatomically read, modify, and write data. This type of transaction isexternally consistent.

When you use read-write transactions, we recommend that you minimize the timethat a transaction is active. Shorter transaction durations result in locksbeing held for less time, which increases the probability of a successful commitand reduces contention. This is because long-held locks can lead to deadlocksand transaction aborts. Spanner attempts to keep read locksactive as long as the transaction continues to perform reads and the transactionhas not terminated through commit or roll back. If the client remains inactivefor long periods of time,Spanner might release the transaction's locks and abort thetransaction.

To perform a write operation that depends on one or more read operations, usea read-write transaction:

  • If you must commit one or more write operations atomically, perform thosewrites within the same read-write transaction. For example, if you transfer$200 from account A to account B, perform both write operations (decreasingaccount A by $200 and increasing account B by $200) and the reads of theinitial account balances within the same transaction.
  • If you want to double the balance of account A, perform the read and writeoperations within the same transaction. This ensures the system reads thebalance before doubling and updating it.
  • If write operations depend on read operations, perform both within the sameread-write transaction, even if the writes don't execute. For example, ifyou want to transfer $200 from account A to account B only if A's balance isgreater than $500, include the read of A's balance and the conditional writeoperations within the same transaction, even if the transfer doesn't occur.

To perform read operations, use a single read method or read-only transaction:

  • If you're only performing read operations, and you can express the readoperation using asingle read method,use the single read method or a read-only transaction. Unlike read-writetransactions, single reads don't acquire locks.

Repeatable read isolation

Preview — Repeatable read isolation

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

In Spanner,repeatable read isolationis implemented using a technique known as snapshot isolation. Repeatable readisolation ensures that all read operations within a transaction are consistentwith the database as it existed at the start of the transaction. It alsoguarantees that concurrent writes on the same data only succeed if there are noconflicts.

With its default optimistic locking, no locks are acquired until commit time ifdata needs to be written. If there is a conflict with the written data or due totransient events within Spanner like a server restart,Spanner might still abort transactions. Because reads inread-write transactions don't acquire locks in repeatable read isolation, thereis no difference between executing read-only operations within a read-onlytransaction or read-write transaction.

Consider using read-write transactions in repeatable read isolation in thefollowing scenarios:

  • The workload is read-heavy and has low write conflicts.
  • The application is experiencing performance bottlenecks due to delays fromlock-contention and transaction aborts caused by older, higher-prioritytransactions wounding newer, lower-priority transactions to preventpotential deadlocks (wound-wait).
  • The application doesn't require the stricter guarantees provided by theserializable isolation level.

When performing a write operation that depends on one or more read operations,write skew is possible under repeatable read isolation. Write skew arise from aparticular kind of concurrent update, where each update is independentlyaccepted, but their combined effect violates application data integrity.Therefore, make sure you perform reads that are part of a transaction's criticalsection with either aFOR UPDATE clause or alock_scanned_ranges=exclusivehint to avoid write skew. For more information, seeRead-write conflicts and correctness,and the example discussed inRead-write semantics.

Interface

TheSpanner client librariesprovide an interface for executing a body of work within a read-writetransaction, with retries for transaction aborts. A transaction might requiremultiple retries before it commits.

Several situations can cause transaction aborts. For example, if twotransactions attempt to modify data concurrently, a deadlock might occur. Insuch cases, Spanner aborts one transaction to let the otherproceed. Less frequently, transient events within Spanner canalso cause transaction aborts.

All read-write transactions provide the ACID properties of relational databases.Because transactions are atomic, an aborted transaction doesn't affect thedatabase. Spanner client libraries retry such transactionsautomatically, but if you don't use the client libraries, retry the transactionwithin the same session to improve success rates. Each retry that results in anABORTED error increases the transaction's lock priority. In addition,Spanner client drivers include an internal transaction retrylogic that masks transient errors by rerunning the transaction.

When using a transaction in a Spanner client library, you definethe transaction's body as a function object. This function encapsulates thereads and writes performed on one or more database tables. TheSpanner client library executes this function repeatedly untilthe transaction either commits successfully or encounters an error that can't beretried.

Example

Assume you have aMarketingBudget column in theAlbums table:

CREATE TABLE Albums (  SingerId        INT64 NOT NULL,  AlbumId         INT64 NOT NULL,  AlbumTitle      STRING(MAX),  MarketingBudget INT64) PRIMARY KEY (SingerId, AlbumId);

Your marketing department asks you to move $200,000 from the budget ofAlbums(2, 2) toAlbums (1, 1), but only if the money is available in that album'sbudget. You should use a locking read-write transaction for this operation,because the transaction might perform writes depending on the result of a read.

The following client library examples show how to execute a read-writetransaction using the default serializable isolation level:

C++

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#

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;}}

Go

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}varbudgetint64iferr:=row.Column(0,&budget);err!=nil{return0,err}returnbudget,nil}album2Budget,err:=getBudget(spanner.Key{2,2})iferr!=nil{returnerr}consttransferAmt=200000ifalbum2Budget>=transferAmt{album1Budget,err:=getBudget(spanner.Key{1,1})iferr!=nil{returnerr}album1Budget+=transferAmtalbum2Budget-=transferAmtcols:=[]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

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

// 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 libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=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 budgettransaction.read('Albums',queryOne).then(results=>{// Gets second album's budgetconstrows=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 enoughif(secondBudget <transferAmount){thrownewError(`The second album's budget (${secondBudget}) is less than the transfer amount (${transferAmount}).`,);}}),// Reads the first album's budgettransaction.read('Albums',queryTwo).then(results=>{// Gets first album's budgetconstrows=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 albumsfirstBudget+=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 databasereturntransaction.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 finishedreturndatabase.close();});});

PHP

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

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=200000ifsecond_album_budget <transfer_amount:# Raising an exception will automatically roll back the# transaction.raiseValueError("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_amountfirst_album_budget+=transfer_amountprint("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

# 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_idclient=spanner.clientinstance_id,database_idtransfer_amount=200_000client.transactiondo|transaction|first_album=transaction.read("Albums",[:MarketingBudget],keys:[[1,1]]).rows.firstsecond_album=transaction.read("Albums",[:MarketingBudget],keys:[[2,2]]).rows.firstraise"The second album does not have enough funds to transfer"ifsecond_album[:MarketingBudget] <transfer_amountnew_first_album_budget=first_album[:MarketingBudget]+transfer_amountnew_second_album_budget=second_album[:MarketingBudget]-transfer_amounttransaction.update"Albums",[{SingerId:1,AlbumId:1,MarketingBudget:new_first_album_budget},{SingerId:2,AlbumId:2,MarketingBudget:new_second_album_budget}]endputs"Transaction complete"

For examples on how to execute a read-write transaction using repeatable readisolation, seeUse repeatable read isolation level.

Semantics

This section describes the semantics for read-write transactions inSpanner.

Properties

Serializable isolation is the default isolation level in Spanner.Under serializable isolation, Spanner provides clients with thestrictest concurrency-control guarantees for transactions,external consistency. Aread-write transaction executes a set of reads and writes atomically. Writes canproceed without being blocked by read-only transactions. The timestamp at whichread-write transactions execute matches elapsed time. The serialization ordermatches this timestamp order.

Because of these properties, as an application developer, you can focus on thecorrectness of each transaction on its own, without worrying about how toprotect its execution from other transactions that might execute at the sametime.

You can also execute your read-write transactions using repeatable readisolation. Repeatable read isolation ensures that all read operations within atransaction see a consistent strong snapshot of the database as it existed atthe start of the transaction. For more information, seeRepeatable read isolation.

Read-write transactions with serializable isolation

After successfully committing a transaction that contains a series of reads andwrites in the default serializable isolation, the following applies:

  • The transaction returns values that reflect a consistent snapshot at thetransaction's commit timestamp.
  • Empty rows or ranges remain empty at commit time.
  • The transaction commits all writes at the transaction's commit timestamp.
  • No transaction can see the writes until after the transaction commits.

Spanner client drivers include transaction retry logic that maskstransient errors by rerunning the transaction and validating the data the clientobserves.

The effect is that all reads and writes appear to have occurred at a singlepoint in time, both from the perspective of the transaction itself and from theperspective of other readers and writers to the Spanner database.This means the reads and writes occur at the same timestamp. For an example, seeSerializability and external consistency.

Read-write transactions with repeatable read isolation

After successfully committing a transaction with repeatable read isolation, thefollowing applies:

  • The transaction returns values that reflect a consistent snapshot ofthe database. The snapshot is typically established during the firsttransaction operation, which might not be the same as the commit timestamp.
  • Since repeatable read is implemented using snapshot isolation, the transactioncommits all writes at the transaction's commit timestamp only if the write-sethasn't changed between the transaction snapshot timestamp and the committimestamp.
  • Other transactions don't see the writes until after the transaction commits.

Isolation for read-write transactions with read-only operations

When a read-write transaction performs only read operations, it provides similarconsistency guarantees as a read-only transaction. All reads within thetransaction return data from a consistent timestamp, including confirmationof non-existent rows.

One difference is when a read-write transaction commits without executing awrite operation. In this scenario, there's no guarantee that the data readwithin the transaction remained unchanged in the database between the readoperation and the transaction's commit.

To ensure data freshness and validate that data hasn't been modified since itslast retrieval, a subsequent read is required. This re-read can be performedeither within another read-write transaction or with a strong read.

For optimal efficiency, if a transaction is exclusively performing reads, use aread-only transaction instead of a read-writetransaction, especially when using serializable isolation.

How serializability and external consistency differs from repeatable read

By default, Spanner offers strong transactional guarantees,includingserializability andexternal consistency. These properties ensurethat data remains consistent and operations occur in a predictable order, evenin a distributed environment.

Serializability ensures that all transactions appear to execute one afteranother in a single, sequential order, even if they are processed concurrently.Spanner achieves this by assigning commit timestamps totransactions, reflecting the order in which they were committed.

Spanner provides an even stronger guarantee known asexternalconsistency. This means that notonly do transactions commit in an order reflected by their commit timestamps,but these timestamps also align with real-world time. This lets you comparecommit timestamps to real time, providing a consistent and globally ordered viewof your data.

In essence, if a transactionTxn1 commits before another transactionTxn2 inreal time, thenTxn1's commit timestamp is earlier thanTxn2's committimestamp.

Consider the following example:

Timeline that shows the execution of two transactions that read the same data

In this scenario, during the timelinet:

  • TransactionTxn1 reads dataA, stages a write toA, and thensuccessfully commits.
  • TransactionTxn2 begins afterTxn1 starts. It reads dataB andthen reads dataA.

Even thoughTxn2 started before Txn1 completed,Txn2 observes the changesmade byTxn1 toA. This is becauseTxn2 readsA afterTxn1 commitsits write toA.

WhileTxn1 andTxn2 might overlap in their execution time, their committimestamps,c1 andc2 respectively, enforce a linear transaction order. Thismeans:

  • All reads and writes withinTxn1 appear to have occurred at a single pointin time,c1.
  • All reads and writes withinTxn2 appear to have occurred at a single pointin time,c2.
  • Crucially,c1 is earlier thanc2 for committed writes, even if thewrites occurred on different machines. IfTxn2 performs only reads,c1 is earlier or at the same time asc2.

This strong ordering means that if a subsequent read operation observes theeffects ofTxn2, it also observes the effects ofTxn1. This property holdstrue for all successfully committed transactions.

On the other hand, if you use repeatable read isolation, the following scenariooccurs for the same transactions:

  • Txn1 starts by reading dataA, creating its own snapshot of thedatabase at that moment.
  • Txn2 then begins, reading dataB, and establishes its own snapshot.
  • Next,Txn1 modifies dataA, and successfully commits its changes.
  • Txn2 attempts to read dataA. Crucially, because it's operating at anearlier snapshot,Txn2 doesn't see the updateTxn1 just made toA.Txn2 reads the older value.
  • Txn2 modifies dataB and commits.

In this scenario, each transaction operates on its own consistent snapshot ofthe database, taken from the moment the transaction starts. This sequence canlead to a write skew anomaly if the write toB byTxn2 was logicallydependent on the value it read fromA. In essence,Txn2 made its updatesbased on outdated information, and its subsequent write might violate anapplication-level invariant. To prevent this scenario from arising, considereitherusingSELECT...FOR UPDATE for repeatable readisolation, orcreatingcheck constraints in your schema.

Note: Regardless of isolation level, when usingDML statements,changes are immediately visible to subsequent read statements within the sametransaction. However, if you usemutations,changes are buffered locally and are only visible after the transaction commits.

Read and write guarantees on transaction failure

If a call to execute a transaction fails, the read and write guarantees you havedepend on what error the underlying commit call failed with.

Spanner might execute a transaction's operations multiple timesinternally. If an execution attempt fails, the returned error specifies theconditions that occurred and indicates the guarantees you receive. However, ifSpanner retries your transaction, any side effects from itsoperations (for example, changes to external systems or a system state outside aSpanner database) might occur multiple times.

When a Spanner transaction fails, the guarantees you receive forreads and writes depend on the specific error encountered during the commitoperation.

For example, an error message such as "Row Not Found" or "Row Already Exists"indicates an issue during the writing of buffered mutations. This can occur if,for example, a row the client is attempting to update doesn't exist. In thesescenarios:

  • Reads are consistent: Any data read during the transaction is guaranteedto be consistent up to the point of the error.
  • Writes are not applied: The mutations the transaction attempted aren'tcommitted to the database.
  • Row consistency: The non-existence (or existing state) of the row thattriggered the error is consistent with the reads performed within thetransaction.

You can cancel asynchronous read operations in Spanner at anytime without affecting other ongoing operations within the same transaction.This flexibility is useful if a higher-level operation is cancelled, or if youdecide to abort a read based on initial results.

However, it's important to understand that requesting the cancellation of a readdoesn't guarantee its immediate termination. After a cancellation request, theread operation might still:

  • Successfully complete: the read might finish processing and returnresults before the cancellation takes effect.
  • Fail for another reason: the read could terminate due to a differenterror, such as an abort.
  • Return incomplete results: the read might return partial results, whichare then validated as part of the transaction commit process.

Cancelling a commit operation aborts the entire transaction, unless thetransaction has already committed or failed due to another reason.

Atomicity, consistency, durability

In addition to isolation, Spanner provides the other ACIDproperty guarantees:

  • Atomicity: A transaction is considered atomic if all its operations arecompleted successfully, or none at all. If any operation within a transactionfails, the entire transaction is rolled back to its original state, ensuringdata integrity.
  • Consistency: A transaction must maintain the integrity of the database'srules and constraints. After a transaction completes, the database should bein a valid state, adhering to predefined rules.
  • Durability: After a transaction is committed, its changes are permanentlystored in the database and persist in the event of system failures,power outages, or other disruptions.

Performance

This section describes issues that affect read-write transaction performance.

Locking concurrency control

By default, Spanner permits multiple clients to interact with thesame database concurrently in its default serializable isolation level. Tomaintain data consistency across these concurrent transactions,Spanner has a locking mechanism that uses both shared andexclusive locks. These read locks are only acquired for serializabletransactions, but not for transactions that use repeatable read isolation.

When a serializable transaction performs a read operation,Spanner acquires shared read locks on the relevant data. Theseshared locks let other concurrent read operations access the same data. Thisconcurrency is maintained until your transaction prepares to commit its changes.

In serializable isolation, during the commit phase, as writes are applied, thetransaction attempts to upgrade its locks to exclusive locks. To achieve this,Spanner does the following:

  • Blocks any new shared read lock requests on the affected data.
  • Waits for all existing shared read locks on that data to be released.
  • After all shared read locks are cleared, it places an exclusive lock, grantingit sole access to the data for the duration of the write.

When committing a transaction in repeatable read isolation, the transactionacquires exclusive locks for the written data. The transaction might have towait for locks if a concurrent transaction is also committing writes to the samedata.

Notes about locks:

  • Granularity: Spanner applies locks at the row-and-columngranularity. This means that if transactionT1 holds a lock on columnAof rowalbumid, transactionT2 can still concurrently write to columnB of the same rowalbumid without conflict.

  • Writes without reads:

    • When there are no reads in the transaction, Spanner mightnot require an exclusive lock for writes without reads. Instead, it mightuse a writer shared lock. This is because the order of application forwrites without reads is determined by their commit timestamps, lettingmultiple writers operate on the same item concurrently without conflict.An exclusive lock is only necessary if your transaction first reads thedata it intends to write.
    • In repeatable read isolation, transactions commonly acquire exclusivelocks for written cells at commit time.

  • Secondary indexes for row lookups: in serializable isolation, whenperforming reads within a read-write transaction, using secondary indexescan significantly improve performance. By using secondary indexes to limitthe scanned rows to a smaller range, Spanner locks fewer rowsin the table, thereby enabling greater concurrent modification of rowsoutside of that specific range.

  • External resource exclusive access: Spanner's internallocks are designed for data consistency within the Spannerdatabase itself. Don't use them to guarantee exclusive access to resourcesoutside of Spanner. Spanner can aborttransactions for various reasons, including internal system optimizationslike data movement across compute resources. If a transaction is retried(either explicitly by your application code or implicitly by clientlibraries like the Spanner JDBC driver), locks are only guaranteedto have been held during the successful commit attempt.

  • Lock statistics: to diagnose and investigate lock conflicts within yourdatabase, use theLock statisticsintrospection tool.

Deadlock detection

Spanner detects when multiple transactions might be deadlockedand forces all but one of the transactions to abort. Consider this scenario:Txn1 holds a lock on recordA and is waiting for a lock on recordB, whileTxn2 holds a lock on recordB and is waiting for a lock on recordA. Toresolve this, one of the transactions must abort, releasing its lock andallowing the other to proceed.

Spanner uses the standard wound-wait algorithm for deadlockdetection. Under the hood, Spanner tracks the age of eachtransaction requesting conflicting locks. It lets older transactions abortyounger ones. An older transaction is one whose earliest read, query, or commitoccurred sooner.

By prioritizing older transactions, Spanner ensures that everytransaction eventually acquires locks after it becomes old enough to have higherpriority. For example, an older transaction needing a writer-shared lock canabort a younger transaction holding a reader-shared lock.

Distributed execution

Spanner can execute transactions on data that spans multipleservers, though this capability comes with a performance cost compared tosingle-server transactions.

What types of transactions might be distributed? Spanner candistribute responsibility for database rows across many servers. Typically, arow and its corresponding interleaved table rows are served by the same server,as are two rows in the same table with nearby keys. Spanner canperform transactions across rows on different servers. However, as a generalrule, transactions affecting many co-located rows are faster and cheaper thanthose affecting many rows scattered throughout the database or a large table.

The most efficient transactions in Spanner include only the readsand writes that should be applied atomically. Transactions are fastest when allreads and writes access data in the same part of the key space.

Read-only transactions

In addition to locking read-write transactions, Spanner offersread-only transactions.

Use a read-only transaction when you need to execute more than one read at thesame timestamp. If you can express your read using one of Spanner'ssingle read methods, you should use thatsingle read method instead. The performance of using such a single read callshould be comparable to the performance of a single read done in a read-onlytransaction.

If you are reading a large amount of data, consider using partitions toread the data in parallel.

Because read-only transactions don't write, they don'thold locks and they don't block other transactions. Read-only transactionsobserve a consistent prefix of the transaction commit history, so yourapplication always gets consistent data.

Interface

Spanner provides an interface for executing a body of work in thecontext of a read-only transaction, with retries for transaction aborts.

Example

The following example shows how to use a read-only transaction to get consistentdata for two reads at the same timestamp:

C++

voidReadOnlyTransaction(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoread_only=spanner::MakeReadOnlyTransaction();spanner::SqlStatementselect("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");usingRowType=std::tuple<std::int64_t,std::int64_t,std::string>;// Read#1.autorows1=client.ExecuteQuery(read_only,select);std::cout <<"Read 1 results\n";for(auto&row:spanner::StreamOf<RowType>(rows1)){if(!row)throwstd::move(row).status();std::cout <<"SingerId: " <<std::get<0>(*row)              <<" AlbumId: " <<std::get<1>(*row)              <<" AlbumTitle: " <<std::get<2>(*row) <<"\n";}// Read#2. Even if changes occur in-between the reads the transaction ensures// that Read #1 and Read #2 return the same data.autorows2=client.ExecuteQuery(read_only,select);std::cout <<"Read 2 results\n";for(auto&row:spanner::StreamOf<RowType>(rows2)){if(!row)throwstd::move(row).status();std::cout <<"SingerId: " <<std::get<0>(*row)              <<" AlbumId: " <<std::get<1>(*row)              <<" AlbumTitle: " <<std::get<2>(*row) <<"\n";}}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;usingSystem.Transactions;publicclassQueryDataWithTransactionAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}}publicasyncTask<List<Album>>QueryDataWithTransactionAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";varalbums=newList<Album>();usingTransactionScopescope=newTransactionScope(TransactionScopeAsyncFlowOption.Enabled);usingvarconnection=newSpannerConnection(connectionString);// Opens the connection so that the Spanner transaction included in the TransactionScope// is read-only TimestampBound.Strong.awaitconnection.OpenAsync(SpannerTransactionCreationOptions.ReadOnly,options:null,cancellationToken:default);usingvarcmd=connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");// Read #1.using(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.ReadAsync()){Console.WriteLine("SingerId : "+reader.GetFieldValue<string>("SingerId")+" AlbumId : "+reader.GetFieldValue<string>("AlbumId")+" AlbumTitle : "+reader.GetFieldValue<string>("AlbumTitle"));}}// Read #2. Even if changes occur in-between the reads,// the transaction ensures that Read #1 and Read #2// return the same data.using(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.ReadAsync()){albums.Add(newAlbum{AlbumId=reader.GetFieldValue<int>("AlbumId"),SingerId=reader.GetFieldValue<int>("SingerId"),AlbumTitle=reader.GetFieldValue<string>("AlbumTitle")});}}scope.Complete();Console.WriteLine("Transaction complete.");returnalbums;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcreadOnlyTransaction(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()ro:=client.ReadOnlyTransaction()deferro.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, AlbumTitle FROM Albums`}iter:=ro.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varsingerIDint64varalbumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}iter=ro.Read(ctx,"Albums",spanner.AllKeys(),[]string{"SingerId","AlbumId","AlbumTitle"})deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerIDint64varalbumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}}

Java

staticvoidreadOnlyTransaction(DatabaseClientdbClient){// ReadOnlyTransaction must be closed by calling close() on it to release resources held by it.// We use a try-with-resource block to automatically do so.try(ReadOnlyTransactiontransaction=dbClient.readOnlyTransaction()){try(ResultSetqueryResultSet=transaction.executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))){while(queryResultSet.next()){System.out.printf("%d %d %s\n",queryResultSet.getLong(0),queryResultSet.getLong(1),queryResultSet.getString(2));}}// queryResultSet.close() is automatically called heretry(ResultSetreadResultSet=transaction.read("Albums",KeySet.all(),Arrays.asList("SingerId","AlbumId","AlbumTitle"))){while(readResultSet.next()){System.out.printf("%d %d %s\n",readResultSet.getLong(0),readResultSet.getLong(1),readResultSet.getString(2));}}// readResultSet.close() is automatically called here}// transaction.close() is automatically called here}

Node.js

// Imports the Google Cloud client libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);// Gets a transaction object that captures the database state// at a specific point in timedatabase.getSnapshot(async(err,transaction)=>{if(err){console.error(err);return;}constqueryOne='SELECT SingerId, AlbumId, AlbumTitle FROM Albums';try{// Read #1, using SQLconst[qOneRows]=awaittransaction.run(queryOne);qOneRows.forEach(row=>{constjson=row.toJSON();console.log(`SingerId:${json.SingerId}, AlbumId:${json.AlbumId}, AlbumTitle:${json.AlbumTitle}`,);});constqueryTwo={columns:['SingerId','AlbumId','AlbumTitle'],};// Read #2, using the `read` method. Even if changes occur// in-between the reads, the transaction ensures that both// return the same data.const[qTwoRows]=awaittransaction.read('Albums',queryTwo);qTwoRows.forEach(row=>{constjson=row.toJSON();console.log(`SingerId:${json.SingerId}, AlbumId:${json.AlbumId}, AlbumTitle:${json.AlbumTitle}`,);});console.log('Successfully executed read-only transaction.');}catch(err){console.error('ERROR:',err);}finally{transaction.end();// Close the database when finished.awaitdatabase.close();}});

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Reads data inside of a read-only transaction. * * Within the read-only transaction, or "snapshot", the application sees * consistent view of the database at a particular timestamp. * Example: * ``` * read_only_transaction($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function read_only_transaction(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $snapshot = $database->snapshot();    $results = $snapshot->execute(        'SELECT SingerId, AlbumId, AlbumTitle FROM Albums'    );    print('Results from the first read:' . PHP_EOL);    foreach ($results as $row) {        printf('SingerId: %s, AlbumId: %s, AlbumTitle: %s' . PHP_EOL,            $row['SingerId'], $row['AlbumId'], $row['AlbumTitle']);    }    // Perform another read using the `read` method. Even if the data    // is updated in-between the reads, the snapshot ensures that both    // return the same data.    $keySet = $spanner->keySet(['all' => true]);    $results = $database->read(        'Albums',        $keySet,        ['SingerId', 'AlbumId', 'AlbumTitle']    );    print('Results from the second read:' . PHP_EOL);    foreach ($results->rows() as $row) {        printf('SingerId: %s, AlbumId: %s, AlbumTitle: %s' . PHP_EOL,            $row['SingerId'], $row['AlbumId'], $row['AlbumTitle']);    }}

Python

defread_only_transaction(instance_id,database_id):"""Reads data inside of a read-only transaction.    Within the read-only transaction, or "snapshot", the application sees    consistent view of the database at a particular timestamp.    """spanner_client=spanner.Client()instance=spanner_client.instance(instance_id)database=instance.database(database_id)withdatabase.snapshot(multi_use=True)assnapshot:# Read using SQL.results=snapshot.execute_sql("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")print("Results from first read:")forrowinresults:print("SingerId:{}, AlbumId:{}, AlbumTitle:{}".format(*row))# Perform another read using the `read` method. Even if the data# is updated in-between the reads, the snapshot ensures that both# return the same data.keyset=spanner.KeySet(all_=True)results=snapshot.read(table="Albums",columns=("SingerId","AlbumId","AlbumTitle"),keyset=keyset)print("Results from second read:")forrowinresults:print("SingerId:{}, AlbumId:{}, AlbumTitle:{}".format(*row))

Ruby

# 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_idclient=spanner.clientinstance_id,database_idclient.snapshotdo|snapshot|snapshot.execute("SELECT SingerId, AlbumId, AlbumTitle FROM Albums").rows.eachdo|row|puts"#{row[:AlbumId]}#{row[:AlbumTitle]}#{row[:SingerId]}"end# Even if changes occur in-between the reads, the transaction ensures that# both return the same data.snapshot.read("Albums",[:AlbumId,:AlbumTitle,:SingerId]).rows.eachdo|row|puts"#{row[:AlbumId]}#{row[:AlbumTitle]}#{row[:SingerId]}"endend

Semantics

This section describes semantics for read-only transactions.

Snapshot read-only transactions

When a read-only transaction executes in Spanner, it performs allits reads at a single logical point in time. This means that both the read-onlytransaction and any other concurrent readers and writers see a consistentsnapshot of the database at that specific moment.

These snapshot read-only transactions offer a simpler approach for consistentreads compared to locking read-write transactions. Here's why:

  • No locks: read-only transactions don't acquire locks. Instead, theyoperate by selecting a Spanner timestamp and executing allreads against that historical version of the data. Because they don't uselocks, they won't block concurrent read-write transactions.
  • No aborts: these transactions never abort. While they might fail iftheir chosen read timestamp is garbage collected, Spanner'sdefault garbage collection policy is typically generous enough that mostapplications won't encounter this issue.
  • No commits or rollbacks: read-only transactions don't require calls tosessions.commit orsessions.rollback and are actually prevented from doingso.

To execute a snapshot transaction, the client defines a timestamp bound, whichinstructs Spanner how to select a read timestamp. The types oftimestamp bounds include the following:

  • Strong reads: these reads guarantee that you'll see the effects of alltransactions committed before the read began. All rows within a single readare consistent. However, strong reads aren't repeatable, although strongreads do return a timestamp, and reading again at that same timestamp isrepeatable. Two consecutive strong read-only transactions might producedifferent results due to concurrent writes. Queries on change streams mustuse this bound. For more details, seeTransactionOptions.ReadOnly.strong.
  • Exact staleness: this option executes reads at a timestamp youspecify, either as an absolute timestamp or as a staleness durationrelative to the current time. It ensures you observe a consistent prefixof the global transaction history up to that timestamp and blocksconflicting transactions that might commit with a timestamp less than orequal to the read timestamp. While slightly faster than boundedstaleness modes, it might return older data. For more details, seeTransactionOptions.ReadOnly.read_timestampandTransactionOptions.ReadOnly.exact_staleness.
  • Bounded staleness: Spanner selects the newest timestampwithin a user-defined staleness limit, allowing execution at the nearestavailable replica without blocking. All rows returned are consistent. Likestrong reads, bounded staleness isn't repeatable, as different reads mightexecute at different timestamps even with the same bound. These readsoperate in two phases (timestamp negotiation, then read) and are usuallyslightly slower than exact staleness, but they often return fresher resultsand are more likely to execute at a local replica. This mode is onlyavailable for single-use read-only transactions because timestampnegotiation requires knowing which rows will be read beforehand. For moredetails,seeTransactionOptions.ReadOnly.max_stalenessandTransactionOptions.ReadOnly.min_read_timestamp.

Partitioned DML transactions

You can usepartitioned DML to executelarge-scaleUPDATE andDELETE statements without encountering transactionlimits or locking an entire table. Spanner achieves this bypartitioning the key space and executing the DML statements on each partitionwithin a separate read-write transaction.

To use non-partitioned DML, you execute statements within read-writetransactions that you explicitly create in your code. For more details, seeUsing DML.

Interface

Spanner provides theTransactionOptions.partitionedDmlinterface for executing a single partitioned DML statement.

Examples

The following code example updates theMarketingBudget column of theAlbumstable.

C++

You use theExecutePartitionedDml() function to execute a partitioned DML statement.

voidDmlPartitionedUpdate(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoresult=client.ExecutePartitionedDml(spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000""  WHERE SingerId > 1"));if(!result)throwstd::move(result).status();std::cout <<"Updated at least " <<result->row_count_lower_bound            <<" row(s) [spanner_dml_partitioned_update]\n";}

C#

You use theExecutePartitionedUpdateAsync() method to execute a partitioned DML statement.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateUsingPartitionedDmlCoreAsyncSample{publicasyncTask<long>UpdateUsingPartitionedDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");longrowCount=awaitcmd.ExecutePartitionedUpdateAsync();Console.WriteLine($"{rowCount} row(s) updated...");returnrowCount;}}

Go

You use thePartitionedUpdate() method to execute a partitioned DML statement.

import("context""fmt""io""cloud.google.com/go/spanner")funcupdateUsingPartitionedDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}rowCount,err:=client.PartitionedUpdate(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) updated.\n",rowCount)returnnil}

Java

You use theexecutePartitionedUpdate() method to execute a partitioned DML statement.

staticvoidupdateUsingPartitionedDml(DatabaseClientdbClient){Stringsql="UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";longrowCount=dbClient.executePartitionedUpdate(Statement.of(sql));System.out.printf("%d records updated.\n",rowCount);}

Node.js

You use therunPartitionedUpdate() method to execute a partitioned DML statement.

// Imports the Google Cloud client libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);try{const[rowCount]=awaitdatabase.runPartitionedUpdate({sql:'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',});console.log(`Successfully updated${rowCount} records.`);}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}

PHP

You use theexecutePartitionedUpdate() method to execute a partitioned DML statement.

use Google\Cloud\Spanner\SpannerClient;/** * Updates sample data in the database by partition with a DML statement. * * This updates the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $rowCount = $database->executePartitionedUpdate(        'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'    );    printf('Updated %d row(s).' . PHP_EOL, $rowCount);}

Python

You use theexecute_partitioned_dml() method to execute a partitioned DML statement.

# instance_id = "your-spanner-instance"# database_id = "your-spanner-db-id"spanner_client=spanner.Client()instance=spanner_client.instance(instance_id)database=instance.database(database_id)row_ct=database.execute_partitioned_dml("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1")print("{} records updated.".format(row_ct))

Ruby

You use theexecute_partitioned_update() method to execute a partitioned DML statement.

# 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_idclient=spanner.clientinstance_id,database_idrow_count=client.execute_partition_update("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1")puts"#{row_count} records updated."

The following code example deletes rows from theSingers table, based on theSingerId column.

C++

voidDmlPartitionedDelete(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoresult=client.ExecutePartitionedDml(spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));if(!result)throwstd::move(result).status();std::cout <<"Deleted at least " <<result->row_count_lower_bound            <<" row(s) [spanner_dml_partitioned_delete]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassDeleteUsingPartitionedDmlCoreAsyncSample{publicasyncTask<long>DeleteUsingPartitionedDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");longrowCount=awaitcmd.ExecutePartitionedUpdateAsync();Console.WriteLine($"{rowCount} row(s) deleted...");returnrowCount;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner")funcdeleteUsingPartitionedDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:"DELETE FROM Singers WHERE SingerId > 10"}rowCount,err:=client.PartitionedUpdate(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) deleted.",rowCount)returnnil}

Java

staticvoiddeleteUsingPartitionedDml(DatabaseClientdbClient){Stringsql="DELETE FROM Singers WHERE SingerId > 10";longrowCount=dbClient.executePartitionedUpdate(Statement.of(sql));System.out.printf("%d records deleted.\n",rowCount);}

Node.js

// Imports the Google Cloud client libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);try{const[rowCount]=awaitdatabase.runPartitionedUpdate({sql:'DELETE FROM Singers WHERE SingerId > 10',});console.log(`Successfully deleted${rowCount} records.`);}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Delete sample data in the database by partition with a DML statement. * * This updates the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $rowCount = $database->executePartitionedUpdate(        'DELETE FROM Singers WHERE SingerId > 10'    );    printf('Deleted %d row(s).' . PHP_EOL, $rowCount);}

Python

# instance_id = "your-spanner-instance"# database_id = "your-spanner-db-id"spanner_client=spanner.Client()instance=spanner_client.instance(instance_id)database=instance.database(database_id)row_ct=database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")print("{} record(s) deleted.".format(row_ct))

Ruby

# 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_idclient=spanner.clientinstance_id,database_idrow_count=client.execute_partition_update("DELETE FROM Singers WHERE SingerId > 10")puts"#{row_count} records deleted."

Semantics

This section describes the semantics for partitioned DML.

Understanding partitioned DML execution

You can execute only one partitioned DML statement at a time, whether you areusing a client library method or the Google Cloud CLI.

Partitioned transactions don't support commits or rollbacks.Spanner executes and applies the DML statement immediately. Ifyou cancel the operation, or the operation fails, Spanner cancelsall the executing partitions and doesn't start any remaining ones. However,Spanner doesn't roll back any partitions that have alreadyexecuted.

Partitioned DML lock acquisition strategy

To reduce lock contention, partitioned DML acquires read locks only on rows thatmatch theWHERE clause. Smaller, independent transactions used for eachpartition also hold locks for less time.

Old read timestamps and version garbage collection

Spanner performs version garbage collection to collect deleted oroverwritten data and reclaim storage. By default, data older than one hour isreclaimed. Spanner can't perform reads at timestamps older thanthe configuredVERSION_RETENTION_PERIOD, which defaults to one hour but can beconfigured to up to one week. When reads become too old during execution, theyfail and return theFAILED_PRECONDITION error.

Queries on change streams

Achange stream is a schema object you can configure to monitor datamodifications across an entire database, specific tables, or a defined set ofcolumns within a database.

When you create a change stream, Spanner defines acorresponding SQL table-valued function (TVF). You can use this TVF to query thechange records in the associated change stream with thesessions.executeStreamingSqlmethod. The TVF's name is generated from the change stream's name and alwaysstarts withREAD_.

All queries on change stream TVFs must be executed using thesessions.executeStreamingSql API within a single-use read-only transactionwith a strong read-onlytimestamp_bound. The change stream TVF lets youspecifystart_timestamp andend_timestamp for the time range. All changerecords within the retention period are accessible using this strong read-onlytimestamp_bound. All otherTransactionOptions areinvalid for change stream queries.

Additionally, ifTransactionOptions.read_only.return_read_timestampis set totrue, theTransaction message describingthe transaction returns a special value of2^63 - 2 instead of a valid readtimestamp. You should discard this special value and not use it for anysubsequent queries.

For more information, seeChange streams query workflow.

Idle Transactions

A transaction is considered idle if it has no outstanding reads or SQL queriesand hasn't started one in the last 10 seconds. Spanner can abortidle transactions to prevent them from holding locks indefinitely. If an idletransaction is aborted, the commit fails and returns anABORTED error.Periodically executing a small query, such asSELECT 1, within the transactioncan prevent it from becoming idle.

What's next

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

Last updated 2025-12-15 UTC.