SQL Transactions（事务）、隔离机制

目录

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’s Program

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’;

Joe’s Program

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）	❌	❌	❌