I’ve an occasion desk with an ID area and a few knowledge. A number of producer threads needs to be inserting occasions to this desk on the similar time. Every batch of inserts is finished in a separate transaction, accurately atomic with respect to different knowledge.
I even have some customers studying from this desk periodically. Every shopper has a pointer to the final ID processed inside this occasion desk.
After some experimenting it appears IDs are assigned to new information at insert time, not commit time. Because of this I state of affairs like this may come up:
- Producer A inserts occasion EA into desk (ID assigned: 1)
- Producer B inserts occasion EB into desk (ID assigned: 2)
- Producer B commits
- Client reads newest occasion EB, and units “final processed” pointer to 2
- Producer A commits
- Client won’t ever course of occasion EA, as a result of it has a decrease ID, assuming already processed
How do I guarantee, that occasions (DB rows) are numbered in rising commit order? I do not thoughts gaps in
the collection if transactions are rolled again and many others., however there ought to by no means be dedicated an merchandise with a decrease ID than what has already been dedicated (and due to this fact perhaps already processed), and naturally they should be distinctive.
I would really like to not lock your entire desk, since many transactions run in parallel and a few may run for some time.
Can I exploit some form of insert set off, saved process or comparable to make sure this, or is it higher to do some form of post-processing in utility code?