1

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.

Improve this question
5
  • 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.
    – user1822
    Jun 12, 2018 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, 2018 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.
    – user1822
    Jun 12, 2018 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, 2018 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"
    – user1822
    Jun 12, 2018 at 18:40

2 Answers 2

Reset to default
1

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.

Improve this answer
4
  • 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, 2018 at 17:25
  • Incredible. Both will attempt a lock. The first one gets it, the other one waits.
    – Gerard H. Pille
    Jun 12, 2018 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, 2018 at 20:41
  • My doubt came from the fact that the SELECT is read only. Thank you for clarifying
    – ivarec
    Jun 13, 2018 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.

Improve this answer
1
  • 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, 2018 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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