SQL SERVER TRANSACTION ISOLATION LEVELS

SQL SERVER TRANSACTION ISOLATION LEVELS

by Mukiibi Ivan

On a busy day, our streets are given order by the traffic police, they keep the traffic flowing and they also keep motorists safe. Maximum safety can be ensured by only allowing one person to use a junction at a time.
Unfortunately, if there is heavy traffic the safer and therefore the more isolated the cars are, the slower the process becomes. As the number of drivers increases, the situation becomes less acceptable to those left waiting, and soon becomes untenable. Everyone wants to navigate the junction at the same time since they are all trying to reach to their destinations on time — they want concurrent access to the junction.

Ultimately, there needs to be a trade-off between safety and practicality. The traffic police therefore reduce the safety level for increased throughput and greater concurrency by allowing more than one driver to use the junction at the same time. Though drivers want to achieve different goals when using the junction, if their intent doesn’t conflict with one another, all is well. Otherwise, the drivers’ actions must be sequenced to avoid a collision or crash.

Just like the traffic police needs to provide concurrency on our roads, SQL Server needs to provide highly concurrent access to data. Rather than cars, SQL Server uses transactions to marshal activity in the database. Transactions must exhibit the four ACID properties. One of these properties in particular is all about SQL Server “road safety” — the isolation property. In order to isolate one’s data (and keep it safe), SQL Server implements a number of isolation levels from which you can choose.

Before we look into how SQL Server implements isolation levels to deliver highly concurrent data access, we are going to baseline our knowledge in relation to transactions.
This is due to the fact that without transactions there wouldn’t be any need to implement isolation levels. Database users would happily overwrite each other’s data.

TRANSACTIONS

A transaction is “a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions”

Simply put, transactions exist to group individual operations into a single logical unit of work. The properties that assure transactions work in this manner are the ACID properties of Atomicity, Consistency, Isolation and Durability.

Atomic – One unit of work. All operations within a transaction must succeed. A partially complete transaction cannot be committed.

Consistent – Transactions should move the DB from one consistent state to another. If an insert violates
a check constraint or the referential integrity of the table in question, then the transaction must be rolled back or a compensating action in the transaction must take place. The Data Definition Language (DDL) of the database determines its consistency, and SQL Server ensures that any rules imposed in the DDL are adhered to.

Isolated – Prevent concurrency issues. This issue relates to the state of the data. The isolation property is also the most flexible of the four properties. In other words, you can augment its behavior.

Durable – Changes performed in a transaction should be stored on hard disk. that is when the transaction is committed, it stays committed.

Simply put this means transactions must completely commit or roll back their changes (i.e., Atomicity), data is logically correct when a transaction is complete (i.e., Consistency), each transaction happens as if no other activity exists in the system (i.e., Isolation), and all changes are permanent (i.e., Durability).

In SQL Server, there are several manners in which to start and end transactions: Automatic, User-Defined, and Implicit. These enable SQL Server to handle the Atomicity property of transactions – the transaction must completely commit or roll back modifications.

Automatic Transactions refer to SQL Server’s built-in behavior of wrapping every UPDATE, DELETE and INSERT statement with a transaction. This is done simply because each of these statements may affect more than one record; therefore, if one record cannot be modified, the entire statement fails. No commands are needed to activate this behavior because it is always on.
User-Defined Transactions occur when you specify the BEGIN and COMMIT or ROLLBACK TRANSACTION commands within any T-SQL code. You use these commands to wrap multiple statements that affect data and must do so as a single unit of work, such as this example:

SET XACT_ABORT ON
 
BEGIN TRANSACTION
 
/*Withdraw money */
UPDATE Current_AC SET balance = balance – 10000 WHERE account = 12345
 
/* put money into savings */
UPDATE saving SET balance = balance + 10000 WHERE account = 12346
COMMIT TRANSACTION

