Retrieve commit statistics for a transaction

To help you better understand, optimize, and diagnose transaction issues, Spanner gives you access to transaction commit statistics. Currently, you can retrieve the total number of mutations for a transaction.

When to use commit statistics

Knowing the mutation count for a transaction can be useful in the following scenarios.

Optimize for round trips

To help improve the performance of your application you can reduce the number of round trips to the database by doing as much work as possible in each transaction. In this scenario you want to maximize the number of mutations per transaction, while at the same time staying within system limits.

To determine how many rows you can commit per transaction while staying under the limit, first commit one row in a transaction. This gives you a baseline of the mutation count per row. Then divide the system limit by your baseline to get a rows-per-transaction number. For more information on how mutations are counted, refer to this note.

Note that optimizing for round trips is not always beneficial, particularly if it results in more lock contentions. You can troubleshoot lock conflicts in your database using lock statistics.

Monitor your transactions to avoid hitting system limits

As application usage increases, it's possible that the number of mutations in your transaction also grows. To avoid hitting the system limit and having your transaction eventually fail, you can proactively monitor the mutation count commit statistic over time. If you observe this value increasing for the same transaction, it might be time to re-optimize your transaction as described in the preceding section.

How to access commit statistics

Commit statistics are not returned by default. Instead, you need to set the return_commit_stats flag to true on each CommitRequest. If your commit attempt exceeds the maximum allowable number of mutations for a transaction, the commit fails and an INVALID_ARGUMENT error is returned.

Here's an example of how to return commit statistics using the Spanner client libraries.

Retrieve commit statistics

The following sample shows how to get commit statistics using the Spanner client libraries.

C++

The following code calls set_return_stats() on CommitOptions and returns a mutation count of 6, because we are inserting or updating 2 rows and 3 columns in each row.

voidGetCommitStatistics(google::cloud::spanner::Clientclient){
namespacespanner=::google::cloud::spanner;
autocommit=client.Commit(
spanner::Mutations{
spanner::UpdateMutationBuilder(
"Albums",{"SingerId","AlbumId","MarketingBudget"})
.EmplaceRow(1,1,200000)
.EmplaceRow(2,2,400000)
.Build()},
google::cloud::Options{}.set<spanner::CommitReturnStatsOption>(true));
if(!commit)throwstd::move(commit).status();
if(commit->commit_stats){
std::cout << "Updated data with " << commit->commit_stats->mutation_count
 << " mutations.\n";
}
std::cout << "Update was successful [spanner_get_commit_stats]\n";
}

C#

In C#, commit statistics are not returned directly through the API. Instead, they are logged at the Information log level by the default logger.

The following code enables commit statistics logging for all transactions by setting the LogCommitStats property on SpannerConnectionStringBuilder to true. The code also implements a sample logger that keeps a reference to the last seen commit response. The MutationCount is then retrieved from this response and displayed.


