Transactions in Oracle Database

Let us understand how user queries are handled in an Oracle Database, what all the processes involved in it are. A transaction involves a specific set of queries, where all the queries need to be implemented or all should be rolled back. The following are the properties of transactions:

1. Atomicity: It means that all the statements in the transactions should be performed together or none at all.
2. Consistency: The database needs to maintain the consistency of the data. For example, if a delete statement is issued to remove an employee who is also a manager and has any dependency in the emp table for the manager id column then the database should not permit this due to referential integrity.
3. Isolation: This means that even if there are multiple statements executing, they must execute in isolation of each other. Isolation makes query execution look serial. We will explain it in details later in the article.
4. Durability: This property helps in database recovery, this property ensures that the committed transactions are saved permanently, this is done by Oracle using the write-ahead protocol.

The transaction details can be viewed via "V$TRANSACTION" view. All SQL queries or PL/SQL operations involves some form of DML operations like modify, insert or data deletion. In Oracle the transaction process is designed such that the data consistence is maintained.

The following example will help us get more clarification on the topic:

1. The Oracle user tries to connect to the Oracle server using Oracle Net Services.
2. User is authenticated for its role and access to DB.
3. Thereafter, a dedicated server process is started and allocated to the user process.
4. User executes say, any "DML" statement on the DB ("SELECT", "INSERT", "UPDATE" or "DELETE").
5. At this point Oracle first looks for the user privileges on the elements of the DB for which the query is about to be executed. If the privilege information is absent from the library cache then it will read from the disk into the cache area.
6. If the privileges are satisfactory then Oracle moves on to look for an already present execution plan in the shared pool, if its present it is picked up or else the Oracle parses and passed on to the query optimizer.
7. The query optimizer, guided by the user provided hints, compares various plans generated and chooses the good plan for timely completion if not the best plan. It estimates the cost using the cardinality of the table.
8. Further, the Oracle allocated space in user PGA as a private SQL area.
9. Moreover, it now checks for the required data in the data buffer cache. If it's a miss then the server process reads the data form the datafiles kept into the DB buffer cache.
10. Now, the Oracle process will apply row level / table locks except for the select statement only. This is to avoid simultaneous manipulation of the same rows.
11. The change making statements are recorded into the redo log buffer by the server process.
12. The data in the DB buffer cache is changed into the new value for INSERT, UPDATE and DELETE statements by the server process, these manipulated bits are called dirty bits.
13. Finally, the user commits the transaction (except SELECT) to make the manipulation permanent, thus releasing the lock for workable rows.
14. On data commit, a checkpoint is done that directs the background process of the log writer LGWR and database writer DBWR.
15. At this the Log Writer becomes effective and flushes the redo log buffer into the online redo log files on disk.
16. The Database Writer flushes the dirty bytes form the database buffer to the datafiles, making it a permanent change.
17. The control files and the headers of the datafiles are updated with the SCN generated for the transaction.
18. Furthermore, the successful completion message will be displayed like Transaction over or commit complete to the user, or of failure otherwise.

Let us look further into the concurrency control of transactions.

The data concurrency means simultaneous access of consistent data to multiple users, allowing DML by several users on the same table that are needed to be complete without interference and inconsistency. For this, Oracle uses the lock mechanism but in a restricted fashion only to maintain the maximum amount of concurrency. Concurrency with it brings several problems too, as explained below:

1. The Phantom-Read Problem: It's the occurrence of extra data between two database operations in transactions.
2. The Dirty Read Problem: This occurs when a transaction reads data from an ongoing transactions that is yet to commit the transaction. This creates error reads if the updated data is rolled back. This problem can simply be avoided by using the lock mechanism.
3. The Fuzzy Read problem: This problem occurs due to non-repeatable reads, which means the data is changed from the last read done by the transaction. This happens if any other process is committing the changes on the rows that are being read simultaneously by another process.
4. The Lost Update Problem: This problem is caused by allowing simultaneous read and update by multiple transactions. For example, a transaction is reading the data from the table, while another transaction updates the data and commits successfully. Now, the former transactions further updates the data as read, that overrides the later committed data. This can cause loss of intermediate update data, known as lost update problem.

SERIALIZATION WITH SCHEDULING