The above example is attempting to transfer $ 10000 out of a current account and move it to a savings account. If anything really goes wrong, the SET XACT_ABORT statement ensures that the transaction is automatically rolled back. But if both updates succeed, the COMMIT TRANSACTION completes the updates.
Another type of transaction is an Implicit Transaction, and is activated by the statement SET IMPLICIT_TRANSACTIONS ON. Essentially, with this setting enabled, SQL Server implicitly issues a BEGIN TRANSACTION statement when you issue certain commands, such as SELECT, INSERT, UPDATE or DELETE. However, the transactions do not implicitly end; therefore, you must issue your own COMMIT or ROLLBACK statements. This setting can easily cause you to have long-lasting open transactions and should only be used in scenarios where it is absolutely required (e.g. a migration from another database platform that defaults to this behavior).
To see if a transaction is currently active, you can query the @@TRANCOUNT system variable. Note that each BEGIN statement increments this value by one, each COMMIT decrements by one, but a ROLLBACK sets this value to zero. This means: first, that you can nest transactions, but only the final COMMIT statement will write the changes; second, that any ROLLBACK statement cancels all of these nested levels.
Suppose you are doing a transaction for withdrawing money from the ATM machine and at the same time the bank manager is doing a routine checkup of your transaction which is totally a different operation and suppose at the same time the bank teller is checking your account for the remaining balance. All these operations are different but accessing the same entity or resource and that is your account information that is kept inside the database. Out of these operations only you are doing write operation in the database as you are withdrawing money and the remaining balance has to be updated in the database. So a proper security mechanism must be implemented here to ensure non-conflict or smooth going of these operations. Here the security can be ensured by putting locks (and of course the type of locks) for each type of operations, which means you are isolating the resources from other transactions that may hamper its consistency. Here comes the role of Isolation levels.
ISOLATION LEVELS
Isolation levels are used to restrict access to a resource to which other concurrent transactions have access. They allow users to lock down or isolate shared database resources to four levels of granularity: The isolation level that your transaction runs in determines how sensitive your application is to changes other users’ transactions make, and consequently, how long your transaction must hold locks to protect against these changes. What locks need to be set and how it has to be established 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 (concurrency) but they may introduce concurrency related problems such as dirty-reads and data inaccuracy. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking.
Normally, it’s best to allow SQL Server to enforce isolation between transactions in its default manner; after all, isolation is one of the basic tenets of the ACID model . However, sometimes business requirements force database administrators to stray from the default behavior and adopt a less rigid approach to transaction isolation. 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.
Before we look at the different isolation levels, let us look at the anomalies that may occur as a result of concurrency.
Lost updates: It generally occurs when more than one transaction tries to update any specific record at a time i.e. when one update is successfully written to the database, but accidently a second update from different transaction overwrites the previous update information. This is called Lost Updates. For example if on your pay day your respective employers deposit your salaries into the joint account. To perform the update, each process reads the data. Assuming the deposits are of different amounts the outstanding balance would naturally reflect both deposits. In the case of lost updates, only one deposit would be reflected.

Non-repeatable reads (also called Inconsistent analysis): Dealing with inconsistent data i.e. suppose you read one value from a table and started working on it but meanwhile some other user process modifies the value in the source resulting a false output in your transaction, then it is called Non-repeatable reads. A practical example would be, suppose before withdrawing money from your account, you check your balance and find it is $140. Then you perform withdraw operation and try to withdraw $100 from your account but meanwhile the bank manager debits $75 from your account as a penalty of minimum balance (150$), as a result you have only $65 in your account now. So your transaction either fails as the demanded amount ($100) is not there in your account or it may show ($-35).We can simply say Non-repeatable reads take place if a transaction is able to read the same row several times and gets a different value for each time. Data read twice inside the same transaction cannot be guaranteed to contain the same value. Depending on the isolation level, another transaction could have nipped in and updated the value between the two reads as illustrated by the above example.

Repeatable Reads: This specifies that transactions cannot read data that has been modified by other transactions but not yet committed and if the current transaction is reading some data then no other transactions can modify that data until the current transaction completes.

Phantom reads: Phantom reads occur when a row is inserted into or deleted from a range of data by one transaction that is being read by another set of data. Suppose a user selects novels that fall under a given category and the select gets back 10 records. Another user inserts a new novel under the same category. Shortly afterward, the first user refreshes the list of the list of novels in the same category. There are now 11. This additional row is a phantom row.

Dirty reads: This is one of the types of Non-repeatable Reads. This happens when a process tries to read a piece of data while some other process is performing some update operations on that piece of data and is not completed yet. A dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back.
Now that we’ve looked at transactions and the anomalies that may occur as a result of concurrency, we will look at the different transactional levels in SQL Server. The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL transactions. The phenomena that may occur are the anomalies mentioned above.
Assumption: We have two users who are connected to SQL Server
Database Name: LocationDB
Table Name: Location
Table Column Information:

1. READ UNCOMMITTED Isolation Level:
This is very useful in case you need higher concurrency in the transactions. Here one transaction can access the data that has been modified by the second transaction even if the second transaction is not committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Example: Suppose the User1 is trying to update the Latitude from ‘37’ to ‘45’ for City with value ATHENS. And at the same time User2 is trying to read the data for the City with value ATHENS. Under normal circumstances or default setting, User2 cannot read the data from that row. But if the User2 sets the transaction isolation level to ‘Read Uncommitted’, then it is possible to read that row with updated information even if the transaction is not committed by User1.
SQL-transaction T1: User1

