为什么可序列化交易将其视为冲突

Question

在我的理解中，PostgreSQL使用某种监视器来猜测是否存在可序列化隔离级别的冲突。许多示例是关于在并发交易中修改相同的资源，并且可序列化的交易效果很好。但是我想以另一种方式测试并发问题。

我决定测试2个用户修改自己的帐户余额，并希望PostgreSQL足够聪明，以至于不将其视为冲突，但结果不是我想要的。

下面是我的表格，有4个属于2个用户的帐户，每个用户都有一个支票帐户和一个保存帐户。

create table accounts (
  id serial primary key,
  user_id int,
  type varchar,
  balance numeric
);
insert into accounts (user_id, type, balance) values
  (1, 'checking', 1000),
  (1, 'saving', 1000),
  (2, 'checking', 1000),
  (2, 'saving', 1000);

表数据是这样的：

 id | user_id |   type   | balance
----+---------+----------+---------
  1 |       1 | checking |    1000
  2 |       1 | saving   |    1000
  3 |       2 | checking |    1000
  4 |       2 | saving   |    1000

现在，我为2个用户运行2次并发交易。在每笔交易中，我都会用一些钱减少支票帐户，并检查用户的总余额。如果大于1000，则提交，否则回滚。

用户1的示例：

begin;
-- Reduce checking account for user 1
update accounts set balance = balance - 200 where user_id = 1 and type = 'checking';
-- Make sure user 1's total balance > 1000, then commit
select sum(balance) from accounts where user_id = 1;
commit;

用户2是相同的，除了where中的user_id = 2：

begin;
update accounts set balance = balance - 200 where user_id = 2 and type = 'checking';
select sum(balance) from accounts where user_id = 2;
commit;

我第一次提交用户1的交易，毫无疑问。当我提交用户2的交易时，它会失败。

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

我的问题是：

1. 为什么PostgreSQL认为这两项交易是冲突？我为所有SQL添加了USER_ID条件，并且没有修改User_id，但所有这些都没有效果。
2. 这是否意味着可序列化交易不允许在同一表上发生并发交易，即使他们的读/写没有冲突？
3. 每个用户做的事情很常见，我是否应该避免使用经常发生的操作的可序列交易？

Answer 1

您可以使用以下索引来解决此问题：

CREATE INDEX accounts_user_idx ON accounts(user_id);

由于您的示例表中的数据很少，因此您必须告诉PostgreSQL使用索引扫描：

SET enable_seqscan=off;

现在您的示例将起作用！

如果看起来像黑魔法，请查看您的SELECT和UPDATE语句的查询执行计划。

没有索引，两者都将在表上使用顺序扫描，从而读取表中的所有行。因此，两项交易最终都将在整个桌子上都有SIReadLock。

这触发了序列化失败。

Answer 2

我的知识序列化具有最高的隔离水平，因此最低的并发级别。交易发生一次，同时发生零。