Comment by gethly

Not a bug but rather an engineering oversight. Also not hard and it did not affect me, I caught it soon, but it was one of those surprising moments worth mentioning.

I have a write-online table in MariaDB and ordering of records is important. I have realised that the database has no such thing as append-only table that stores records in the order they are submitted into the database. Every record has one or more indices, and it is these indices that dictate the ordering and only for the data they index. What I have overlooked is when a transaction A starts, then transaction B starts, the transaction A might have records with smaller keys, as it started sooner, but transaction B commits first with higher keys, which means I end up with out-of-order entries. This is not too bad, actually, it depends on the context and in my case the context was that there were readers constantly waiting for new records. And so if a reader reads records after transaction B commits but not before transaction A commits, the reader will never see new records from transaction A. I have solved it by blocking the readers based on number of active transactions with ordering being considered.

I have wrote about it in this blog post, in the "Event Log and proper ordering of events" section https://gethly.com/blog/how-of-gethly/event-sourcing-right-w...