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)
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.
- 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?