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?


Query is as below:

INSERT INTO <table2> SELECT * FROM <table1> WHERE table1.workCompleted = 'NO'
  • 2
    It doesn't lock the table in postgresql. – Scott Marlowe Sep 30 '11 at 15:33
  • Need more @RPK. With the table DDL and a sample of the inserts we can give you an accurate explanation of whats happening. Without it we're just guessing. – Mark Storey-Smith Sep 30 '11 at 16:23
  • This question is too vague. I'm removing any reference to other DBMS's and limiting the responses to SqlServer. If the OP or any other reader wants to discuss the merits of this core concept on other platforms, then we should have it discussed once per platform. It is detrimental to make this a cartesian join, there will be too many different threads of conversation on one page. – jcolebrand Sep 30 '11 at 17:55

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.


USE [master]

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LockDemo')

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

( NAME = N''LockDemo'', FILENAME = N''' + @DataFilePath + N'LockDemo.mdf' + ''', SIZE = 2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
( NAME = N''LockDemo_log'', FILENAME = N''' + @DataFilePath + N'LockDemo_log.ldf' + ''', SIZE = 1MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB )


USE [LockDemo]

SELECT DB_ID() AS DatabaseId

CREATE TABLE [dbo].[MyTable]
    , [filler] CHAR(4030) NOT NULL DEFAULT REPLICATE('A', 4030) 

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

select ErrorCode=@rc


Insert a row and stop the trace:

USE LockDemo
EXEC sp_trace_setstatus 3, 0
EXEC sp_trace_setstatus 3, 2

Open the trace file and you should find the following:

Profiler window

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!

  • 2
    Very informative. Nice work @Mark! – jcolebrand Oct 3 '11 at 16:43

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.

  • 4
    Hadn't noticed that rather badly worded statement in BOL before. An exclusive lock on something within the resource hierarchy will be required but it most definitely is not always the table. – Mark Storey-Smith Sep 30 '11 at 16:20
  • 6
    -1 for the docs (not your fault) - it is easy to prove this is not true in snapshot isolation so the blanket "always acquires an exclusive (X) lock" is wrong. Not sure about other isolation levels. – Jack Douglas Oct 3 '11 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.