Truncate table process blocked by select with(nolock)

I had to kill a user Query today since it was causing blocking. The user did just what was advised, use NOLOCK.. yet the user’s Query had to be killed because of this.

no-nonsense SQL Server

Where you would expect that writers don’t get blocked by readers using dirty reads, this is not the case when the writer executes a TRUNCATE TABLE command. This is because a truncate is regarded as a DDL operation (not DML). More technically, a select statement holds a sch-s(schema stability) lock (even using isolation level read uncommitted) on the object where a truncate will hold a sch-m(schema modification) lock on the object and these locks are not compatible.

View original post

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s