Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, ALLAN.

Asked: June 15, 2000 - 9:13 pm UTC

Last updated: February 02, 2012 - 7:54 am UTC

Version: 7

Viewed 50K+ times! This question is

You Asked

Hi Sir,

We have an application that executes stored procedure
via VBA . Apparently ,it auto commits that statement.

Is there a way to configure the oracle database to set
the auto commit to off so that we have to issue "COMMIT"
for every stored procedure we execute ?


Thanks very much .

Allan


and Tom said...

Well, it is not an Oracle configuration issue -- the only method Oracle operates in is "client tells us when to commit". We do not have an autocommit mode. Various TOOLS and API's do (eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement).

Microsofts various API's pretty much autocommit by default (SQLServer hates locks, performance pretty much goes down the drain, hence they love to commit for you -- at the expense of things like transactional integrity but thats another story).

You'll have to dig around in the VBA api to find out the proper set of calls to make. I don't program VBA myself but if you are using ODBC for example, the solution is:

...
In native ODBC, there are 2 API calls that affect transactions:
SQLSetConnectOption and SQLTransact. The SQLSetConnectOption is used to set the transaction mode for that connection to read write or read only and also to enable/disable the Autocommit transaction mode. The SQLTransact call is used to commit or rollback a transaction.

Autocommit is enabled by default as defined by the Microsoft ODBC
specification. With Autocommit, a transaction is committed as soon as it is executed. While this increases user concurrency in some databases, it does not allow the programmer to control when a transaction is committed nor does it allow a transaction to be rolled back. To disable Autocommit, call SQLSetConnectOption with the SQL_AUTOCOMMIT_OFF qualifier.

Since there are native ODBC calls to handle transactions, the ODBC driver must keep track of the transaction state at all times. For this reason, executing any statement that affects transactions is not supported via SQLExecute or SQLExecDirect calls. This includes SET TRANSACTION, DECLARE TRANSACTION, COMMIT, and ROLLBACK.

While this is not a problem for Native ODBC programmers, this may be a problem for people who use 4GL development tools to generate the native ODBC for them. Many of these 4GLs do not allow the developer to access the proper parameters to make these calls successfully. These parameters are the connection handle (HDBC) and the statement handle (HSTMT).

....

Hope this gets you pointed in the right direction.



Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

autocommit

A reader, September 16, 2003 - 6:22 pm UTC

if autocommit is off - when I log off sqlplus - it
still commits. Is there a way to prevent this?
I want sqlplus to never commit unless I say so
explicitly.

thanx!!!!

Tom Kyte
September 16, 2003 - 8:18 pm UTC

autocommit ONLY affects statements

autocommit off:

insert
update
delete
commit

that is a single transaction. autocommit on

insert
update
delete
commit

is now 4 transactions.


sqlplus always commits upon exit -- it is just they way it works. exit and commit.

thanx!

A reader, September 17, 2003 - 4:56 pm UTC

that makes it clear!!

Will DDL raise COMMIT

Arangaperumal, August 10, 2004 - 7:35 am UTC

Hi Tom,
         My doubt " whether DDL raise COMMIT "
        This is my observation

SQL> create table exam1(x int)
  2  /

Table created.

SQL> insert into exam1(x) values(1)
  2  /

1 row created.

SQL> insert into exam1(x) values(2)
  2  /

1 row created.

SQL> insert into exam1(x) values(3)
  2  /

1 row created.

SQL> insert into exam1(x) values(4)
  2  /

1 row created.

SQL> select * from exam1
  2  /

         X
----------
         1
         2
         3
         4

SQL> create table exam2(x int)
  2  /

Table created.

SQL> insert into exam2(x) values(1)
  2  /

1 row created.

SQL>  insert into exam2(x) values(2)
  2  /

1 row created.

SQL> select * from exam1
  2  /

         X
----------
         1
         2
         3
         4

SQL> select * from exam2
  2  /

         X
----------
         1
         2

      If I exit at this point, without COMMITing, what will be the output  for

         select * from exam1         
         select * from exam2
I have tested this in SQL*PLUS, this gives 
SQL> select * from exam1
  2  /

         X
----------
         1
         2
         3
         4

SQL> select * from exam2
  2  /

         X
----------
         1
         2

SQL> disconnect
SQL> conn scott/tiger
Connected.
SQL> select * from exam1
  2  /

         X
----------
         1
         2
         3
         4

SQL> select * from exam2
  2  /

         X
----------
         1
         2
 
        I know this is because of autocommit ON.
        I have tested this in TOAD, disconnect without COMMITing, then i login SQL*PLUS

SQL> select * from exam1
  2  /

         X
