Transactions

Introduction

A transaction in Cloud Spanner is a prepare of reads and writes that execute atomically at a single logical point in fourth dimension beyond columns, rows, and tables in a database.

Deject Spanner supports these transaction modes:

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

  • Read-but. This transaction type provides guaranteed consistency across several reads, merely does not let writes. Read-simply transactions tin can be configured to read at timestamps in the by. Read-merely transactions do not demand to be committed and do non take locks.

  • Partitioned DML. This transaction type executes a Data Manipulation Language (DML) argument every bit Partitioned DML. Partitioned DML is designed for bulk updates and deletes, peculiarly periodic cleanup and backfilling.

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

Read-write transactions

Here are scenarios in which y'all should use a locking read-write transaction:

  • If you practise a write that depends on the result of one or more than reads, yous should practice that write and the read(s) in the same read-write transaction.
    • Instance: double the residue of bank account A. The read of A'due south balance should be in the aforementioned transaction every bit the write to supervene upon the residuum with the doubled value.

  • If you practise ane or more than writes that need to be committed atomically, yous should exercise those writes in the same read-write transaction.
    • Example: transfer $200 from account A to account B. Both of the writes (one to subtract A by $200 and one to increase B by $200) and the reads of initial account balances should be in the same transaction.

  • If you might practice ane or more writes, depending on the results of one or more reads, you lot should do those writes and reads in the same read-write transaction, even if the write(s) don't end upwardly executing.
    • Example: transfer $200 from depository financial institution account A to banking concern account B if A's current balance is greater than $500. Your transaction should contain a read of A's balance and a conditional statement that contains the writes.

Hither is a scenario in which you should non use a locking read-write transaction:

  • If you lot are only doing reads, and yous can express your read using a single read method, you lot should use that single read method or a read-merely transaction. Single reads do not lock, different read-write transactions.

Backdrop

A read-write transaction in Cloud Spanner executes a set of reads and writes atomically at a unmarried logical point in time. Furthermore, the timestamp at which read-write transactions execute matches wall clock time, and the serialization guild matches the timestamp society.

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

Isolation

Transactions that read and write

Hither are the isolation properties y'all get for a read-write transaction that contains a series of reads and writes:

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

The upshot is that all reads and writes announced to have occurred at a single betoken in time, both from the perspective of the transaction itself and from the perspective of other readers and writers to the Deject Spanner database. In other words, the reads and the writes end upwards occurring at the same timestamp (see an illustration of this in the Serializability and external consistency section beneath).

Transactions that only read

The guarantees for a read-write transaction that only reads are similar: all reads within that transaction render information from the same timestamp, even for row non-beingness. One difference is that if you read data, and later commit the read-write transaction without any writes, in that location is no guarantee that the data did not change in the database subsequently the read and earlier the commit. If you want to know whether data has inverse since you lot read it last, the best arroyo is to read information technology again (either in a read-write transaction, or using a strong read.) Also, for efficiency, if you know in advance that you'll merely be reading and not writing, y'all should apply a read-simply transaction instead of a read-write transaction.

Atomicity, Consistency, Durability

In addition to the Isolation property, Cloud Spanner provides Atomicity (if any of the writes in the transaction commit, they all commit), Consistency (the database remains in a consequent state after the transaction) and Immovability (committed data stays committed.)

Benefits of these properties

Because of these properties, as an application developer, you lot can focus on the correctness of each transaction on its own, without worrying about how to protect its execution from other transactions that might execute at the aforementioned time.

Interface

The Cloud 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's a bit of context to explicate this betoken: a Cloud Spanner transaction may take to be tried multiple times before information technology commits. For instance, if two transactions attempt to work on data at the aforementioned time in a mode that might cause deadlock, Cloud Spanner aborts one of them so that the other transaction tin make progress. (More rarely, transient events inside Cloud Spanner may issue in some transactions aborting.) Since transactions are atomic, an aborted transaction has no visible effect on the database. Therefore, transactions should be executed by retrying them until they succeed.

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