BEGIN TRAN
UPDATE [LocationDB].[dbo].[LOCATION]
   SET [Latitude] = 45
WHERE City= 'ATHENS'

Note that the transaction is still running, as there is no commit statement in the above code. Under default settings, the query ran by User2 will keep executing till the User1 commits the transaction.
SQL-transaction T1: User2

USE locationDB
Go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--Above statment is used to read the updated value even if the transaction is not committed.
SELECT [City]
      ,[Country]
      ,[Continent]
      ,[Latitude]
      ,[NorthSouth]
      ,[Longitude]
      ,[EastWest]
  FROM [LocationDB].[dbo].[LOCATION]
where City= 'ATHENS'

As in the above code, we set the transaction isolation level to ‘Read Uncommitted’; User2 can access that record with updated data
Output:

<a href="http://sqlconcept.com/wp-content/uploads/2012/02/Result1.jpg"><img class="alignnone size-medium wp-image-833" title="Result1" src="http://sqlconcept.com/wp-content/uploads/2012/02/Result1-300x54.jpg" alt="" width="300" height="54" /></a>

Although it increases the concurrency of the transactions one can easily tell the disadvantage behind this. What if User1 issues a ROLLBACK to his transaction or management studio of User1 crashed or hanged (As the transaction is not committed yet, it will rollback itself, resulting false or inconsistent value to User2).If this were to occur in a banking environment, it would raise confusion among clients.
Despite the fact that the Read Uncommitted transactional level leads to
• Higher Concurrency
it has a limitation of causing;
• Dirty-reads

• Lost Updates
• Phantom reads
• Non-repeatable reads
2. READ COMMITTED Isolation Level:
This is the default level set in SQL Server and the immediate higher level of ‘READ UNCOMMITTED Isolation Level’. It prevents transactions to read data if some other transaction is doing some update operation on the data and as a result eliminates Dirty Reads. It prevents reading of uncommitted data. This isolation level is however prone to the disadvantage of ‘Lost Updates’.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Example: Considering our previous example, let the Latitude for City with value ATHENS be NULL and User1 is trying to update the Latitude to 45.At the same time User2 starts a new transaction after confirming that value is Null.User2 starts updating the record to 47 before the transaction is committed by User1. As a result User1 loses his updated value since it is overwritten by User2.
SQL-transaction T1: User1

USE locationDB
Go
BEGIN TRAN
 
DECLARE @Latitude int
SELECT @Latitude = [Latitude] FROM [LocationDB].[dbo].[LOCATION] where City= 'ATHENS' 
--The below waitfor statement is used for other operations that User1 is doing for this transaction.
WAITFOR DELAY '00:00:30' --For acheiving real time Concurrency in this example
IF @Latitude IS NULL
BEGIN
UPDATE [LocationDB].[dbo].[LOCATION]
   SET [Latitude] = 45
 WHERE City= 'ATHENS' 
END
ELSE
BEGIN
	Print 'Record is already updated'
END
 
COMMIT TRAN

SQL-transaction T2: User2

USE locationDB
Go
BEGIN TRAN
 
DECLARE @Latitude int
SELECT @Latitude = [Latitude] FROM [LocationDB].[dbo].[LOCATION] where City= 'ATHENS' 
--Here waitfor statement is the same as that of user1
WAITFOR DELAY '00:00:30' --For acheiving real time Concurrency in this example
IF @Latitude IS NULL
BEGIN
UPDATE [LocationDB].[dbo].[LOCATION]
   SET [Latitude] = 47
 WHERE City= 'ATHENS' 
END
ELSE
BEGIN
	Print 'Record is already updated'
END
 
COMMIT TRAN

In this case both the users successfully updated the value, but the value updated by User2 persists and User1 lost their updated value.
Output:

 

Despite the fact that the Read Uncommitted transactional level leads to
• Elimination of Dirty Reads
it has a limitation of causing;
• Lower Concurrency than ReadUncommitted
• Lost Updates

3. REPEATABLE READ Isolation Level:

