In order to understand different isloation levels in SQL Server, we should first understand what is an Isolation level.
** As we must be aware that for any transaction in a database, 4 primary attributes are ensured which we remember as ACID (Atomicity, Consistency, Isolation, Durability) **
For every transaction in database, the database guarentees isolation. That means every transaction is isolated from each other and SQL server provides 5 diffferent levels of isolation for the transactions.
- READ UNCOMMITED.
- READ COMMITTED (locking).
- READ COMMITTED (snapshot).
- REPEATABLE READ.
- SERIALIZATION.
You can change the level of isolation that a particular connection is operating in by using the SET TRANSACTION ISOLATION LEVEL command. Keep in mind that the SET command applies only to your current connection, and every time you make a new connection (or open a new window in the Query Analyzer), you’ll be back in the default isolation level.
READ UNCOMMITTED:
Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred. Although this scenario isn’t desirable, with Uncommitted Read you won’t get stuck waiting for a lock, nor will your reads acquire share locks that might affect others.
For example, if you open two instances of SSMS.
In instance 1, open a transaction and update some data in a table and do not commit the transaction.
Let say, the Employee with ID=1 had a salary of 500 initially and we update it to 1000 in a transaction without commiting that transaction.
BEGIN TRAN UPDATE dbo.Employee SET EmpSalary = 1000 WHERE EmpID = 1; -- In Instance 1
Now, if we read the salary in instance 2 , with transaction Isolation level as READ UNCOMMITED, we will be able to read 1000 instead of 500. Which is also called as dirty read as that update statement in a transaction is not committed yet.
If we rollback that transaction in instance 1, the actual value in the table will be 500, but in instance 2 we have read a dirty read with value 1000.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1 ; -- In Instance 2 <This will fetch Salary=1000 for us>.
READ COMMITTED (Snapshot):
Read Commited will read the last consistent row of the table we are quering. This ensures that dirty reads are not retreived. With Read committed (snapshot), if we update the salary to 1000 from 500 in a transaction in SSMS instance 1.
And try to read the value of salary for EMP with Id=1, it will fetch us the last consistent row version , which is 500.
ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON GO BEGIN TRAN UPDATE dbo.Employee SET EmpSalary = 1000 WHERE EmpID = 1 ; --In Instance 1
Now in Instance 2, if we fetch the salary of employee with empId=1, It will return us 500 and not 1000.
SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1; --In Instance 2 < will fetch 500>
READ COMMITTED ( locking):
In Read commited (locking), if we do the same operation as above, it wil still prevent the dirty read by locking the select query untill the transaction is commited.
ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON GO BEGIN TRAN UPDATE dbo.Employee SET EmpSalary = 1000 WHERE EmpID = 1 ; --In Instance 1
In instance 2, if we read the Salary of empID=1, the query will hang and wait for the transaction to complete.
SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WITH (READCOMMITTEDLOCK) WHERE EmpID = 1; -- In Instance 2< Read query will hang >
REPEATABLE READ:
If you want the read operations to be repeatable, choose this isolation level. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won’t pick up any changes to data values that another user’s transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.
In Read committed scenario , if we have two instances. In intance 1, we read same value (salary) twice from a table with a delay in execution in one transaction scope. In instance 2, we update that value(salary) in another transaction scope.
Note that, In instance 1 , we will be getting two different values of salary in the same transaction. One with the original value and another which is updated in second instance.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON GO BEGIN TRAN SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1; --fetches Salary=500 WAITFOR DELAY '00:00:15' SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1; -- fetches Salary=1000 COMMIT
It fetched, Salary=1000 because we updated salary in Instance 2 as below within those 15 seconds for whcih Instance 1 was waiting.
BEGIN TRAN UPDATE dbo.Employee SET EmpSalary = 1000 WHERE EmpID = 1 ; --In Instance 2 COMMIT
Now to avoid these different reads in a transaction, we can use Repeatable Read.
With Repeatable Read , in a transaction it is ensured that consistent values are being fetched irrespective of updates in another transactions.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ SET NOCOUNT ON GO BEGIN TRAN SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1;-- Instance 1 <fetches 500> WAITFOR DELAY '00:00:15' SELECT EmpID, EmpName, EmpSalary FROM dbo.Employee WHERE EmpID = 1;-- Instance 1 <fetches 500> COMMIT
Now, within that delay period of 15 seconds , if in Instance 2 , we update the salary to 1000, the Instance 1 will still fetch the older consistent value of 500 instead of 1000 because of Repeatable Read.
BEGIN TRAN UPDATE dbo.Employee SET EmpSalary = 1000 WHERE EmpID = 1;-- In Instance 2 COMMIT
** But if you insert a new row in Instance 2 , and do a select * from Employee two times with a delay, In first statement , it will read all the rows of the table and after the insert is done, it will also read all the rows including the newly inserted row. **
Hence , Serializable is the best way for Isolation which also prevents dirty read in case new rows are added.
SERIALIZABLE:
SERIALIZABLE has all the features of READ COMMITTED, REPEATABLE READ but also ensures concurrent transactions are treated as if they had been run in serial. This means guaranteed repeatable reads, and no phantom rows. Be warned, however, that this (and to some extent, the previous two isolation levels) can cause large performance losses as concurrent transactions are effectively queued. Here’s the phantom rows example used in the previous section again but this time using the SERIALIZABLE isolation level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET NOCOUNT ON GO BEGIN TRAN SELECT EmpName FROM dbo.Employee ;-- Instance 1 <Returns all rows with count 5> WAITFOR DELAY '00:00:15' SELECT EmpName FROM dbo.Employee ;-- Instance 1 <Returns all rows with count 5> COMMIT
Now , if in the timeframe of 15 seconds if we insert a new row in the employee table as below in Instance 2, The Insert will wait for the transaction in Instance 1 to get committed and then will insert the row in the table.
BEGIN TRAN INSERT INTO dbo.Employee VALUES (2, 'Phantom Employee 1', 2000) COMMIT ;-- Instance 2 <creating a new record (row 6)>
