I was recently studying about transaction isolation levels in database. First of all, let us see the definition of transaction isolation level.

Definition: Every transaction specify an isolation level that defines the level at which one transaction must be isolated from resource or modifications made by other transactions.

For example:- There are two transaction T1 and T2. Whether T1 can see the modifications/changes made by T2(or vice-versa) depends on the transaction isolation level.

There are basically 3 levels of isolation:-

Read committed: It is an isolation level which guarantees that the data read was committed at the moment it is read. It always sees the effect of any previous update statement executed in the same transaction, even if the changes are not committed.

Repeatable Read: It is an isolation level that only sees data committed before the transaction began. It guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data. In simple words, during the course of a transaction, if a row is retrieved again and again, same value will be reflected.

Serializable: It is an isolation level in which, database engine acquires locks and holds them till the end of the transaction. It ensures that, once a transaction reads data, no other transaction can modify it, even new rows can’t be inserted until the transaction holding the lock completes.

By default, the isolation level of MYSQL is REPEATABLE-READ and in POSTGRES its READ-COMMITTED.

To change the transaction isolation level in MYSQL, use this:-

SET GLOBAL tx_isolation='REPEATABLE-READ';

OR

SET GLOBAL tx_isolation='READ-COMMITTED';

To display the transaction isolation level in MYSQL:-

mysql> SELECT @@GLOBAL.tx_isolation;

transaction_mysql

Here is a comparison table:-

transaction_isolation

That was all!

Thanks