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