sql - 理解sql中的事务

Question

关系 R(x) 由一组整数组成 --- 即具有整数分量的单分量元组。 Alice 的交易是一个查询:

SELECT SUM(x) FROM R;
COMMIT;

Betty 的交易是一系列插入:

INSERT INTO R VALUES(10);
INSERT INTO R VALUES(20);
INSERT INTO R VALUES(30);
COMMIT;

Carol 的事务是一系列删除操作:

DELETE FROM R WHERE x=30;
DELETE FROM R WHERE x=20;
COMMIT;

在任何这些交易执行之前，R 中的整数之和为 1000，并且这些整数都不是 10、20 或 30。如果 Alice、Betty 和 Carol 的交易几乎同时运行，并且每个在隔离级别 READ COMMITTED 下运行，哪些金额可以由 Alice 的交易产生？从下面的列表中找出其中一项。

a)1040b)950c)1030d)1080

这个问题让我有点困惑，因为没有任何信息告诉我们哪个事务先提交或者它们是否同时提交。你如何理解这个问题？

Answer 1

这个问题有两条规则:

• Read Committed隔离级别:在一个事务内部，每条SQL语句看到数据的新版本(新的“快照”)，包括另一个版本的更改并发事务可能刚刚提交。这意味着要回答这个问题，必须考虑每个事务中语句的相对顺序，而不仅仅是每个事务开始或结束的时间。

• 原子性:事务中的变化不被其他人看到事务直到它提交。脏读是不可能的关于 Transaction Isolation 的文档章节中提到的 PostgreSQL

让我们列举一下语句:

 AliceA1: SELECT SUM(x) FROM R;A2: COMMIT; BettyB1: INSERT INTO R VALUES(10);B2: INSERT INTO R VALUES(20);B3: INSERT INTO R VALUES(30);B4: COMMIT; CarolC1: DELETE FROM R WHERE x=30;C2: DELETE FROM R WHERE x=20;C3: COMMIT;

对于每个值 a)1040 b)950 c)1030 d)1080，您想要查找这些语句的执行顺序是否满足 SELECT SUM( x) 根据上述交易规则，A1 步骤中的 FROM R 恰好产生该值。

a=1040

1040 可以用这个执行顺序(以及其他)读取:

B1B2C1 -- DELETE WHERE x=30 does nothing because x=30 does not exist in R.B3 B4 -- now A1 would see 10,20,30 in R so SUM(x) is 1060 at this pointC2 -- DELETE WHERE x=20 does remove x=20 which exists and is visible by CC3 -- commits deletion of x=20A1 -- now A1 can see 10,30 so SUM(x)=1040A2 -- commit (no effect, A only reads)

编辑:有些人不相信这个结果是可能的，所以请在问题底部查看从 postgres session 复制粘贴的实际演示。

b=950

值 20,30 不在表的开头，所以如果 C1 和 C2 可以删除 30 和 20，那是因为 B2 和 B3 添加了它们，如果他们这样做了，B1 先添加了 10，所以结果至少是 960。在 C1 和 C2 由于尚未写入而无法删除 30 或 20 的情况下，结果 SUM(x) 必然大于 960。所以A1永远不可能看到小到950的SUM(x)，这是不可能的。

c=1030

要让 A1 读取 1030，它必须在表格中看到两行 (10)、(20) 或一行 (30)，不包括任何其他内容，因为其他组合的总和不等于 30。

由于原子性，B1,B2,B3 插入的 (10,20,30) 行将同时变为可见。 C1和C2分别可以撤销B3和B2的作用，但是如果C1找到并删除x=30，那么C2也必然会找到并删除x=20。A1 在 C3 运行之前看不到 C1 的结果，如果 C3 运行了，C2 也运行了，所以 30 和 20 从 A 的可见性中消失了，所以它只能看到 (10)，而不是 (10),(20) .单独看到 (30) 也是不可能的，因为如果 (30) 存在，那么 (10) 也会存在，因为 x=10 由 B1 插入并且之后从未删除。

所以 A1 永远看不到 SUM(x)=1030。

d=1080

1000+10+20+30 = 1060 并且代码不再添加任何行，因此无论执行顺序如何，都不可能获得超过 1060。

---

编辑:

使用 postgresql 9.5 的实际演示

# Init $ psql -d testtest=# create table R(x int);CREATE TABLEtest=# insert into R values(1000);INSERT 0 1test=# show transaction_isolation ; transaction_isolation ----------------------- read committed(1 row)test=# \q

** 启动 3 个不同的并发 session A、B、C，使用不同的提示来区分:

$ psql -d test\set PROMPT1 'alice%R%# 'alice=# $ psql -d test\set PROMPT1 'betty%R%# 'betty=# $ psql -d test\set PROMPT1 'carol%R%# 'carol=# 

现在让我们按照提到的顺序运行命令，哪个 session 执行的操作在提示中显而易见。

alice=# begin;BEGINbetty=# begin;BEGINcarol=# begin;BEGINbetty=# INSERT INTO R VALUES(10);INSERT 0 1betty=# INSERT INTO R VALUES(20);INSERT 0 1carol=# DELETE FROM R WHERE x=30;DELETE 0betty=# INSERT INTO R VALUES(30);INSERT 0 1betty=# commit;COMMITcarol=# DELETE FROM R WHERE x=20;DELETE 1carol=# commit;COMMITalice=# select sum(x) from r; sum  ------ 1040(1 row)alice=# commit;COMMIT