It is the next higher level than the previous isolation level. In this isolation level,a shared lock is not released once a transaction starts reading data. In simple terms, a transaction cannot read data if it has been modified by other transaction but not yet committed. Also no other transactions can modify data if that data has been read by the current transaction until the current transaction completes. The concurrency rate is very low in this level. As a result, eliminates ‘Lost updates’, non-repeatable reads, etc. But still has a big problem and that is called ‘Phantom read’. The example below will elaborate this.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Example: Suppose one is asked to transfer all the countries in the Continent of Asia to China and maintain a proper record for this operation. We need to add one more column called ‘TransferredStatus’ to indicate whether that country is transferred or not. The DBA will check for the presence of any Country in the record that are not yet transferred by checking the value of the column ‘TransferredStatus’. If he finds any, then corresponding transfer operations will end up being performed and the record will be updated to ‘1’ (i.e. transferred). In this scenario using ‘Repeatable Read’ isolation level, we can eliminate ‘Lost Update’, ‘dirty reads’ and ‘non-repeatable reads’. But what if at the time of updating the database, someone else from the inventory system inserts one record of a country that does not exist in the LOCATION table. The example below will show the effect of such an occurrence.
SQL-transaction T1: User1

USE locationDB
Go
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
 
	--check the existance of Countries in ASIA
	Declare @Country varchar
	Declare TransferingCountryCursor CURSOR FOR 
	Select Country from [LocationDB].[dbo].[LOCATION] where Continent = 'ASIA' and TransferredStatus = 0
 
	OPEN TransferingCountryCursor
 
	FETCH NEXT FROM TransferingCountryCursor 
	INTO @Country
	WHILE @@FETCH_STATUS = 0
	BEGIN
		------Country transfering operations—
 
	FETCH NEXT FROM TransferingCountryCursor 
	INTO @Country
	END 
	CLOSE TransferingCountryCursor
	DEALLOCATE TransferingCountryCursor
 
	WAITFOR DELAY '00:00:10' --For acheiving real time Concurrency in this example
	-- This is the time when the other user inserts new record with ASIA as continent 
 
	Update [LocationDB].[dbo].[LOCATION]
		set TransferredStatus = 1 where Continent = 'ASIA' and TransferredStatus = 0
 
COMMIT TRAN

Only 1 record matches the criteria.
SQL-transaction T2: User2

BEGIN TRAN
INSERT INTO [LocationDB].[dbo].[LOCATION]
           ([City]
      ,[Country]
      ,[Continent]
      ,[Latitude]
      ,[NorthSouth]
      ,[Longitude]
      ,[EastWest]
      ,[TransferredStatus])     
VALUES ('Kuala Lumpur',' Malaysia ','ASIA',35,'N',139,'E',0)
 
COMMIT TRAN

From the above, between the executions of SQL-transaction T1, SQL-transaction T2 inserts one new record. Assume the record is inserted before the Update statement of SQL-transaction T1, instead of updating only 1 record; SQL-transaction T1 updates the new record as well along with the earlier records. This leads to wrong information in the table. This is called ‘Phantom Read’. Even the ‘Repeatable Read’ isolation level cannot resolve the above issue. In this kind of scenario, one needs to implement a higher isolation level i.e. SERIALIZABLE.

Output for SQL-transaction T1
(2 row(s) affected)
Despite the fact that the REPEATABLE READ Isolation Level
• Eliminates Dirty Reads
• Eliminates Lost Updates
• Eliminates Non-Repeatable Reads
it has a limitation of causing;
• Lower Concurrency
• Phantom Reads
4. SERIALIZABLE Isolation Level:
It is highest level in Isolation levels and as a result the concurrency rate is low. It eliminates all issues related to concurrency like dirty read, non-repeatable reads, lost updates and even phantom reads as shown in the previous level. According to this Isolation Level:
1. Statements cannot read data if other transactions are performing update operations on the data and is not committed yet.
2. No other transactions can perform any update operations until the current transaction completes its read operations.
3. The important point to note here is that it is performing a Range Lock based on the filters used to get the data from the table i.e. it locks not only the current records but also the new records that are falling under the current filter condition. Simply put, there are no other transactions that can insert new rows that are falling under the current filter condition until the transaction completes.
Using our previous example, let us set the isolation level to Serializable.

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SQL-transaction T1: User1

