atomically compare and update multiple rows using postgres transaction with maximum concurrency

Question

Have tried researching the existing questions, but did not find posts with a definitive answer.

What we want to implement is to atomically update the value of two rows in one postgres table. we would like to implement logic similar to the blow. We can capture the whole logic in one transaction and set the transaction level to serializable to guarantee atomicity. My understand is that that will have the database to lock the whole table, and reduce concurrency and throughput. What is the best practice for increasing concurrency while gauaranteeing atomicity?

 create table accounts (
     id  int,
     remain double
}

remain = account1.remain
if val <= remain:   
    account1.remain = account1.remain - val
    account2.remain = account2.remain + val

serializable will not "lock the whole table"

user330315
– user330315

2018-07-22 07:34:17 +00:00
Commented Jul 22, 2018 at 7:34 — user330315
– user330315, Commented Jul 22, 2018 at 7:34

krokodilko · Accepted Answer · 2018-07-22 16:01:14Z

1

The simplest way is to use a single UPDATE statement, because each individual SQL statement is always atomic.

An example - let say that:

account1_id = 222
account2_id = 333
val = 567

then the update might look like this:

UPDATE accounts
SET remain = remain + 567 * CASE id
         WHEN 222 THEN -1
         WHEN 333 THEN 1
    END
WHERE id IN ( 222,333 )
  AND 567 >= (
     SELECT remain FROM accounts
     WHERE id = 222
);
COMMIT;

this statement will lock only two rows 222 and 333 (which must be locked in order to be updated), leaving the rest of rows in the table in unlocked state, so the concurency will be the highest.

edited Jul 22, 2018 at 16:01

answered Jul 22, 2018 at 15:36

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yuyang Over a year ago

thanks for the answer! i think that read committed isolation should be sufficient for this query. is my understanding correct?

Collectives™ on Stack Overflow

atomically compare and update multiple rows using postgres transaction with maximum concurrency

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related