loading
loading
Spanner: Removing cumulative mutation limits for DML transactions Rajeshwar Vanka Staff Software Engineer Justin Makeig Product Manager Spanner is Google Cloud’s no-compromise operational database that gives you the horizontal scale and always-on availability of a modern distributed system along with the rich feature set and familiar ecosystem of a relational database. Innovators in industries like banking, retail, media and entertainment, and AI infrastructure rely on Spanner today for their most critical workloads. We’re excited to announce a new, flexible way to handle larger, more complex transactions in Spanner, simplifying applications that need the highest levels of data consistency. Operational workloads typically combine real-time decision making with granular updates: Think: identifying fraud as part of a multi-step checkout process in an ecommerce app. These changes must be transactional; either all of them succeed or none of them do and subsequent requests see the correct data. This update to Spanner’s ACID transactions allows applications to handle more data in an update without compromising on consistency, scalability, or availability using familiar DML. Higher ceiling, more flexibility Previously, Spanner capped the changes a query could perform in a transaction, for example using DML, at 80,000. That was roughly computed as the product of the number of rows and number of columns updated, plus any dependent indexes. Applications evolve over time to handle more data and provide new functionality. These changes increase the size of transactions, potentially causing previously small transactions to hit this limit. This update shifts the 80,000 mutation mod limit from the transaction to individual DML statements. DML statements no longer contribute to an overall transaction-level mutation limit. A single transaction can now contain any number of DML statements, such as INSERT, UPDATE, or DELETE, provided that each individual statement generates fewer than 80,000 mutation mods. Key benefits - Larger transactions: Group DML statements logically based on business requirements rather than artificially splitting them to comply with cumulative mutation limits. - Seamless transition: This change is compatible with all existing Spanner client libraries and requires no updates to application code. Technical considerations Locking and aborts While you can now include more DML statements in a single transaction, be aware that larger and longer-running transactions hold locks for a greater duration. This may increase the likelihood of lock contention and transaction aborts. Keeping transactions concise helps maintain high performance and minimize resource contention. DML vs. Mutation API The application of limits depends on the method used to modify data: - DML Statements: Each statement (e.g., executeUpdate) is evaluated independently against the 80,000 mod limit. - Mutation API: When using client library methods like insert() or update(), mutations are provided during the Commit call. The 80,000 limit continues to apply to the entire set of mutations included in that single call. Understanding mutation mods Spanner counts "mods" based on the complexity of changes, including modified cells, primary keys, and secondary index updates. Please look at this blog for more details on how mutations are counted. You can monitor the total mods for a committed transaction via the mutation_count in the CommitStats. Note that the mutation_count will include all the mutations that are part of the transaction, across all DML statements and commit calls. Java implementation example The following example demonstrates how multiple DML statements can be executed within a single transaction under the new limit logic. What has not changed - Individual statement limit: Any single DML statement that generates more than 80,000 mods on its own will still return the same error as we do today. - Other transaction limits: Other constraints such as the maximum transaction size in bytes remain in effect. They are documented here. Best practices - Monitor CommitStats: Utilize the mutation_count returned in CommitStats to understand the load generated by your operations. - Optimize large operations: If a single statement (like a bulk update) exceeds the limit, consider using Partitioned DML or paginating through keys. Spanner is the trusted choice for operational applications that need to scale without downtime. This increase to the mutation limit provides developers new flexibility to run larger transactions that leverage Spanner’s global consistency. Learn how Spanner can help your teams innovate faster with less risk, or try it on your own, with a free trial or production instances starting as low as $54/month.
document fetched 2026-09-10 02:55 · canonical https://cloud.google.com/blog/products/databases/spanner-removes-dml-mutation-limits