.gif){kind=small}
.gif){kind=small}
Isolation levels come into play when a transaction requires an exclusive right to use a resource thereby isolating it from other transactions. This isolation is provided by obtaining locks on the resource. What locks need to be set for the transaction is determined by SQL Server referring to the Isolation Level that has been set. Lower Isolation Levels allow multiple users to access the resource simultaneously thereby increasing concurrency but also introducing the possibility of concurrency related problems. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking and in some cases result in deadlocks.
The isolation levels defined by the ANSI/ISO SQL standard are:
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED:
This level of isolation allows users to read data that has not been committed. This increases throughput since the user does not have to wait for the other transaction to complete its execution before it can read the data. But if the user reads data that has been modified by another transaction, which happens to fail at some point of time and roll-back, the data read initially by the user, is wrong or inconsistent. This is called dirty reads. To illustrate with an example,
Create a new database.
Create table Employee with the following fields: Emp_Name, Salary
Open two query windows in SQL Server and execute the following lines of code.
First query window:
begintran
UPDATE [Employee]
SET [Emp_Name]='ABC',
[Salary]=1234
Second query window:
begintran
SETTRANSACTIONISOLATIONLEVELREAD UNCOMMITTED
SELECT*FROM [Employee]
Execute the query in the first window. After that, execute the second query in a different query window. Note that the transaction in the first window has not committed or ended.
The result of the select statement gives,
Emp_Name | Salary
ABC| 1234
Now go back to the first query window and execute the command,
ROLLBACKTRAN
The value in the [Employee] table goes back to the original value, say ‘DEF’.
So the second user ended up with the wrong data or was subjected to a dirty read. This happens because the level of isolation allows you to read data that is not yet committed.
TRANSACTION ISOLATION LEVEL READ COMMITTED:
It offers a higher level of isolation as it prevents users from reading data that has not been commited yet.
Execute the same steps as above, after changing the level of isolation as‘READ COMMITTED’ in the second query window.
First query window:
begintran
UPDATE [Employee]
SET [Emp_Name]= 'ABC',
[Salary]=1234
Second query window:
begintran
SETTRANSACTIONISOLATIONLEVELREAD COMMITTED
SELECT*FROM [Employee]
Execute the query in the first window.Now on executing the second query, we do not get any result and the status is shown as ‘executing’.
Now go back to the first query window and commit the transaction.
committran
As soon as the first transaction commits, the result is displayed in the second query window. As a result, the end-user always gets data that is consistent.In this isolation level, read locks are acquired on selected data but they are released immediately whereas write locks are released only at the end of the transaction. This level of isolationhas an adverse effect on performance as the second process has to wait for the first transaction to commit.
TRANSACTION ISOLATION LEVEL REPEATABLE READ:
This level of isolation implies that once a transaction reads existing data, read data would never be changed in that transaction irrespective of how many times the same select statement is issued.
Consider the case where two users are accessing data from the same table. User1 beginstransaction and executes a select query on the table. At the same time user2 performs an update on the same table. Read Committed isolation level does not prevent this from happening. Now when user1 executesthe same select query again, within the same transaction he gets a set of values different from the first select statement.This is a data consistency problem related to Read Committed isolation Level called Non Repeatable Read.
This can be avoided in Repeatable Read Isolation level by not releasing Shared Locks (acquired to read data)till the end of transaction. Therefore in the above mentioned scenario, user2 will have to wait till shared locks on the data are released, thus allowing the update only after the first transaction completes execution.
In Repeatable Read Isolation level a select statement also would hold locks till the end of transaction. Again as the level of isolation increases, understand how Concurrency is gradually coming down.
TRANSACTION ISOLATION LEVEL SERIALIZABLE:
This is the highest level of isolation, where each statement is executed serially in an isolated manner. i.e, each statement is executed one after the other. Almost all database anomalies including phantom reads are eliminated in this level of isolation.
Consider the case where two users are accessing data from the same table. User1 starts his transaction and executes a select query on the table. At the same time user2 performs an insert on the same table. TheRepeatable Read Isolation level prevents updates on data holding a shared lock, but it allows new rows to be inserted.Now when user1 executes the same select query again, within the same transaction, it returns an extra row which was not expected by the first user. This is the problem of phantom reads.
The serializable isolation level does not allow updates on data till the current transaction completes execution. It also prevents new rows to be inserted to the table until the transaction completes, thereby returning the same result set no matter how many times the same select statement is executed within a single transaction. But this level of isolation has a serious effect on concurrency thereby reducing performance. So it should be used only in circumstances which require a high level of data consistency.
The above four isolation levels are said to provide pessimistic control, because they acquire locks on data foreseeing the problems related to concurrency. There are two more levels of isolation which are new to SqlServer that provide optimistic control.UnderOptimistic Control, SQL Server does not hold locks but once read checks for inconsistency for the next read.
The two new levels of isolation are:
SNAPSHOT ISOLATION LEVEL:
This Isolation Level prevents all concurrency related problems just like Serializable Isolation Level; in addition to that it allows multiple updates for the same resource by different transactions at the same time. It maintains consistent versions of records in a version store.
When a user performs a select statement on a set of records it obtains a versioned copy from the version store. Every time a select statement is issued, the last consistent copy of the record, when the transaction started, is returned and the same version is read during the entire transaction, irrespective of the data being modified by other transactions. Now a second user also obtains a versioned copy and performs some updates on the data and commits the transaction. Now if the first user tries to perform an update on the data, the SqlServer detects this conflict and aborts the transaction.If another transaction starts changing the same record, another version will be stored and so on; resulting in a longer linked list in the version store. Maintaining and traversing through longer lists will impact the performance. SQL Server keeps versions of the record until there is no reference for it.
READ COMMITTED SNAPSHOT ISOLATION LEVEL:
The only difference between ReadCommitted and Read Committed Snapshot isolation levels is that, Read Committed Snapshot is Optimistic whereas Read Committed is Pessimistic.
Read Committed Snapshot Isolation Level is set at the database level. It differs from Snapshot in two ways; Unlike Snapshot, it always returns the latest consistent version and there is no conflict detection. Since this level does not require maintaining previous versions, there will be no impact for performance like Snapshot but all the concurrency related problems except dirty reads can happen.
-DB Team
Avoid Cursor, use forward; cursor only if necessary.