In this Document

Applies to:

Purpose

Transaction Management is one of most important properties for a database to maintain Data concurrency and consistency. The purpose of this document is to give an overview of how Oracle Transaction Management works.

Details

Concepts:

In a database which operates with a single user, the management of transactions is simple as only one user accesses the database but in a multi-user environment, there is high chance that transactions from different users update the same data. Therefore, the database must provide 2 important features "Data concurrency" and "Data consistency".

Oracle database maintains "Data concurrency" through "transaction isolation models".

Oracle database maintains "Data consistency" through "multiversion consistency model", "locks" and "transactions".

Transactions

A transaction in an Oracle database is a logical unit of work which comprises of a single or multiple SQL statements. These sql statements together complete the transaction, either by committing(which means the changes are permanent) or by rolling back(no changes to the database).

Similar to other relational model databases, Oracle also follows the basic properties of a transaction below, widely referred to as ACID properties:

Atomicity - There wouldn't be any partial transactions in the database and it follows the "all or none" approach, where in all the changes in a transaction would either be applied or rolled back.

Consistency - The transaction moves the database from one consistent state to another. It means the data changes made by the transaction are consistent with each other.

Isolation - The transaction will work as an isolated unit and all other transactions will not see any changes from this transaction until the changes are committed.

Durability - We see that the changes made by committed transactions are permanent.

There are also different types of transactions called autonomous and distributed transactions.

Note 100664.1 - Master Note for Troubleshooting Oracle Managed Distributed Transactions

Note 13229.1 - Distributed Database, Transactions and Two Phase Commit 

Multiversion consistency model

This is the feature of the Oracle database where it keeps multiple versions of the same data so that multiple users can see different versions to maintain consistency, depending on when their transaction started. The main attributes of this multiversion consistency model are "Statement-Level Read Consistency" and "Transaction-Level Read Consistency".

A statement-level read consistency ensures that a statement is always consistent with respect to the point in time when the statement actually started. It doesn't show the data that is modified after the statement started executing.

A transaction-level read consistency ensures that each statement in that transaction actually sees the data that is consistent with respect to the point in time when the transaction actually started.

Oracle achieves this multiversion consistency model by maintaining read-consistent images of data when multiple transactions concurrently access the same rows/tables. Oracle maintains these images in undo segments and the undo entries in these segments are referred as undo data. With the help of this undo data, read-consistency can be achieved in both single instance and RAC systems.

In a RAC system, as a block can be accessed by any of the available instances, RAC software uses a special mechanism called cache fusion to transfer read-consistent images of blocks. You can refer to the documention below for more information on cache fusion.

The large objects (LOB) is a special data type in the Oracle database that helps in maintaining large amounts of "unstructured" data in binary or character format. For a LOB, the database stores the before images in the LOB segment itself rather than in the undo data(for normal datatypes). You can refer to the below documentation for an overview of LOBs.

Locks

Locks allow multiple users to access the database simultaneously and ensures that no other user is modifying and committing the same data that is currently being modified. Typically, the reads never block writers and writers never block readers(except in the case of pending distributed transactions).

In Oracle typically there are 2 types of locks: exclusive locks and shared locks. An exclusive lock is obtained when a transaction modifies the data and a shared lock is obtained when a transaction reads the particular data. A shared lock can be acquired on a single resource(row or table) by multiple users, but only one exclusive lock can be acquired on a single resource at any point in time.

The locks are acquired automatically by the database instance and there is no need to set them explicitly while executing statements. Oracle will also convert one lock type to another depending on the situation when required. If a user holds a shared lock on a particular row of a table and later he modifies the row, the lock will automatically be converted from row share lock to row exclusive lock. If a user holds a row level lock on several rows of a particular table and no explicit lock at the table level, the number of locks on this particular table increases and so some databases will raise the locking level from row level to table level. This is called lock escalation and due to this the number of locks decreases(as there is a single lock on the entire table rather than multiple locks at row level) but the restrictiveness increases as now the entire table is locked. Oracle never does lock escalation as it increases the possibility of deadlocks.