USE locationDB
Go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 
BEGIN TRAN
 
	--check the existance of Countries in ASIA
	Declare @Country varchar
	Declare TransferingCountryCursor CURSOR FOR 
	Select Country from [LocationDB].[dbo].[LOCATION] where Continent = 'ASIA' and TransferredStatus = 0
 
	OPEN TransferingCountryCursor
 
	FETCH NEXT FROM TransferingCountryCursor 
	INTO @Country
	WHILE @@FETCH_STATUS = 0
	BEGIN
 
		------Country transfering operations--
 
	FETCH NEXT FROM TransferingCountryCursor 
		INTO @Country
	END 
	CLOSE TransferingCountryCursor
	DEALLOCATE TransferingCountryCursor
 
	WAITFOR DELAY '00:00:10' --For acheiving real time Concurrency in this example
	-- This is the time when the other user inserts new record with ASIA as continent 
	Update [LocationDB].[dbo].[LOCATION]
	set TransferredStatus = 1 where Continent = 'ASIA' and TransferredStatus = 0
 
COMMIT TRAN

SQL-transaction T1: User2

BEGIN TRAN
INSERT INTO [LocationDB].[dbo].[LOCATION]
           ([City]
      ,[Country]
      ,[Continent]
      ,[Latitude]
      ,[NorthSouth]
      ,[Longitude]
      ,[EastWest]
      ,[TransferredStatus])     
VALUES ('Kuala Lumpur',' Malaysia ','ASIA',35,'N',139,'E',0)
 
COMMIT TRAN

Output for SQL-transaction T1:
(1 row(s) affected)
In this scenario, SQL-transaction T2 will have to wait until SQL-transaction T2 completes to avoid ‘Phantom reads’.
Despite the fact that the SERIALIZABLE Isolation Level
• Eliminates Dirty Reads
• Eliminates Lost Updates
• Eliminates Non-Repeatable Reads
• Eliminates Phantom Reads
it has a limitation of causing;
• Lower Concurrency
5. SNAPSHOT Isolation Level:
The basic idea is for each transaction to “see” a consistent snapshot of the database as of transaction start, and that this snapshot remains unaffected by other concurrent update transactions. It implements Row Versioning to isolate data for each transaction i.e. it will keep separate version of each modified row in the transaction in the tempdb database totally dedicated to that transaction. Any update of data in the original row will not affect the current transaction.
The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. It is by default kept as OFF because of performance implications.
To enable the SNAPSHOT isolation level, issue the alter database command below.

ALTER DATABASE locationDB SET ALLOW_SNAPSHOT_ISOLATION ON

You can then issue the SET command below to set the SNAPSHOT isolation level,

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Example: We will try to insert a new record in the [LocationDB].[dbo].[LOCATION] table by User1 and at the same time try to fetch the records by User2.
SQL-transaction T1: User1

USE locationDB
BEGIN TRAN
INSERT INTO [LocationDB].[dbo].[LOCATION]
           ([City]
      ,[Country]
      ,[Continent]
      ,[Latitude]
      ,[NorthSouth]
      ,[Longitude]
      ,[EastWest]
      ,[TransferredStatus])     
VALUES ('Jakarta',' Malaysia ','ASIA',35,'N',139,'E',0)
 
---- Transaction not committed

SQL-transaction T2: User2

USE locationDB 
Go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
Select * from [LocationDB].[dbo].[LOCATION] where Continent = 'ASIA' 
COMMIT TRAN

Output for SQL-transaction T1
(1 row(s) affected)


Output for SQL-transaction T2:

 

One record is successfully inserted by SQL-transaction T1, but a consistent version of the previous data is kept in a Version store (in tempdb) before the starting of the transaction. So SQL-transaction T2 is accessing the data from the version store and is unable to display the newly inserted record by SQL-transaction T1
Commit the transaction for SQL-transaction T1 using the “COMMIT TRAN” command, and then re-execute SQL-transaction T2, the output will now appear as shown below:

It is possible to see which version information in the case of SQL-transaction T1.This can be done before the one COMMITS their transaction. The Dynamic Management View (DMV) below will display the version information.

select * from sys.dm_tran_active_snapshot_database_transactions

Conclusion
Working with a database system like SQL Server, it is critical to comprehend the manner in which the server deals with multiple users and their access to shared data. One needs to understand how transactions and locking are implemented in SQL Server, with an emphasis on how they apply to performance, concurrency, and the overall integrity of data.

Reference:
[1] MSDN Books Online
http://msdn.microsoft.com/en-us/library/ms173763.aspx
[2] Atul Adya, Barbara Liskov, and Patrick O’Neil (March 2000). Generalized Isolation Level Definitions. In Proceedings of the 2000 IEEE International Conference on Data Engineering, San Diego, California, pp. 67-78.
[3] Alan Fekete, Dimitrios Liarokapis, Elizabeth O’Neil, Patrick O’Neil, and Dennis Shasha (June 2005). Making snapshot isolation serializable. ACM Transactions on Database Systems 30(2), pp. 492-528.

 

Comments are closed.