Question 90615 in PostgreSQL Question posted in
The official documentation can be found here.

Relational databases guaranties when transfering money between accounts – Postgresql

Let’s image we have a database table:

account
id | balance

There is only one operation of moving money from one account to another. Like this:

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

From application, we can initiate transfer from Account1 to Account2 and from Account2 to Account1 in parallel.

I’m trying to understand Isolation level guarantees. As I see there is no case when Non-Repeatable problem can occur as there is not two reads from same row inside one transaction. So I just should care of Dirty Read scenario and Read Commited Isolation level gives me enough guarantees that my Application is going to work correctly?

I don’t even need to have any pessimistic lock to support only this case, am I thinking right?
if it is not, could you give me an example when my guarantees are violated?

Tags:

2

Answers


  1. 0

    In this scenario you can lock the table untill the transation is finished

    BEGIN
SELECT * FROM account  WHERE  account_id IN(1,2) FOR UPDATE;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
END ;

    Also is possible to lock a entire table and even specify a condition:

    LOCK TABLE account IN ACCESS EXCLUSIVE MODE;

    Login or Signup to reply.
  2. 0

    Yes, you are right. The default READ COMMITTED isolation level guarantees that no concurrent transaction can see dirty data, and no anomaly can happen, because the UPDATEs read and write in the same transaction.

    What could happen is a deadlock if a second money transfer from account 2 to account 1 happens at exactly the same time. But that is also no problem for data integrity. You can avoid such deadlocks if all transactions update the account with the lower number first, no matter in which direction the transfer is done.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search