Locking row that is being currently inserted to avoid race conditions when reading same

Question

I have a system in which I'll dispatch events as part of transactions when inserting rows into the database.

This is to ensure that events only ever get dispatched for a successful insert, and that inserts are rolled back incase event dispatching fails.

Problem is that I'm seeing a race condition when dispatching an event after a successful insert. Sometimes the corresponding event listeners will receive the event, before the transaction has been committed, which will result in the row not being available at this time

Want I want to achieve is when inserting row A, any processes trying to read row A must wait until row A's transaction has been committed

Is this a sound approach and how is it best achieved?

For two processes A and B

How it currently works

1. A Start transaction
2. A Attempt insert
3. A dispatch event
4. B Receives event and attempts to read the inserted row
5. B Exception is raised as record is not visible yet
6. A commits transaction

How I'd want it to work

1. A Start transaction
2. A Attempt insert
3. A dispatch event
4. B Receives event and attempts to read the inserted row
5. B The row is currently locked by transaction so it waits until released
6. A commits transaction
7. B Lock is released and newly inserted row is returned

Answer 1

Based on the question and the comments, I think the main issue here is that you want "A" to dispatch an event based on the content of an uncommitted row, and you want "B" to read and act on that uncommitted row. But in relational theory and in SQL databases, you don't know whether a commit will succeed. A commit can fail for lots of reasons--not only because some constraint fails, but because of things like insufficient permissions or disk space.

To borrow words from one of your comments, the dbms manages a database's transition from one consistent state to another. Uncommitted rows aren't known to be part of a consistent database state. Only successfully committed rows are part of a consistent database state.

So I think, in general, you need "A" to commit before "B" tries to read the new row. And I think this is true even if you switch to a dbms that supports the "read uncommitted" transaction isolation level. (PostgreSQL does not, at least not in the way you might think.)

That means "B" will be responsible for deleting the new row (or for telling "A" to delete it) if "dispatching" fails. Neither "A" nor "B" can roll back a committed transaction.

It might make more sense for another process, "C", to oversee the actions of both "A" and "B".