Case

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

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

Your marketing department decides to do a marketing push for the anthology keyed by Albums (1, 1) and has asked you to motility $200,000 from the budget of Albums (two, two), but only if the money is available in that anthology'due south upkeep. Y'all should use a locking read-write transaction for this operation, because the transaction might do writes depending on the upshot of a read.

The post-obit shows how to execute a read-write transaction:

C++

C#

Become

Coffee

Node.js

PHP

Python

Ruby

Semantics

Serializability and external consistency

Cloud Spanner provides 'serializability', which ways that all transactions announced as if they executed in a serial order, even if some of the reads, writes, and other operations of distinct transactions actually occurred in parallel. Cloud Spanner assigns commit timestamps that reflect the society of committed transactions to implement this holding. In fact, Cloud Spanner offers a stronger guarantee than serializability called external consistency: transactions commit in an lodge that is reflected in their commit timestamps, and these commit timestamps reflect existent time so you can compare them to your watch. Reads in a transaction see everything that has been committed before the transaction commits, and writes are seen past everything that starts afterward the transaction is committed.

For example, consider the execution of two transactions every bit illustrated in the diagram below:

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

Transaction Txn1 in blue reads some data A, buffers a write to A, then successfully commits. Transaction Txn2 in green starts afterwards Txn1, reads some data B, then reads the data A. Since Txn2 reads the value of A after Txn1 committed its write to A, Txn2 sees the event of Txn1'south 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 guild, which means that all effects of the reads and writes of Txn1 announced to accept occurred at a single point of fourth dimension (c1), and all effects of the reads and writes of Txn2 announced to have occurred at a unmarried point of time (c2). Furthermore, c1 < c2 (which is guaranteed because both Txn1 and Txn2 committed writes; this is true even if the writes happened on dissimilar machines), which respects the order of Txn1 happening before Txn2. (Nonetheless, if Txn2 only did reads in the transaction, and so c1 <= c2).

Reads detect a prefix of the commit history; if a read sees the effect of Txn2, it too sees the effect of Txn1. All transactions that commit successfully have this holding.

Read and write guarantees

If a phone 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 instance, an error such as "Row Not Institute" or "Row Already Exists" means that writing the buffered mutations encountered some error, e.g. a row that the client is trying to update doesn't exist. In that case, the reads are guaranteed consequent, the writes are not applied, and the non-beingness of the row is guaranteed to exist consistent with the reads likewise.

Cancelling transaction operations

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

Even so, even if you have attempted to abolish the read, Deject Spanner does not guarantee that the read is really cancelled. After yous request the cancellation of a read, that read tin still successfully complete or fail with some other reason (eastward.g. Abort). Furthermore, that cancelled read might actually render some results to y'all, and those perchance incomplete results will be validated equally part of the transaction Commit.

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

Operation

Locking

Cloud Spanner allows multiple clients to simultaneously interact with the same database. In club to ensure the consistency of multiple concurrent transactions, Cloud 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, Cloud Spanner acquires shared read locks, which allows other reads to nevertheless admission the information until your transaction is set up to commit. When your transaction is committing and writes are existence 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 articulate, then places an sectional lock for sectional access to the data.

Notes well-nigh locks:

  • Locks are taken at the granularity of row-and-column. If transaction T1 has locked cavalcade "A" of row "foo", and transaction T2 wants to write column "B" of row "foo" and so there is no disharmonize.
  • Writes to a data item that don't also read the data being written (aka "blind writes") don't conflict with other blind writers of the same item (the commit timestamp of each write determines the society in which it is practical to the database). A outcome of this is that Cloud Spanner only needs to upgrade to an exclusive lock if you accept read the data yous are writing. Otherwise Cloud Spanner uses a shared lock called a writer shared lock.
  • When performing row lookups inside a read-write transaction, utilize 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 exterior of the range.

  • Locks should not be used to ensure sectional access to a resource outside of Cloud Spanner. Transactions can be aborted for several reasons past Deject Spanner such as, for instance, when assuasive data to movement effectually the instance's compute resources. If a transaction is retried, whether explicitly by application code or implicitly by client code such equally the Cloud Spanner JDBC driver, information technology is only guaranteed that the locks were held during the endeavor that actually committed.

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

