Oracle Transactions

A transaction is logical piece of work consisting of one or more SQL statements. A transaction is started whenever data is read or written and they are ended by a COMMIT or ROLLBACK. DDL statements always perform a commit first this is called an implicit commit this is because the user did not issue the commit.

Oracle uses transaction locking and multiversion concurrency control using undo records to ensure serializability in transactions, this stops any user conflicts while ensuring database consistency.

Transaction Properties

Database transactions should exhibit attributes described by the ACID properties:

Atomicity - A transaction either happens completely, or none of it happens
Consistency - A transaction takes the database from consistent state to the next
Isolation - The effects of a transaction may not be visible to other transaction until the transaction has committed.
Durability - Once the transaction is committed, it is permanent all changes are written to the redo log first then the data files.

Transaction Concurrent Control

Oracle uses locking to ensure data consistency but the locking is done via the least restrictive fashion, with the goal of maintaining the maximum amount of concurrency.

Concurrency problems can be any of the following

Dirty Reads	Occurs when a transaction reads data that has been updated by an ongoing transaction but has not been committed permanently to the database, it is possible that the transaction may be rolled back.
Phantom Reads	Are caused by the appearance of new data in between two database operations in a transaction.
Lost Updates	Is caused by transactions trying to read data while it is being updated by other transaction.
Non-Repeatable Reads	When a transaction finds data that it has read previously has been modified by some other transaction, you have a non-repeatable-read or fuzzy read. Basically when you read data at one time and its different when you read it again.

To overcome the above problems you could serialize all the transactions making sure that data is consistent, however this does not scale well. Oracle serializes the transaction via isolation levels and the management of undo data.

Isolation Levels

The main isolation levels are the following

Serializable	Then transaction will lock all the tables it is accessing to prevent other transactions updating data until it either rollbacks or commits
Repeatable Read	A transaction that reads the data twice from a table at different points in time will find the same values each time. Both dirty reads and non-repeatable are avoided with this level of isolation.
Read uncommitted	Allows a transaction to read another transaction's immediate value before it commits
Read committed	Guarantees that the row data won't change while you're accessing a particular row in a table.

Oracle uses locks and multiversion concurrency control system, it uses row-level locking (it never uses lock escalation), it will automatically place the lock for you and store the lock information in the data block, locks are held until the transaction is either committed or rolled back. Multiversion concurrency is a timestamp approach to read the original data, oracle will write the original data to a undo record in the undo tablespace, queries then have a consistent view of the data which provide read consistency- they only see data from a single point in time, for more information see Oracle locking.

Oracle Locks

There are a number of different locks in Oracle and tables that can obtain information regarding locks.

DML Locks	Oracle uses row-level locks, this is to protect the row while its being changed, the lock will never block a reader of the same row. A table lock is also placed but this ensures that no DDL is used on the table.
DDL Locks	When changing table attributes Oracle places a exclusive lock on the table to prevent any modifications to the rows. This lock is also used during DML transactions to make sure the table is not changed when changing or inserting data.
Latches	Latches protect the memory structure with the SGA, they control the processes that access the memory area's.
Internal Locks	Are used by oracle to protect access to structures such as data files, tablespaces and rollback segments.
Distributed Locks	Are specialized locking mechanisms used in distributed systems.
Blocking Locks	Occurs when a lock is placed on an object by a user to prevent other users accessing the same object.
DeadLocks	Occurs when two sessions block each other while each waits for a resource that the other session is holding. Oracle always steps in to resolve the issue by killing one of the sessions, check the alert.log for deadlocks.
Useful Views
DBA_LOCK	lists all locks or latches held in the database, and all outstanding requests for a lock or latch
DBA_WAITERS	shows all the sessions that are waiting for a lock
DBA_BLOCKERS	displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting
V$LOCK	This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch
V$SESSION	This view lists session information for each current session

See Oracle Locking for more information.

Integrity Constraints and Transaction

See constraints for more information about deferable and immediate constraints.

Autonomous Transactions

You can create a transaction within a transaction that will rollback independently of its parent transaction. They can be used in the following

top-level anonymous blocks
local (a procedure in a procedure), stand-alone or packaged functions and procedures
methods of object types
database triggers

example

create or replace procedure autonomous_example
as
   pragma autonomous_transaction;
begin
   insert into test_table ('Autonomous insert');
   commit;
end;
/

The pragma directive tells oracle that this is a new autonomous transaction and that it is independent from its parent.

A trigger cannot contain a commit or rollback statement, however by using autonomous transactions you can overcome this limitation, it is considered bad practice but it is possible.

Trigger with a commit

create table tab1 (col1 number);
create table log (timestamp date, operation varchar2(2000));

create trigger test_trig
after insert on tab1
begin
insert into log values (SYSDATE, 'Insert and commit via trigger');
commit;
end;
/

SQL> insert into tab1 values (1);
insert into tab1 values (1)
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "TEST01.TEST_TRIG", line 3
ORA-04088: error during execution of trigger 'TEST01.TEST_TRIG'

Overcome commit problem

create or replace trigger test_trig
  after insert on tab1
  declare
  pragma autonomous_transaction;
begin
  insert into log values (SYSDATE, 'Insert and commit using autonomous transaction');
  commit; -- only allowed in autonomous triggers
end;
/

Undo Data

Undo data provides read consistency, there are two ways to control the undo manual or automatic. see undo data for more details

Oracle Transaction

Simple Oracle transaction

User requests a connection to oracle
A new dedicated server process is started for the user
User executes a statement to insert data in to a table
Oracle checks the users privileges, it first checks the library cache (cache hit) for the information and if not found retrieve it from disk.
Check to see if the SQL statement has been parsed before (library cache) if it has then this is called a soft parse, otherwise the code has to be compiled a hard parse.
Oracle creates a private SQL area in the users session's PGA
Oracle checks to see if the data is in the buffer cache, otherwise perform a read from the data file
Oracle will then apply row-level locks where needed to prevent others changing the row (select statements are still allowed on the row)
Oracle then writes the change vectors to the redo log buffer
Oracle then modifies the row in the data buffer cache
The user commits the transaction making it permanent, the row-level locks are released
The log writer process immediately writes out the changed data in the redo log buffers to the online redo log files, in other words the change is now recoverable.
Oracle informs the user process that the transaction was completed successfully
It may be sometime before the data buffer cache writes out the change to the data files.

Note: if the users transaction was an update then the before update row would have been written to the undo buffer cache, this would be used if the user rolls back the change of if another user run's a select on that data before the new update was committed.