Forum Stats

  • 3,837,949 Users
  • 2,262,310 Discussions
  • 7,900,436 Comments

Discussions

DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

13»

Comments

  • Stew Ashton
    Stew Ashton Member Posts: 2,907 Bronze 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.

    connor_mc_d-Oracle wrote: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

    Also, suppose you send that SMS if an UPDATE meets that condition. If you put that code directly into a trigger:

    • the UPDATE may be restarted by Oracle
    • the trigger will run twice
    • the audit team will get two messages
    • or after the restart the sale price is less that $10,000, but the audit team gets the SMS anyway.

    Within the transaction, we should not directly code an action that cannot be rolled back - even if it is blindingly fast!

    • We should have separate code outside the transaction that acts for us
    • Within the transaction, we should simply request the action
    • Our request should be part of the transaction, so that it only gets communicated if our commit succeeds.

    AQ lets us do this, yes, but DBMS_JOB is a simple, tried and true mechanism. It already provides the views we need to investigate if things go wrong.

    As a tool behind a UI, DBMS_SCHEDULER is fine and it makes sense for it to commit.

    As a tool for developers to manage "non-transactional" actions from transactions, DBMS_JOB is simpler and does not commit!

    DBAs should use DBMS_SCHEDULER. Developers should be able to use DBMS_JOB.

    Deprecate the deprecation!

    Best regards,

    Stew Ashton

  • GregV
    GregV Member Posts: 3,086 Gold Crown
    connor_mc_d-Oracle wrote: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

    Also, suppose you send that SMS if an UPDATE meets that condition. If you put that code directly into a trigger:

    • the UPDATE may be restarted by Oracle
    • the trigger will run twice
    • the audit team will get two messages
    • or after the restart the sale price is less that $10,000, but the audit team gets the SMS anyway.

    Within the transaction, we should not directly code an action that cannot be rolled back - even if it is blindingly fast!

    • We should have separate code outside the transaction that acts for us
    • Within the transaction, we should simply request the action
    • Our request should be part of the transaction, so that it only gets communicated if our commit succeeds.

    AQ lets us do this, yes, but DBMS_JOB is a simple, tried and true mechanism. It already provides the views we need to investigate if things go wrong.

    As a tool behind a UI, DBMS_SCHEDULER is fine and it makes sense for it to commit.

    As a tool for developers to manage "non-transactional" actions from transactions, DBMS_JOB is simpler and does not commit!

    DBAs should use DBMS_SCHEDULER. Developers should be able to use DBMS_JOB.

    Deprecate the deprecation!

    Best regards,

    Stew Ashton

    Stew Ashton wrote:AQ lets us do this, yes, but DBMS_JOB is a simple, tried and true mechanism. It already provides the views we need to investigate if things go wrong.

    Hi Stew,

    The views provided for DBMS_JOB jobs are really poor and not so useful. For example there's no job run history.

    You suggest that developers should use DBMS_JOB but I'd rather Oracle remove the implicit commit from DBMS_SCHEDULER instead. They could do it for DBMS_STATS, so why not for DBMS_SCHEDULER?

    The only time I've used DBMS_JOB was for a trigger's logic because it made sense (submit the job only if the transaction commits). Except for that DBMS_SCHEDULER is far better.

  • Stew Ashton
    Stew Ashton Member Posts: 2,907 Bronze Crown
    Stew Ashton wrote:AQ lets us do this, yes, but DBMS_JOB is a simple, tried and true mechanism. It already provides the views we need to investigate if things go wrong.

    Hi Stew,

    The views provided for DBMS_JOB jobs are really poor and not so useful. For example there's no job run history.

    You suggest that developers should use DBMS_JOB but I'd rather Oracle remove the implicit commit from DBMS_SCHEDULER instead. They could do it for DBMS_STATS, so why not for DBMS_SCHEDULER?

    The only time I've used DBMS_JOB was for a trigger's logic because it made sense (submit the job only if the transaction commits). Except for that DBMS_SCHEDULER is far better.

    "The only time I've used DBMS_JOB was for a trigger's logic because it made sense (submit the job only if the transaction commits)"

    My point.

    "I'd rather Oracle remove the implicit commit from DBMS_SCHEDULER instead."

    Either way works, but I have found the DBMS_SCHEDULER API to be unintuitive. Maybe its views are better though...

    Regards,

    Stew

  • luboid
    luboid Member Posts: 14 Blue Ribbon
  • User_EHQOS
    User_EHQOS Member Posts: 1 Green Ribbon

    Hello,

     is there a form to call DBMS_SCHEDULE in a transaction?

  • jflack
    jflack Member Posts: 1,518 Bronze Trophy

    Late to this conversation...

    I've used DBMS_JOB's transactional nature to create a job from a trigger - the job only gets created if the transaction that triggered the trigger is committed. Good example is when you want to send an e-mail if a particular change happens in a table, but you only want the e-mail sent if the change is saved. Sending the e-mail directly from the trigger could happen even if the transaction is rolled back. But if the e-mail is sent from a job, it only gets sent if the job is saved and run.

    Niall
  • user10607686
    user10607686 Member Posts: 1 Blue Ribbon

    I agree 1000% with what Hemant said, "A call to create a job might be part of a transaction with one or more preceding DMLS and succeeding DMLs. So an auto-commit would break the model. Yes, I'd like to remove the forced commit." Very well put Hemant!

    I have been gradually switching from user_jobs to user_schedule_jobs and I just ran into a case where a complex transaction ended up partially committed because it called dbms_scheduler.create_job in the middle prior to reaching the error which should have rolled the whole thing back. In 25 years I have never seen anything like this and it took me a couple of hours to figure out what Oracle had done to me.

    Somebody who understands transaction processing should have been involved in this whole dbms_scheduler thing. Or better yet, they just shouldn't have done it.

    Procedures call procedures that call procedures etc. Success needs to be all or nothing. My system has almost 8000 stored procedures operating on 1800 tables, so proper transaction processing is not optional.

    Fortunately, I had my call to dbms_scheduler.create_job embedded in a wrapper procedure, so I was able to get around Oracle's rogue commit by putting "pragma autonomous_transaction;" in my wrapper procedure. Nowhere do I call dbms_scheduler.create_job directly. Everything calls the wrapper procedure where I need to create a job.

    I am pretty sure I don't have anyplace where not rolling back the creation of the scheduler_job will cause a problem. I would rather have the scheduler_job not get rolled back in a failed transaction than allow dbms_schedure.create_job to commit half of a transaction and get everything out of whack.

    WHAT A MESS!!!