Rotating Header Image

Microsoft…. Y U NO FIX??

YuNO

 

I would like…. if I may… it take you on a strange journey….

Consider this:

  • A SQL Server database with ALLOW_SNAPSHOT_ISOLATION set to true
  • A stored procedure that creates a #temp table in tempdb and subsequently drops it
  • Multiple calls of this stored procedure (running in Snapshot Isolation mode) happening at the same time

Please tell me how/why I would get this error:

Msg 3961, Level 16, State 1, Procedure CREATETABLE, Line 8
Snapshot isolation transaction failed in database ‘tempdb’ 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.

The_Riddler_(FG)

Riddle me that!!!! (and yes I saved you the horror of seeing me in skintight green spandex)

SETUP (credit: Loris Chiocca):

USE [master]
GO
CREATE DATABASE [SnapshotIsolationDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SnapshotIsolationDB', FILENAME = N'C:\TEMP\SnapshotIsolationDB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SnapshotIsolationDB_log', FILENAME = N'C:\TEMP\SnapshotIsolationDB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SnapshotIsolationDB] SET ALLOW_SNAPSHOT_ISOLATION ON 
GO
USE SnapshotIsolationDB
GO
CREATE PROCEDURE [dbo].[ACCESSTABLE]
AS
BEGIN
    SELECT * FROM #TEMPORARYTABLE
END
GO
CREATE PROCEDURE [dbo].[CREATETABLE]
AS
BEGIN
     CREATE TABLE #TEMPORARYTABLE (
      ID int NOT NULL primary key
     )
     waitfor delay '00:00:01'
        exec ACCESSTABLE
 
    DROP TABLE #TEMPORARYTABLE
END
GO

MULTIPLE CONNECTIONS RUNNING CODE BELOW AT THE SAME TIME (credit: Loris Chiocca):

set transaction isolation level snapshot
begin tran
    exec CREATETABLE
commit tran
go

Keep in mind you’ll probably have to rush-click-execute a few times before being able to produce the error…

So this isn’t my code or my find in the first place.

Why am I talking about it then?

3 Reasons…

  1. To hopefully bring more light to this bug
  2. To hopefully help others who run into this problem and their google-fu wasn’t able to point them to the Microsoft Connect Item.
  3. Cause it’s a thorn in my ass right now

Check this out…

https://connect.microsoft.com/SQLServer/feedback/details/777261/concurrency-issue-with-temp-tables-in-snapshot-isolation-level

And the comment of particular concern:

“Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

Inconvievable

 

If anyone is curious as to how I “fixed” my problem…. I stopped explicitly dropping the #temp tables in my stored procedure and allowed SQL Server to “clean up after itself” as the Snapshot Isolation errors always seemed to have an issue with the:

DROP TABLE #TEMPORARYTABLE

 

The question outstanding though is if #temp tables are created with the same name by different SPIDs and they exist in tempdb with the unique name that looks like : #temp_____00000090019545666 how can ANOTHER SPID be affected by the creation and dropping of said #temp table???

 

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. In light of the fact that TSMC’s 16-nanometer technology looks to be the superior of the two foundry 14/16-nanometer processes, I suspect that fabless customers will prefer to build their chips at TSMC, rather than at Samsung, given the choice. In fact, that’s exactly what Apple is said to be doing with next year’s A10.

  2. Finished off with a versatile gum sole unit that also blends well with its color scheme, you can purchase this pair at select adidas retailers overseas now

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.