The ISO standard defines the following isolation levels in SQL Server Database Engine:
Microsoft SQL Server supports these transaction isolation levels:
- Read Committed
SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.
- Read Uncommitted
SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.
- Repeatable Read
SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. So phantom rows are This has the same effect as specifying HOLDLOCK on a SELECT statement.
(Phantom read:Phantom reads occurs when an insert or delete action is performed against a row that is being read by a transaction.The second transaction read shows a row that did not exist in the original read as the result of an insertion by a different transaction or due to deletion operation some rows doesn’t appear)
In serializable read phantom reads are not allowed because while the first transaction is in progress other transaction wont execute.
SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursor is populated with the values as of the time when the transaction first started. Scroll locks are still requested regardless of use of snapshot isolation.
Read uncommitted example: Uncommitted Read allows your transaction to read any data that is currently on a data page, whether that has been committed or not. For example,another user might have a transaction in progress that has updated data, and even though it’s holding exclusive locks on the data, your transaction can read it anyway.
If we execute the select query before the update transaction gets committed,it will not wait for the update transaction to commits.Query will be executed immediately without any time lapse.
- Read Committed example
Read committed allows your transaction to read only if the data is committed.Read Committed operation never reads data that another application has changed but not yet committed.
If we execute the select query before the update transaction gets committed,it will wait till the update transaction gets committed.
Repeatable Read example :In Repeatable Read issuing the same query twice within a transaction will not make any changes to data values made by another user’s transaction.Repeatable Read allows phantom reads(Data getting changed in current transaction by other transactions is called Phantom Reads).So phantom rows will appear.
While the transaction(first query) is in progress,repeatable read allows another transaction(second query) to execute.It means it allow phantom reads.So second transaction(second query),need not wait till first transaction(first query) completes.Here values will be added before first query completes.
Serializable example : The Serializable isolation level adds to the properties of Repeatable Read by ensuring that if a query is reissued, rows will not have been added in the table. In other words, phantoms will not appear if the same query is issued twice within a transaction.
While the transaction(first query) is in progress,serializable read does not allow another transaction(second query),It means it don’t allow phantom reads.So second transaction(second query), must wait till first transaction(first query) completes.Here values will be added only after first query completes.
snapshot example : To use the snapshot isolation level you need to enable it on the database by running the following command
ALTER DATABASE DBname
SET ALLOW_SNAPSHOT_ISOLATION ON