usingGoogle.Cloud.Spanner.Data ;
usingGoogle.Cloud.Spanner.V1 ;
usingGoogle.Cloud.Spanner.V1.Internal.Logging ;
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Diagnostics;
usingSystem.Threading.Tasks;
publicclassLogCommitStatsAsyncSample
{
publicasyncTask<long>LogCommitStatsAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
// Commit statistics are logged at level Info by the default logger.
// This sample uses a custom logger to access the commit statistics.
// See https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.Spanner.Data/logging.html
// for more information on how to use loggers.
varlogger=newCommitStatsSampleLogger();
varoptions=newSessionPoolOptions ();
varpoolManager=SessionPoolManager .Create (options,logger);
varconnectionStringBuilder=newSpannerConnectionStringBuilder
{
ConnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}",
// Set LogCommitStats to true to enable logging commit statistics for all transactions on the connection.
// LogCommitStats can also be enabled/disabled for individual Spanner transactions.
LogCommitStats=true,
SessionPoolManager=poolManager,
};
usingvarconnection=newSpannerConnection (connectionStringBuilder);
awaitconnection.OpenAsync ();
usingvarcmd=connection.CreateDmlCommand ("INSERT Singers (SingerId, FirstName, LastName) VALUES (110, 'Virginia', 'Watson')");
varrowCount=awaitcmd.ExecuteNonQueryAsync();
varmutationCount=logger._lastCommitResponse.CommitStats.MutationCount ;
Console.WriteLine($"{rowCount} row(s) inserted...");
Console.WriteLine($"{mutationCount} mutation(s) in transaction...");
returnmutationCount;
}
/// <summary>
/// Sample logger that keeps a reference to the last seen commit response.
/// Use the default logger if you only want to log the commit stats.
/// </summary>
publicclassCommitStatsSampleLogger:Logger
{
internalCommitResponse _lastCommitResponse;
/// <summary>
/// This method is called when a transaction that requested commit stats is committed.
/// </summary>
publicoverridevoidLogCommitStats(CommitRequest request,CommitResponse response)
{
_lastCommitResponse=response;
base.LogCommitStats(request,response);
}
protectedoverridevoidLogImpl(LogLevellevel,stringmessage,Exceptionexception)=>
WriteLine(exception==null?$"{level}: {message}":$"{level}: {message}, Exception: {exception}");
protectedoverridevoidLogPerformanceEntries(IEnumerable<string>entries)
{
stringseparator=Environment.NewLine+" ";
WriteLine($"Performance:{separator}{string.Join(separator, entries)}");
}
privatevoidWriteLine(stringline)=>Trace.TraceInformation(line);
}
}

Go

The following code sets the ReturnCommitStats flag and prints out the mutation count when the transaction is successfully committed.


import(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
funccommitStats(wio.Writer,dbstring)error{
ctx:=context.Background()
client,err:=spanner.NewClient(ctx,db)
iferr!=nil{
returnfmt.Errorf("commitStats.NewClient: %w",err)
}
deferclient.Close()
resp,err:=client.ReadWriteTransactionWithOptions (ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{
stmt:=spanner.Statement {
SQL:`INSERT Singers (SingerId, FirstName, LastName)
					VALUES (110, 'Virginia', 'Watson')`,
}
rowCount,err:=txn.Update(ctx,stmt)
iferr!=nil{
returnerr
}
fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)
returnnil
},spanner.TransactionOptions{CommitOptions:spanner.CommitOptions {ReturnCommitStats:true}})
iferr!=nil{
returnfmt.Errorf("commitStats.ReadWriteTransactionWithOptions: %w",err)
}
fmt.Fprintf(w,"%d mutations in transaction\n",resp.CommitStats.MutationCount)
returnnil
}

Java


importcom.google.cloud.spanner.CommitResponse ;
importcom.google.cloud.spanner.DatabaseClient ;
importcom.google.cloud.spanner.DatabaseId ;
importcom.google.cloud.spanner.Mutation ;
importcom.google.cloud.spanner.Options ;
importcom.google.cloud.spanner.Spanner ;
importcom.google.cloud.spanner.SpannerOptions ;
importjava.util.Arrays;
publicclass GetCommitStatsSample{
staticvoidgetCommitStats(){
// TODO(developer): Replace these variables before running the sample.
finalStringprojectId="my-project";
finalStringinstanceId="my-instance";
finalStringdatabaseId="my-database";
try(Spanner spanner=
SpannerOptions .newBuilder().setProjectId(projectId).build().getService()){
finalDatabaseClient databaseClient=spanner
.getDatabaseClient(DatabaseId .of(projectId,instanceId,databaseId));
getCommitStats(databaseClient);
}
}
staticvoidgetCommitStats(DatabaseClient databaseClient){
finalCommitResponse commitResponse=databaseClient.writeWithOptions (Arrays.asList(
Mutation .newInsertOrUpdateBuilder ("Albums")
.set("SingerId")
.to ("1")
.set("AlbumId")
.to ("1")
.set("MarketingBudget")
.to ("200000")
.build(),
Mutation .newInsertOrUpdateBuilder ("Albums")
.set("SingerId")
.to ("2")
.set("AlbumId")
.to ("2")
.set("MarketingBudget")
.to ("400000")
.build()
),Options .commitStats ());
System.out.println(
"Updated data with "+commitResponse.getCommitStats ().getMutationCount ()+" mutations.");
}
}

