1. What Is a Transaction

• Combine multiple read and write operations into one logical unit and treat them as one operation.
• This operation satisfies the ACID properties.

2. Why Transactions Are Needed

• Mainly to simplify how the application layer handles a series of problems.
• A transaction is an abstraction layer: application -> transaction -> database.
  • Through the transaction abstraction, an application can pretend that the database will not crash (atomicity), nobody else is accessing the database at the same time (isolation), and the storage device is completely reliable (durability).
  • All these errors are simplified into transaction abort, and the application only needs to retry.

3. Transaction Properties

3.1. A (Atomicity)

3.1.1. What Is Atomicity

• No matter how many SQL statements there are, either all of them fail as a whole or all of them succeed as a whole.
• Atomicity
  • The atomicity here does not refer to concurrency; that is, it does not describe what happens when multiple threads access the same data at the same time. Concurrency is described by isolation.
  • It is more accurate to describe it as abortability: if an error occurs in the middle of a group of operations, the transaction can be aborted and all writes performed by that transaction can be discarded.

3.1.2. How Is Atomicity Implemented

InnoDB Transactions.md

3.2. C (Consistency)

3.2.1. What Is Consistency

• The system moves from one correct state to another correct state.
  • A current state is called correct if it satisfies the predefined constraints.
• Consistency is the most fundamental property; the other three properties exist to ensure consistency.

3.2.2. How Is Consistency Implemented

• Atomicity, isolation, and durability are properties of the database, while consistency is more accurately an application property. An application can use the database’s atomicity and isolation properties to implement part of consistency, but more needs to be considered by the application itself. Therefore, consistency is implemented in two aspects:
  • The database itself: consistency of a single transaction is guaranteed by atomicity, while consistency of concurrent transactions needs to be guaranteed by isolation.
  • Application level: guaranteed by the programmer.

3.3. I (Isolation)

3.3.1. What Is Isolation

• When multiple transactions access the same data at the same time, concurrency problems may occur. Isolation is used to solve concurrency problems.
• Two transactions being isolated means that concurrently executing transactions do not interfere with each other; that is, transaction A cannot see the intermediate results while transaction B is running.

3.3.2. Concurrency Problems

3.3.2.1. Dirty Write

• Transaction A modifies data that transaction B has modified but not yet committed.

• Scenario

  • If there is a val whose value is 10, transaction B first modifies it to 10, then transaction A modifies it to 20. The final result should be 20, but in fact transaction A’s write is overwritten by transaction B’s write.

  	Transaction A	Transaction B
  		Write val as 10 (uncommitted)
  	Write val as 20 (committed)
  		Commit write
  	Read val as 10

3.3.2.1.1. How to Solve Dirty Writes

• Row locks

3.3.2.2. Lost Update

• Two transactions read some values from the database, modify them, and write them back to the database. That is, the read-modify-write sequence is not completed in one operation.

• Scenario

  • If there is a val whose value is 10, and both transaction A and transaction B need to add 10 to it, the final result should be 30, but in fact transaction B overwrites transaction A’s val.

  	Transaction A	Transaction B
  	Read val as 10
  		Read val as 10
  	Write val + 10 as 20 (committed)
  		Write val + 10 as 20 (committed)

3.3.2.2.1. How to Solve Lost Updates

• Atomic write
  • udpate table set val=val+10 where xxx=yyy
• Pessimistic locking
  • Database Optimistic Locking and Pessimistic Locking.md
• Optimistic locking
  • Database Optimistic Locking and Pessimistic Locking.md

3.3.2.3. Dirty Read

• Transaction A reads data that transaction B has modified but not committed.

• Scenario

  • If there is a val whose value is 10, transaction B adds 10 to it but does not commit. Transaction A’s two reads should be the same, but in fact transaction A reads different results.

  	Transaction A	Transaction B
  		Read val as 10
  	Write val + 10 as 20 (uncommitted)
  		Read val as 20

3.3.2.3.1. How to Solve Dirty Reads

• Row locks can be used, but the efficiency is too poor.
• MVCC

3.3.2.4. Non-Repeatable Read

• Transaction A performs two queries with the same conditions, but the results are different because transaction B modifies this data and commits.

• Scenario

  • If there is a val whose value is 10, transaction B adds 10 to it and commits. Transaction A’s two reads should be the same, but in fact transaction A reads different results.

  	Transaction A	Transaction B
  		Read val as 10
  	Write val + 10 as 20 (update + committed)
  		Read val as 20

3.3.2.4.1. How to Solve Non-Repeatable Reads

• MVCC

3.3.2.5. Phantom Read

• Transaction A performs two queries with the same conditions, but the number of rows is different because transaction B adds records and commits.

• Scenario:

  • If a table has 10 records, transaction A inserts a new record. Transaction B’s two reads of the record count should both be 10, but in fact transaction B reads different record counts.

  	Transaction A	Transaction B
  		Read record count as 10
  	Write one new record (insert or delete + committed)
  		Read record count as 11

3.3.2.5.1. How to Solve Phantom Reads

• Serial access
• Two-phase locking
  • Predicate locks: difficult to implement
  • Index-range locks: MySQL uses this, called Gap Locks
• Serializable Snapshot Isolation

3.3.3. Isolation Levels

Different isolation levels can solve different concurrency problems. The problems solved by the isolation levels of each specific database are different, but generally speaking, dirty writes are too serious, so no isolation level of any database allows them.

• MySQL: InnoDB Transactions.md
• PostgreSQL: PostgreSQL Isolation Levels.md

3.3.4. D (Durability)

3.3.4.1. What Is Durability

• Once a transaction is committed, even if the system crashes, the changes made by the transaction to the database cannot be lost.

3.3.4.2. How Is Durability Implemented

• InnoDB Transactions.md

3.4. References

• A Brief Discussion of ACID in SQL SERVER - CareySon - cnblogs
• What Is the Difference and Relationship Between Database Transaction Isolation and Locking Mechanisms? - Zhihu
• How Are Atomicity and Consistency of Database Transactions Implemented? - Zhihu
• How Should the Concept of Consistency in Database Transactions Be Understood? - Zhihu
• ddia/ch7.md at master · Vonng/ddia · GitHub
• Transactions and Isolation Levels — Designing Data-Intensive Applications Reading Notes 10 - HappenLee - cnblogs
• Database First-Type and Second-Type Lost Updates - Tencent Cloud