No, that's not what happened.
> If a cache tries to update itself while there's an open transaction against the data it's trying to read, shouldn't the database block the operation until the write is complete?
Yes. Generally speaking, having a read transaction here would be better. There are unrelated reasons why it's done this way. The point of the example is that it's really intricate and we can still identify the bug regardless.
I'm not too familiar with your database, but I wonder if you could have a "read transaction" with such a short timeout that you could return a "try again soon" error to the cache? (The only time I dealt with dirty reads like this was Microsoft SQL, I suspect you're using something with very different transaction semantics or guarantees.)
I'm a little confused:
- Is the database updated notification going out while the transaction is still in-progress? Doesn't it make more sense to delay the notification until after the transaction commits?
- If a cache tries to update itself while there's an open transaction against the data it's trying to read, shouldn't the database block the operation until the write is complete? And then shouldn't another notification after the transaction commits trigger the cache to update?