----------
         1
         2
         3

SQL> select * from exam2
  2  /

no rows selected


          Does it mean that every DDL raise COMMIT?
       

Tom Kyte
August 10, 2004 - 8:08 am UTC

DDL is executed logically like this:


begin
COMMIT;
do the ddl;
COMMIT;
exception
when others then
ROLLBACK;
RAISE;
end;


meaning, before the DDL is done, a commit is executed, the ddl is then performed. if the ddl is succesful, we commit again, else we rollback and re-raise the error.

In any case -- succeed or fail - your data is committed.


If this is not what you desire, you can use an autonomous transaction to execute ddl in "another transaction" --but to make DDL transactional consistent, you could use DBMS_JOB to schedule the execute of the DDL for right after you commit (meaning if you rollback, the ddl gets "unscheduled" -- rolled back in effect)



If you have "Expert One on One Oracle" -- i talk about this in detail. How transactions and such work in Oracle.

Thank you Tom.

Arangaperumal, August 10, 2004 - 8:46 am UTC


Autocommit.

Harschil Kaparwan, October 08, 2008 - 8:29 am UTC

Hi Tom,

Thanks for your time.

You said:
.....autocommit on
insert
update
delete
commit

is now 4 transactions.

sqlplus always commits upon exit -- it is just they way it works. exit and commit.

My Questions
a)How autocommit on
insert
update
delete
commit

leads to 04 transactions, as ==> 1 insert + 1 update + 1 delete = 3 Transaction. Are we considering "commit" 4th statement issued by user as 4th Trnasaction. And Autocommit will "Commit" the "Commit" issued by user?

b) If Autocommit is set to OFF and there is a single update statement that I am updating using SQLPLUS and that is taking huge time ( more then 15 mins say). Then If I close the sqlplus session (using x) same time as the Transaction completeion time. In such scenario what will be fate of my transaction.

c) What would have been the case. If Autocommit is set to Off in SQLPLUS and if user is exiting the session after update without commit, then transaction would have been rolled back rather then committed.? I mean why SQLPLUS design is not so? Because my Transaction should not get commited until I issue COMMIT or I set AUTOCOMMIT on.

Cheers.


Tom Kyte
October 08, 2008 - 10:08 pm UTC

a)
insert + commit = 1
update + commit = 2
delete + commit = 3
commit = 4

b) 15 minutes huge, ok... ;)

It depends on how the session was closed and the operating system and how it dealt with it and so on.

In some cases - closing the sqlplus session won't close anything. It'll just run and wait for input (unix typically - you closed a telnet window but the things it ran stay running). The sqlplus session might finish the update but just wait and wait and wait....

Then again, on another OS, the killing of the window might ctl-c the application, might send a break to sqlplus, might rollback the update.

Then again, you might just kill the client in which case it should roll back when the database tries to tell the client "update done" but the client is dead.

c) see b. It depends.

zizu1985, February 03, 2010 - 6:50 am UTC

Hi,

In SQL*PLUS 11.2 we have parameter:

EXITCOMMIT

Synopsis:
SET EXITCOMMIT [ON|OFF]

"With this fix if a session issues "SET EXITCOMMIT OFF" then on exit from sqlplus any uncommitted changes will be rolled back , rather than committed."

Information is from Oracle My Support.



Tom Kyte
February 03, 2010 - 10:12 am UTC

you should always finish your own work - you should explicitly commit or rollback based on YOUR logic. Never rely on the environment to do it, it might do something different

Eg: the addition of this feature to sqlplus changes the rules of sqlplus - all of a sudden - COMMIT is not the rule and anyone that relied on that - well, they have to review their stuff now.

autocommit

A reader, October 26, 2010 - 1:07 pm UTC

Hi Tom,

1) Why does Oracle auto commits upon "exit"? Is it something to do with locking?
2) If autocommit happens while doing "exit" then why not autocommit happens during undesired termination or system crash?
Tom Kyte
October 26, 2010 - 8:16 pm UTC

1) we don't, SQLPLUS - an application, a piece of code written by someone - decided to use "commit upon graceful exit". Other environments might not - do not. It DEPENDS.

and sqlplus only does that by default, in current releases - that is configurable. But remember, sqlplus is just a program, it is not oracle, it is not sql, it is just a program.

2) well, that would be a bad idea, wouldn't it?

autocommit

A reader, October 26, 2010 - 10:17 pm UTC

Thanks a lot Tom. I had different thought for this and you showed the exact reason.
I will remember that sqlplus is a code written under $ORACLE_HOME/bin and that can be run from anywhere in an OS user if $ORACLE_HOME is specified there. Isn't it true?
Tom Kyte
October 27, 2010 - 5:53 am UTC

