Sql-server – TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE

snapshot-isolationsql serversql-server-2008-r2truncate

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?

Best Answer

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.

Related Question