Modify data using batch write

Preview —Batch write

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.

This page describes Spanner batch write requests and how you can usethem to modify your Spanner data.

You can use Spanner batch write to insert, update, or deletemultiple rows in your Spanner tables. Spannerbatch write supports low latency writes without a read operation, and returnsresponses as mutations are applied in batches. To use batch write, you grouprelated mutations together, and all mutations in a group are committedatomically. The mutations across groups are applied in an unspecified order andare independent of one another (non-atomic). Spanner doesn't needto wait for all mutations to be applied before sending a response, which meansthat batch write allows for partial failure. You can also execute multiple batchwrites at a time. For more information, seeHow to use batch write.

Use cases

Spanner batch write is especially useful if you want to commit alarge number of writes without a read operation, but don't require an atomictransaction for all your mutations.

If you want to batch your DML requests, usebatch DMLto modify your Spanner data. For more information on thedifferences between DML and mutations, seeComparing DML and mutations.

For single mutation requests, we recommend using alocking read-writetransaction.

Limitations

Spanner batch write has the following limitations:

  • Spanner batch write is not available using theGoogle Cloud console or Google Cloud CLI. It is only available using REST and RPCAPIs and the Spanner client libraries.

  • Replay protectionis not supported using batch write. It's possible for mutations to beapplied more than once, and a mutation that is applied more than once mightresult in a failure. For example, if an insert mutation is replayed, it mightproduce an already exists error, or if you use generated or committimestamp-based keys in the mutation, it might result in additional rows beingadded to the table. We recommend structuring your writes to be idempotent toavoid this issue.

  • You can't rollback a completed batch write request. You can cancel anin-progress batch write request. If you cancel an in-progress batch write,mutations in uncompleted groups are rolled back. Mutations in completed groupsare committed to the database.

  • The maximum size for a batch write request is the same as the limit for acommit request. For more information, seeLimits for creating, reading, updating, and deleting data.

How to use batch write

To use batch write, you must have thespanner.databases.write permission onthe database that you want to modify. You can batch write mutationsnon-atomically in a single call using aRESTorRPC APIrequest call.

You should group the following mutation types together when using batch write:

  • Inserting rows with the same primary key prefix in both the parent and childtables.
  • Inserting rows into tables with a foreign key relationship between the tables.
  • Other types of related mutations depending on your database schema andapplication logic.

You can also batch write using the Spanner client libraries.The following code example updates theSingers table with new rows.

Client libraries

Java

