Isolation level is one of the property of ACID . Sql server has different levels of Isolations implemented using locks .
The Term Isolation defines Isolating the transaction & doesn't having impact on other transactions happening in a different user session.
Sql Server 2008 defines different isolation types to cater the needs from High Concurrent executions to the accuracy of the data.
What is the default Isolation Level of Sql Server ?
Well .. it is Read Committed. So by default if you do not specify any isolation level for a query then it will take default isolation level of Read Committed.
to find the default Isolation Level execute the query :
dbcc useroptions
Before going in to the isolation levels , let us see some of the locks that sql server uses during transactions :
Row Level Locks :
shared locks (S) , Update locks (U) , Exclusive locks (X)
By default every time you connect to database, shared locks are applied. these locks are for more concurrency.
Once a row need to be updated , Update lock is applied & then exclusive lock is applied . so that only that transaction can exclusively have access to the row.
Update additionally have 3 other kinds of locks
1) table level exclusive lock
2) page level exclusive lock
3) row level exclusive lock
Update Locks :
1) update locks provide high concurrancy
2) they isolate rows before the update happens
3) they does not prevent the select statements , that means readable statements can still read the data
4) they upgrade to exclusive lock to update a row.
Page & Table Level locks will be discussed in a seperate thread..
Let us go in to the isolation levels now :
1) ReadUncommitted :
In this isolation level, transaction T1 can have simultaneous access to data that is being modified by another transaction T2 running in a different session. It means T1 will have dirty reads as T2 can either commit the data or roll back the data . Even if T2 rollbacks the changes still they are displayed in T1 . How ever this isolation level provides more concurrancy.
Pros : No read locks required, no read / write blocking
Cons : High inconsistancy in data reads, as other transactions can roll back the changes
Usage: These are used in transactions where data consistancy is not of high priority & can have stale data.
NOTE : Read Uncommitted & No Lock - both does the same thing.
ex:
Create table
Begin the transaction & update the records , do not commit (or) rollback
Query the records in a seperate Window with readuncommitted mode. It will return the result set as it doesn;'t hold read locks & gets the stale ( dirty ) data.
T2 can roll back the updates resulting in stale data shown in Transaction T1.
2) Read Committed :
In this mode, Transaction T1 can read only committed data. Each time a row is accessed it will be apply a shared lock & releases the shared lock once the row is read. If another transaction T2 modifies the data , then T1 can't read the data which is modified in T2 till it is committed or rolled back.
ex: if T2 modifies row2 & T1 query need to return the row1 & row2 , then T1 Reads row1 how ever it will wait for row2 to be released by T2. This creates some blocking.
Pros : high consistancy of data
Cons : Can have blocking caused till data is committed or rolled back
Usage : This is sql server default isolation mode.
In the above example if updates are done & not committed or rolled back , then select query will not return the result set as they will be blocked.
ex :
The Data is not returned till data is either committed or rolled back, causing some kind of blockage but data will be highly consistent.
If you take the spid for above transaction & query the locks for the spid, then we will find that Shared lock is required & waiting for a shared lock .
3) Repeatable Read :
In this isolation level , transaction T1 can only access committed data & additional guarantee of not modifying the already read data till the transaction is completed. This is achieved by applying a shared lock on rows.How ever it doesn't protect from new inserts .
Pros: High data consistency
Cons: Locking & Blocking, Rows are locked till the transaction is completed, How ever doesn't protect against Phantom rows.
Usage : Rare
The below example illustrates the situation :
Create table, insert some records in to it
Begin Transaction & Update 3rd row. Don't commit
As Transaction is not yet committed , select statement would be blocked on 3rd row.
it already reads 1st , 2nd rows & waiting on 3rd row.
The above query is looking for condition technology = sql server , it already read row 1 as it satisfied the condition. Now update the rows making 2rd row as sql server instead of row 1 & commit the data.
The commit will release the locks & select query will be able to read row3 , how ever data retrieved says row 1 as sql server consultant where as actual record that matches now is row 2
This incosistency is caused because select query has read row 1, row2 & found row 1 is satisfying the query.
It is blocked at Row3 as different transaction has updated the row 3 & didn't commit it.
When the row2, row1 are updated & committed, lock is released & able to read row 3
How ever it will not look again for Row 1 & row 2 for consistency of data.
To avoid this inconsistency of data during a transaction , repeatable read can be used.
Repeatable Read will lock each row that it transverses & doesn't release the lock till the complete transaction is done. In the above example , if we use repeatable read isolation then First update query will lock 3rd row. Select will lock 1st & 2nd row , waiting for 3rd row. Second Update will be waiting for 1st & 2rd rows which were locked by select statement resulting in dead lock.
Dead lock is fine in some cases, where data consistency is a must till the transaction is completed.
Let us try a different scenario :
Drop the table , recreate it again & insert some records in to it.
Set the isolation level to repeatable read & run the select query with begin transaction :
The transaction will apply a shared lock on the row & will not release a lock till the transaction is completed.
Run the update query in another window. Update mode will be blocked as the row is already locked by above select statement.
Now go back to the select window & commit the transaction , it will release the lock & update statement can acquire the lock .
Update statement will be executed :
How ever Repeatable Reads doesn't stop the phantom reads . phantom reads are read that read the newly inserted data,
Since repeatable reads just lock the rows that are scanned through , it just blocks the existing rows . It will not lock the table from inserting any new rows , new rows can be inserted & can be read next time.
ex: Run the select query , with out committing.
Now the select query has locked the first row , how ever it doesn't block any new inserts from happening.
Now run the insert query in a seperate window & it inserts the record.
4) Serializable : This isolation level provides highest data consistency including excluding phantom rows by applying range level locks (or) table level locks, resulting in low concurrency. The serializable isolation takes the transactions one at a time in a serializable fashion. Let us say 4 transactions runs viz. T1, T2, T3, T4 .
They run one after the another transaction is completed.
Pros : High data consistency
Cons : Severe blockage, low concurrency.
Usage:: Used in High Data Consistent environments.
Ex : Use the same example from above repeatable read.
Create table , insert records, Run select query in serializable isolation.
Select query will apply a range lock blocking any other transaction to access the range.
Insert statements will be blocked.
Now insert statement will be blocked , as select statement has applied range level lock.
Insert statement will wait till select statement commits or roll backs.
5) Snapshot Isolation :
This isolation level provides the data consistency as much as read committed does & provides high performance as read uncommitted . This isolation level doesn't apply locks on the rows.
How ever it doesn't read the modified data till it is committed. Means no dirty reads. It reads data from a snapshot.
Let us take the same sample example.
Update a row in the table , it locks the row .
Now set the isolation level to snapshot & run the select query :
You may see the error that snapshot isolation is not defined on database:
Alter database to set the snapshot isolation level for a database :
NOw run the select statement in Snapshot isolation , it will not apply locks on the rows & returns the committed data only.
