Service Architecture for OLTP --- Part 2. Transaction in Database

ACID is a buzz word in database jargon and you can find a good explanation of its meaning on many sources. Out of those four properties:

• Atomicity is best understood with least ambiguity.
• Consistency in ACID means very different from the same term in the context of distributed systems. C in ACID refers to that a transaction takes the data in DB to a valid state. For example, the result of a transaction has to satisfy all the data constraints, such as primary-foreign keys, or the transaction can not commit.
• Isolation defines how the states a transaction sees could be affected by other transactions. It covers many alternative semantics as compromises on the most ideal isolation for implementation efficiency.
• Durability is conceptually simple and clear for an abstract database, but implies a lot complexity in the context of Distributed Systems, where a database is replicated for HA and DR.

This post will explain local transaction in a database, particularly the implementation of A and I in ACID.

Serializable Isolation Level

Ansi SQL defines 4 isolation levels: Serializable, Repeatable reads, Read committed and Read uncommitted. Serializable is the strongest of the four and also the most popular choice for applications based on safety. In Serializable isolation, a transaction sees only the states generated/updated by transactions committed before the start of the transaction.

Use MVCC to avoid Read Lock

Serializable isolation can be implemented simply with read locks on read set, which either blocks parallel transactions that share the same records for read or write in terms of pessimistic locking (all locks are acquired at the first access), or cause a lock conflicts with those transactions in terms of opportunistic locking. MVCC (Multi-version Concurrency Control) can be used to avoid locks on read set, and hence achieve higher concurrency of transaction processing. In fact, Read-only transaction does not need to acquire any locks. MVCC keeps multiple versions of the same record (a table row in relational DB). Creation timestamp and expiration timestamp are tracked for each version of a record. A transaction finds the version of a record to read based on that its timestamp fall into the range of those two timestamps.

MVCC though is not needed for isolation level of Read-Committed, where the latest committed version is always read in a transaction.

Locking Writes

Locking will always be needed for write set in a transaction. The granularity of locking is usually a record (a table row) in most relational DBs, which is important for parallelism of RW-transactions. In pessimistic locking, a record is locked at its first update in a transaction and released when the transaction commits or aborts. But a record is not be locked for read in a MVCC based implementation for serializable isolation.

Locking Reads

Serializable isolation does not prevent the following scenario:

"At time t0, record r1 is read by transaction T1. At time t1, r1 is updated by another transaction t2 and t2 commits. At time t2, T1 performs a mutation on record r2 based on its read version of r1, and T1 commits."

The above scenario is actually OK for most of the application usages, which is the reason why serializable isolation is considered enough for most. Though an even higher isolation level would be locking both read set and write set. Most relational DBs provide such way to lock read set just as if there were updated. For example, MySQL uses "SELECT ... FOR UPDATE" as a read lock.

Scope of Transaction

Most DBs implements local transactions with primitives based on shared memory. Hence the scope of a local transaction is usually a DB shard, which is hosted on a single node. Local transaction is sufficient for majority of application usages, where data can be naturally partitioned and there is little  need to have transaction across partitions. For example, Google Ads once used a database sharded by advertiser accounts, where every transaction is within the scope of the data of a single advertiser.

Transaction Language

A transaction is a sequence of operations. A language is needed to describe such sequence of operations and that language is SQL for relational DBs. The question whether SQL has the same expressive power as other functional languages or imperative languages turned out to be rather complex. But practically, the expressive power of SQL has successfully eliminated the necessity of interactive transactions for application programs. An interactive transaction is similar to a transaction performed thru a DB client console, where output from operations are fed to client and client feeds input to subsequent operations. An interactive transaction is inefficient for two reasons:

• Network time is added to the duration of transaction and hence the duration of locking on some data. This not only increase the latency of transaction but also reduce the concurrency of transaction processing.
• May not be possible to optimize a sequence of operation as a whole as they are not submitted to DB server at once.

There are clear reasons for SQL being the transaction language of relational DB:

• SQL is a domain specific language for relational algebra.
• SQL is declarative for the most part.

A declarative language describes what to do as opposed to how, hence relieves programmers from coding all details correctly. This is especially important for operations programmed by uses but running on servers (regardless whether it is relational DB or other types of data stores). Such scenario requires both efficiency and safety (Eg, preventing DB service crashes caused by running user-coded transaction).

This post provides the necessary background knowledge for the next one on application patterns of OLTP.