Anomalies in database

Posted on In Views: Disqus:

之前学习过各种异象，总是记乱，整理成文档备份

为了更好理解相关异象，做如下数据准备：

1
2
3
4
5
6
7
CREATE TABLE accounts(
	id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
	client text,
	amount numeric
);

INSERT INTO accounts VALUES(1, 'alice', 1000.00), (2, 'bob', 100.00), (3, 'bob', 900.00);

Lost Update

当两个事务读取同一表行，然后其中一个事务更新该行，最后另一个事务更新同一行而不考虑第一个事务所做的任何更改时，就会发生丢失更新异常。

Dirty Reads

当一个事务读到了其他事务尚未尚未提交的更改时，就会发生脏读异常

Phantom Reads anomaly

当同一个事务执行两个相同的查询返回一组满足特定条件的行，而另一个事务添加一些满足该条件的其他行并在这些查询之间的时间间隔内提交更改时，就会发生幻读异常。

Read skew

假设我们要在bob的账户之间转账
Transaction1

1
2
3
4
5
woodhead=# BEGIN;
BEGIN
woodhead=*# UPDATE accounts SET amount = amount - 100 WHERE id = 2;
UPDATE 1
woodhead=*#

与此同时，另一个事务2 开始循环bob的账户计算bob的账户结余（金额）
Transaction2

1
2
3
4
5
6
7
woodhead=# BEGIN;
BEGIN
woodhead=*# SELECT amount FROM accounts WHERE id = 2;
 amount 
--------
 100.00
(1 row)

这个时候，事务1提交成功：

1
2
3
4
woodhead=*# UPDATE accounts SET amount = amount + 100 WHERE id = 3;
UPDATE 1
woodhead=*# commit;
COMMIT

事务2 开始读取第二个账户（读已提交的隔离状态下会读取到已提交的数据）

1
2
3
4
5
6
7
woodhead=*# SELECT amount FROM accounts WHERE id = 3;
 amount  
---------
 1000.00
(1 row)
woodhead=*# commit;
COMMIT

最终结果：事务2读取到了1100块钱，因为它读到了不正确的数据，这种异常叫做读偏序

Write skew

我们定义如下一致性规则：只要总余额非负，就允许客户的部分账户出现负余额。

事务1获取总额

1
2
3
4
5
6
7
8
9
10
11
12
13
14
woodhead=# select * from accounts;
 id | client | amount  
----+--------+---------
  1 | alice  | 1000.00
  2 | bob    |     200
  3 | bob    |     700
(3 rows)
woodhead=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
woodhead=*# SELECT sum(amount) FROM accounts WHERE client = 'bob';
 sum 
-----
 900
(1 row)

事务2同样获取总额

1
2
3
4
5
6
7
woodhead=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
woodhead=*# SELECT sum(amount) FROM accounts WHERE client = 'bob';
 sum 
-----
 900
(1 row)

第一个事务合理地假设，它可以从某个账户扣除 600 元。

1
2
woodhead=*# UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;
UPDATE 1

第二个事务得到同样的结论，但是从另一个账户扣除：

1
2
3
4
woodhead=*# UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
UPDATE 1
woodhead=*# commit;
COMMIT

事务1也提交，然后查询：

1
2
3
4
5
6
7
8
woodhead=*# commit;
COMMIT
woodhead=# SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount  
----+--------+---------
  2 | bob    | -400.00
  3 | bob    |  100.00
(2 rows)

现在bob的总余额是负的，虽然每个事务单独运行都是正确的。这种异常叫写偏序