FIX: The locks of a table are not escalated when you insert many rows into a table in SQL Server 2008 or in SQL Server 2008 R2

Article translations Article translations
Article ID: 2448971 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

Consider the following scenario:

  • You insert many rows into a table in Microsoft SQL Server 2008 or in SQL Server 2008 R2 by using one of the following queries:
    • INSERT INTO <target_table> SELECT * FROM <target_table> WHERE <predicate>
    • SELECT * INTO <target_table> FROM <source_table> WHERE <predicate>
    Notes
    • The <target_table> placeholder represents the actual target table name.
    • The <predicate> placeholder represents the actual predicate.
    • The <source_table> placeholder represents the actual source table.
  • The lock escalation threshold of the table is exceeded.
In this scenario, the database engine does not escalate the locks of the table.

CAUSE

This issue occurs because SQL Server does not internally count all the new locks that are generated by INSERT operations. Therefore, the lock escalation threshold might not be triggered when it is required.

RESOLUTION

Service pack information for SQL Server 2008

To resolve this issue, obtain the latest service pack for SQL Server 2008.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
968382 How to obtain the latest service pack for SQL Server 2008

Service pack information for SQL Server 2008 R2

To resolve this problem, obtain the latest service pack for SQL Server 2008 R2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2527041How to obtain the latest service pack for SQL Server 2008 R2

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem was first corrected in SQL Server 2008 Service Pack 2 for SQL Server 2008.
This problem was first corrected in SQL Server 2008 R2 Service Pack 1 for SQL Server 2008 R2.

MORE INFORMATION

For more information about lock escalation, visit the following Microsoft TechNet website:
General information about lock escalation
To determine whether the locks of a table are escalated, run the following Transact-SQL statements:
USE tempdb
GO

CREATE TABLE x 
(
i INT NOT NULL PRIMARY KEY
)
GO

BEGIN TRAN
INSERT x
SELECT TOP (40000)
ROW_NUMBER() OVER 
(
ORDER BY (SELECT NULL)
) AS r
FROM 
master..spt_values a, 
master..spt_values b
ORDER BY
r

SELECT
COUNT(*)
FROM sys.dm_tran_locks
WHERE 
request_session_id = @@SPID 
ROLLBACK
GO

DROP TABLE x   
If the locks of the table are escalated, the last SELECT statement returns a value of either 1 or 2. If the locks of the table are not escalated, the last SELECT statement returns a value of either 40,066 or 40,067.


Properties

Article ID: 2448971 - Last Review: April 22, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
Keywords: 
kbqfe kbfix kbexpertiseadvanced kbsurveynew KB2448971

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com