Transactions

Introduction

A transaction in Cloud Spanner is a set of reads and writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.

Cloud Spanner supports these transaction modes:

  • Locking read-write. This type of transaction is the just transaction type that supports writing data into Cloud Spanner. These transactions rely on pessimistic locking and, if necessary, ii-stage commit. Locking read-write transactions may arrest, requiring the awarding to retry.

  • Read-only. This transaction type provides guaranteed consistency across several reads, but does not permit writes. Read-simply transactions can be configured to read at timestamps in the past. Read-simply transactions do non need to exist committed and do not take locks.

  • Partitioned DML. This transaction type executes a Data Manipulation Language (DML) argument as Partitioned DML. Partitioned DML is designed for majority updates and deletes, particularly periodic cleanup and backfilling.

This page describes the general properties and semantics of transactions in Cloud Spanner and introduces the read-write, read-only, and Partitioned DML transaction interfaces in Cloud Spanner.

Read-write transactions

Here are scenarios in which you should use a locking read-write transaction:

  • If y'all practice a write that depends on the result of ane or more than reads, you lot should do that write and the read(s) in the same read-write transaction.
    • Example: double the residue of bank account A. The read of A's balance should be in the same transaction every bit the write to replace the rest with the doubled value.

  • If you practise ane or more than writes that need to exist committed atomically, y'all should do those writes in the same read-write transaction.
    • Example: transfer $200 from business relationship A to account B. Both of the writes (i to decrease A by $200 and one to increment B past $200) and the reads of initial business relationship balances should be in the same transaction.

  • If yous might practise one or more writes, depending on the results of ane or more than reads, you should do those writes and reads in the same read-write transaction, even if the write(s) don't cease up executing.
    • Example: transfer $200 from bank account A to bank account B if A's current balance is greater than $500. Your transaction should contain a read of A's balance and a provisional statement that contains the writes.

Here is a scenario in which y'all should not employ a locking read-write transaction:

  • If yous are only doing reads, and you can limited your read using a single read method, you should utilise that single read method or a read-only transaction. Single reads do non lock, unlike read-write transactions.

Properties

A read-write transaction in Cloud Spanner executes a gear up of reads and writes atomically at a single logical signal in time. Furthermore, the timestamp at which read-write transactions execute matches wall clock time, and the serialization order matches the timestamp lodge.

Why use a read-write transaction? Read-write transactions provide the Acrid properties of relational databases (In fact, Cloud Spanner read-write transactions offer even stronger guarantees than traditional Acid; see the Semantics section below.).

Isolation

Transactions that read and write

Here are the isolation properties you get for a read-write transaction that contains a series of reads and writes:

  • All reads within that transaction render data from the same timestamp.
  • If a transaction successfully commits, and then no other author modified the data that was read in the transaction after it was read.
  • These properties hold fifty-fifty for reads that returned no rows, and the gaps between rows returned by range reads: row non-existence counts as data.
  • All writes within that transaction are committed at the same timestamp.
  • All writes within that transaction are only visible after the transaction commits.

The effect is that all reads and writes announced to take occurred at a unmarried bespeak in fourth dimension, both from the perspective of the transaction itself and from the perspective of other readers and writers to the Cloud Spanner database. In other words, the reads and the writes finish upwardly occurring at the same timestamp (see an illustration of this in the Serializability and external consistency department below).

Transactions that simply read

The guarantees for a read-write transaction that only reads are similar: all reads within that transaction return data from the same timestamp, even for row non-being. One difference is that if you read data, and later on commit the read-write transaction without whatever writes, there is no guarantee that the information did not change in the database subsequently the read and before the commit. If y'all want to know whether data has changed since yous read it concluding, the best approach is to read information technology again (either in a read-write transaction, or using a strong read.) As well, for efficiency, if you know in advance that you'll just be reading and not writing, y'all should use a read-simply transaction instead of a read-write transaction.

Atomicity, Consistency, Durability

In addition to the Isolation property, Deject Spanner provides Atomicity (if any of the writes in the transaction commit, they all commit), Consistency (the database remains in a consistent state afterward the transaction) and Durability (committed data stays committed.)

Benefits of these properties

Because of these backdrop, as an application developer, you tin focus on the correctness of each transaction on its own, without worrying most how to protect its execution from other transactions that might execute at the same time.

Interface

The Deject Spanner client libraries provide an interface for executing a body of work in the context of a read-write transaction, with retries for transaction aborts. Here'due south a bit of context to explain this betoken: a Deject Spanner transaction may have to exist tried multiple times before it commits. For example, if two transactions attempt to work on data at the aforementioned fourth dimension in a mode that might cause deadlock, Deject Spanner aborts one of them so that the other transaction can make progress. (More rarely, transient events inside Deject Spanner may effect in some transactions aborting.) Since transactions are diminutive, an aborted transaction has no visible event on the database. Therefore, transactions should be executed by retrying them until they succeed.

When you lot use a transaction in a Cloud Spanner client library, you lot ascertain the body of a transaction (i.e., the reads and writes to perform on one or more than tables in a database) in the form of a function object. Under the hood, the Cloud Spanner client library runs the part repeatedly until the transaction commits or a non-retryable error is encountered.

