committing transaction

When and how do you commit a transaction?

SQL> insert into lsc_t(x) values(1);         ... (1) not committed
SQL> commit;                                 ... (1) committed

SQL> insert into lsc_t(x) values(2);         ... (2) not committed
SQL> alter table lsc_t disable primary key;  ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3);         ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4);         ... (4) not committed
SQL> disc                                    ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed after before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?


SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
   PID TTY      TIME CMD
 13903 pts/33   0:00 ksh
 22384 pts/33   0:00 sqlplus

SQL> !kill 22384
Terminated

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
– A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
– A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
– A user disconnects from Oracle Database. The current transaction is committed.
– A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION

Please read On Implicit Commit

5 thoughts on “committing transaction”

  1. Hi Laurent

    I would like to precise your statement about implicit commit when executing a DDL statement.

    In fact Row 2 (from your example) is committed before the alter table statement is executed. If you execute a DDL statement, it is always surrounded with a pair of implicit commit statements.

    More details can be found in an article from Tom Kyte http://www.dbazine.com/oracle/or-articles/kyte2 in the chapter DDL Locks.

    Regards
    Dani

  2. Oh yes, thanks for pointing this out, the row is committed even if the ddl fails, madness!

    
    SQL> insert into lsc_t(x) values (5);             
      --> (5) not committed
    
    1 row created.
    
    SQL> create table tttt(x number) tablespace system;
    create table tttt(x number) tablespace system
    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'SYSTEM' 
      --> (5) committed

  3. Hi Laurent

    Another way to commit:
    exec dbms_stats.gather_table_stats(user,’T’);

    The documentation of dbms_stats package says:
    Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.

    So don’t gather stats on temporary tables (on commit delete rows) :-)

    Regards,
    Stefan

  4. Pingback: committing transaction | Oracle

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>