Understanding Transactions and Distributed Transactions in Database Systems

Author: Su Shixiang, member of Haijing Tech PaaS component team, works on sharding middleware solutions.

1. Introduction The article introduces the concept of transactions in databases, defines a transaction as a series of operations ensuring correctness, and mentions the ACID properties. It then outlines the structure of the article: from single‑node transactions to distributed transactions, protocols, and personal insights.

2. Basic concepts of transactions A transaction is an indivisible unit of work that either fully succeeds or fully fails. Examples of a single‑statement transaction ( select * from test; ) and a multi‑statement transaction ( select * from test where id = 1; update test set name = 'john' where id = 1; ) are shown. In MySQL, a statement without explicit transaction boundaries is treated as an implicit transaction.

The transaction lifecycle consists of begin_transaction , the SQL operations, and commit or rollback . The following diagram illustrates the basic model:

Explanation of the diagram:

Transaction start: begin_transaction

Database operations: one or more SQL statements

Commit: commit_transaction

Rollback: rollback_transaction

2.2 Transaction properties (ACID)

Atomicity : all operations succeed or none do; no intermediate state.

Consistency : the database moves from one consistent state to another.

Isolation : concurrent transactions do not interfere; uncommitted changes are invisible to others.

Durability : once committed, changes survive crashes.

Images illustrate consistency and other properties.

3. Distributed transactions Unlike single‑node transactions, distributed transactions span multiple machines, forming a global transaction composed of sub‑transactions. The article shows diagrams of a single‑node transaction and a distributed transaction.

To guarantee atomicity across nodes, a coordinator and participants are required. The coordinator decides commit or rollback based on participants' votes.

3.2 Two‑phase commit (2PC) The protocol consists of a “prepare” phase where the coordinator asks participants to ready‑commit, and an “execute” phase where a global commit or abort is issued. The article provides a step‑by‑step description and a flow diagram.

3.3 Thoughts on distributed transactions The author notes the blocking problem of 2PC when the coordinator fails, and mentions three‑phase commit (3PC) as a mitigation, though its complexity limits practical use. Recovery strategies for various failures (node, network, participant) are also discussed, and the author concludes that perfect distributed transaction implementation remains challenging and is often discouraged.

4. Conclusion The article summarizes the discussion of transaction concepts, distributed transactions, and personal viewpoints, inviting readers to point out any inaccuracies.

References

《Distributed Database Systems》, Yu Ge & Shen Derong, Mechanical Industry Press, 2016 (2nd edition).