Transaction
数据库往往要有如下要求
- Concurrent database access
Execute sequence of SQL statements so they appear to be running in isolation
But want to enable concurrency whenever safe to do so - Resilience to system failure
Guarantee all-or-noting execution, regardless of failures
Transaction
为了满足上述要求而存在，是把一些 SQL 语句看做一个单元
- 看起来事务运行起来互相独立
- 事务能保证它的影响 all-or-noting
SQL standard:
Transaction begins automatically on first SQL statement
On "commit" transaction ends and new one begins
Current transaction ends on session termination
"Autocommit" turns each statement into transaction
Transaction Property (ACID)
- Atomicity
- Consistency
- Isolation
- Durability
Durability
只要事务提交，即使在提交后的那一瞬间系统崩溃，也能保证事务的效果留在数据库中
内部实现是用了 Logging
Atomicity
与 Durability 对应，在提交前系统奔溃，保证事务效果没有留在数据库中
内部实现是用 Logging 进行 undo
Consistency
保证事务开始和结束时所有的 constraint 成立
Isolation
要分隔离等级，隔离等级越低，性能越好，但相对的对 consistency 的保证就越低
注意每种隔离等级针对某一事务范围而言
isolation level.jpg
Serializable:
默认是这种
在这种情况下，每个事务执行前都会上锁，结束后释放锁，因此所有事务执行的效果跟按某种（具体哪一种不确定，因为你不知道哪个会上锁）顺序执行的效果一样。
注意只能保证事务的独立而不能保证执行顺序
Serializable 1.jpg
Serializable 2.jpg
Read Uncommitted
Read Uncommitted.jpg
Read Committed
Read Committed.jpg
Repeatable Read
Repeatable Read 1.jpg
Repeatable Read 2.jpg
Logging
Record UNDO information for every update!
- Sequential writes to log
- Minimal info (diff) written to log
The log consists of an ordered list of actions
Log record contains: <XID, location, old data, new data>
Write-ahead Logging (WAL) Commit Protocol
log 会记录每次更新信息，只有 log 存储到磁盘后，才能把数据写入到数据库
