Lock Escalation and Outer Joins

A common work around to avoid lock escalation is to limit the number of rows per transaction, for example deleting 1,000 rows at a time. However, if a delete is based on an outer join, for example a left join, and the left table is large enough that will require more than 5,000 page locks to read it, the Database Engine will escalate those page locks to table lock regardless of how many rows are deleted.

I can reproduce the issue by running the below queries that populate a parent table with 20,000 values and a child table with 200,000 values.

 
create table parent_table(
parent_id int identity primary key
,parent_col char(255) not null
)

insert parent_table(parent_col)
values('a')
go 20000

create table child_table(
child_id int identity primary key
,parent_id int not null
,child_col char(255) not null
)

insert child_table(parent_id, child_col)
select parent_id, parent_col
from parent_table
go 10

Then delete from the child table rows that don’t have a match in the parent table using a left outer join. In this example it won't find any orphans so it won't delete any row.

 
delete top (1000) child_table
from child_table c
left join parent_table p
on c.parent_id = p.parent_id
where p.parent_id is null

I used the lock acquired event to show what locks were acquired (I will explain the extended events I used in the next post). You will see that due to the left outer join logic the query has to scan all the rows in the child table using an Update (U) lock, and it eventually escalates to Exclusive (X) table lock, blocking any subsequent query on the child table for the duration of the transaction.

A work around is to use a temp table as an intermediate step to find the matching child id

 
select c.child_id
into #temp
from child_table c
left join parent_table p
on c.parent_id = p.parent_id
where p.parent_id is null

delete top (1000) child_table
from child_table c
join #temp t
on c.child_id = t.child_id

Now you will see on the child table an Intend Shared (IS) table lock on the select into portion, and an Intend Exclusive (IX) table lock on the delete section, instead of X table lock in the previous query, allowing more concurrency on the child table.