Concurrency Issue with Temp Tables in Snapshot Isolation Level

Concurrency Issue with Temp Tables in Snapshot Isolation Level by Loris Chiocca, Infonic AG

Active

Type:	Bug
ID:	777261
Opened:	1/23/2013 7:40:46 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Creating a temp table in a snapshot isolation level transaction causes concurrency issues.

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows 7 (all editions)

US English

The bug can be reproduced by:
1. Create an empty database
2. Add the two stored procedures "CREATETABLE" and "ACCESSTABLE" to this DB
3. Open two connections and paste the "execution script" in both (but don't run yet)
4. Run the "execution script" from the first connection
5. Wait two seconds
6. Run the "execution script" from the second connection

(Note: this is a very reduced version of what the actual code does)

You will get "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" although no DDL operations are done.

Runs without any problems.

X64

File Name	Submitted By	Submitted On	File Size
object creation.sql	Loris Chiocca, Infonic AG	1/23/2013	266 bytes
execution script.sql	Loris Chiocca, Infonic AG	1/23/2013	131 bytes

Posted by Loris Chiocca, Infonic AG on 4/30/2013 at 5:32 AM

Hi Gus,

In your last post, you state that you need additional details from my side. Could you elaborate on this? What exactly would you additonally require? Should I upload a database with the scripts attached to this issue? If you wish I can create a web-ex and demonstrate it. I'll be glad to help in any way I can

Regards
Loris

Posted by Microsoft on 4/29/2013 at 2:35 PM

Hello,

Thank you for submitting this feedback. Unfortunately the description is not detailed enough, and we need more information from the customer. Resolving back to the customer for more details.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager

Posted by Microsoft on 4/29/2013 at 2:34 PM

Hello,

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.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager