How to save Timestamp of commit into a table
Hi there,
Is there any way to insert the timestamp of the commit into the table which is commited?
I have multiple jobs which process messages and which are running in parallel. Depending of the kind of message type the processing time differs. All messages descriptions are stored in a table which have a climbing ID as primary key.
When our client is asking for new messages he asks select * from table where id > max_id_of_last_result;
So, when running in one instance only, the client gets all messages (no duplicates and no losses).
When running in parallel the following happens:
1. Client asks for new messages. max_id of result is 100.
2. Instance 1 is processing message 1. It gets ID 101 from sequence.
3. Instance 2 is processing message 2. It gets ID 102 from sequence.
4. Instance 2 is faster than instance 1. It commits its transaction.
5. Client asks for new messages. It gets ID 102 and saves it for further requests.
6. Instance 1 has finished. Commit.
7. Client asks for new messages greater than 102. -> It will not get 101.
It is not possible to fix that problem in the client because every customer is only allowed to view his own messages. So it does not know if it is a gap caused by other user's messages which it is not allowed to view or if the situation above has occurred.
We are looking for ways to solve this problem. Our aim is max scalability recording to the number of message processing instances.
Is there any way to save the commit time into the table?
If so we could map the commit timestamp to the message_ids. Or is it possible to save the scn into the table while commiting the transaction which generates the scn? (probably not).
I am looking forward to hear your solutions for such problem.
Thanks a lot,
Andreas