sqlplus is just a program. You could write your own sqlplus (people have, one person called theirs "toad", another called theirs "sql developer" and so on).

It is just a program.

We tend to keep it in $ORACLE_HOME/bin when we install it - yes.

Autocommit after x rows are inserted

Ms, July 08, 2011 - 12:01 am UTC

Hi Tom,

I am facing issues on the similar lines. I'm trying to run an archiving stored procedure, which moves very large number of records from the current staging table to an archive table. But as many times I run it, it fails with the error that undo table space getting full. I even tried to split the query to two, so that the number of records the original query had to insert now decreased by half. Still the same error.

Is there any way where I can tell my session to autocommit after x number of records have been inserted?

Any help on this will be more than just helpful.

Regards,
MS
Tom Kyte
July 08, 2011 - 2:41 pm UTC

no, there isn't (thank goodness!!!!! how horrific would that 'feature' be, ouch, I hurt just thinking about it)


You should either

a) use partitioning to archive with DDL instead of DML
b) archive more often
c) code your processes to be really slow and go procedurall

or my favorite:

d) actually size your undo for the amount of work you do (what a concept, I know, why didn't anyone think of that before???)


CommitOnDisconnect - PowerBuilder

Burhan, October 09, 2011 - 3:04 am UTC

After reading the original question and all the reviews, conclusion is
Oracle has no say in when to commit,
it is user who decides when to issue and
manages by what ever tool being used...,
PowerBuilder has something called "CommitOnDisconnect" which can be set to 'Yes' or 'No'...
Tom Kyte
October 09, 2011 - 11:13 am UTC

Oracle has no say in when to commit

mostly, yes. Some things like DDL "auto commit", but normal transactions are committed only when the client says "commit me"

commit

A reader, October 14, 2011 - 8:33 am UTC

Hi Tom,

I am reading your book (Redo-Undo) – just bit confused here regarding the ‘commit cleanouts’

1.As part of 'commit cleanout' by the same session(transaction), when the session goes back to the modified blocks in the 'commit lists' - Does it not remove the lock information in the row header ? It seems it just updates the transaction status to 'U' in the ITL entry - ?
If it is so - who will remove the LOCK information ? is it the next query?

3.'commit cleanout' -> the same session will go back to the modified blocks(fit 10% of cache) in the ‘commit lists’ if they are available and update the transaction status='U' and DONT TOUCH THE LOCK INFO IN THE ROW HEADERS OF ROWS IN THE BLCOKS..
In this case - who will remove the LOCK info from the ROW HEADERS?

'delayed commit cleanout' ---> means next transaction(query) which visits the blocks (that are not fit in cache/unavailable in memory) - will update the status to 'U', update the SCN (known from UNDO) and REMOVE the lock info.

Is this correct ?

thanks for your time

Tom Kyte
October 14, 2011 - 11:27 pm UTC

1) the lock information is managed in the undo segment actually. We put locks on the data by putting information in the block header that points to our transaction in the undo segment. If someone else comes along - they'll follow that pointer to see if we are still active - if so, they'll enqueue on our transaction, if not - they'll clean us out and lock the data themselves.

In a commit cleanout, we are saving them the work of doing that later - that is all, the data is already "ok and ready to go", just our act of committing and updating the undo information - marking our transaction complete is what "unlocks" the rows.

2) missing in action :)

3) it is already gone enough. They know we are committed, that means the rows are not locked.


A reader, October 15, 2011 - 4:59 pm UTC

Hi Tom,

you said above-
"1) the lock information is managed in the undo segment actually" how it is ??

Each block has an ITL entry for a trnsaction ,it has

1.transaction id -->this identifies the undo segment/slot/wrap etc

2.lock info - how many locks this transaction has put in the row headers of the rows in the block

3.the address of the last undo record (uba)
4.status of the transaction (--,U,C,C-U)
and some other details..

- so how come the lock information is managed in undo segments ?? I believe this lock information is in the ITL entry in the block ..

Tom Kyte
October 16, 2011 - 10:16 pm UTC

We know from the block header that transaction X has OR HAD rows 1, 3, 5 locked on this block.

Now, if transaction X is committed, then 1, 3, 5 are no longer locked. If transaction X is not committed they are locked.

How do we discover if transaction X is committed? We go to undo segment and ask it - if it is - they are not locked.


that is why I say the lock information is in the undo segment - we have to find out the state of transaction X - that dictates whether anything is locked or not.

SQL*Plus and autocommit

Jack Douglas, October 24, 2011 - 8:59 am UTC

Hi Tom,

