patternsqlMinor
Truncate Table locking sys views
Viewed 0 times
viewstruncatesyslockingtable
Problem
We have a proc that runs
Is there a convenient work around for this? I like the efficiency of not taking excess logs that happens with truncate, but don't want to lock up my
I'm happy to post the code upon request, but I'm pretty sure this is some behavior of
Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions. Is there a convenient work around for this? I like the efficiency of not taking excess logs that happens with truncate, but don't want to lock up my
sys.partitions. I'm happy to post the code upon request, but I'm pretty sure this is some behavior of
Truncate being inside a Snapshot transaction. that i'm just unaware of.Solution
Locking for truncate and delete is different. As for locking TRUNCATE behave more like 'ALTER TABLE' than DELETE.
From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row".
As you can see, there is a schema lock on the whole table.
From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row".
As you can see, there is a schema lock on the whole table.
Context
StackExchange Database Administrators Q#245898, answer score: 3
Revisions (0)
No revisions yet.