Inconsistency in data occurs only from concurrent access of the tables in a database. If all transactions occur in total isolation then there will never be any rudimentary data present in the tables, but this will drastically degrade the performance of the database. To overcome the above problem the database uses the concept of a schedule, that is a sequence of operations on a table from one or more transactions and this is known as a serializable schedule. Such schedule consists of operations from multiple transactions that are scheduled one after the other so that the outcome is the consistent database.

ISO TRANSACTION STANDARDS

The ISO standard for transactions includes the following points:

1. All transactions must begin with the statements of "SELECT", "INSERT", "UPDATE" or "DELETE".
2. One transaction cannot use another transaction's intermediate results.
3. All transactions must end with "COMMIT" or "ROLLBACK".
4. One transaction can view the result of another transaction only after its completion.

The ISO standard for a transaction is to avoid the concurrency problems listed above using atomicity and isolation. A database that was consistent before the transaction must remain consistent even after the completion of the transaction. High concurrency increases the risk of anomalies in the data and attempts to reduce the anomaly, reduces performance.

Stages of SQL processing

Oracle automatically opens cursors for all SQL processing and allocates a private SQL area to store parsed statements and information. The following procedure explains this in detail.

1. PARSING

1. The syntax of the SQL statements are checked, the tables are looked in to the data dictionary.
2. User privileges to perform the requested operation on the provided tables are verified.
3. A SQL hashid is allocated to the processing SQL statement. Similar statements generate a similar hash value. If the SQL statement is not being executed for the first time then the Oracle processes look for the hash value and picks up the execution plan already created for the hash value. This is called SOFT PARSING.
4. If the hash value is not present for the SQL, the Oracle process must create a fresh execution plan. For this library cache and data dictionary cache are hit repeatedly to evaluate numerous possibilities. Oracle uses the latch mechanism, that is a low line serialization control, to protect the shared data structure in SGA and thus HARD PARSING contributes to an increase of latch contention. Too much hard parsing also causes fragmentation of the shared pool.
5. After soft or hard parsing, Oracle processes draw an execution plan and allots a shared SQL area for processing.

2. BINDING

The variables defined in the query are assigned the user provided values during this phase.

3. EXECUTION

In this stage the data from the database is read into the data buffer. The changes made during execution are logged into the redo log. Further Oracle closes the cursor after execution.

Isolation levels of Oracle database

We read about isolation as one of the properties of transactions. Now, let us explain about various levels of isolation in an Oracle database.

1. Serialization: It brings complete isolation of transactions from one another. This is done by using write logs on the table or set of data on which DML is to be executed. It further prevents the read operation on dirty bits, moreover it helps to overcome the problem of Phantom data read due to insert in the data block of read transaction.
    
2. Repeatable Read: This isolation level helps avoid two problems at one go, namely: Dirty Read Problem and Non Repeatable Read Problem, by guaranteeing read consistency. This means that if a similar query executes at two different points in time, it will return a similar result each time.
    
3. Read Committed: This the default level of isolation for an Oracle database, it means Oracle transactions view the data that were committed at the beginning of these transactions. In this way it guarantees that the data will not change during the transaction lifetime.
    
4. Read Uncommitted: As the name suggests at this level of isolation one transaction can read data from the intermediate result of another transaction even before it commits, leading to all the problems of concurrent usage.

CHANGING THE DEFAULT ISOLATION LEVEL

The default level of isolation is query level read consistency, this level provides protection against dirty reads and the lost update problem since data is read only after the commit statement is executed. Oracle allows us to change the default read level isolation by an alternative serializable isolation level. Using an alter session statement, we can change the level of isolation to serialize the isolation level, thus avoiding the data anomalies caused by multiple user access and modification at the same time. This level of isolation treats the database as a single user database, eliminating the concurrency problem.

SQL> ALTER SESSION SET ISOLATION LEVEL SERIALIZABLE;
SQL> ALTER SESSION SET TRANSACTION READ ONLY; (Helps avoid phantom read)

Serialization of isolation level reduces concurrency of the database and leads to the following problems:

1. Transaction concurrency is highly compromised since data access are exclusive for the transactions. The Locking mechanism is used for transaction access
   control.
2. Serialization isolation level has lower throughput than read committed isolation level, for high concurrency databases.
3. By using in it a TRANS parameter we can set the default number of concurrent transactions on a table.
4. Serialization makes transactions stuck in a wait state for each other to release logs over data objects leading to deadlocks. This also causes costly rollbacks of transactions.
5. To use a serializable mode of isolation we need to incorporate error checking code in the application.