Forum Stats

  • 3,838,619 Users
  • 2,262,384 Discussions
  • 7,900,697 Comments

Discussions

DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

2

Comments

  • blessed DBA
    blessed DBA Member Posts: 218

    Then You can go for a Application server Scheduler  So it will be same like transaction

  • user466593
    user466593 Member Posts: 9 Green Ribbon

    I have implemented this using event-based scheduler jobs. https://docs.oracle.com/html/E25494_01/scheduse005.htm

    But it is more complicated than just creating one job.

  • GregV
    GregV Member Posts: 3,086 Gold Crown

    I have implemented this using event-based scheduler jobs. https://docs.oracle.com/html/E25494_01/scheduse005.htm

    But it is more complicated than just creating one job.

    There's no need for that. The commit/rollback capability has been replaced with the "enabled" paramater when you create a job. I'm still waiting for a concrete example where an explicit commit would be useful when creating a job through dbms_scheduler.

    William Robertson
  • There's no need for that. The commit/rollback capability has been replaced with the "enabled" paramater when you create a job. I'm still waiting for a concrete example where an explicit commit would be useful when creating a job through dbms_scheduler.

    eg Function "BUY_PRODUCT" is
    - insert a new row into SALES table

    - if that sale was more than $10,000, send an SMS alert to our audit team

    - do some more queries and DML as part of this transaction


    I don't want to send that SMS inline with the code because SMS/network comms etc is way slower than a db insert, so I'll do it asynchronously

    Now I'm in a bind

    - if I call the DBMS_SCHEDULER to create a job send that SMS, I've committed which breaks the transaction model for subsequent DML - I've now got a corruption

    - if I move the scheduler part to the end of the transaction, I'm lowering that risk, but I've got app changes to make, and what if the call to create the scheduler job fails... I've committed but will never send an SMS

    - Ideally I want "sending an SMS" to be transactional (or as close to it as it can be). Using DBMS_JOB solves this - I submit the job, but it only really comes into existence when the parent transaction commits.

    Granted I could re-architect this with AQ, but if I've *currently* got code that relies on DBMS_JOB to do this, the ideal fix would be a DBMS_SCHEDULER drop-in replacement.

    Jeffrey KempSentinelStew AshtonNiall
  • GregV
    GregV Member Posts: 3,086 Gold Crown

    eg Function "BUY_PRODUCT" is
    - insert a new row into SALES table

    - if that sale was more than $10,000, send an SMS alert to our audit team

    - do some more queries and DML as part of this transaction


    I don't want to send that SMS inline with the code because SMS/network comms etc is way slower than a db insert, so I'll do it asynchronously

    Now I'm in a bind

    - if I call the DBMS_SCHEDULER to create a job send that SMS, I've committed which breaks the transaction model for subsequent DML - I've now got a corruption

    - if I move the scheduler part to the end of the transaction, I'm lowering that risk, but I've got app changes to make, and what if the call to create the scheduler job fails... I've committed but will never send an SMS

    - Ideally I want "sending an SMS" to be transactional (or as close to it as it can be). Using DBMS_JOB solves this - I submit the job, but it only really comes into existence when the parent transaction commits.

    Granted I could re-architect this with AQ, but if I've *currently* got code that relies on DBMS_JOB to do this, the ideal fix would be a DBMS_SCHEDULER drop-in replacement.

    Hi Connor,

    As you said, the thing is to move the DBMS_SCHEDULER.create_job at the end of the code. I'm trying to think what would be the reason for a create_job to fail? Even if you specify an invalid pl/sql block or stored procedure as the job_action (either for a plsql_block or stored_procedure job_type), the job's creation will still succeed. And if you specify a wrong value for a parameter, for example job_type => 'procedure', the job's creation will fail, but no commit will happen.

    That being said, I must admit there's one useful situation when the job's creation should be transactionnal :when it's part of a trigger's logic. The job is called by a trigger and should be created only if the transaction commits. In that particular situation I agree there should be an option to prevent DBMS_SCHEDULER from committing.

    David KurtzNiall
  • TimHall
    TimHall Member Posts: 293 Bronze Badge

    Since the introduction of the DBMS_SCHEDULER in Oracle 10g, people have been asking for some form of commit flag to be introduced. The use of DBMS_JOB to decouple processes has a long history. The fact that this was no longer possible with DBMS_SCHEDULER was a mistake IMHO. Several people, including me, have raised enhancement requests for this functionality.

    We aren't asking for this because we don't know alternative ways to code it in Oracle. See:

    https://oracle-base.com/articles/misc/decoupling-to-improve-performance

    We aren't asking for this because we are unaware of other schedulers. We are asking for it because it's a feature we used to have that's been taken away, and it was really useful. The fact you can code it a different way, or some people never use this method is irrelevant. Having a transactional job scheduler is important, which is why people still use DBMS_JOB for this purpose.

    Possibly worth people reading this post by Connor.

    https://connor-mcdonald.com/2019/05/28/dbms_job-the-joy-of-transactions/

    It's interesting that Oracle have implemented DBMS_JOB in Oracle 19c using DBMS_SCHEDULER, yet still maintained the transactional nature of DBMS_JOB. This proves it's really easy to do!

    With that in mind, give it us in DBMS_SCHEDULER directly please!

    Cheers

    Tim...

    Dom BrooksSven W.David KurtzNiall
  • user7111641
    user7111641 Member Posts: 4 Blue Ribbon

    Hello All,

    Allowing the DBMS_SCHEDULER to be transactional , based on a parameter, is a top essential feature,
    if Oracle does indeed encourage the idea to have it replace completely the DBMS_JOB functionality.

    Submitting a job as part of a business transaction is very legitimate and many developers have probably used this feature
    to implement essential functionality in their applications.

    So, it is essential to have this feature available, for ALL types of DBMS_SCHEDULER jobs, not just for lightweight jobs.

    After such a long time since this package was first introduced, it is surprising that this enhancement has not yet been implemented.

    Thanks a lot & Best Regards,
    Iudith Mentzel

    user1001802
  • MortenBraten
    MortenBraten Member Posts: 303 Bronze Badge

    I will add this comment to say that I strongly support this idea. It should be possible to submit a job without an implicit commit, in other words, if dbms_job is to be deprecated (which it now has been in Oracle 19c) and removed altogether sometime in the future, then Oracle must provide a way to achieve the same (ie "transactional job creation") via the new API.

    Come on, Tom Kyte said this 13 years ago, and it's way overdue... !

    https://asktom.oracle.com/pls/apex/asktom.search?tag=dbms-scheduler-emailing-after-inserting-into-database-tables#577780…

    - Morten

    SentinelNiall
  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    In recognition of the legitimacy of the technique, how about an official way to de-couple rather than the DBMS_JOB hack?

    EXEC  DBMS_UTILITY.INCLUDE_IN_TRANSACTION (pl_sql code goes here);     

    And it only gets executed upon a COMMIT.

  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    For those watching you may find this proof of concept of interest, borrowing the idea of javascript promises: https://github.com/morten-egan/promises_ninja