Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Scheduler Jobs arguments gets overwritten

nydnJul 20 2018 — edited Oct 31 2018

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.

IDTEXTTIMESTAMP
.........
86256func_check_xxx is true for: 8805753 (edit: this is param1)2018-07-18 15:50:05,681960
86257func_check_xxx is true for: 8805756 (edit: this is param1)2018-07-18 15:50:05,682106
...... some other things ......
86275func_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

This post has been answered by spajdy on Jul 25 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 28 2018
Added on Jul 20 2018
9 comments
2,099 views