0

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might be in the middle of an UPDATE statement and, in my perception, any SELECT statements performed during this period will return the old balance. My questions are:

  1. Is my perception correct, i.e. is it possible for the SELECT statement to get old data in this trivial scenario?

  2. If yes, how do I implement locks to protect myself from this? I've read about FOR UPDATE in the docs, but I'm not sure it is meant for this specific situation.

Please assume that I cannot serialize the requests in the clients.

  • 1
    The only way to block a select is to either use for update or to manually lock the entire table in exclusive mode before starting the update. – a_horse_with_no_name Jun 12 '18 at 6:10
  • @a_horse_with_no_name if I use for update, will it block the select without changes to the update logic? That wasn't my understanding from the docs – ivarec Jun 12 '18 at 6:11
  • 1
    for update will simply lock the rows that the select returns. It will not change anything. It will however block any update that is issued after that (and tries to update the selected/locked rows). Another option you have is to use the serializable isolation level for both the update and the select. – a_horse_with_no_name Jun 12 '18 at 6:15
  • @a_horse_with_no_name check jjanes answer below. It seems to contradict what you just said, but from the docs my understanding is the same as yours. Do you agree with me or did I misinterpret jjanes' comment? – ivarec Jun 12 '18 at 18:08
  • It does not contradict what I said. Why do you think that? Maybe my wording "change" was unfortunate - of course that means "change" in terms of "changing data", i.e. "update" – a_horse_with_no_name Jun 12 '18 at 18:40
0

Yes, a SELECT...FOR UPDATE will block the select if the same row was already updated in another transaction which is still open.

A more general solution would be to run your transactions under the serializable isolation level, rather than using FOR UPDATE. This might not block the SELECT, but would instead deliver an ERROR to the transaction later when it detected that a non-serializable situation has developed.

  • From what I understood from @a_horse_with_no_name in the comments, it is the other way around: an UPDATE will wait on a SELECT...FOR UPDATE statement, not the other way around. I don't know which one is right. Can you point me to the documentation that explains this? Maybe I should look in the source code – ivarec Jun 12 '18 at 17:25
  • Incredible. Both will attempt a lock. The first one gets it, the other one waits. – Gerard H. Pille Jun 12 '18 at 20:39
  • Row lock confliction is symmetric. Whatever got there first wins, the other waits. I don't know where this would be documented, it is mostly just implicit to what a lock is. – jjanes Jun 12 '18 at 20:41
  • My doubt came from the fact that the SELECT is read only. Thank you for clarifying – ivarec Jun 13 '18 at 1:11
0

"sufficient balance to perform an operation", this should be one transaction, the check and the operation, otherwise the check is a waste of time. Plenty could happen between check and operation, if you separate them.

  • The question is more about other transactions that are still not closed, if I understand correctly, and separating them so that they cannot run at the same time. – dezso Jun 12 '18 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.