undefined | Better HN

0 pointstheptip7y ago0 comments

I don't think you need a separate queue; if you have an "Events" table then you can just write everything there.

It solves the consistency problem because you can create your event inside a transaction, which will rollback if another event touching the same source is created simultaneously.

E.g. if you have these incompatible events in a ledger:

CreditAccount(account_id=123, amount=100)

DebitAccount(account_id=123, amount=60)

You'd want one of the debit transactions to fail, assuming you want to preserve the invariant that the account's balance is always positive. You could put the `account_id` UUID as an `Event.source` field, which would allow you to lock the table for rows matching that UUID.

0 comments

Yokohiii7y ago

If your idea in the example is that the second "debit" is created by another transaction while your transaction is in progress, then this will not work out. Firstly it requires a dirty read, which is nothing I would rely on in a transaction. Secondly, if the dirty read works, assuming the outcome of several rows is just a read operation, which forces you to rollback on the client and still leaves a window for inconsistencies if you decide to commit. Maybe SELECT .. FOR UPDATE can do a trick here, but that is like giving up life.

To round this up: RDBMS are bad for queue like read semantics. All you can do is polling. Which is even worse if you end up being lock heavy.

hvidgaard7y ago

No matter how you model things and no matter what technology you use, a race condition like this needs to be handled one way or another. You either handle it such that the data is always consistent, or you handle inconsistent data.

You can use an SQL database with transaction and locking to ensure that you will never debit money that isn't there. Or you can save commands in a queue that only a single process at any given time (that incidentally includes the SQL scenario). Or you can use a distributed consensus algorithm with multiple stage commits. There is no way around it.

j / k navigate · click thread line to collapse