Transaction Isolation Levels

Isolation Levels indicate what kind of modified/committed/uncommitted data are visible to database users to maintain the data integrity and consistency.

MySQL uses InnoDB engine for DBMS and supports all 4 isolation levels as dictated by SQL:1992 standards: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE.
The default isolation level for InnoDB is REPEATABLE READ. which can be changed using SET TRANSACTION command.

Different RDBMSs would have one of these as default isolation level but they also provide a mechanism to switch to any required isolation level.

Isolation levels are defined in terms of below three phenomena that are either permitted or not at a given isolation level:

Dirty read: The meaning of this term is as bad as it sounds. You're permitted to read uncommitted, or dirty, data. You can achieve this effect by just opening an OS file that someone else is writing and reading whatever data happens to be there. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored.
Nonrepeatable read: This simply means that if you read a row at time T1 and try to reread that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.
Phantom read: This means that if you execute a query at time T1 and re-execute it at time T2, additional rows may have been added to the database, which may affect your results. This differs from a nonrepeatable read in that with a phantom read, data you already read hasn't been changed, but instead, more data satisfies your query criteria than before.

Different isolation levels are a combination of the above factors.

Isolation Level    Dirty Read   Nonrepeatable Read    Phantom Read
READ UNCOMMITTED   Permitted    Permitted             Permitted
READ COMMITTED     --           Permitted             Permitted
REPEATABLE READ    --           --                    Permitted
SERIALIZABLE       --           --                    --

Previous Index Next

SQL Tutorial

Transaction Isolation Levels

XP

Loading comments...