Deadlock detection

Cloud Spanner detects when multiple transactions might be deadlocked, and forces all just i of the transactions to abort. For example, 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 abort one of the transactions so it releases its lock, allowing the other transaction to continue.

Cloud Spanner uses the standard "wound-wait" algorithm to handle deadlock detection. Under the hood, Cloud Spanner keeps track of the age of each transaction that requests alien locks. Information technology also allows older transactions to abort younger transactions (where "older" means that the transaction'due south primeval read, query, or commit happened sooner).

By giving priority to older transactions, Deject Spanner ensures that every transaction has a take a chance to learn locks eventually, subsequently it becomes old enough to have higher priority than other transactions. For case, a transaction that acquires a reader shared lock can exist aborted by an older transaction that needs a author shared lock.

Distributed execution

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

What types of transactions might exist distributed? Under the hood, Cloud Spanner can divide responsibleness 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 unlike servers; nevertheless, as a rule of thumb, transactions that bear upon many co-located rows are faster and cheaper than transactions that touch on many rows scattered throughout the database, or throughout a big tabular array.

The most efficient transactions in Cloud Spanner include only the reads and writes that should be applied atomically. Transactions are fastest when all reads and writes admission data in the aforementioned part of the key space.

Read-but 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 than ane read at the same timestamp. If you lot can express your read using ane of Cloud Spanner'south single read methods, you should utilise that single read method instead. The functioning of using such a single read phone call should exist comparable to the performance of a single read washed in a read-simply transaction.

If y'all are reading a large amount of information, consider using partitions to read the data in parallel.

Because read-merely transactions don't write, they don't hold locks and they don't block other transactions. Read-just transactions observe a consistent prefix of the transaction commit history, so your awarding always gets consistent data.

Properties

A Deject Spanner read-just transaction executes a gear up of reads at a single logical bespeak 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 find a consequent state of the database at a called point in the transaction history.

Interface

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

Example

The following shows how to use a read-just transaction to get consistent information for ii reads at the same timestamp:

C++

C#

Go

Coffee

Node.js

PHP

Python

Cherry-red

Partitioned DML transactions

Using Partitioned Information Manipulation Language (Partitioned DML), you can execute large-scale UPDATE and DELETE statements without running into transaction limits or locking an entire table. Cloud Spanner partitions the key space and executes the DML statements on each partitioning in a separate read-write transaction.

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

Properties

You can execute merely one Partitioned DML statement at a time, whether you are using a customer library method or the Google Cloud CLI.

Partitioned transactions do non back up commit or rollback. Cloud Spanner executes and applies the DML statement immediately. If you abolish the operation, or the functioning fails, and so Cloud Spanner cancels all the executing partitions and doesn't start any of the remaining partitions. Cloud Spanner does non rollback any partitions that take already executed.

Interface

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

Examples

The following code example updates the MarketingBudget column of the Albums table.

C++

You use the ExecutePartitionedDml() function to execute a Partitioned DML statement.

C#

You lot utilize the ExecutePartitionedUpdateAsync() method to execute a Partitioned DML argument.

Go

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

Java

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

Node.js

Y'all use the runPartitionedUpdate() method to execute a Partitioned DML argument.

PHP

Yous apply the executePartitionedUpdate() method to execute a Partitioned DML argument.

Python

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

Carmine

Yous employ the execute_partitioned_update() method to execute a Partitioned DML argument.

The following code example deletes rows from the Singers table, based on the SingerId column.

C++

C#

Go

Java

Node.js

PHP

Python

Ruby