Instance

Assume you lot added a MarketingBudget column to the Albums table shown in the Schema and Data Model page:

CREATE Table Albums (   SingerId        INT64 NOT Zip,   AlbumId         INT64 Non NULL,   AlbumTitle      STRING(MAX),   MarketingBudget INT64 ) PRIMARY Key (SingerId, AlbumId);

Your marketing department decides to practice a marketing push for the album keyed past Albums (one, 1) and has asked you to movement $200,000 from the upkeep of Albums (2, 2), but only if the coin is available in that album's budget. You lot should utilize a locking read-write transaction for this operation, because the transaction might do writes depending on the issue of a read.

The following shows how to execute a read-write transaction:

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

Semantics

Serializability and external consistency

Cloud Spanner provides 'serializability', which means that all transactions appear as if they executed in a serial club, even if some of the reads, writes, and other operations of distinct transactions really occurred in parallel. Cloud Spanner assigns commit timestamps that reflect the club of committed transactions to implement this property. In fact, Cloud Spanner offers a stronger guarantee than serializability called external consistency: transactions commit in an social club that is reflected in their commit timestamps, and these commit timestamps reflect real time so you can compare them to your scout. Reads in a transaction meet everything that has been committed earlier the transaction commits, and writes are seen by everything that starts afterward the transaction is committed.

For example, consider the execution of ii transactions as illustrated in the diagram beneath:

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

Transaction Txn1 in bluish reads some data A, buffers a write to A, then successfully commits. Transaction Txn2 in green starts after Txn1, reads some information B, then reads the data A. Since Txn2 reads the value of A later Txn1 committed its write to A, Txn2 sees the event of Txn1's write to A, even though Txn2 started before Txn1 completed.

Even though there is some overlap in time in which Txn1 and Txn2 are both executing, their commit timestamps c1 and c2 respect a linear transaction social club, which ways that all effects of the reads and writes of Txn1 appear to have occurred at a single indicate of time (c1), and all effects of the reads and writes of Txn2 appear to accept occurred at a unmarried point of time (c2). Furthermore, c1 < c2 (which is guaranteed considering both Txn1 and Txn2 committed writes; this is truthful even if the writes happened on different machines), which respects the gild of Txn1 happening before Txn2. (Withal, if Txn2 only did reads in the transaction, so c1 <= c2).

Reads observe a prefix of the commit history; if a read sees the outcome of Txn2, it also sees the effect of Txn1. All transactions that commit successfully accept this holding.

Read and write guarantees

If a telephone call to run a transaction fails, the read and write guarantees you take depend on what error the underlying commit phone call failed with.

For example, an error such as "Row Not Found" or "Row Already Exists" means that writing the buffered mutations encountered some error, e.1000. a row that the customer is trying to update doesn't exist. In that instance, the reads are guaranteed consistent, the writes are not practical, and the non-existence of the row is guaranteed to exist consistent with the reads as well.

Cancelling transaction operations

Asynchronous read operations may exist cancelled any time by the user (e.grand., when a higher level operation is cancelled or yous decide to terminate a read based on the initial results received from the read) without affecting any other existing operations within the transaction.

However, even if you have attempted to cancel the read, Cloud Spanner does non guarantee that the read is really cancelled. Afterward yous request the cancellation of a read, that read tin can still successfully consummate or fail with another reason (eastward.grand. Arrest). Furthermore, that cancelled read might actually return some results to y'all, and those possibly incomplete results volition exist validated every bit office of the transaction Commit.

Note that dissimilar reads, cancelling a transaction Commit operation will upshot in aborting the transaction (unless the transaction has already Committed or failed with another reason).

Performance

Locking

Cloud Spanner allows multiple clients to simultaneously interact with the aforementioned database. In order to ensure the consistency of multiple concurrent transactions, Deject Spanner uses a combination of shared locks and exclusive locks to control access to the data. When you perform a read every bit part of a transaction, Deject Spanner acquires shared read locks, which allows other reads to notwithstanding access the data until your transaction is ready to commit. When your transaction is committing and writes are beingness applied, the transaction attempts to upgrade to an exclusive lock. It blocks new shared read locks on the data, waits for existing shared read locks to clear, and then places an sectional lock for sectional access to the data.

Notes most locks:

  • Locks are taken at the granularity of row-and-cavalcade. If transaction T1 has locked column "A" of row "foo", and transaction T2 wants to write cavalcade "B" of row "foo" then there is no disharmonize.
  • Writes to a information item that don't also read the data being written (aka "blind writes") don't disharmonize with other blind writers of the same item (the commit timestamp of each write determines the order in which it is applied to the database). A effect of this is that Cloud Spanner just needs to upgrade to an exclusive lock if yous have read the information you are writing. Otherwise Cloud Spanner uses a shared lock called a writer shared lock.
  • When performing row lookups inside a read-write transaction, use secondary indexes to limit the rows scanned to a smaller range. This causes Cloud Spanner to lock a fewer number of rows in the table, allowing concurrent modification to rows outside of the range.

  • Locks should non be used to ensure exclusive access to a resource outside of Deject Spanner. Transactions tin can be aborted for several reasons by Cloud Spanner such as, for example, when assuasive data to move around the case's compute resources. If a transaction is retried, whether explicitly by awarding code or implicitly by client code such as the Cloud Spanner JDBC driver, information technology is simply guaranteed that the locks were held during the attempt that actually committed.

  • Y'all can use the Lock statistics introspection tool to investigate lock conflicts in your database.