importcom.google.api.gax.rpc.ServerStream;importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.Mutation;importcom.google.cloud.spanner.MutationGroup;importcom.google.cloud.spanner.Options;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.common.collect.ImmutableList;importcom.google.rpc.Code;importcom.google.spanner.v1.BatchWriteResponse;publicclassBatchWriteAtLeastOnceSample{/***   * Assume DDL for the underlying database:   * <pre>{@code   *   CREATE TABLE Singers (   *     SingerId   INT64 NOT NULL,   *     FirstName  STRING(1024),   *     LastName   STRING(1024),   *   ) PRIMARY KEY (SingerId)   *   *   CREATE TABLE Albums (   *     SingerId     INT64 NOT NULL,   *     AlbumId      INT64 NOT NULL,   *     AlbumTitle   STRING(1024),   *   ) PRIMARY KEY (SingerId, AlbumId),   *   INTERLEAVE IN PARENT Singers ON DELETE CASCADE   * }</pre>   */privatestaticfinalMutationGroupMUTATION_GROUP1=MutationGroup.of(Mutation.newInsertOrUpdateBuilder("Singers").set("SingerId").to(16).set("FirstName").to("Scarlet").set("LastName").to("Terry").build());privatestaticfinalMutationGroupMUTATION_GROUP2=MutationGroup.of(Mutation.newInsertOrUpdateBuilder("Singers").set("SingerId").to(17).set("FirstName").to("Marc").build(),Mutation.newInsertOrUpdateBuilder("Singers").set("SingerId").to(18).set("FirstName").to("Catalina").set("LastName").to("Smith").build(),Mutation.newInsertOrUpdateBuilder("Albums").set("SingerId").to(17).set("AlbumId").to(1).set("AlbumTitle").to("Total Junk").build(),Mutation.newInsertOrUpdateBuilder("Albums").set("SingerId").to(18).set("AlbumId").to(2).set("AlbumTitle").to("Go, Go, Go").build());staticvoidbatchWriteAtLeastOnce(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";batchWriteAtLeastOnce(projectId,instanceId,databaseId);}staticvoidbatchWriteAtLeastOnce(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){DatabaseIddbId=DatabaseId.of(projectId,instanceId,databaseId);finalDatabaseClientdbClient=spanner.getDatabaseClient(dbId);// Creates and issues a BatchWrite RPC request that will apply the mutation groups// non-atomically and respond back with a stream of BatchWriteResponse.ServerStream<BatchWriteResponse>responses=dbClient.batchWriteAtLeastOnce(ImmutableList.of(MUTATION_GROUP1,MUTATION_GROUP2),Options.tag("batch-write-tag"));// Iterates through the results in the stream response and prints the MutationGroup indexes,// commit timestamp and status.for(BatchWriteResponseresponse:responses){if(response.getStatus().getCode()==Code.OK_VALUE){System.out.printf("Mutation group indexes %s have been applied with commit timestamp %s",response.getIndexesList(),response.getCommitTimestamp());}else{System.out.printf("Mutation group indexes %s could not be applied with error code %s and "+"error message %s",response.getIndexesList(),Code.forNumber(response.getStatus().getCode()),response.getStatus().getMessage());}}}}}

Go

import("context""fmt""io""cloud.google.com/go/spanner"sppb"cloud.google.com/go/spanner/apiv1/spannerpb""google.golang.org/grpc/status")// batchWrite demonstrates writing mutations to a Spanner database through// BatchWrite API - https://pkg.go.dev/cloud.google.com/go/spanner#Client.BatchWritefuncbatchWrite(wio.Writer,dbstring)error{// db := "projects/my-project/instances/my-instance/databases/my-database"ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Database is assumed to exist - https://cloud.google.com/spanner/docs/getting-started/go#create_a_databasesingerColumns:=[]string{"SingerId","FirstName","LastName"}albumColumns:=[]string{"SingerId","AlbumId","AlbumTitle"}mutationGroups:=make([]*spanner.MutationGroup,2)mutationGroup1:=[]*spanner.Mutation{spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{16,"Scarlet","Terry"}),}mutationGroups[0]=&spanner.MutationGroup{Mutations:mutationGroup1}mutationGroup2:=[]*spanner.Mutation{spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{17,"Marc",""}),spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{18,"Catalina","Smith"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{17,1,"Total Junk"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{18,2,"Go, Go, Go"}),}mutationGroups[1]=&spanner.MutationGroup{Mutations:mutationGroup2}iter:=client.BatchWrite(ctx,mutationGroups)// See https://pkg.go.dev/cloud.google.com/go/spanner#BatchWriteResponseIterator.DodoFunc:=func(response*sppb.BatchWriteResponse)error{iferr=status.ErrorProto(response.GetStatus());err==nil{fmt.Fprintf(w,"Mutation group indexes %v have been applied with commit timestamp %v",response.GetIndexes(),response.GetCommitTimestamp())}else{fmt.Fprintf(w,"Mutation group indexes %v could not be applied with error %v",response.GetIndexes(),err)}// Return an actual error as needed.returnnil}returniter.Do(doFunc)}

Node

// Imports the Google Cloud client libraryconst{Spanner,MutationGroup}=require('@google-cloud/spanner');/** * TODO(developer): Uncomment the following lines before running the sample. */// const instanceId = 'my-instance';// const databaseId = 'my-database';// const projectId = 'my-project-id';// Creates a clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);// Create Mutation Groups/** * Related mutations should be placed in a group, such as insert mutations for both a parent and a child row. * A group must contain related mutations. * Please see {@link https://cloud.google.com/spanner/docs/reference/rpc/google.spanner.v1#google.spanner.v1.BatchWriteRequest.MutationGroup} * for more details and examples. */constmutationGroup1=newMutationGroup();mutationGroup1.insert('Singers',{SingerId:1,FirstName:'Scarlet',LastName:'Terry',});constmutationGroup2=newMutationGroup();mutationGroup2.insert('Singers',{SingerId:2,FirstName:'Marc',});mutationGroup2.insert('Singers',{SingerId:3,FirstName:'Catalina',LastName:'Smith',});mutationGroup2.insert('Albums',{AlbumId:1,SingerId:2,AlbumTitle:'Total Junk',});mutationGroup2.insert('Albums',{AlbumId:2,SingerId:3,AlbumTitle:'Go, Go, Go',});constoptions={transactionTag:'batch-write-tag',};try{database.batchWriteAtLeastOnce([mutationGroup1,mutationGroup2],options).on('error',console.error).on('data',response=>{// Check the response code of each response to determine whether the mutation group(s) were applied successfully.if(response.status.code===0){console.log(`Mutation group indexes${response.indexes}, have been applied with commit timestamp${Spanner.timestamp(response.commitTimestamp,).toJSON()}`,);}// Mutation groups that fail to commit trigger a response with a non-zero status code.else{console.log(`Mutation group indexes${response.indexes}, could not be applied with error code${response.status.code}, and error message${response.status.message}`,);}}).on('end',()=>{console.log('Request completed successfully');});}catch(err){console.log(err);}

Python

defbatch_write(instance_id,database_id):"""Inserts sample data into the given database via BatchWrite API.    The database and table must already exist and can be created using    `create_database`.    """fromgoogle.rpc.code_pb2importOKspanner_client=spanner.Client()instance=spanner_client.instance(instance_id)database=instance.database(database_id)withdatabase.mutation_groups()asgroups:group1=groups.group()group1.insert_or_update(table="Singers",columns=("SingerId","FirstName","LastName"),values=[(16,"Scarlet","Terry"),],)group2=groups.group()group2.insert_or_update(table="Singers",columns=("SingerId","FirstName","LastName"),values=[(17,"Marc",""),(18,"Catalina","Smith"),],)group2.insert_or_update(table="Albums",columns=("SingerId","AlbumId","AlbumTitle"),values=[(17,1,"Total Junk"),(18,2,"Go, Go, Go"),],)forresponseingroups.batch_write():ifresponse.status.code==OK:print("Mutation group indexes{} have been applied with commit timestamp{}".format(response.indexes,response.commit_timestamp))else:print("Mutation group indexes{} could not be applied with error{}".format(response.indexes,response.status))

C++

namespacespanner=::google::cloud::spanner;// Use upserts as mutation groups are not replay protected.autocommit_results=client.CommitAtLeastOnce({// group #0spanner::Mutations{spanner::InsertOrUpdateMutationBuilder("Singers",{"SingerId","FirstName","LastName"}).EmplaceRow(16,"Scarlet","Terry").Build(),},// group #1spanner::Mutations{spanner::InsertOrUpdateMutationBuilder("Singers",{"SingerId","FirstName","LastName"}).EmplaceRow(17,"Marc","").EmplaceRow(18,"Catalina","Smith").Build(),spanner::InsertOrUpdateMutationBuilder("Albums",{"SingerId","AlbumId","AlbumTitle"}).EmplaceRow(17,1,"Total Junk").EmplaceRow(18,2,"Go, Go, Go").Build(),},});for(auto&commit_result:commit_results){if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Mutation group indexes [";for(autoindex:commit_result->indexes)std::cout <<" " <<index;std::cout <<" ]: ";if(commit_result->commit_timestamp){autoconst&ts=*commit_result->commit_timestamp;std::cout <<"Committed at " <<ts.get<absl::Time>().value();}else{std::cout <<commit_result->commit_timestamp.status();}std::cout <<"\n";}

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.