Mercy

Posted on Jun 13

[🗄️DataBase] Database Transactions 底層到底做了什麼：從記憶體到磁碟

#architecture #computerscience #database #tutorial

1. 為什麼要理解這件事

不管用哪個資料庫、哪種語言，你每天都在做類似的事

# Python + psycopg2 (PostgreSQL)
cur.execute("INSERT INTO roles (name) VALUES (%s)", ("admin",))
conn.commit()

// C# + EF Core
_db.Roles.Add(role);
await _db.SaveChangesAsync();
await tx.CommitAsync();

// Node.js + pg (PostgreSQL)
await client.query("INSERT INTO roles (name) VALUES ($1)", ["admin"]);
await client.query("COMMIT");

但你真的知道這些 API 背後發生了什麼事嗎？

ExecuteNonQuery() / SaveChangesAsync() 執行後，資料到底在哪？
什麼叫「在記憶體裡」？
Flush 和 Commit 有什麼差別？
Transaction 是怎麼保護你的資料的？

這篇文章會從 CPU 如何存取資料開始，一步步講到資料庫引擎怎麼保證你的資料不遺失。

2. 電腦儲存層級：從 Register 到 SSD

2.1 CPU Register（暫存器）

CPU 晶片內部有幾十個暫存器，每個 64-bit（以現代 x64 CPU 來說）。資料要被運算（加減乘除、比對大小），一定要先載到暫存器。

mov rax, [memory_address]   ; 從 RAM 搬到 CPU 暫存器
add rax, 1                  ; 在 CPU 裡面加 1
mov [memory_address], rax   ; 存回 RAM

前面例子寫的 role.name = "新名稱"，編譯器編譯後最終會變成好幾條這種機器指令，把值從一個 RAM 位址搬到 CPU，改完再搬回去。

屬性	值
容量	~幾十 bytes
速度	~0.3 ns
斷電是否消失	是

2.2 RAM（主記憶體）

RAM（Random Access Memory）是電容 + 電晶體做成的儲存陣列，每個 cell 儲存 1 bit 的電荷。優點是讀寫極快，缺點是斷電就全部消失。

256 GB RAM 大概有 2 兆個這樣的 cell。

你的變數全部存在這裡。
當你修改一個變數，你修改的是 RAM 中某個位址上的 byte。

屬性	值
容量	~GB ~ TB
速度	~50-100 ns
斷電是否消失	是

2.3 SSD / HDD（硬碟）

硬碟是最終儲存資料的地方。HDD 用磁碟片，SSD 用 NAND Flash 晶片。

屬性	HDD	SSD
速度	~5-10 ms	~10-100 μs
比 RAM 慢	~100,000 倍	~1,000 倍
斷電是否消失	否	否

3. 資料庫引擎核心架構：Page Cache、WAL、Checkpoint

不管哪個資料庫，設計目標都一樣：提供 ACID 保證，同時要有夠好的效能。

直接讀寫磁碟太慢了（比 RAM 慢 1000~100000 倍），所以所有資料庫都有一個核心機制：

你的程式 (Application)                    Database Engine
┌─────────────────────────┐        ┌──────────────────────────────────┐
│  Application Layer      │  SQL   │         Page Cache (RAM)          │
│  ┌───────────────────┐  │ ─────→ │  ┌──────┬──────┬──────┬───────┐  │
│  │ 資料 (物件/row)    │  │        │  │Page 1│Page 2│Page 3│ ...   │  │
│  │ SQL 語句           │  │        │  │ dirty│ clean│ dirty│       │  │
│  └───────────────────┘  │        │  └──┬───┴──────┴──┬───┴───────┘  │
│                         │        │     │             │               │
│                         │        │     ↓             ↓               │
│                         │        │  Background    Background         │
│                         │        │  Writer        Checkpoint         │
│                         │        │     │             │               │
└─────────────────────────┘        │     ↓             ↓               │
                                    │  Write-Ahead Log  Data Files     │
                                    │  (WAL / Redo Log / Tx Log)       │
                                    │  (磁碟)           (磁碟)          │
                                    └──────────────────────────────────┘

假設你寫了一行：

UPDATE users SET name = 'Alice' WHERE id = 1;

你的程式把這行 SQL 丟給資料庫引擎。然後呢？

第一站：Application RAM（你的程式記憶體）

當你的程式執行：

var role = new Role { Name = "管理員" };
db.Roles.Add(role);

這筆資料只在你程式的記憶體裡（.NET managed heap / Python heap / 等等），還沒透過任何 API 送出去。

