ACID 属性
理解 Atomicity, Consistency, Isolation, and Durability in 数据库事务.
你将学到
- 什么 ACID stands for and 为什么每个属性 matters
- Atomicity — 所有 or 无
- Consistency — 有效状态之前 and 之后
- Isolation levels — 读取 Uncommitted 到 Serializable
- Durability — committed 数据 survives crashes
概念讲解
ACID 概述
ACID is a set of four properties that guarantee reliable processing of database transactions. They were defined by Andreas Reuter and Theo Härder in 1983 and remain the gold standard for transactional integrity. Relational databases like PostgreSQL and MySQL (with InnoDB) provide full ACID compliance; many NoSQL databases relax these properties for better scalability (the BASE model).
Atomicity
Atomicity means a transaction is an indivisible unit: either all its operations succeed, or none take effect. If you transfer money between accounts and the system crashes mid-transfer, the partial change is rolled back. There is no "half-applied" state. This is enforced by the transaction log (WAL — Write-Ahead Log).
Consistency
Consistency means a transaction takes the database from one valid state to another. All constraints (primary keys, foreign keys, checks, triggers) must be satisfied after the transaction. If a transaction would violate a constraint, it is rolled back. Consistency is partly enforced by the database (constraints) and partly by the application (business rules).
Isolation
Isolation determines how concurrent transactions interact. The SQL standard defines four isolation levels:
- Read Uncommitted — can read uncommitted data (dirty reads); rarely used
- Read Committed — only sees committed data; default in PostgreSQL
- Repeatable Read — same query returns the same result within a transaction
- Serializable — transactions behave as if executed one at a time; strongest but slowest
Higher isolation means more safety but less concurrency and performance.
Durability
Durability means once a transaction is committed, its changes survive system crashes, power failures, and restarts. This is achieved by writing committed changes to non-volatile storage (disk or SSD) before acknowledging the commit. The write-ahead log (WAL) ensures that on restart, the database can replay committed transactions and undo uncommitted ones.
示例代码
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Read a product's stock
SELECT stock FROM products WHERE id = 42;
-- Another transaction cannot modify this row at
-- SERIALIZABLE level until we commit or rollback
-- Decrement stock and create an order
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
INSERT INTO orders (product_id, user_id, total)
VALUES (42, 1, 19.99);
COMMIT;
-- Check current isolation level (PostgreSQL)
SHOW transaction_isolation;
-- Set default isolation level for the session
SET SESSION CHARACTERISTICS AS TRANSACTION
ISOLATION LEVEL READ COMMITTED;
设置 SERIALIZABLE isolation for 关键读取-然后-编写事务, 检查 current level, and 设置会话默认.
动手试一试
- SQL Formatter
格式事务 isolation level 语句.
- HTTP 状态 Codes
API 响应经常 mirror 事务 outcomes — 200 对比 409.
常见错误
错误写法
Using 默认 isolation level for 关键读取-然后-编写模式
正确做法
读取 Committed (默认) 允许非-repeatable 读取. For 检查-然后-act 模式 (e.g. 检查 stock 然后 decrement), use REPEATABLE READ or SERIALIZABLE, or use SELECT ... FOR UPDATE.
错误写法
Assuming ACID 平均值否 concurrency issues
正确做法
ACID guarantees 每-事务 integrity, 但 concurrent 事务能 still conflict. 选择正确 isolation level and use explicit locks 当需要.
练习 ACID 属性
3 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。