0

General setup

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.

Issue faced

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

Question

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?

Improve this question
3
  • 3
    There is nothing built in that does anything remotely similar to what you need. Consider that there may be many other statements in the same transaction after the INSERT. SQL Server would need to execute those and then go back to the row and fill in the missing identity value at commit time. If you are just using this for multiple competing consumers performing a single process against the queue probably easier to just have an IsProcessed flag and they just pick off rows where that is false rather than trying to keep track off a numeric offset
    – Martin Smith
    Apr 26, 2020 at 11:11
  • BTW are you using READPAST hint? If not most of the time I would expect it to be blocked waiting for EA to commit (though probably there is still some possibility that rows can be written to the page out of identity allocation order)
    – Martin Smith
    Apr 26, 2020 at 11:25
  • Thanks for the READPAST hint. I will look into that. IsProcessed is not good enough as there can be multiple consumers as well. But I guess I then need to create another field and another logic layer, so that some singleton will look for new un-numbered inserted rows, and number them, so the consumers may access them in order.
    – Henrik
    Apr 26, 2020 at 11:29

2 Answers 2

Reset to default
2

How do I ensure, that events (DB rows) are numbered in increasing commit order?

You don't. At least not with eliminating concurrent writers. You simply don't use a high-water mark for your readers. Delete the rows after reading, or update them to show that they were processed.

See eg Using tables as Queues

Improve this answer
-1

Try sequence instead of identity . In a loop ,Before committing EB read table with nolock and check , if you get a lower number than EB, wait for delay and loop again

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.