This discussion is archived
11 Replies Latest reply: Jul 10, 2009 8:07 AM by Sven W. RSS

auto commit issue

absriram Newbie
Currently Being Moderated
Hi All,
I am facing some issue with my database. I am not sure how to avoid this.
Database is 10g.

I created a table and inserts some data into that and exit the session with out any commit or rollback and logged in again all the rows I was inserted committed automatically even my set autocommit off. Please help me. This makes my DB as inconsistency.

see the below statements:
=======================
SQL> set autocommit off
SQL> create table a(a number);

Table created.

SQL> insert into a values(1);

1 row created.

SQL> insert into a values(2);

1 row created.

SQL> exit
=======================
Again logged in and checked:-
SQL> select * from a;

A
----------
1
2
  • 1. Re: auto commit issue
    Walter Fernández Expert
    Currently Being Moderated
    Hi,

    When you log off all uncommited changes are commited.

    Regards,
  • 2. Re: auto commit issue
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you are exiting normally, why wouldn't you issue a rollback before exiting?

    Since you cannot disconnect with an open transaction, the application has to determine how to end the transaction if it is to log off cleanly. That means the application has to choose whether to commit or whether to roll back your transaction. SQL*Plus chooses to commit. Other apps choose to roll back. Other apps will refuse to exit until you specify how to terminate the transaction. If an application terminates abnormally, Oracle will roll back the transaction.

    If you don't want to be stuck with the vagaries of your application's decision on this point, you need to explicitly determine whether to commit or roll back before exiting the application.

    Justin
  • 3. Re: auto commit issue
    absriram Newbie
    Currently Being Moderated
    Hi Justin,
    Thank you for your response.
    My system faced some issue and hanged so I restarted and found all are committed.
    Is there any way to make it rollback even if I clean exit or abnormal exit.

    Bhargava S Akula.
  • 4. Re: auto commit issue
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you exit abnormally, SQL*Plus won't commit (and won't have a chance to log off). Your transaction would be killed and rolled back automatically by PMON in the database at some point after the client process failed.

    You only have problems if you exit cleanly.

    Justin
  • 5. Re: auto commit issue
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Is there any way to make it rollback even if I clean exit or abnormal exit.
    What exits? The client application.

    Who makes the decision to commit or rollback? The client.

    So as Justin clearly explained, this is client issue and not an Oracle issue.

    What Oracle will do, is perform a rollback when the client suddenly disconnects (e.g. client crashes and the crash tears up the network connection to Oracle).

    However, when the client does not crash, and you exit the client, the client decides to send a commit.. or a rollback. Oracle gets a commit or a rollback from the client. Oracle does not make the decision - the client does when it exists. And the client explicitly tells Oracle to commit or rollback.

    How can you now expect Oracle to ignore that explicit instruction from the client - just because the client decided to send that instruction and you did not tell the client too?

    Oracle does not know what you told the client to do or not to do. All it gets is a commit or rollback from the client, and the client then cleanly disconnecting.

    Oracle's behaviour in this aspect is 100% correct and expected.

    You need to look at the client and your usage and interaction with the client if you if you have a problem in this regard.
  • 6. Re: auto commit issue
    MichaelS Guru
    Currently Being Moderated
    Is there any way to make it rollback even if I clean exit or abnormal exit.
    please study the exit command of sql*plus and it's options intensively ;)
  • 7. Re: auto commit issue
    absriram Newbie
    Currently Being Moderated
    Hi Billy,
    I agree with both you and Justin and oracle behaviour too :)
    Nothing is impossible in this virtual world. I am requesting you guys is there any option or any scipt that we can write for which even a clean exit should do only ROLLBACK.
    If I use any TOAD or any such client is it the same behaviour by oracle or can change??
    Bhargava S Akula.
  • 8. Re: auto commit issue
    RobvanWijk Oracle ACE
    Currently Being Moderated
    I think you are misinterpreting the autocommit setting in SQL*Plus

    As you can read in the documentation here: http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch12040.htm#SQPUG063


    +SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}+

    Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.

    ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executes n successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.

    SET AUTOCOMMIT does not alter the commit behavior when SQLPlus exits. Any uncommitted data is committed by default.*+



    So the autocommit does not control whether Oracle will commit or rollback when SQL*Plus exits, it controls whether each individual statement is automatically committed.
    SQL> create table a (a int);
    
    Table created.
    
    SQL> set autocommit on
    SQL> insert into a values (1);
    
    1 row created.
    
    Commit complete.
    SQL> rollback;
    
    Rollback complete.
    
    SQL> select * from a;
    
             A
    ----------
             1
    
    1 row selected.
    Regards,
    Rob.
  • 9. Re: auto commit issue
    26741 Oracle ACE
    Currently Being Moderated
    Do not use AUTOCOMMIT.

    Hard code an "EXIT ROLLBACK" at the end of that specific SQL script.
  • 10. Re: auto commit issue
    absriram Newbie
    Currently Being Moderated
    Hi Hemant,
    My autocommit is always off.
    I am writing some script for script and putting exit rollback in that this is some thing feasible.
    Bhargava S Akula.
  • 11. Re: auto commit issue
    Sven W. Guru
    Currently Being Moderated
    absriram wrote:
    Hi Hemant,
    My autocommit is always off.
    I am writing some script for script and putting exit rollback in that this is some thing feasible.
    Bhargava S Akula.
    If you read carefully thorugh all the previous answers then you might find that this will not solve your problem.

    If you run a script and it finishes without an error why would you ever want to do a rollback? In that case it would have been better not to run the script in the first place.

    If however you get an error then the script would end abnormally or you could tell sql*plus to end abnormally (WHENEVER SQLERROR command). In this case a rollback does take place (usually, you can configure it otherwise using WHENEVER SQLERROR COMMIT).

    I still think you're somehow on the wrong track with the solution to exit every script with an error.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points