Node.js

The following code sets the returnCommitStats flag and returns a mutation count of 6, because we are inserting or updating 2 rows and 3 columns in each row.

// 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.
constalbumsTable=database.table('Albums');
// Updates rows in the Venues table.
try{
const[response]=awaitalbumsTable.upsert(
[
{SingerId:'1',AlbumId:'1',MarketingBudget:'200000'},
{SingerId:'2',AlbumId:'2',MarketingBudget:'400000'},
],
{returnCommitStats:true},
);
console.log(
`Updated data with ${response.commitStats.mutationCount} mutations.`,
);
}catch(err){
console.error('ERROR:',err);
}finally{
// Close the database when finished.
database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
 * Creates a database and tables for sample data.
 * Example:
 * ```
 * create_database($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function get_commit_stats(string $instanceId, string $databaseId): void
{
 $spanner = new SpannerClient();
 $instance = $spanner->instance($instanceId);
 $database = $instance->database($databaseId);
 $commitStats = $database->runTransaction(function (Transaction $t) {
 $t->updateBatch('Albums', [
 [
 'SingerId' => 1,
 'AlbumId' => 1,
 'MarketingBudget' => 200000,
 ],
 [
 'SingerId' => 2,
 'AlbumId' => 2,
 'MarketingBudget' => 400000,
 ]
 ]);
 $t->commit(['returnCommitStats' => true]);
 return $t->getCommitStats();
 });
 print('Updated data with ' . $commitStats['mutationCount'] . ' mutations.' . PHP_EOL);
}

Python

Instead of returning commit statistics directly through the API, the Python client library logs them using stdout at level Info.

The following code enables commit statistics logging for all transactions by setting database.log_commit_stats = True. The code also implements a sample logger that keeps a reference to the last seen commit response. The mutation_count is then retrieved from this response and displayed.

deflog_commit_stats(instance_id, database_id):
"""Inserts sample data using DML and displays the commit statistics."""
 # By default, commit statistics are logged via stdout at level Info.
 # This sample uses a custom logger to access the commit statistics.
 classCommitStatsSampleLogger(logging.Logger):
 def__init__(self):
 self.last_commit_stats = None
 super().__init__("commit_stats_sample")
 definfo(self, msg, *args, **kwargs):
 if (
 "extra" in kwargs
 and kwargs["extra"]
 and "commit_stats" in kwargs["extra"]
 ):
 self.last_commit_stats = kwargs["extra"]["commit_stats"]
 super().info(msg, *args, **kwargs)
 spanner_client = spanner.Client()
 instance = spanner_client.instance(instance_id)
 database = instance.database(database_id, logger=CommitStatsSampleLogger())
 database.log_commit_stats = True
 definsert_singers(transaction):
 row_ct = transaction.execute_update(
 "INSERT Singers (SingerId, FirstName, LastName) "
 " VALUES (110, 'Virginia', 'Watson')"
 )
 print("{} record(s) inserted.".format(row_ct))
 database.run_in_transaction(insert_singers)
 commit_stats = database.logger.last_commit_stats
 print("{} mutation(s) in transaction.".format(commit_stats.mutation_count))

Ruby

The following code sets the return_commit_stats flag and returns a mutation count of 6, because we are inserting or updating 2 rows and 3 columns in each row.

# 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
records=[
{SingerId:1,AlbumId:1,MarketingBudget:200_000},
{SingerId:2,AlbumId:2,MarketingBudget:400_000}
]
commit_options={return_commit_stats:true}
resp=client.upsert"Albums",records,commit_options:commit_options
puts"Updated data with #{resp.stats.mutation_count} mutations."

What's next

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年11月10日 UTC.