Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you use DELETE FROM foo instead? That won't place the schema lock. –  SqlACID Jun 5 at 22:57
    
DELETE FROM is indeed the way I am working around this. I am also interested in why I am getting the error (and only after Procedure1 returns). –  Mark Jun 6 at 3:21
add comment

1 Answer 1

up vote 14 down vote accepted

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.

share|improve this answer
    
I tend to fall on the DDL side of the argument due to the reset of IDENTITY columns on TRUNCATE. Would be nice to see the official docs agreeing if nothing else! :) –  Mark Storey-Smith Jun 5 at 20:46
    
@MarkStorey-Smith I'm more on the DML side of the issue, but as I say, there are good arguments both ways. I'm not certain it really matters much, to be honest. –  Paul White Jun 5 at 23:33
    
TRUNCATE TABLE does not delete rows; it deallocates extents. (I vote for DDL.) This means, as the error says, that there is no metadata about any changes to rows that could be used by Snapshot isolation level. As @SqlACID says above, use DELETE FROM instead of TRUNCATE TABLE. Also note that only the table owner can run TRUNCATE TABLE ; it is not a command that can be delegated to other principals. –  Greenstone Walker Jun 6 at 3:18
    
@Greenstone, let's just say that the security for this database leaves a lot to be desired - the third-party application was originally installed with all users getting db_owner. I'm hoping to tighten things up over time, and it sounds like I need to replace the TRUNCATEs with DELETEs as a step on that road anyway. –  Mark Jun 6 at 3:25
    
@Paul, I definitely agree about it not being documented well. I searched Google and read articles for over an hour before posting here because I couldn't find a definitive answer from Microsoft, or anyone else, that specifically addressed SNAPSHOT vs. TRUNCATE. Thanks for your help. –  Mark Jun 6 at 3:27
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.