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!!!!
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?
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.
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.
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?
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?
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
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'...
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
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 ..
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?
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.
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...
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.