1

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?

1

If you're using a database that supports transactions, you can solve the race condition problem by wrapping each deposit/withdrawal in a transaction. You can then run these transactions on any node you want.

Further Reading
Transaction Management from Oracle

4
  • Transactions obviously solve the issue of data corruption, but they don't solve the issue of optimizing the throughput. If all workers are trying to work on the same account, all but one of them will be stuck waiting. – Alexey Jan 28 at 10:23
  • If transactions are atomic, if they don't involve large traversals over huge tables and views the locking time should be low too. On the other hand, some optimizations might involve better hardware or a different programming language or execution environment. – Laiv Jan 28 at 14:11
  • @alexey: It would be no worse than pessimistic locking, and probably better. – Robert Harvey Jan 28 at 14:49
  • Deposits and withdrawals are already wrapped in transactions. What I want to avoid is multiple workers trying to work on a single account while requests for another accounts are present. – Alexey Jan 28 at 22:41
-1

Even if not all accounts are equal, would you say that a random bucket sized group of accounts generate a roughly equal amount of work?

If so (and the stream of work is smooth enough) you can still use partitioning.

To address scaling the nodes: This is exactly what Kafka is good for. It will guarantee for you that work belonging to the same account will always go to the same node. (The same thread even). It will gracefully handle node failures, downtimes, new nodes for you. You can even explicitly listen to re-balancing events, but you probably won't have to if you're doing it right.

With Kafka Streams, you can basically just forget about all that and even can create stateful processing nodes with Kafka doing everything for you, including transfering the state if your nodes dies over to another node.

If you can't use Kafka (you're on Windows, or it's just too complicated for the project) you can try to roll your own re-balancing. If your use-case is simple enough, you might be able to get it working in a couple of days. You'll probably need something like Rendezvous Hashes, or something similar.

1
  • > "Even if not all accounts are equal, would you say that a random bucket sized group of accounts generate a roughly equal amount of work?" That's unfortunately not the case. Random hashing results in unbalanced buckets. I could use existing statistics to manually assign the accounts to buckets, but that's fiddly, so I would prefer to have autonomous work-stealing workers. – Alexey Jan 28 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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