Why "Begin Transaction" before "Insert Query" locks the entire table?

Question

I am using SQL Server 2005 Express.

In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire table and all concurrent connections showed a hung display till the time this INSERT finished.

Why does the entire table get locked and how do I overcome this problem in SQL Server 2005 Express?

Edited

Query is as below:

INSERT INTO <table2> SELECT * FROM <table1> WHERE table1.workCompleted = 'NO'

Answer 1

This answer may prove helpful to the original question but is primarily to address inaccurate information in other posts. It also highlights a section of nonsense in BOL.

And as stated for the INSERT documentation, it will acquire an exclusive lock on the table. The only way a SELECT can be made against the table is to use NOLOCK or set the isolation level of the transaction.

The linked section of BOL states:

An INSERT statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. For more information, see Locking in the Database Engine.

NB: As of 2014-8-27 BOL has been updated to remove the incorrect statements quoted above.

Thankfully this is not the case. If it were so inserts to a table would occur serially and all readers would be blocked from the entire table until the insert transaction completes. That would make SQL Server as efficient a database server as NTFS. Not very.

Common sense suggests it cannot be so but as Paul Randall points out, "Do yourself a favour, trust no-one". If you can't trust anyone, including BOL, I guess we'll just have to prove it.

Create a database and populate a dummy table with a bunch of rows, noting the DatabaseId returned.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LockDemo')
DROP DATABASE [LockDemo]
GO

DECLARE @DataFilePath NVARCHAR(4000)
SELECT 
    @DataFilePath = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM 
    master.sys.master_files
WHERE 
    database_id = 1 AND file_id = 1

EXEC ('
CREATE DATABASE [LockDemo] ON  PRIMARY 
( NAME = N''LockDemo'', FILENAME = N''' + @DataFilePath + N'LockDemo.mdf' + ''', SIZE = 2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
 LOG ON 
( NAME = N''LockDemo_log'', FILENAME = N''' + @DataFilePath + N'LockDemo_log.ldf' + ''', SIZE = 1MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB )
')

GO

USE [LockDemo]
GO

SELECT DB_ID() AS DatabaseId

CREATE TABLE [dbo].[MyTable]
(
    [id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [filler] CHAR(4030) NOT NULL DEFAULT REPLICATE('A', 4030) 
)
GO

INSERT MyTable DEFAULT VALUES;
GO 100

Setup a profiler trace that will track lock:acquired and lock:released events, filtering on the DatabaseId from the previous script, setting a path for the file and noting the TraceId returned.

declare @rc int
declare @TraceID int
declare @maxfilesize BIGINT
declare @databaseid INT
DECLARE @tracefile NVARCHAR(4000)

set @maxfilesize = 5 
SET @tracefile = N'D:\Temp\LockTrace'
SET @databaseid = 9

exec @rc = sp_trace_create @TraceID output, 0, @tracefile, @maxfilesize, NULL 
if (@rc != 0) goto error

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 24, 32, @on
exec sp_trace_setevent @TraceID, 24, 1, @on
exec sp_trace_setevent @TraceID, 24, 57, @on
exec sp_trace_setevent @TraceID, 24, 3, @on
exec sp_trace_setevent @TraceID, 24, 51, @on
exec sp_trace_setevent @TraceID, 24, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 3, @on
exec sp_trace_setevent @TraceID, 60, 51, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 23, 32, @on
exec sp_trace_setevent @TraceID, 23, 1, @on
exec sp_trace_setevent @TraceID, 23, 57, @on
exec sp_trace_setevent @TraceID, 23, 3, @on
exec sp_trace_setevent @TraceID, 23, 51, @on
exec sp_trace_setevent @TraceID, 23, 12, @on

-- DatabaseId filter
exec sp_trace_setfilter @TraceID, 3, 0, 0, @databaseid

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Insert a row and stop the trace:

USE LockDemo
GO
INSERT MyTable DEFAULT VALUES
GO
EXEC sp_trace_setstatus 3, 0
EXEC sp_trace_setstatus 3, 2
GO

Open the trace file and you should find the following:

The sequence of locks taken is:

1. Intent-Exclusive lock on MyTable
2. Intent-Exclusive lock on the page 1:211
3. RangeInsert-NullResource on the clustered index entry for the value being inserted
4. Exclusive lock on key

The locks are then released in reverse order. At no point has an exclusive lock been acquired on the table.

But this is just one batch inserting! That's not the same as two, three or dozens running in parallel.

Yes it is. SQL Server (and arguably any relational database engine) has no foresight as to what other batches may be running when it processes a statement and/or batch, so the sequence of lock acquisition does not vary.

What about higher isolation levels e.g. Serializable?

For this particular example exactly the same locks are taken. Don't trust me, try it!

Answer 2

I don't do much T-SQL work but from reading documentation...

This is by design, as stated in the BEGIN TRANSACTION:

Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.

And as stated for the INSERT documentation, it will acquire an exclusive lock on the table. The only way a SELECT can be made against the table is to use NOLOCK or set the isolation level of the transaction.