Summary: This post discusses the basics of SQL Server Transaction Isolation Levels.

One of the basic characteristics of a transaction is the Isolation Level. This is the "I" in ACID (Atomicity, Consistency, Isolation and Durability) properties. Transaction isolation specifies the level at which one transaction should be isolated from others.

Let me give an example, Assume that transaction A is updating a sales order. it has to update order header and order details. At first it updates sales order. But did not update the details yet. At this stage (header is updated and detail is not), if user B queries the table, what should he see? should he see the partially updated data? Well there may be a few possibilities

• We may allow user B to see the partially updated data
• May be we can show the values before Transaction A started
• May be we should not allow user B to read this data. The query that user B runs should wait until we complete the update
• May we should skip the row being updated. When user B queries, he should see all rows, but not the order being updated currently
• etc etc

Well, based on the nature of the application and specific business activity being performed, you might want perform one of the operations mentioned above. This behavior depends on the isolation level of the current transaction. (Transaction isolation is much more than the simple example I have given above. We will see it in the next few posts where I will explain the various transaction Isolation levels).

SQL Server 2000 supports the following transaction isolation levels.

READ COMMITTED

READ UNCOMMITTED

REPEATABLE READ

SERIALIZABLE

SQL Server 2005 added a new transaction isolation level: SNAPSHOT.

I will explain each of the above transaction isolation levels in the next few posts, with a few examples. SQL Server uses (in most cases) different locking mechanism to implement the desired transaction isolation level. So we will also look at locks, lock escalation and locking optimizer hints after we discuss transaction isolation levels.

See also

1. Introduction to SQL Server Transaction Isolation Levels
2. SQL Server Transaction Isolation Level - READ COMMITTED
3. SQL Server Transaction Isolation Level - READ UNCOMMITTED
4. SQL Server Transaction Isolation Level - REPEATABLE READ
5. SQL Server Transaction Isolation Level - SERIALIZABLE
6. SQL Server Transaction Isolation Level - SNAPSHOT