Why does PostgreSQL serializable transaction think this as conflict?

Question

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.

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.

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);

The table data is like this:

 id | user_id |   type   | balance
----+---------+----------+---------
  1 |       1 | checking |    1000
  2 |       1 | saving   |    1000
  3 |       2 | checking |    1000
  4 |       2 | saving   |    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.

The user 1's example:

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;

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;

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.

My questions are:

1. Why PostgreSQL thinks this 2 transactions are conflict? I added user_id condition for all SQL, and doesn't modify user_id, but all these have no effect.
2. Does that mean serializable transaction doesn't allow concurrent transactions happened on the same table, even if their read/write have no conflict?
3. Do something per user is very common, Should I avoid use serializable transaction for operations which happen very frequently?

Answer 1

You can fix this problem with the following index:

CREATE INDEX accounts_user_idx ON accounts(user_id);

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!

If that seems like black magic, take a look at the query execution plans of your SELECT and UPDATE statements.

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.

This triggers the serialization failure.