Suggested Pages

Thursday, August 2, 2012

Transaction Isolation Level and Anomalies

Conflict Transactions


Two concurrent transactions T1 and T2 are in conflict if they perform an action on the same object. There are three possible conflicts:
  • (WR) Write-Read: t1 reads an object that was previously written by t2;
  • (RW) Read-Write: t2 writes an object that was previously read by t1;
  • (WW) Write-Write: t2 writes an object that was previously written by t1

Anomalies

Consider the following table:
Employee
id
name
age
salary


Lost Update (WW)

We can distinguish two kinds of lost update.
  • First type: two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values; It may occur when a transaction t1 can write a value of an object written by another transaction t2 not yet completed. The transaction t1 overwrites the update made by T2.
  • Second Type: two transactions each retrieve the same row, and then later update the row based on the originally retrieved values.
The first type can be resolved by the Read Uncommitted isolation level, instead the second type can be resolved only at Repeatable Read.

Dirty Read (WR)

It may occur when a transaction t1 can read a value modified by another transaction t2 not yet completed. T1 reads an intermediate value because t2 should rollback.

    TRANSACTION1 (t1)             TRANSACTION2 (t2)


    SELECT emp.salary 
    FROM employee
    WHERE id = 100;
                                  UPDATE users emp 
                                  SET emp.salary = 5000 
                                  WHERE id = 1;
    /*intermediate read value*/ 
    SELECT emp.salary 
    FROM employee
    WHERE id = 100;
                                  ROLLBACK;



Unrepeatable Read (RW)

It may occur when a transaction t2 can write a value of an object read by another transaction t1 not yet completed. So the transaction t1 reads different values ​​for the same data at different times.


     TRANSACTION1 (t1)             TRANSACTION2 (t2)

    /* first read*/
    SELECT emp.salary 
    FROM employee
    WHERE id = 100;
                                  UPDATE users emp 
                                  SET emp.salary = 5000 
                                  WHERE id = 1;
                                  COMMIT;
    /* second read*/
    SELECT emp.salary 
    FROM employee
    WHERE id = 100;
    COMMIT;
 

Phantom Read

It may occur when a set or of tuples,read by a transaction t2, are changed by t1. It usually occurs when a transaction t1 that performs a SELECT with a WHERE clause reads a set of tuples and another transaction t2 adds tuples in that set. This anomaly can be consider a special unrepeatable read where a lock on a set of tuples has not been acquired.

       TRANSACTION1                     TRANSACTION2

      
      SELECT * FROM Employee
      WHERE salary<1000 and salary>500;


                                        INSERT INTO Employee VALUES ( 100, 'Simone', 27 ,800);                               COMMIT;


      SELECT * FROM Employee
      WHERE salary<1000 and salary>500;




Isolation Level



Read Uncommitted

This isolation level avoids lost update (first type described in the anomalies section above). A transaction t1 can't override changes made by another transaction. In this isolation level a transaction can read data that will be rollbacked (uncommitted) because read locks are not managed.
At this isolation level, if you perform a SELECT query, you don't need any lock to acquire.

Read Committed

This isolation level avoids dirty read and lost update (first type described in the anomalies section above). A write lock (exclusive lock) can't be released until the end of the transaction, instead a read lock (shared lock) can be released after a read is performed. No range-locks is managed in this isolation level.
At this isolation level before reading a row, a transaction must check if another transaction has a write lock. It cannot read any write-locked rows and so it must wait the write lock to be released.

Repeatable reads

This isolation level avoids unrepeatable read, dirty read and lost update(first type and second type described in the anomalies section above) . Both write lock and read lock are exclusive locks and they are released only at the end of the transaction. No range-locks is managed in this isolation level.
At this isolation level to avoid unrepeatable read, a transaction that has to read an object, acquires a read lock and holds it until the transaction completes.

Serializable

This isolation level avoids all anomalies. Both write lock and read lock are exclusive locks. Besides range locks are managed in this level. So when you make a SELECT with a WHERE clause, a range-locks must be acquired.

Shared Lock vs Exclusive Lock


Shared Lock

Shared Locks are released after the read or write operation completes.

Exclusive Lock

Exclusive Locks are released only at the end of the transaction.

No comments :

Post a Comment

Suggested Pages