A simplified version of my problem looks like this:

* the database contains two tables:
	+ one contains account balances (`Accounts`)
	+ the other contains account withdrawal and deposit requests (`Requests`)
* the application consists of N identical workers that process these requests

Two workers processing two withdrawals from the same account is a classic race condition, so all requests to a single account are processed by a single worker. One way to do this is to partition the space of account IDs into N buckets to match the number of workers, but it has two failure modes:

* not all accounts are created equal: some have several orders of magnitude more requests than others, so some workers will be overloaded while others are idling
* there is no guarantee that N workers numbered 1 to N are always up and we want to scale the number of workers based on the depth of the requests queue, so the number of workers is 0..Nmax, not N

Another way to balance the load between the workers is to use pessimistic locking at the database level:

* the worker looks for the first M (> Nmax) oldest unprocessed requests with distinct account numbers and tries to obtain a lock on the account (`select ... for update nowait`)
* when a lock is obtained, the worker processes all requests for this account and releases the lock at the end when the outermost transaction is committed
* even if the worker is gracelessly terminated, the lock is automatically released by the DBMS and the request can be picked by the next available worker

The problem with this is that most ORMs do not support pessimistic locking. Microsoft explicitly recommends optimistic locking in its EF Core documentation. They must have a good reason to recommend this. However, I think optimistic locking will be problematic in this specific use case, especially when the request queue is flooded with requests targeting a single account.

Is there another way to solve this that I am just unable to see? Or should I just bypass the ORM and use the battle-tested synchronization available in the RDBMS?