A deadlock is a situation where 2 users are trying to acquire a lock on the data that is being modified by each other. This deadlock will automatically be detected by Oracle and it rolls back the statement from any of the transactions involved in the deadlock. The transaction which identifies the deadlock first is the one whose statement gets rolled back. Please note that there will be statement level rollback only so the transaction remains active and the changes made before this statement will still be there.

The transaction holds the lock placed on a resource till it ends or till some event occurs which makes the transaction no longer requirie the lock. If you are rolling back to a previous save point, the locks that were placed after this savepoint will also be released.

See the following note for more information on locking mechanisms:

Transaction isolation models

These models ensure that multiple users can access the data simultaneously but based on different isolation models being used, the throughput of the transaction depends. Below are the 4 types of transaction isolation levels as defined by the ANSI and ISO/IEC:

Read uncommitted - In this isolation level, the statements inside a transaction read uncommitted data from another transaction.

Read committed - Statements from a transaction read only committed data with respect to the starting point of a statement. Read consistency is maintained at the statement level.

Repeatable read - If the same statement is executed again inside a transaction, it will see the same data it has seen in the previous run even if the data was modified and committed by another transaction.

Serializable - In this mode, the statements inside the transaction see only the committed data with respect to the starting point of the transaction(not at the statement level unlike in read committed). Read consistency is maintained at the transaction level.

Oracle database provides Read committed and Serializable isolation levels with "Read committed" as the default. In addition, Oracle database also provides another isolation level - Read-only isolation level, which is similar to the Serializable level but doesn't allow DML statements in the transaction(except for SYS).

These isolation levels can be set at the session level using the "SET TRANSACTION..." command

Getting Started:

A transaction starts when a DML/DDL or a SET TRANSACTION statement is fired. When a transaction is started, Oracle allocates a transaction ID to that transaction and also assigns it to an undo segment and a transaction table slot in the undo segment. A transaction table which is also called Interested Transaction List(ITL) is a table which is stored in the block header. This table stores the information about transactions, like which transactions placed locks in this block and which rows have committed and uncommitted data. The transaction table contains transaction ids which is a combination of undo segment number, slot, and sequence number. Once a transaction is started, you can query the v$transaction table to query the status of the transaction:

When a statement inside a transaction is in progress and it was stopped or terminated in between, Oracle ensures that all changes that were made by this statement are rolled back. The changes that were made by the previous statements were not rolled back but only the changes made by this statement were rolled back. This is called statement-level atomocity. All dependant actions that were done as part of this statement were also rolled back. Say for example, you have a trigger which inserts some data into another table when this statement is run, those changes will also be undone from the database if this statement fails. The transaction would still be active.

To know until what point it has to rollback, Oracle uses an internal logical timestamp called SCN. SCN plays a vital role in database operations and SCNs order events that occur in the database, which are necessary to satisfy ACID properties. The SCN will be used by Oracle to query and track the changes. So, if a DML statement is executed, the SCN corresponding to it's entry point is noted and later if the statement fails, all the changes that are done after this SCN will be undone from the database. Similarly at a transaction level, the SCN at the start of the transaction is noted and later if that transaction fails or rolls back, the changes made after it's start SCN will be rolled back and if the transaction commits, the commit SCN(transaction end time) will be written by the log writer process to the online redo logs. You can refer to the following note for additional information on SCNs:

The transaction will be ended either by explicity firing a commit/rollback statement or implicitly by running any DDL statement. The DDL statement will first commit the transaction and will run the DDL as a single statement with another transaction id. Some applications can also be configured in such a way that if they exit normally from a database, the current transaction can be implicitly committed.

Oracle also provides important features called flashback transaction query and flashback version query to deal with any human or application errors and to view the previous set of data as they existed.

Using flashback version query, you can retrieve various versions of a row during a specific interval of time. A new version of row is created whenever a change is made to that row and it is committed. Using flashback transaction query, you can retrieve the historical data for a transaction during a specific time interval. Using both these features and by querying the fixed table FLASHBACK_TRANSACTION_QUERY, you will be able to undo the operation made by any sql in the transaction. The undo_sql column from the FLASHBACK_TRANSACTION_QUERY gives the previous image of the sql during that particular time. You can refer to the following documentation for more details on these features:

