1

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]
romsac
  • 67
  • 8
  • 1
    Do you have any estimate about how many rows those 20 ids are? – James Z Jun 19 '15 at 08:48
  • Often you can spot it in the query plan (as an intelligent guess). Post the actual execution plan as XML, maybe on pastebin. – usr Jun 19 '15 at 11:56
  • JamesZ good question! It's even up to 3400. I was not expecting that it will be so many rows.. So I have reached threshold after which SQL Server choose PAGE/TABLE Lock instead of simply row lock. Thank you! – romsac Jun 19 '15 at 13:06

2 Answers2

0

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.

Ananda Kumar Jayaraman
  • 380
  • 1
  • 6
0

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!

Community
  • 1
  • 1
romsac
  • 67
  • 8