committing transaction

Transaction Management

When and how do you commit a transaction?

SQL> insert into lsc_t(x) values(1);         ... (1) not committedSQL> commit;                                 ... (1) committedSQL> insert into lsc_t(x) values(2);         ... (2) not committedSQL> alter table lsc_t disable primary key;  ... (2) committedSQL> set autocommit ONSQL> insert into lsc_t(x) values(3);         ... (3) committedSQL> set autocommit OFFSQL> insert into lsc_t(x) values(4);         ... (4) not committedSQL> 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 sqlplusSQL> !kill 22384Terminated

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 …

http://laurentschneider.com/wordpress/2009/06/committing-transaction.html