0

Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no one talk about the problem I have (and I think it's VERY common)

My DB:

  • Table accounts with a boolean column called disabled (the other columns can be ignored)

  • Table request, that represents some relation between 2 accounts. An account can "request" some action to another account.

The issue:

  • Accounts cannot request actions to disabled accounts. It is a business rule, so I don't want to put any logic related to this in the database (using triggers/constraints). The database should only guarantee things related with the data (FK's, unique indexes, etc)

Since the database will not handle this business rule, my application will. To do it, every time a new request is issued, the following steps happen:

  • Open a transaction
  • Read target account disabled value // Concurrency issues happens here // What happens if the target account update it's disabled value here?
  • If the target account is not disabled, create a new "request" row

Do you see the concurrency issue?

At first, I tried to use Explicit Locking, but it is too complexity to handle (my real case is a little bit more complex, but the above example is sufficient). It works, but its not maintainable.

After, I tried to use Serializable transactions, but the problem above persists, although everyone saying "When using Serializable transactions you don't have to think about concurrency"

What do I have to do to ensure this consistency? The explicit locking strategy is the only way?

Thanks!

Improve this question
2
  • 1
    In step 2, lock the rows with a SELECT ... FOR UPDATE statement. There are other levels of strictness to that method. I'll let the PostgreSQL experts tell you the correct one.
    – Michael Kutz
    Commented Jun 19, 2020 at 19:34
  • Commands like "FOR UPDATE" are "explicit locking". I want to avoid it every time it is possible. Too complexity
    – user211017
    Commented Jun 19, 2020 at 22:53

2 Answers 2

Reset to default
4

You don't get a serializable violation, because there is not one.

If the actual order is:

  • T1 begin serializable
  • T2 begin serializable
  • T1 see that account is not disabled
  • T2 disable account
  • T1 insert request
  • T2 commit
  • T1 commit

The outcome of the above is compatible with the serial order of:

  • T1 begin serializable
  • T1 see that account is not disabled
  • T1 insert request
  • T1 commit
  • T2 begin serializable
  • T2 disable account
  • T2 commit

Because the outcome is compatible with some serial execution, it is not a violation. If the serial execution it is compatible with is not the one you want, that is an error in your expectations, not in the execution.

If T2 were both to disable the account, and delete all pending requests for that account, in one serializable transaction, then that would be a violation. One of the two would get served an ERROR when it tried to commit. But if T2 only disables the account without deleting pending request against it, your business logic allows that outcome, so that is not a violation.

Improve this answer
4
  • Hm, of course... Thanks for the reply! How can I achieve the expected outcome? Only using explicit locking?
    – user211017
    Commented Jun 19, 2020 at 19:34
  • What's the desired outcome if an account it disabled while actions are pending? If you want to delete the pending actions, just do it and that will throw an error if you are using serializable (and then you need to retry the one that got the error).
    – jjanes
    Commented Jun 19, 2020 at 21:05
  • I cant do it since the state of the account when it is disabled must be preserved (eg the pendent requests just stay there, nothing happens) I think there's no way to solve it unless explicitly locking the tables/rows... I will create code conventions like "always lock tables in alphabetic order" and hope no errors occur. What do you think?
    – user211017
    Commented Jun 19, 2020 at 22:51
  • The easiest solution would be just to decide it is not a problem. If the serializable enforcement code says the disablement could have been done after the insertion was, then maybe you can accept that determination. If not, then select disabled from accounts...for update is probably the next best option. Unless you are doing it for multiple accounts in the same transaction, there should not be a deadlock risk. If you do this, you don't need to use serializable mode anymore.
    – jjanes
    Commented Jun 20, 2020 at 18:29
0

If you read the status of an account before you disable it, you will get a serialization error in SERIALIZABLE isolation level. So that would be a way to get what you want.

Improve this answer

Your Answer

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

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