TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

Question

I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE.

Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = False.

Procedure1 (Replaces content of table foo from a long-running complex SELECT with lots of joins):

BEGIN TRAN; 
TRUNCATE TABLE foo; 
INSERT INTO foo SELECT...; 
COMMIT;

Procedure2 (Reads from table foo):

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; 
SELECT * FROM foo;

If Procedure1 is running while Procedure2 is executed, Procedure2 is held up with a LCK_M_SCH_S wait (according to sp_WhoIsActive) until Procedure1 finishes. And when Procedure2 does complete, it raises this exception:

Snapshot isolation transaction failed in database 'DatabaseName' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

However, Microsoft does not list TRUNCATE as a DDL statement not permitted under SNAPSHOT isolation: http://msdn.microsoft.com/en-us/library/bb933783.aspx

Clearly I'm not understanding something correctly, as I would have expected a best case of Procedure2 immediately returning the most recently committed data from the table before the TRUNCATE or a worst case of being held up by Procedure1 and then returning the new content of the table. Can you help?

Answer 1

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online.

From the point of view of a snapshot isolation transaction, truncate has the essential quality of taking a Sch-M lock, which explains the blocking ( because RCSI and SI still acquire Sch-S locks); and it also bumps the internal metadata version (for internal reasons*) resulting in error 3961.

So, the behaviour you are seeing is expected, just not documented very well.

* _{The current implementation of TRUNCATE TABLE does not generate row versions. Bumping the metadata version is the simplest way to ensure correct behaviour.}