Also, as we know transactions make entries in the redo log during their course of activity, the amount of redo generated mainly depends on how the application is designed and how it handles the statements inside it's transaction. You can use the following note to troubleshoot any high redo generation issues. Please make a note that it's not necessary that only committed transactions get into the redo logs as redo logs will contain uncommitted transactions as well.

We can also make transactions not log any redo entries during it's activities(only a few statements can be controlled). This can be achieved through the NOLOGGING option. This option can be specified while creating an object. However, nologging at an object level is achieved only if the database and the tablespace allows the nologging. You can refer to the following documentation for more information on logging and nologging:

FAQ:

How to find the total transactions inside a database?

The following note provides queries to verify the current existing transactions inside the database:

How do we know how many concurrent transactions a database can process?

The parameters INITRANS and MAXTRANS control how many transactions can exist concurrently. INITRANS controls how many entries can be reserved initially for transactions and MAXTRANS controls the maximum number of concurrent transactions for a specific block. These are stored in the block header. You can refer to the following note for information on INITRANS and MAXTRANS:

What happens exactly when a data change is being made through a transaction?

Once the data block comes to the buffer cache(or already exists), undo data(original image) will be generated for this database block and this will be stored in the SGA. As the block is being modified, a redo record will be generated in the log buffer for this modified block. A redo record for the undo data(original image) will also be generated as it also represents a block change in the SGA. Once the transaction commits, the lgwr process writes the redo entries corresponding to this transaction to the online redo logs. The locks that were placed on the data block are released and dbwr need not necessarily write the modified data block from buffer to the datafiles.

What is a savepoint inside a transaction?

A savepoint acts like a marker inside a transaction which gives the feasibility to move back to a certain point in the transaction. For example, you have run a couple of DMLs inside a database and later you realize that you ran some unwanted DML also. In this case, if you have a savepoint before this DML, you can rollback until that savepoint. The transaction will not be terminated and can be continued. You can create a savepoint using "SAVEPOINT ".

What are the common types of lock categories placed when a transaction is running in a database?

Oracle automatically places locks on database resources to maintain data consistency for a transaction. The below define the typical lock categories placed inside an Oracle database:

DML Locks - These locks are to protect the data. Typically seen while updating the tables/rows(TX/TM)
DDL Locks - These locks are to protect the structure of the schema objects.
System locks - These locks are placed to protect internal database structures. The latches, mutexes are generally seen system locks.

How to find how many transactions existed in the database during a particular time in the past?

You can verify the Load profile section of the statspack or awr report to see the average transactions per second for the given period.

How to find the sqltext of all the locked transactions inside the database?

You can use the script in the following note to find out the sqltext from these transactions:

What does shutdown transactional mean?

Shutdown transactional allows the currently existing transactions to complete first and then proceed with the shutdown. No new connections or new transactions are allowed. Once all the transactions are completed, it will proceed with the shutdown similar to a shutdown immediate.

Why am I seeing an entry in v$rollstat but no corresponding entry in v$transaction?

There are situations where some large transactions have to be rolled back manually or due to some failure. In this case, as the transaction has failed, the rollback should happen and so you will see an entry in v$rollstat but no entry exists in v$transaction as the transaction has already finished.

What does the parameter TRANSACTIONS in v$resource_limit signify? As each session can only start one transaction, why is it's value more than that of the parameter SESSIONS?

The parameter TRANSACTIONS specifies the maximum number of concurrent transactions that can exist in the database. This value defaults to 1.1 times the value of sessions and the value is greater than sessions to accommodate the recursive transactions.

Dirty Reads

When a statement inside a transaction sees uncommitted data from other transactions, this are dirty reads. Dirty reads are seen only in the "Read uncommitted" isolation level.

Nonrepeatable (fuzzy) reads

If the same statement is executed again inside a transaction, it will see different data than what it has seen in it's previous run. This is because some other transaction would have committed some changes between these 2 runs. Fuzzy reads are seen only in "Read uncommitted" and "Read committed" isolation levels.

