目錄

Why Transactions?

Example: Bad Interaction

Transactions

ACID Transactions

COMMIT

ROLLBACK

How the Transaction Log Works

How Data Is Stored?

Example: Interacting Processes

Interleaving of Statements

Example: Strange Interleaving

Fixing the Problem by Using Transactions

Another Problem: Rollback

Isolation Levels

---

Why Transactions?

• Database systems are normally being accessed by many users or processes at the same time. Both queries and modifications.

• Unlike operating systems, which support interaction of processes, a DMBS needs to keep processes from troublesome interactions.（不像操系統一樣支持不同進程之間的交互 ，數據庫管理系統需要阻止進程之間的有害交互）

Example: Bad Interaction

• You and your domestic partner each take $100 from different ATM’s at about the same time.

• The DBMS better make sure one account deduction doesn’t get lost.（數據庫管理系統需要保證多進程的交互不會造成數據的丟失）

Compare: An OS allows two people to edit a document at the same time. If both write, one’s changes get lost.

Transactions

• Transaction = process involving database queries and/or modification.（數據包括了數據修改和數據查詢的過程）

• Normally with some strong properties regarding concurrency.（事務具有四大核心特性AICD）

• Formed in SQL from single statements or explicit programmer control.

ACID Transactions

• Atomic : Whole transaction or none is done.（原子性事務要么全部執行要么全部不執行）

• Consistent : Database constraints preserved.（一致性，數據庫約束始終保持）

• Isolated : It appears to the user as if only one process executes at a time.（隔離性，對于用戶來說，同一時間只有一個進程在運行）

• Durable : Effects of a process survive a crash.（持久性，在系統崩潰后事務依舊存在）

Optional: weaker forms of transactions are often supported as well.

COMMIT

• The SQL statement COMMIT causes a transaction to complete.（commit將會使得事務操作被完成）

• It’s database modifications are now permanent in the database.（提交之后事務操作對于數據庫的修改才能永久的保存保存到數據庫中）

ROLLBACK

• The SQL statement ROLLBACK also causes the transaction to end, but by aborting.（ROLBACK同樣是會使得事務結束，但是是強制中斷）

• No effects on the database.（對于數據庫沒有影響）

• Failures like division by 0 or a constraint violation can also cause rollback, even if the programmer does not request it.（部分錯誤出現時，即使程序沒有主動要求，也會進行數據回滾）

How the Transaction Log Works

• 應用程序發出修改請求
• 定位數據頁或者讀入緩沖池并且修改
• 修改被記錄在磁盤的事務日志上
• 檢查點將事務提交到數據庫中

How Data Is Stored?

數據文件和日志文件共同支撐數據庫的持久性?

Example: Interacting Processes

• Assume the usual Sells(bar,beer,price) relation, and suppose that Joe’s Bar sells only Bud for $2.50 and Miller for $3.00.

• Sally is querying Sells for the highest and lowest price Joe charges.

• Joe decides to stop selling Bud and Miller, but to sell only Heineken at $3.50.

Sally executes the following two SQL statements called (min) and (max) to help us remember what they do.

(max) SELECT MAX(price) FROM Sells WHERE bar = ’Joe’’s Bar’;

(min) SELECT MIN(price) FROM Sells WHERE bar = ’Joe’’s Bar’;

At about the same time, Joe executes the following steps: (del) and (ins).

(del) DELETE FROM Sells WHERE bar = ’Joe’’s Bar’;

(ins) INSERT INTO Sells VALUES(’Joe’’s Bar’ , ’Heineken’ , 3.50);

Interleaving of Statements

Although (max) must come before (min), and (del) must come before (ins), there are no other constraints on the order of these statements, unless we group Sally’s and/or Joe’s statements into transactions.

即使我們保證max查詢一定在min查詢之前，del一定在ins之前，但是除了這些限制，語句的執行順序可以是任意的，除非是將Sally和Joe的操作進行事務分組

Example: Strange Interleaving

Fixing the Problem by Using Transactions

• If we group Sally’s statements (max)(min) into one transaction, then she cannot see this inconsistency.

• She sees Joe’s prices at some fixed time.

• Either before or after he changes prices, or in the middle, but the MAX and MIN are computed from the same prices.?

如果將兩個人的操作分成兩組事務來進行，那么在執行max、min操作時，Sally看到的數據就一定是基于同一組數據的結果?

Another Problem: Rollback

• Suppose Joe executes (del)(ins), not as a transaction, but after executing these statements, thinks better of it and issues a ROLLBACK statement.

• If Sally executes her statements after (ins) but before the rollback, she sees a value, 3.50, that never existed in the database.

這個現象就是臟讀，也就是說：Joe在插入一個3.5數據之后并沒有立即commit，Joe反悔了想回滾操作，但是此時Sally執行了查詢，查出了3.5，然后回滾繼續，就會導致Sally讀出了一個數據庫中本來就不存在的數據。

Isolation Levels

• SQL defines four isolation levels = choices about what interactions are allowed by transactions that execute at about the same time.（隔離機制決定了事物之間允許那些交互）

• Only one level (“serializable”) = ACID transactions.（只有一種隔離機制可串行化滿足了ADCI的特性）

• Each DBMS implements transactions in its own way（每一種數據庫管理系統都有自己的方式來實現事物的隔離機制）

隔離級別	允許臟讀	允許不可重復讀	允許幻讀
讀未提交（Read Uncommitted）	?	?	?
讀已提交（Read Committed）	?	?	?
可重復讀（Repeatable Read）	?	?	?
可串行化（Serializable）	?	?	?