数据库Transaction

DB Transaction

transaction的isolation level

不隔离可能出现的几个问题：

dirty read 脏读： A transaction reads data written by a concurrent uncommitted transaction
nonrepeatable read 不可重复读：在事务A重新读取数据D的时候，发现数据D被其他事务修改了
phantom read 幻读：事务A重新执行查询的过程中，发现查询结果因为其他事务修改了
serialization anomaly 序列化异常：提交一组事务的结果和任何一种一一提交都不同（例如， Ta修改O1, O2为1， Tb修改O1, O2为1，得到O1=2，O2=1）

Isolation Level 隔离级别	Dirty Read 脏读	Nonrepeatable Read 不可重复读	Phantom Read 幻读	Serialization Anomaly 序列化异常
Read uncommitted 未提交的读	Allowed, but not in PG 允许，但在 PG 中不允许	Possible 可能的	Possible 可能的	Possible 可能的
Read committed 读已提交	Not possible 不可能	Possible 可能的	Possible 可能的	Possible 可能的
Repeatable read 可重复读取	Not possible 不可能	Not possible 不可能	Allowed, but not in PG 允许，但在 PG 中不允许	Possible 可能的
Serializable 可串行化	Not possible 不可能	Not possible 不可能	Not possible 不可能	Not possible 不可能

一些PostgreSQL数据类型和函数对于事务行为有特殊规则。特别是，对序列（以及使用serial声明的列的计数器）所做的更改对所有其他事务立即可见，并且如果进行更改的事务中止，则不会回滚。请参见第 9.17 节和第 8.1.4 节。

pg“没有” read-uncommitted，其mvcc最低的要求是read-committed

read commit: 默认隔离级别，看得到：

查询开始前提交的数据
自身事务的所有数据（即使未提交）
其他事务已经提交的数据

update, delete 等

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.

在read-commit的并发控制下，一边修改，另一边并发更改的事务提交，修改可能会丢失或达成意外的效果

核心在于这样的并发事务修改之后，只能看到和重新执行自己那一行，而不是整个数据库

Because of the above rules, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions;

requires new开新事物的时候要注意外键关联和之前的事务提交时间（注意提交模式）

mvcc详细理解见小林coding

undo log + readview = mvcc

为什么需要undo log: 事务支持, 回滚支持

redo log: 持久化, 崩溃恢复支持(数据的持久化 + undo log的持久化)

数据更新先写buffer pool再落盘, 故先写redo log再存到buffer pool防丢失

事务提交前宕机: 无影响

事务执行错误: undo log回滚

事务提交后刷盘前崩溃：redo log回滚

崩溃恢复依赖于redo log, 但redo log也不是一直写, 根据redo log配置的刷盘策略（每秒存文件(page cache), 每秒刷盘, 每个事务刷盘三种）

DB Transaction​

DB Transaction