As usual, Oracle introduces new features, and we read the documentation about how they work and we start using them… but what about what is not in the documentation? That we find out by practice, experience, or accident. And that is exactly what happened to me today.
Whenever I write a procedure to be called from the scheduler (and previously via dbms_job), if I expect the changes I make to the data to be permanent, I explictly add a commit to the code. This makes the intention obvious to myself and others reviewing my code in the future. Today, I was reviewing someone else’s code. It was a packaged procedure that was called by the scheduler and it did not have a commit.
I was curious to determine how the scheduler handles this. Does the scheduler issue an implicit commit?
I scanned the online documentation and did not find any reference to whether the Oracle scheduler does implicit commits. I googled and did not find any articles that covered it. So I did what every other DBA would do — I created a test case to prove it one way or the other. Here are the steps I took.
- Create the test table.
SQL> create table babette ( thedate date, timesupdate number); SQL> insert into babette values ( sysdate, 0, null, null); SQL> commit; SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette; TO_CHAR(THEDATE,'YY TIMESUPDATE ------------------- ----------- 2007/02/26 14:50:21 0
- Create the procedure.
SQL> create or replace procedure update_babette is 2 begin 3 update babette set thedate = sysdate, timesupdate = timesupdate + 1; 4 end; 5 /
- Create the Scheduler job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'BABETTE_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'avail.update_babette',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY',
enabled => TRUE);
END;
/
- Confirm that the job is running.
select log_id, to_char(log_date,'YYYY/MM/DD HH24:MI') log_date,
owner, job_name, status,
to_char(req_start_date, 'YYYY/MM/DD HH24:MI') start_date,
to_char(actual_start_date,'YYYY/MM/DD HH24:MI') actual_start_dt
from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name = 'BABETTE_JOB' and actual_start_date > sysdate - 5
order by owner, actual_start_dt;
LOG_ID LOG_DATE OWNER JOB_NAME STATUS ACTUAL_START_DT
432292 2007/02/26 15:10 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:10
432294 2007/02/26 15:11 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:11
- Check if the table updates are kept and visible to other sessions.
SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette; TO_CHAR(THEDATE,'YY TIMESUPDATE ------------------- ----------- 2007/02/26 15:10:23 3
So yes, it does an implicit commit.
10 Comments. Leave new
In my opinion, this is a significant disadvantage of DBMS_SCHEDULER over DBMS_JOB. The creation of schedules and jobs using DBMS_SCHEDULER is pretty good. But when using DBMS_JOB, the creation of the scheduled job can be part of a wider transaction. However this is not the case when using DBMS_SCHEDULER.
interesting find, Babette. Thanks for that.
Babette,
Are you referring to an implicit commit done by the statement:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘BABETTE_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘avail.update_babette’,
start_date => sysdate,
repeat_interval => ‘FREQ=MINUTELY’,
enabled => TRUE);
END;
/
or are referring to the commit done in the job which got execute.
The jobs created in dbms_job behave no differently since they do
an implicit commit also.
However, the calls to dbms_scheduler do an implicit commit
which calls to dbms_job did not.
So after dbms_scheduler.create_job, one does not need to
do a commit to have the job submitted.
However, one needed to do a commit after dbms_job.submit
for the job to be submitted.
Regards,
Anurag
I am refering to the procedure that is run by the scheduler job, in this case “avail.update_babette”. So although I did NOT issue a commit in the procedure “update_babette”, there is a commit that DBMS_SCHEDULER does.
I guess dbms_job might work the same way, but I never left the “explicit commit” out of the code before and was not aware of the behaviour.
I checked the documentation and could not find it…at least it was not in any place obvious….kinda like the implicity commit in SQL*Plus when you exit. But that one I knew about already.
Babette,
I’d think this behavior is normal .. since the procedure is run in a different session and upon exit it does an implicit commit. That behavior is not limited to a sql*plus session or for a dbms_job job…
Most oracle utilities and tools issue an implicit commit upon session exit.
The doco states that too:
https://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4010.htm
Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database. If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back.
A normal exit from most Oracle utilities and tools causes the current transaction to be committed. A normal exit from an Oracle precompiler program does not commit the transaction and relies on Oracle Database to roll back the current transaction.
So even with dbms_scheduler, one should issue an explicit commit so that the
final intention is clear.
However, an odd behavior with dbms_scheduler.submit_job is that it submits
the job and does an implicit commit. Folks used to use dbm_job.submit to
submit jobs which came in effect only if an explicit commit was issued. Coding
same thing using dbms_scheduler might give them a rude shock since the
call itself is doing an implicit commit…
Anurag
dbms_job was great in that you can include it as part of the transaction, eg. in a procedure;
1) do some DML
2) submit a dbms_job which does some DDL
3) then an error happens, ROLLBACK occurs and the job doesn’t run which is what we want
Any ideas on how to achieve the above now that dbms_job is supposed to be superseded and I guess I’m trying to do a bit of future proofing?
It is interesting to read the discussion regarding auto commit for job coming from dbms_scheduler. For last few days , I am attempting to enable parallel DML (using alter session enable parallel DML) from a stored procedure submitted as a job through dbms_scheduler.
Oracle support tells me that dbms_scheduler creates autonomous transaction for a job where it gets disabled. Has anybody experienced it before? I consider it a serious restriction (though undocumented in Oracle manual) specially while dealing with large volume of data.
Hi,
This is a good point. Two things
– tou can prevent the implicit commit by using an autonomous transaction (google for more)
– there is a good reason for this behaviour. scheduler objects are full database objects like tables and stored procedures so they appear in all_objects and can be granted privileges on. So a create_job call is basically a DDL statement like a CREATE TABLE, which is why it auto-commits.
Cheers,
Ravi.
Scheduler and Parallel DML…
Just ran into exactly the same issue with AIX and 10.2.0.4
The answer i got from support
It’s not a bug…this is the expected behavior.
Stored procedures calls invoked by the scheduler are wrapped in an autonomous transaction…and in 10g,parallel DML is disabled inside autonomous transactions.
So I was given 3 choices…
1. Upgrade to 11g…Which does allow you to use PDML
2. Not use the scheduler …which for us defeats the whole purpose…
3. There is a one of patch for 10.2.0.3 and 10.2.0.4 which will enable the use parallel DML inside procedures called by the scheduler
Hi guys,
Have found a piece of doc from Oracle stating that Oracle job slave creates a transaction and ends the transaction once the job is complete.
How Jobs Execute
Job slaves actually execute the jobs you submit. They are awakened by the job coordinator when it is time for a job to be executed. They gather metadata to run the job from the job table.
When a job is picked for processing, the job slave does the following:
1. Gathers all the metadata needed to run the job, for example, program arguments and privilege information.
2. Starts a database session as the owner of the job, starts a transaction, and then starts executing the job.
3. Once the job is complete, the slave commits and ends the transaction.
4. Closes the session.
(Oracle® Database Administrator’s Guide, 11g Release 2 (11.2), E25494-05, 28 Oracle Scheduler Concepts)