An isolation levels mechanism is used to isolate each transaction in a multi-user environment. The correct use of the isolation levels mechanism prevents applications from introducing errors.
The errors that can occur while managing concurrent transactions are
Lost Updates – This situation occurs when two transactions attempt to update the same data.
Dirty Reads – This situation occurs when transactions read data that has not been committed.
Nonrepeatable Reads – This situation occurs when a transaction reads the same query multiple times and results are not the same each time.
Phantoms- This situation occurs when a row of data matches the first time but does not match subsequent times.
Isolation levels are:
1. Read uncommitted
On this isolation level database engine does not issue shared lock while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures if the data is not physically corrupt, then it will read.
On this isolation level, database engine issues shared lock for reading data. This is the default isolation level in SQL Server. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it does not ensure that the data will not be changed before the end of the transaction.
When it’s used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.
Most restrictive isolation level. When this is used, then phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction is complete.