Phantom reads

If a statement inside a transaction uses a search criteria and if that statement returns different results when run multiple times, you are seeing phantom reads. Phantom reads are seen in all isolation levels except "Serializable" level.

Transaction recovery

Transaction recovery is the phase when a transaction has failed and we need to undo the changes that were done to the database. This will be done by the SMON process or by the server process which tries to access the data block which has uncommitted data. The following note describes the different phases in transaction recovery and the common issues seen:

Waits due to Insufficient ITL slots in a Block

Sometimes, even if the MAXTRANS parameter is not reached, you might see the transactions waiting for the lock to get free ITL and this would happen if the block has exhausted space. You can refer to the following note on issues related to waits on ITL slots:

You are trying to place a tablespace in read-only mode but it is hanging

There is a known bug 2227223 which affects all versions below 11.1.0.7. Due to this bug, when you are trying to place a tablespace in read-only mode, your statement will hang if there are any active transactions inside the database, even if they are not touching any objects in that tablespace.

You are using TAF in a RAC environment and you observe that your session was disconnected when the database issued a shutdown transactional even though your transaction still had not completed.

This behaviour is due to a known issue where the transactions will abruptly end in RAC even if shutdown transactional was issued. There were couple of bugs raised for this issue which were closed as "Not a bug" saying that TAF in fact works with shutdown transactional, provided your application handles the ORA-1033 error properly. The following docs can be reviewed to understand this behaviour:

What is ORA-01555 "Snapshot too old" and when is it observed?

ORA-01555 error is raised when a reader doesn't find a read consistent image of the data it is looking for. This is one of the prominent errors caused due to Oracle's read consistency model. The following note discusses the issues and the actual reason for ORA-01555 errors and how to avoid these errors:

ORA-08177: Cannot serialize access for this transaction

When you are running a serializable transaction(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE), you might receive the error ORA-8177. This is because the transaction is trying to access data that was modified and committed after the transaction has started. You can either rollback the entire transaction, commit till that point, or rollback to a previous savepoint.

You are receiving the error "ORA-01453: SET TRANSACTION must be first statement of transaction" while executing the SET transaction command in the middle of a transaction even though the DMLs above this command hasn't changed any data.

This is due to the unpublished bug 6644341 which is fixed in 11gR2. This error is supposed to be thrown only when executed after performing some DML changes. The below note discusses more on this issue:

You inserted some rows into a table using INSERT INTO and later you observe that the inserted row is not showing up when selecting from the same table.

This is due to the bug 440317, which says SELECT or UPDATE may fail to find the row which has just been INSERTed when using ISOLATION LEVEL SERIALIZABLE. The bug is still under investigation. However, this error happens when using indexes in SERIALIZABLE transaction level and so to temporarily avoid this issue, you can avoid using indexes when selecting. You can also refer to the below note for more information:

You are receiving the error "ORA-30036: unable to extend segment by 8 in undo tablespace <> " in the alert log but when you verify the v$undostat, you observe that there is lots of free space in the form of expired extents. Simply these extents are not being used at all.

This is due to the bug 5442919, which is fixed in 10.2.0.4 and above. As a workaround per Note 247184.1, you can offline and then online the rollback segment and that should clear the full flag.

You observe that the database crashed and during startup it is hanging with the message "SMON: enabling tx recovery" and after sometime the following messages are seen in alert log >> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=xxx.

This is due to the bug 12934890 which can occur on all versions below 12.1. Applying this patch resolves the database hanging and will raise the corresponding ORA-00600 error if it is related to the system trying to update undo$.

Known bugs:

Additional References:

References

Related …

Products …

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Transaction Management Local

Keywords …

BLOCK HEADER;CONCURRENCY;CONTENTION;DATA BLOCK;DEADLOCK;DIRTY;INTERESTED TRANSACTION LIST;ITL;MULTIPLE VERSIONS;READ UNCOMMITTED;REPEATABLE READ;ROLLBACK;SAVEPOINT;SERIALIZABLE;TRANSACTION;TRANSACTIONS PER SECOND;V$TRANSACTION

Errors …

ORA-01453