I quoted you in an answer on dba.stackexchange.com ( http://dba.stackexchange.com/questions/7087/auto-commit-in-sql-server-and-oracle/7089#7089 ) and someone has raised an interesting question about your aside:

"eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement"

They are saying that SQL*Plus probably uses the "OCI_COMMIT_ON_SUCCESS flag you can pass to OCIStmtExecute()" as it is built on the OCI, rather than actually issuing a separate commit after each statement.

Can you tell us if this is true or not?
Tom Kyte
October 24, 2011 - 10:35 am UTC

In a trace file, I see it look like this:


parse insert
bind insert (i used insert into t values ( :x ))
execute insert
stat records for insert
parse of another statement that turned out to be dbms_output.get_lines
execute of dbms_output
close of dbms_output
XCTEND (commit)
close of insert


That would seem to indicate that the commit is separate from the execution of the insert since there is a call to dbms_output in between there ( I always have serveroutput on unless I explicitly turn it off)



This is the script I used to see this:




connect /

/*
drop table t;
create table t(x int);
*/


set autocommit on
variable x number
exec :x := 1;
alter session set sql_trace=true;
insert into t values ( :x );
insert into t values ( :x );
insert into t values ( :x );


column trace new_val TRACE
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  from v$process a, v$session b, v$parameter c, v$instance d
 where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'
/
disconnect
edit &TRACE
connect /



run it more than once (once with tables getting created, again with them commented out) so you get rid of all of the recursive SQL. You should see the same.


SQL*Plus and autocommit, unsolicited input

Mark Williams, October 24, 2011 - 7:12 pm UTC

Please excuse my unsolicited input beyond what Tom has already posted.

One point that might be interesting to consider is that in SQL*Plus the "set autocommit" is not binary. That is, it can be more than just on or off. You can issue a "set autocommit 42", for example, to issue a commit every 42nd successful insert, update, delete or PL/SQL block operation. Clearly in this case the OCI_COMMIT_ON_SUCCESS mode for OCIStmtExecute would not work as expected.

Also, I think Tom's statement from the 2000 response quoted on the other link is still accurate: the commit is determined by the client. If OCI_COMMIT_ON_SUCCESS mode is used, it is still the client that must set it. The database server has no "run in auto commit" mode. Sure, you could emulate it in an application (client), but, strictly speaking, that's not a server thing.

Also, you can see that SQL*Plus imports the OCITransCommit OCI function:

C:\Oracle\11.2\database\BIN>link /dump /imports:oci.dll sqlplus.exe | findstr /c:"OCITransCommit"
311 OCITransCommit


There's probably a couple of ways to prove it out (other than what Tom has posted above) but I have not taken the time (yet anyway). I'm thinking about measuring network roundtrips and/or sqlnet trace...

Regards,

Mark

autocommit

Biju George, January 16, 2012 - 3:25 am UTC

We recently faced an issue similar to autocommit on. We call a procedure inside a shell script. Sample code --
procedure myproc
( insert into table select * from table;
commit;
exception
end;
shell script --
#!/bin/ksh
echo "Calling Procedure on $(date)"
proc_run="myproc"
exec_sql_dbproc $proc_run
if [ $? -ne 0 ]; then
print error message into log file
exit 1
fi
The problem here is we are not getting the exit status from the procedure execution but the data is visible in the table from another session, i.e. the commit statement inside procedure was executed (assuming). It seems the procedure execution got hanged but if commit was done as we can see the data, then how can the procedure get hanged. If its not hanged then why don't we receive the exit status. We call this procedure multiple times for different inputs. This happens after the 3rd or 4th call of the same. It successfully exits for the first 2 or 3 runs.
Tom Kyte
January 17, 2012 - 1:56 pm UTC

no clue what exec_sql_dbproc is or does. It is rare that google cannot find a string, and it cannot find that one (it will soon though, when it reindexes this page :) )

the 'plsql' you supplied isn't plsql, don't know what it is exactly.


autocommit

biju george, February 02, 2012 - 4:45 am UTC

Apoligies being dummy :-)
exec_sql_dbproc $proc_run
exec_sql_dbproc is a shell script function which connects to the DB and runs an Oracle procedure. it takes the oracle procedure name as parameter. in our case it seems as though even after completion of the insert and commit, the control never got back to unix...i.e. there was no clean exit with exit status as 0...i was sort of giving a psuedo code...:-D sorry to bother...
Tom Kyte
February 02, 2012 - 7:54 am UTC

I'd say there is a bug in exec_sql_dbproc then - it is not actually exiting sqlplus, sqlplus is still active waiting for an "exit" or end of file and exec_sql_dbproc didn't do that.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library