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?