Summary: The default transaction isolation level of SQL server is READ COMMITTED. This article explains the behavior of READ  COMMITTED isolation level.

Under READ COMMITTED isolation levels, changes to the data is not visible to other transactions until the transaction is committed. When reading data, SQL Server will attempt to put a shared lock on all the rows that it reads and when it finds a row  exclusively locked by another transaction, the query will wait till the transaction completes and the lock is released. Let us look at an example to understand this.

Let us create the sample tables needed for this session.

---------------------------------------------------------------------
-- Create tables
---------------------------------------------------------------------
IF OBJECT_ID('Consultants') IS NOT NULL DROP TABLE Consultants
GO

CREATE TABLE Consultants (
    ConsultantID INT IDENTITY,
    ConsultantName VARCHAR(20),
    Technology VARCHAR(15),
    HireDate DATETIME, 
    HourlyRate MONEY)

---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Smith', 'ASP.NET', '2005-01-01', 140000    
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Michael', 'C#.NET', '2002-10-30', 80000

SELECT * FROM Consultants
/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
2            Smith                ASP.NET         2005-01-01 00:00:00.000 140.00
3            Michael              C#.NET          2002-10-30 00:00:00.000 100.00
*/

Open a new query analyzer/Management Studio and run the following. (Query Window 1)

BEGIN TRAN
UPDATE c SET
    HourlyRate = 250
FROM Consultants c
WHERE ConsultantName = 'Smith'

Note that we started a transaction but did not commit or rollback it. So the transaction is open. Now let us see what happens if another session tries to read rows from this table. Open a new query analyzer/Management studio window (Query window 2) and run the following.

SELECT * FROM Consultants

Note that the query blocks and does not return. This query blocks with the transaction running on the other session. Now move back to Query Window 1 and COMMIT the transaction.

COMMIT TRANSACTION

Now switch back again to Query Window 2. You will notice that the query returned the following.

ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
2            Smith                ASP.NET         2005-01-01 00:00:00.000 250.00
3            Michael              C#.NET          2002-10-30 00:00:00.000 100.00

When you run the query initially, one of the rows was locked by the transaction on the other window. Under READ COMMITTED isolation level, SQL Server will attempt to put a SHARED lock on all the rows it reads. It will apply apply a SHARED lock on the first row and will read it. After reading the information, the lock will be released. Then it will try to apply a lock on the next row (smith). Since this row is already locked EXCLUSIVELY by the transaction on Query Window 1, a lock cannot be obtained. So the query processor will wait for the lock on the second row to be released.

When we committed the transaction on Query Window 1, the transaction completes and the locks will be released. At this point, the query processor on Query Window 2 can obtain a SHARED lock on the second row. It will apply a lock and read the information, release the lock and move to the next row. This is the reason why we received the results immediately after the transaction on the other window was committed.

Ok, that means the second row was locked. So can we read the first row without causing a block? Let us try that. Go back to Query Window 1 and run the previous query again.

BEGIN TRAN
UPDATE c SET
    HourlyRate = 250
FROM Consultants c
WHERE ConsultantName = 'Smith'

Now move back to Query Window 2 and run the following.

SELECT * 
FROM Consultants
WHERE ConsultantID = 1

You will note that the query does not return. It blocks!!!  Cancel the query and try to read the third row.

SELECT * 
FROM Consultants
WHERE ConsultantID = 3

This one blocks too. What could be the reason? Well, the answer lies in the execution plan. Let us look at the execution plan of this query. You can view the graphical execution plan from the toolbar or from the menu. Or you can run the following to get the execution plan in text format. (Before you do that, COMMIT or ROLLBACK the transaction on Query Window 1)

SET SHOWPLAN_TEXT ON;
GO

SELECT * 
FROM Consultants
WHERE ConsultantID = 3

/*
StmtText
--------------------------------------------------------------------------------------
  |--Table Scan(OBJECT:([master].[dbo].[Consultants]), 
      WHERE:([master].[dbo].[Consultants].[ConsultantID]=CONVERT_IMPLICIT(int,[@1],0)))
*/

Look at the table scan operator. This means that SQL Server will do a table scan to return the results we requested. Table scan means reading the first row through the last row. So, in the case of the first query, it reads the first row without any problem. But then, the query processor does not know that we have only one row with the given ID. It will move to the next record to see if that matches the given criteria. And this will cause the blocking behavior.

Let us create a clustered index on the ConsultantID column and see what happens.

ALTER TABLE Consultants
ADD CONSTRAINT PK_Consultants PRIMARY KEY (ConsultantID)

Now move to Query Window 1 and update the record of Smith.

BEGIN TRAN
UPDATE c SET
    HourlyRate = 250
FROM Consultants c
WHERE ConsultantName = 'Smith'

Move to Query Window 2 and run the following queries.

SELECT * 
FROM Consultants
WHERE ConsultantID = 3

/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
3            Michael              C#.NET          2002-10-30 00:00:00.000 100.00
*/

SELECT * 
FROM Consultants
WHERE ConsultantID = 1

/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
*/

Note that the query returns immediately. It does not block. Let us see what has changed in the execution plan. Here is the execution plan of this query.

SET SHOWPLAN_TEXT ON
GO

SELECT * 
FROM Consultants
WHERE ConsultantID = 1

/*
StmtText
-----------------------------------------------------------------------------------------------------
  |--Clustered Index Seek(OBJECT:([master].[dbo].[Consultants].[PK_Consultants]), 
    SEEK:([master].[dbo].[Consultants].[ConsultantID]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
*/

Note that the query processor is using a clustered index seek. It is not scanning the entire table any more. Hence it does not need to touch row number 2 which is locked by the transaction on the other session. That is the reason why this query does not block.

SELECT * 
FROM Consultants
WHERE ConsultantName = 'Jacob'

I am pretty sure you know the reason. We dont have an index on ConsultantName and hence a table scan will occur. This will block the query as soon as it reaches the second row.

Locking Hints

You can control the locking behavior using locking hints. For example NOLOCK will not apply a read lock on the row being retrieved. READPAST will skip the rows currently locked etc. We will examine these locking hints in the next couple of posts when we examine the other 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