Finding cause of Lock escalation

778 views Asked by At

While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this.

Is there any way to find out why lock escalation in such place occurres?

Statement is like:

delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids

CREATE TABLE BOOK_IN_LIBRARY(
[libraryId] [bigint] NOT NULL,
[bookId] [bigint] NULL,
[otherData] [bigint]NULL,
[otherData2] [int] NULL,
[otherData3] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[libraryId] ASC,
[bookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2

There are 2 answers

0
romsac On

Deleting 20 of these id's means deleting up to 68000 rows. The statement exceeds the threshold of row locks after which database create lock escalation. More info Lock Escalation (Database Engine). The answer thanks to JamesZ comment. Thank you!

0
Ananda Kumar Jayaraman On

You may not need to worry about Lock Escalation as this will help SQL Server to process your query faster. But at the same time, as it blocks a range of records (either Page level Lock or Range Lock or Table Level Lock), the records in these pages will be blocked for other session. The other user who need to access records in these pages have to wait till the lock is released.