Select blocking a Truncate stamemet

Question

Hi All,

I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 5 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level.

Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive.

Truncate is waiting for="Sch-M" lock.

How to fix this blocking?

Below are the sql stmts involved in blocking.

blocking process

=================

SELECT

blocked process

=================

TRUNCATE TABLE "dbo"."DNB_INCR_REF_ORGS"

estimated plan link

https://www.brentozar.com/pastetheplan/?id=rktPkssh2

Thanks,

Sam

Answer 1

A SELECT always takes locks on tables. In READ UNCOMMITTED, READ COMMITTED SNAPSHOT and SNAPSHOT isolation, that lock will be Sch-S, Schema Stability, which is the lightest lock you can take. The lock prevents the schema from changing, but no rows are locked.

ASYNC_NETWORK_IO means that the SQL Server is waiting for the client to pick up the result set. A poorly written client may have decided to take a holiday in Spain and come back three weeks later to read the remaining rows.

TRUNCATE TABLE is logically a DELETE operation, but physically, it's a kind of a half DROP TABLE. That is, it deallocates all pages for the table. It requires a Sch-M, schema-modification lock, which is the strongest lock you can take. And this lock is blocked by Sch-S.

In this particular sitaution, there are two options:

1. Use DELETE rather than TRUNCATE TABLE. DELETE will not be blocked by that SELECT.
2. Kill the SELECT query. (And with five hours and ASYNC_NETWORK__IO, I would be tempted to that.)

Answer 2

Some client on the network has started a select query, but has not finished reading the rows. as the rows are read, locks are taken and released. The final lock will not be released until all rows in the select have been sent to the client, that is the query completes. Maybe there is a dependency query, those are typically long running.

A truncate, as it removes all rows, can only execute when there are no locks on the table.

you probably should just kill the select.