Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
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
add comment

2 Answers

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.

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:

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!

share|improve this answer
    
Very informative. Nice work @Mark! –  jcolebrand Oct 3 '11 at 16:43
add comment

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.

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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