Blocking A Truncate Statement

Arun Sirpal shows that the TRUNCATE command needs to take locks like any other data modification command:

The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?

What am I talking about? When you issue a truncate it takes a Sch-M lock and it uses this when it is moving the allocation units to the deferred drop queue. So if it takes this lock and you look at the locking compatibility matrix below you will see what can cause a conflict (C).

Arun includes an image which shows what can block what, and also shows us an example.

Related Posts

Rewriting Expensive Updates

Erik Darling takes us through an experiment: Let’s also say that bad query is taking part in a modification. UPDATE u2SET u2.Reputation *= 2FROM Users AS uJOIN dbo.Users AS u2ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0WHERE u2.Reputation >= 100000;AND u.Id <> u2.Id; This query will run for so long that we’ll get sick of waiting for it. […]

Read More

Lock Promotion

Erik Darling tries to figure out why his locks can’t get ahead in the rat race: The first thing I found is that there were 16 attempts at promotion, and four successful promotions.Why did this seem weird? I dunno.Why would there be only 4 successful attempts with no competing locks from other queries?Why wouldn’t all […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031