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 -- -- --
Taking you to the next exercise in seconds...
Want to create exercises like this yourself? Click here.
No hints are availble for this assesment
Answer is not availble for this assesment
Loading comments...