Mysql: InnoDB: Transaction Models and Isolation Levels.

Inconsistency can play havoc while executing concurrent connection.
To make our life easy MySQL's InnoDB supports the following isolation levels.

• Isolation level 1 (read uncommitted):- Can read the *dirty* update done by client 1. It can cause Inconsistent behaviour in case client 1 roll-backs. This isolation level also allow non-repeatable, phantom reads.
• Isolation level 2 (read committed):- Can't read the *dirty* update done by client 1. Hence at this isolation level changes propagate only after the transaction is committed. However this isolation level allows non-repeatable and phantom reads.
• Isolation level 3 (repeatable read):- Can't read the *dirty* update done by client 1. Also at this isolation level read locks are acquired on data. Thus reads are repeatable at this isolation level. Phantom reads however cause problem because range locks are not acquired at that level
• Isolation level 4 (serializable):- Transaction is serialized, all three cases are handled, however slow in performance as at every level locks are acquired.

Default Isolation level for Mysql is Repeatable Read

How to check Isolation level for Mysql:

To know isolation level for current session :-

mysql> select @@tx_isolation;

To check isolation level globally:-

     mysql> select @@global.tx_isolation;

How to change Isolation level for Mysql:

Here is the command to do so:-

mysql> set [session|global] transaction isolation level [read uncommitted| read committed | read repeatable | serializable] ;

Note:
1. Use global if you want to set isolation level for all the new connections made from that point onwards. However it won't change isolation level of existing connections.

How to change Isolation level at service startup:

    Use --transaction-isolation=level, example

$ mysqld --transaction-isolation={ READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE } start

Some examples to clear the air:-

Basic Setup

    * Client one reads device model information and can update device model information.
    * Client two can also read and update device model information.

Test Cases:-

Example 1:- ( Dirty Read ) Client One:

> start transactions;

> select dev_model from client;

| dev_model|
|   1      |
|   2      |
|   3      |

> update dev_model set dev_model=4 where dev_model=1;

Client Two:-

> start transactions;

> select dev_model from client;  
  ( At Isolation level 1 (read uncommitted) )
    |dev_model|
    |   4     |
    |   2     |
    |   3     |

   ( At Isolation level 2 (read committed) )
    |dev_model|
    |   1     |
    |   2     |
    |   3     |
  ( Same as above for level 3, 4)
 

Example 2 (Non-Repeatable Read):-

client 1:-

> start transaction;

> select dev_model from client

|dev_model|
|   1     |
|   2     |

client 2:

> start transaction
> update client set dev_model=4 where dev_model=1
> commit||

client 1:

> select dev_model from client

 
At isolation 1,2:-
|dev_model|
|  1      |
|  4      |
          
As we can see for client 1, reads are non-repeatable.

At isolation 3,4:-
|dev_model|
|  1      |
|  2      |

Thus at isolation level 3 and above reads are repeatable.


As read locks are acquired by client 1, the data does not change even when client 2 commits. Client 2 transaction needs to wait for client one to commit first thus making transaction 1, 2 as serialized. Thus at isolation level 3, the reads are repeatable.

* Isolation level 4 (serializable): At isolation level 3, . for example:-

Client 1:-

> start transaction;

> select dev_model from client where client_id > 5 and client_id < 10
| dev_model |
|   1       |
|   2       |

client 2:-

> start transaction;

> insert into client (client_id, dev_model) values (7, 10);

> commit

client 1:-

>  select dev_model from client where client_id > 5 and client_id < 10 ;

(Isolation level 1,2,3)
|dev_model|
|  1      |
|  2      |
|  10     |

( Isolation level 4)
|dev_model|
|   1     |
|   2     |

Thus transaction 1 take place as if transaction 2 hasn't occurred YET


Thus a phantom read can occur at isolation level 3, however at isolation level 4 i.e serializable the transactions are serialized and all the three cases, i.e. phantom read, non-repeatable reads and dirty read are prevented by acquiring locks for range, data and transaction.