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?

Exhale

You might want to ask this on the Postgres mailing list – Prater

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.

Proper

So the key point is to avoid full table scan, and index is not triggered if the table data is too few, is my understanding correct? – Exhale

Answer 2

To my knowledge serializable has the highest level of isolation, therefore lowest level of concurrency. The transactions occur one after the other with zero concurrency.

Hooker

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. --> not sure why you have this conflict buddy, maybe when it is looking for records since it has a common field of type 'checking' could be a reason. did you try one with 'savings' and one with 'checking' – Hooker

I tried it, still conflict. Actually it's conflict even when all conditions are reduced to where user_id = x . – Exhale