這時如果斷電，這筆資料一定消失。

第二站：Flush（送出 SQL）

db.SaveChangesAsync();          // EF Core
// 或
cmd.ExecuteNonQuery();          // ADO.NET
// 或
cur.execute("INSERT ...");     // psycopg2 (Python)

這步做了：

應用程式把 SQL 或資料透過 TCP/IP（或共用記憶體）送到 Database Engine
Database Engine 開始接手處理
資料正式離開你的行程記憶體，進入資料庫的管轄範圍

第三站：Page Cache（Buffer Pool）

資料庫不直接讀寫磁碟，原因很簡單，因為磁碟太慢了。
所以它在啟動時會跟 OS 要一大塊 RAM，叫 Page Cache。
每個資料庫叫法不同，做的事一模一樣：

資料庫	Page Cache 名稱	位置
PostgreSQL	shared_buffers	資料庫自己的記憶體
MySQL/InnoDB	innodb_buffer_pool	資料庫自己的記憶體
SQL Server	Buffer Pool	資料庫自己的記憶體
SQLite	Page Cache	行程內記憶體

資料庫收到你的 UPDATE 後，第一步是找到那筆資料所在的 Page：

先在 Page Cache 找有沒有這個 Page
有 → 直接拿來用（Cache Hit）
沒有 → 從磁碟載入 Page Cache（Cache Miss）

然後直接在這個 Page 上修改，再把該 Page 標記為 Dirty Page（跟磁碟版本不一致）。

但 Dirty Page 不能一直待在記憶體裡，不然機器斷電資料就不見了。

第四站：WAL（Write-Ahead Log）

所以資料庫做了一件非常重要的事，
在把 Dirty Page 寫回磁碟的資料檔之前，先把修改紀錄寫到 WAL。

流程有四步：

1. 收到 UPDATE / INSERT / DELETE SQL
2. 在 Page Cache 修改對應的 Page（Dirty）
3. 把「我做了什麼修改」寫到 Transaction Log（磁碟）
4. 回傳給用戶「完成了」

WAL 是順序寫入（sequential write），速度快。
它只是 append-only 的日誌，不會回頭修改舊紀錄。

一旦 WAL 寫完，這筆修改就算安全了，就算下一秒機器崩潰或是斷電，重啟時只要 replay WAL，就能復原到你剛修改完的狀態。

這時候資料檔可能還沒有被更新，但 Transaction Log 已經寫了。如果斷電，資料庫重新啟動時會讀 Transaction Log：

有 COMMIT 記錄 → Redo：把還沒寫回磁碟的修改重新套用
沒有 COMMIT 記錄 → Undo：把已做的修改還原

第五站：Commit

await tx.CommitAsync();       // EF Core
// 或
tx.Commit();                   // ADO.NET
// 或
conn.commit();                 // Python DB-API
// 或
COMMIT;                        // CLI

這行在 Transaction Log 中寫入一筆 COMMIT 記錄。

從這一刻起：

如果斷電，資料庫 Recovery 會把這筆資料還原（Redo）
其他連線可以看到這筆資料（隔離級別決定何時看到）

Flush vs Commit：差別在哪

用生活比喻，想像你是一個廚師，要做一道菜給客人：

動作	對應的程式操作	比喻
備料切菜	建立物件 / 組裝 SQL	在流理台上準備材料
下鍋煮	Flush（送出 SQL / SaveChanges）	煮好了，放在廚房檯面上
決定上菜	Commit	端出去給客人吃
覺得太鹹倒掉	Rollback	倒掉重做，反正客人沒看到

Flush: 把菜做好放在廚房檯面上，菜已經熟了（資料庫已執行），但還沒端出去（還沒 Commit），客人看不到，萬一覺得不好吃還可以倒掉（Rollback）。

Commit: 端出去給客人吃，一旦端出去了就來不及了（永久的）。

第六站：Background Writer

Dirty Page 還是要寫回資料檔的，但資料檔是隨機寫入（random write），慢很多。
如果一次大量寫，使用者的查詢就會被卡住。

所以 Background Writer 在後台默默、慢慢地把 Dirty Page 刷回磁碟的資料檔。每次只刷一點點，不搶頻寬，讓使用者感覺不到它的存在。

第七站：Checkpoint

Background Writer 平常一直在刷，但總有一些 Page 它來不及刷，或累積太多 Dirty Page。

Checkpoint 的工作就是定時強制把所有髒 Page 刷回磁碟。
做完之後告訴 WAL：「這之前的日誌都已經同步了，可以砍掉了。」

