为什么 PostgreSQL 可序列化事务认为这是冲突? | 可序列化事务认为这是冲突

本文介绍了为什么 PostgreSQL 可序列化事务认为这是冲突?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

据我所知，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是一样的，除了where中的user_id = 2:

The user 2 is the same, except the user_id = 2 in where:

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 可序列化事务认为这是冲突?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！