Deadlock detection

Cloud Spanner detects when multiple transactions might exist deadlocked, and forces all only one of the transactions to abort. For instance, consider the following scenario: transaction Txn1 holds a lock on record A and is waiting for a lock on record B, and Txn2 holds a lock on record B and is waiting for a lock on record A. The only way to make progress in this situation is to arrest i of the transactions so it releases its lock, allowing the other transaction to go on.

Cloud Spanner uses the standard "wound-wait" algorithm to handle deadlock detection. Nether the hood, Deject Spanner keeps runway of the age of each transaction that requests alien locks. It also allows older transactions to abort younger transactions (where "older" means that the transaction's earliest read, query, or commit happened sooner).

By giving priority to older transactions, Cloud Spanner ensures that every transaction has a chance to learn locks somewhen, after it becomes erstwhile plenty to have higher priority than other transactions. For example, a transaction that acquires a reader shared lock tin can be aborted by an older transaction that needs a writer shared lock.

Distributed execution

Deject Spanner can run transactions on data that spans multiple servers. This ability comes at a performance price compared to single-server transactions.

What types of transactions might exist distributed? Nether the hood, Cloud Spanner tin can carve up responsibility for rows in the database across many servers. A row and the corresponding rows in interleaved tables are usually served by the same perform transactions across rows on dissimilar servers; however, as a rule of thumb, transactions that affect many co-located rows are faster and cheaper than transactions that affect many rows scattered throughout the database, or throughout a large tabular array.

The most efficient transactions in Deject Spanner include simply the reads and writes that should be applied atomically. Transactions are fastest when all reads and writes access information in the same part of the cardinal space.

Read-merely transactions

In addition to locking read-write transactions, Cloud Spanner offers read-simply transactions.

Use a read-only transaction when y'all need to execute more 1 read at the same timestamp. If yous can express your read using i of Cloud Spanner's single read methods, y'all should use that single read method instead. The performance of using such a single read call should be comparable to the performance of a single read done in a read-simply transaction.

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

Considering read-only transactions don't write, they don't hold locks and they don't block other transactions. Read-simply transactions notice a consistent prefix of the transaction commit history, so your awarding always gets consistent data.

Properties

A Deject Spanner read-only transaction executes a set of reads at a single logical signal in time, both from the perspective of the read-only transaction itself and from the perspective of other readers and writers to the Cloud Spanner database. This means that read-only transactions always observe a consistent state of the database at a chosen point in the transaction history.

Interface

Cloud Spanner provides an interface for executing a trunk of work in the context of a read-only transaction, with retries for transaction aborts.

Case

The following shows how to use a read-only transaction to get consistent data for two reads at the aforementioned timestamp:

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

Partitioned DML transactions

Using Partitioned Data Manipulation Language (Partitioned DML), yous can execute large-calibration UPDATE and DELETE statements without running into transaction limits or locking an unabridged table. Deject Spanner partitions the fundamental space and executes the DML statements on each partition in a separate read-write transaction.

You run DML statements in read-write transactions that you explicitly create in your code. For more information, meet Using DML.

Properties

You can execute only one Partitioned DML argument at a time, whether you lot are using a customer library method or the Google Deject CLI.

Partitioned transactions do not support commit or rollback. Cloud Spanner executes and applies the DML argument immediately. If yous cancel the functioning, or the functioning fails, then Cloud Spanner cancels all the executing partitions and doesn't start any of the remaining partitions. Cloud Spanner does not rollback any partitions that have already executed.

Interface

Cloud Spanner provides an interface for executing a single Partitioned DML argument.

Examples

The following code example updates the MarketingBudget column of the Albums tabular array.

C++

You lot employ the ExecutePartitionedDml() office to execute a Partitioned DML statement.

C#

You employ the ExecutePartitionedUpdateAsync() method to execute a Partitioned DML statement.

Go

You employ the PartitionedUpdate() method to execute a Partitioned DML statement.

Java

Yous use the executePartitionedUpdate() method to execute a Partitioned DML statement.

Node.js

You apply the runPartitionedUpdate() method to execute a Partitioned DML statement.

PHP

Yous use the executePartitionedUpdate() method to execute a Partitioned DML statement.

Python

You use the execute_partitioned_dml() method to execute a Partitioned DML statement.

Ruby

You utilise the execute_partitioned_update() method to execute a Partitioned DML statement.

The post-obit lawmaking example deletes rows from the Singers table, based on the SingerId cavalcade.

C++

C#

Go

Java

Node.js

PHP

Python

Ruby