這樣 WAL 不會無限膨脹，也縮短了崩潰後 recovery 的時間，只需要 replay checkpoint 之後的 WAL 就夠了。

崩潰怎麼辦：Recovery

機器永遠會 crash，這時就需要 ARIES 演算法來救。

ARIES（Algorithm for Recovery and Isolation Exploiting Semantics）是資料庫崩潰後如何自動救回來的標準流程，所有現代關聯式資料庫（InnoDB、SQL Server、PostgreSQL、Oracle）都在用：

Analysis — 掃 WAL，查出哪些 Page 是髒的、哪些交易還沒結束
Redo — 從上一個 checkpoint 開始，把所有修改重新做一遍（不管有沒有寫回資料檔），確保資料檔跟 crash 瞬間一致
Undo — 把 crash 時還沒 commit 的交易全部還原

不管你 crash 前做過什麼，redo 全部重做一次，再把沒做完的 undo 掉，結果就是對的。

之所以叫 ARIES，是因為它利用 WAL 裡的語義資訊（知道每筆修改對應哪個 page、哪個 transaction），不需要猜測。

總結流程：

先寫 WAL（按順序寫，保證崩潰安全）→ Background Worker 慢慢刷 Dirty Page 回資料檔（隨機寫）→ Checkpoint 定時總整理、截斷 WAL

4. 實際範例

用一個典型的業務情境：建立一個新角色，同步身分認證資料，記錄稽核日誌。

4.1 沒有 Transaction

# Python pseudo code
db.roles.insert({"name": "admin"})
# 隱含 AUTO COMMIT

ensure_identity_role("admin")
# 內部也有自己的 AUTO COMMIT

audit_service.log("RoleCreated", "admin")
# 隱含 AUTO COMMIT

資料流向：

第 1 次 flush →  DB Page Cache: ✅ INSERT INTO roles
                 Transaction Log:  ✅ INSERT + AUTO COMMIT
                 → 已「永久」

第 2 次 flush →  DB Page Cache: ✅ INSERT INTO identity_roles
                 Transaction Log:  ✅ INSERT + AUTO COMMIT
                 → 又「永久」

第 3 次 flush →  DB Page Cache: ✅ INSERT INTO audit_logs
                 Transaction Log:  ✅ INSERT + AUTO COMMIT
                 → 又「永久」

如果第 1 次 flush 後、第 2 次 flush 前斷電：

資料表 (Table)	DB Recovery 後的狀態	關鍵原因說明
`roles`	存在	第一次 Flush 時，觸發 AUTO COMMIT 永久落地
`identity_roles`	不存在	寫入前系統已崩潰，Transaction Log 無此記錄
`audit_logs`	不存在	寫入前系統已崩潰，Transaction Log 無此記錄

資料庫處於不一致狀態，而且無法恢復。

4.2 有 Transaction

with db.begin_transaction() as tx:
    db.roles.insert({"name": "admin"})
    ensure_identity_role("admin")
    audit_service.log("RoleCreated", "admin")
    tx.commit()

資料流向：

執行動作 / 程式碼	DB Page Cache 狀態	Transaction Log 狀態	結果說明
`db.begin_transaction()`	無變更	BEGIN TRAN	交易正式開始，開啟安全保護傘
`roles.insert()`	INSERT roles	INSERT (無 COMMIT)	資料暫存於快取，尚未真正生效
`ensure_identity_role()`	INSERT identity_roles	INSERT (無 COMMIT)	資料暫存於快取，尚未真正生效
`audit_service.log()`	INSERT audit_logs	INSERT (無 COMMIT)	資料暫存於快取，尚未真正生效
`tx.commit()`	保持不變 (等待 Checkpoint)	COMMIT	日誌強制落地，三筆資料同時正式生效

如果 commit 前斷電：

DB Recovery 後：
  Transaction Log 中沒有 COMMIT 記錄
  → 全部三筆 INSERT 都被 Rollback（Undo）
  → 資料庫像沒發生過一樣

重新執行一次即可，不會有任何殘留資料。

5. 總結

資料從應用程式到磁碟，並不是一步到位

它會先存在 Application RAM，接著透過 Flush 送進 Database Engine，在 Page Cache 中修改資料頁，並寫入 WAL / Transaction Log。

只有當 Transaction Log 中出現 COMMIT 記錄時，這筆交易才正式成立。

至於 Dirty Page 什麼時候真正寫回 Data Files，通常是之後由 Checkpoint 或 背景程序 完成。

整個流程為：

Application RAM → Flush → Page Cache + WAL → Commit → Checkpoint → Data Files

DEV Community