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
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
Oh yes, thanks for pointing this out, the row is committed even if the ddl fails, madness!
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
Pingback: Committing Transaction « Kamran Agayev’s Oracle Blog
Pingback: committing transaction | Oracle