PostgreSQL concurrency control and Serializable transactions 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 &quot;request&quot; 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 &quot;request&quot; 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 &quot;When using Serializable transactions you don't have to think about concurrency&quot;
What do I have to do to ensure this consistency? The explicit locking strategy is the only way?
Thanks!
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.