transaction control and isolation levels in oracle l.
Skip this Video
Download Presentation
Transaction control and isolation levels in Oracle

Loading in 2 Seconds...

play fullscreen
1 / 31

Transaction control and isolation levels in Oracle - PowerPoint PPT Presentation

  • Uploaded on

Transaction control and isolation levels in Oracle. Evgeniya Kotzeva. Vereo Technologies academy . devbg . org. Contents. Transaction control Data Concurrency and Consistency in a Multiuser Environment Locking. Database Transaction.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Transaction control and isolation levels in Oracle' - isanne

Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
transaction control and isolation levels in oracle

Transaction control and isolation levels in Oracle

Evgeniya Kotzeva

Vereo Technologies

  • Transactioncontrol
  • Data Concurrency and Consistency in a Multiuser Environment
  • Locking
database transaction
Database Transaction

A database transaction consists of one of thefollowing:

  • DML statements which constitute one consistentchange to the data
  • One DDL statement
  • One DCL statement
transaction boundaries
A transaction begins with the first executable SQL statement.

A transaction ends with one of the following events:

A COMMIT or ROLLBACK statement is issued

A DDL or DCL statement executes (automatic commit)

The user exits iSQL*Plus

The system crashes

Transaction boundaries
advantages of commit and rollback
Advantages of COMMITand ROLLBACK

With COMMIT and ROLLBACK statements, you can:

  • Ensure data consistency
  • Preview data changes before making changes


  • Group logically related operations
commit transaction

generated rollback segment records in buffers in the SGA

generated redo log entries in the redo log buffer of the SGA.

The changes have been made to the database buffers of the SGA.


The internal transaction table for the associated rollback segment records updated with SCN

LGWR writes SGA redo log entries to the online redo log file

Oracle releases locks

Oracle marks the transaction complete.

COMMIT transaction
rollback transaction

Oracle undoes all transaction changes using the undo tablespace or rollback segments

Oracle releases all the transaction’s locks of data

The transaction ends


Oracle rolls back only the statements run after the savepoint.

Oracle preserves the specified savepoint, but all savepoints that were established after the specified one are lost

Oracle releases all table and row locks acquired since that savepoint

ROLLBACK transaction
state of the data before commit or rollback
State of the DataBefore COMMIT or ROLLBACK
  • The previous state of the datacan be recovered.
  • The current usercan reviewthe results of theDML operations by using the SELECT statement.
  • Other userscan not viewthe results of the DML statements by the current user.
  • The affected rowsare locked
  • Other userscannot changethe data within the affected rows.
state of the data after commit
State of the Data after COMMIT
  • Data changes are made permanent in the database.
  • The previous state of the data is permanently lost.
  • All users can view the results.
  • Locks on the affected rows are released; those rows are available for other users to manipulate.
  • All savepoints are erased.
distributed database
Distributed database
  • Distributed transaction is a transaction that includes one or more statements that update data on two or more distinct nodes of a distributed database
  • A two-phase commit mechanism guarantees the data consistent in all nodes.
autonomous transaction s
Autonomous transactions
  • Autonomous transactions are independent transactions that can be called from within another transaction
  • An autonomous transaction lets you leave the context of the calling transaction
  • You can call autonomous transactions from within a PL/SQL block by using the pragma AUTONOMOUS_TRANSACTION.
data concurrency and consistency
Data Concurrency and Consistency
  • Data concurrency means that many users can access data at the same time.
  • Data consistency means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
the isolation models prevents
The isolation models prevents
  • Dirty reads
  • Nonrepeatable (fuzzy) reads
  • Phantom reads
multiversion concurrency control
Multiversion Concurrency Control
  • Statement-level read consistency

The data returned by a single query comes from a single point in time — the time that the query began

  • Transaction-level read consistency

When a transaction executes in serializable mode, all data accesses reflect the state of the database as of the time the transaction began

snapshot too old
“Snapshot too old”

When commit or rollback has been executed, the pre-images can be overwritten even if they are needed to provide a read-consistent view to another query.

"Snapshot too old" simply means that pre-images which the query needs to maintain a read-consistent view have been overwritten.

common recommendations
Common recommendations

Common recommendations to reduce the possibility of "snapshot too old" are:

  • Keep transactions as fast as possible
  • Increase the size/number of rollback segments
  • Do not specify an OPTIMAL size for your rollback segments.
  • Increase the size of UNDO_RETENTION parameter

(amount of committed undo information to retain in the database)

  • Avoid executing long-running queries when transactions which update the table are also executing.
set the isolation level
Set the Isolation Level

You can set the isolation level of a transaction by usingone of these statements at the beginning of a transaction:

modes of locking
Modes of Locking
  • Exclusive lock

The mode prevents the associates resource from being shared

  • Share lock

The mode allows the associated resource to be shared, depending on the operations involved

table locks
Table Locks
  • RS: row share
  • RX: row exclusive
  • S: share
  • SRX: share row


  • X: exclusive
data lock escalation
Data Lock Escalation
  • A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.
  • Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate
db2 isolation levels
DB2 Isolation Levels

Isolation levels

  • Repeatable Read (RR)
  • Read Stability (RS)
  • Cursor Stability (CS)
  • Uncommitted Read (UR)

Levels of locking

  • Tablespace
  • Table
  • Row
transactions in oracle
Transactions in Oracle