Isolation level types - SERIALIZABLE, REPEATABLE READ ,READ COMMITTED - Database

Discussion

• Nirja Shah   -Posted on 02 Nov 15
  - To describe consistent transaction behaviour when transactions run at the same time, database researchers have defined a transaction isolation model called serializability.
  
  - The serializable mode of transaction behaviour tries to ensure that transactions run in such a way that they appear to be executed one at a time, or serially, rather than concurrently.
  
  - While this degree of isolation between transactions is generally desirable, running many applications in this mode can seriously compromise application throughput.
  
  - Complete isolation of concurrently running transactions could mean that one transaction cannot perform an insert into a table being queried by another transaction.
  
  - In short, real-world considerations usually require a compromise between perfect transaction isolation and performance.
  
  - Oracle offers two isolation levels, providing application developers with operational modes that preserve consistency and provide high performance.
  
  - The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput.
  
  - These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions.
  
  - The three preventable phenomena are:
  
  - Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
  
  - Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
  
  - Phantom reads (or phantoms): A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.
  
  - SQL92 defines four levels of isolation in terms of the phenomena a transaction running at a particular isolation level is permitted to experience. They are as follows:
  
  Isolation Level ----- Dirty Read ----- Nonrepeatable Read ----- Phantom Read
  Read uncommitted ----- Possible ----- Possible ----- Possible
  Read committed ----- Not possible ----- Possible ----- Possible
  Repeatable read ----- Not possible ----- Not possible ----- Possible
  Serializable ----- Not possible ----- Not possible ----- Not possible
  
  - Oracle offers the read committed and serializable isolation levels, as well as a read-only mode that is not part of SQL92. Read committed is the default.
  
  

➨ Post your comment / Share knowledge