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!
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
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