The logical work piece which consist two or more statements is called as a transaction. The transactions are started by reading or writing a data. It is ended by COMMIT or ROLLBACK of the statements. The commits which are performed first by the DDL statements are called as implicit commits. Multiversion concurrency and transaction locking are used by the Oracle for undoing the records. The record undoing by the Oracle ensure the serializability of transactions and upholds data consistency.

• Tuning SQL Index

Transaction Properties

All database transactions will exhibit ACID properties. The ACID is the acronym of following properties shown below

• Atomicity - Either none or complete transaction occurs
• Consistency - The database will be moved from one consistent level to another.
• Isolation - The database transaction effect won’t be visible until the completion of the transaction.
• Durability - Changes made in the database are recorded in the redo log files after the commitment of transaction.

Transaction Concurrent Control

Data Consistency in Oracle is ensured by data locking. Data locking in Oracle is done using the restrictive fashion which maintains the most possible data concurrency. Concurrency problem in transaction can be classified as the following

• Dirty Reads
• Phantom Reads
• Lost Updates
• Non-Repeatable Reads

Dirty Reads

Dirty read problem in a transaction occur when a transaction reads data from a non-committed transaction. Dirty read is concurrency problem because non-committed transaction may be rollbacked after the transaction failure.

• SQL Three Valued Logic

Phantom Reads

Phantom read concurrency problem are caused by new data appearance between databases which are indulging in transaction.

Lost Updates

Lost update occur when a transaction reads a data while it is being updated by another database transaction.

Non- Repeatable Reads

Non-repeatable read in a database transaction occur due to the fuzzy reading of the modified data by another transaction. These fuzzy reads cannot be read again so it is called as non-repeatable reads. The data will be different for every read in the database transaction.

Concurrency problem in database can be avoided by serialization of database transactions. The database serialization of Oracle is done by various data isolation and management.

• Related: SQL & .NET Interview questions and Answers

Isolation Levels in Oracle

The data present in Oracle database are isolated by various database locks and multi level concurrency. Data locking and escalation will automatically place the lock on the particular table or column. The locks will hold until the commitment or rollback of the transaction.

Database Locking in Oracle

Locks used in Oracle are classified according to the information provided by the tables

DML Locks

DML locks are the row level locks used in Oracle to protect a row present in a table while it is being updated. DML locks won’t prevent any reader from accessing the data. But altering the data present in the row during the transaction is not allowed.

DDL Locks

DDL locks are exclusive row level locks which are used to prevent modifications in the row. DDL locks are mostly used during the transaction of DML.

Latches

Latches can be defined as the memory structure which protects the SGA. The processes which can access memory areas are controlled by latches.

Internal Locks

Oracle uses internal locks to protect structures like rollback segments, data files and tablespaces.

Distributed Locks

This specialized locking mechanism is used to protect distributed systems.

Blocking Locks

Blocking locks are the locks used by a user to place lock on a particular object. This lock is used to prevent other users from accessing certain object or data.

• Recommended: Final year Academic projects download

Simple Oracle Transaction Steps

A simple Oracle transaction is done by following the steps given below

• Requesting connection to Oracle database
• Dedicated server process will be followed after the user request
• Statement execution and table insertion inside the database are done by the user.
• User privileges are cross checked by the database
• The statement provided by the user is parsed. There two types of parsing used namely soft parsing and hard parsing.
• Soft parsing is done in library cache while hard parsing is done at disk level.
• A private SQL area is created for every user session
• Presence of buffer cache in the data is checked by the Oracle
• Row level locks are applied to the database according to the requirement.
• Vectors are changed and log buffers are redone by the Oracle.
• Buffer cache in the rows will be modified by the Oracle
• Transaction will be committed and row level locks will be released automatically.
• Changes made in log buffers and log files are now permanent. The log writer will record the changes made in the database.
• Transaction process is now successfully completed and the information is displayed to the user.