I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate transaction, as it should be atomic with respect to other data.
I also have some consumers reading from this table periodically. Each consumer has a pointer to the last ID processed within this event table.
After some experimenting with
identity column it seems IDs are assigned to new records at insert time, not commit time. This means that I situation like this might arise:
- Producer A inserts event EA into table (ID assigned: 1)
- Producer B inserts event EB into table (ID assigned: 2)
- Producer B commits
- Consumer reads latest event EB, and sets "last processed" pointer to 2
- Producer A commits
- Consumer will never process event EA, because it has a lower ID, assuming already processed
How do I ensure, that events (DB rows) are numbered in increasing commit order? I don't mind gaps in the series if transactions are rolled back etc., but there should never be committed an item with a lower ID than what has already been committed (and therefore maybe already processed), and of course they need to be unique. I would like not to lock the entire table, since many transactions run in parallel and some might run for a while.
Can I use some kind of insert trigger, stored procedure or similar to ensure this, or is it better to do some kind of post-processing in application code?