Oracle Transaction Management

Oracle Transaction Management

A transaction contains one or more SQL statements. It is a logical unit of work (atomic unit ).
The SQL statement that starts the first execution of a transaction and ends with a Commit, Rollback, or DDL statement.
Note: Here, Commit and Rollback are displayed Commit transactions, while DDL statements are implicitly committed transactions. The DDL statement operation is

There is no way to roll back.
##########################
Eg:
SQL> create table a (I int );

The table has been created.

SQL> insert into a values (1 );

One row has been created.

SQL> create table B (I int );

The table has been created.

SQL> rollback;

Rollback completed.

SQL> select * from;

I
----------
1
#############################
The transaction is committed when you execute create table B.
Where the transaction ends:
1>. Execute Commit and Rollback. savepoint is not used.
2>. Execute DDL operations such as create, drop, rename, and alter.
3>. Disconnect from Oracle and the transaction will be automatically committed.
4>. The user process terminates abnormally and the current transaction is rolled back.

Note: The submission or Rollback information that must be displayed before the application is terminated ).

Commit has performed the following operations on Oracle:
1>. The internal transaction table associated with the UNDO tablespace records that the transaction has been committed, and a unique system transaction number (SCN) is generated and saved to the table.

.
2>. The LGWR process writes the redo log in SGA to the redo log file. Of course, it also needs to write the SCN to the redo log file.
3>. Oracle releases the rows in the locked table.
4>. Oracle sets the transaction to complete.

Note: The changed data (saved in SGA) before the Commit operation is not immediately written to the data file. This is also intended for Data

Library is more efficient. This is also the case from the developer's perspective, which can reduce the number of writes to the disk for many small transactions.

Transaction-related Oracle 10.2:
Commit work write immediate wait; -- it is the default setting of Oracle.
Alter system set commit_write = nowait; -- change the system submission method
Alter session set commit_write = nowait; -- change the session submission method.

##############################
Commit a transaction. For example:
SQL> commit work;

Submitted.

SQL> show autocommit;
Autocommit OFF
SQL> create table t0 (testcol number );

The table has been created.

SQL> insert into t0 values (1 );

One row has been created.

SQL> commit;

Submitted.

SQL> select * from t0;

Testcol
----------
1

SQL> insert into t0 values (2 );

One row has been created.

SQL> commit work;

Submitted.

SQL> select * from t0;

Testcol
----------
1
2

####################################
Commit comment eg:

See more: http://www.psoug.org/reference/transaction.html