Hey everyone,
We have a strange issue here with DBMS_SCHEDULER (or maybe DBMS_AQ, I'm not sure).
There is a given procedure which accepts three parameters and inside the procedure those parameters are set as arguments for an event based job (X_JOB).
DBMS_SCHEDULER.set_job_argument_value ( job_name => 'X_JOB', argument_position => 1, argument_value => param1);
DBMS_SCHEDULER.set_job_argument_value ( job_name => 'X_JOB', argument_position => 2, argument_value => param2);
DBMS_SCHEDULER.set_job_anydata_value ( job_name => 'X_JOB', argument_position => 3, argument_value => sys.anydata.convertdate( param3 ));
Right after this, I send a new queue payload to inform the aforementioned event based job to start. (still in the same procedure)
l_q_msg := t_event_queue_payload( 'new_event' );
sys.DBMS_AQ.enqueue
(
queue_name => 'X_JOB_queue'
, enqueue_options => l_q_opts
, message_properties => l_m_props
, payload => l_q_msg
, msgid => l_msg_handle
);
And it's done, nothing complicated, and most of the time it works perfectly and correctly.
But if two event is happening in almost the same time, the first job's parameters are overwritten by the next run of the procedure and the event based job runs twice, as expected, but wrongfully with the latter procedure's parameters.
ID | TEXT | TIMESTAMP |
---|
... | ... | ... |
86256 | func_check_xxx is true for: 8805753 (edit: this is param1) | 2018-07-18 15:50:05,681960 |
86257 | func_check_xxx is true for: 8805756 (edit: this is param1) | 2018-07-18 15:50:05,682106 |
... | ... some other things ... | ... |
86275 | func_check_xxx is true for: 8805757 (edit: this is param1) | 2018-07-18 15:50:05,926336 |
... | ... | ... |
In the above log table you can see that 86256 and 86267 and little bit later 86275 was all true, and the procedure I mentioned before, runs three times and sets the job parameters (param1 is above in the table, param2 and param3 are both the same in all three times).
However when the time difference is small enough, eg. between 86256 and 86257 are 46 microseconds, the job runs two times with the same parameters (8805756) instead of two different (8805753 and 8805756).
Third time the job runs correctly again(8805757),but still the difference is only roughly 2,5 milliseconds.
DBMS_SCHEDULER JOB parameter 'PARALLEL_INSTANCES' is set to true, so there can be multiple instances of X_JOB running in the same time.
I'm a little bit lost on why the first job isn't instantiating correctly,but this is the second time it happened in a few hundred attempts, so it bothers me now.
Anyone ever experienced this? Any idea why is this happening?
If you need more info please let me know, I didn't wanted to clutter up the post too much, so I tried include the most relevant (at least for me) information only.
Regards,
Daniel