Isolation levels in SQL Server … part I

Just like any reliable transaction system, SQL Server supports ACID properties. If you have never heard of ACID properties before than it is actually set of four properties Atomicity, Consistency, Isolation and Durability. These set of properties makes sure that what ever transaction we do in SQL Server (or any DBMS) and database performs consistently every time.

One of the ACID property is Isolation. Which basically makes sure that one transaction is completely independent of another transaction and one transaction can’t access resources being used by another transaction. This basically controls concurrent access to any given resource in database. And it is implemented using locking of database resources.

Issues due to improper isolation level selection,

Lost Updates: user can loose update if one transaction changes data updated by another transaction. This could happen when there is no lock on resources.

Dirty Reads: While first transaction is updating data same data is being selected by second transaction, it is possible that second  transaction will see uncommitted data.

Non-repeatable Reads: This effect can be seen when one transaction is selecting same row many times and every time it returns different data because at same time another transaction is changing that data.

Phantom Reads: If some row is part of delete or insert transaction, at same time it is being accessed by second select transaction, this second select transaction can return error that that row doesn’t exists, just because it is already changed by first transaction.

And any database system faces all of these issues and they have their own mechanism to prevent from them happening. In common term this mechanism is called “Isolation levels”. SQL Server supports all 4, SQL92 standard of isolations in addition to 2 proprietary  standards. Default isolation level is READ COMMITTED which is good enough for 99% of the systems. But if needed it can be changed to other options and based on this settings query results will very widely.

1) Read Uncommitted: As name suggest, this will allow user to read uncommitted data from another transaction. And this could potentially cause “Dirty Reads”, “Non-repeatable Reads” and “Phantom Reads”. But it also generates least amount of locks on database object hence it supports highest level of concurrent access to database users.

2) Read Committed: It is default isolation level in SQL Server. And it will allow user to read only committed transaction data. And because of that it will not have dirty reads but still it will have “Non-repeatable Reads” and “Phantom Reads”.

3) Repeatable Read: It is same as “Read Committed” with extra “kick” … it will not allow to change any data that is currently being read by any transaction. Due to this restriction, it will not have “Non-repeatable Reads” but it still can have “Phantom Reads”.

4) Serializable: It is the highest possible isolation level available. It will remove any and all kind of data read issues by locking all range of rows or whole table. But as it works, it will also reduce concurrent access of users to minimum number due to high number of locking.

There are also two other type of isolations available. Which are,

5) Read Committed Snapshot: As name suggests, this isolation level takes snapshot of data before beginning of transaction and returns data from that snapshot data. And thus reducing number of locks on database objects.

6) Snapshot Isolation: This is sort of better isolation level than “Repeatable Read” since it doesn’t block object even for read operation. Yet it take snapshot of data before beginning of transaction and use that data to  return results.

By default, SQL Server uses “Read Committed” isolation level. Which will cause all query to use only committed data and thus query results might have “Dirty Reads”, “Non-repeatable Reads” and “Phantom Reads”. In general these should not be a big issue for end users, but in some specific case where these type of reads are not acceptable, we can always change isolation level in SQL Server.

In next post I will show some demo script to display concept of isolation in SQL Server.

That’s it for now.

It’s Just A Thought … Peace

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *