Relational databases guaranties when transfering money between accounts-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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;