为什么PostgreSQL可序列化事务将其视为冲突？ [英] Why does PostgreSQL serializable transaction think this as conflict?
问题描述
据我所知，PostgreSQL使用某种监视器来猜测可序列化隔离级别是否存在冲突。许多示例都涉及在并发事务中修改相同的资源，而可序列化的事务效果很好。但是我想用另一种方式来测试并发问题。
In my understanding PostgreSQL use some kind of monitors to guess if there's a conflict in serializable isolation level. Many examples are about modifying same resource in concurrent transaction, and serializable transaction works great. But I want to test concurrent issue in another way.
我决定测试2位用户修改自己的帐户余额，并希望PostgreSQL足够聪明以至于不会将其检测为冲突，但结果却不是我想要的。
I decide to test 2 users modifying their own account balance, and wish PostgreSQL is smart enough to not detect it as conflict, but the result is not what I want.
下面是我的表格，有4个帐户属于2个用户，每个用户都有一个支票帐户和一个储蓄帐户
Below is my table, there're 4 accounts which belongs to 2 users, each user has a checking account and a saving account.
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，则提交，否则进行回滚。
Now I run 2 concurrent transaction for 2 users. In each transaction, I reduce the checking account with some money, and check that user's total balance. If it's greater than 1000, then commit, otherwise rollback.
用户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相同，除了 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的事务时，它失败。
I first commit user 1's transaction, it success with no doubt. When I commit user 2's transaction, it fails.
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.
我的问题是：
- 为什么PostgreSQL认为这2个事务是冲突的？我为所有SQL添加了user_id条件，并且没有修改user_id，但是所有这些都没有效果。
- 这是否意味着可序列化事务不允许在同一张表上发生并发事务，即使他们的读/写没有冲突？
- 每个用户有什么常识吗，我应该避免对经常发生的操作使用可序列化事务吗？
推荐答案
您可以使用以下索引解决此问题：
You can fix this problem with the following index:
CREATE INDEX accounts_user_idx ON accounts(user_id);
由于示例表中的数据很少，因此您必须告诉PostgreSQL使用索引扫描：
Since there are so few data in your example table, you will have to tell PostgreSQL to use an index scan:
SET enable_seqscan=off;
现在您的示例将起作用！
Now your example will work!
如果这似乎是不可思议的事情，请查看您的 SELECT 和 UPDATE 语句的查询执行计划。
If that seems like black magic, take a look at the query execution plans of your SELECT and UPDATE statements.
没有索引的两个都将对表使用顺序扫描，从而读取表中的所有行。因此，这两个事务在整个表上都将以 SIReadLock 结尾。
Without the index both will use a sequential scan on the table, thereby reading all rows in the table. So both transactions will end up with a SIReadLock on the whole table.
这将触发序列化失败。
这篇关于为什么PostgreSQL可序列化事务将其视为冲突？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
