6

I want to create a transaction, writing some data in a sub-transaction, reading the data back, and rollback the transaction.

using(var transaction = new TransactionScope()) 
{
     using(var transaction = new TransactionScope()) 
     {
          // save data via LINQ / DataContext
          transaction.Complete();
     }
     // Get back for assertions
     var tempItem = // read data via LINQ / DataContext THROWS EXCEPTION
}

But while reading I get "System.Transactions.TransactionException : The operation is not valid for the state of the transaction.".

How should I set transaction properties to avoid this?

boj
  • 10,935
  • 5
  • 38
  • 57

2 Answers2

9

This exception cannot be debugged without the full stack trace. It has a different meaning depending on the context. Usually it means you're doing something you shouldn't inside the transaction, but without seeing db calls or stack trace all anybody can do is guess. Some common causes I know of (and this is by no means comprehensive I'm sure) include:

  1. Accessing multiple data sources (ie different connection strings) within a nested TransactionScope. This causes promotion to a distributed transaction and if you do are not running DTC it will fail. The answer is usually not to enable DTC, but to clean up your transaction or wrap the other data access with a new TransactionScope(TransactionOptions.RequiresNew).
  2. Unhandled exceptions within the TransactionScope.
  3. Any operation that violates the isolation level, such as trying to read rows just inserted/updated.
  4. SQL deadlocks; transactions can even deadlock themselves in certain cases, but if #1 applies, isolating other ops into new transactions can cause deadlocks if you're not careful.
  5. Transaction timeouts.
  6. Any other error from the database.

I definitely don't know every possible cause, but if you post the complete stack trace and the actual db calls in your code I'll take a look and let you know if I see anything.

AndyM
  • 1,057
  • 1
  • 7
  • 11
  • Andy, thanks for this answer. Question on #3 re: "any operation that violates isolation level". Would the following violate the isolation level? DECLARE (atSmbol)someCount INT; UPDATE dbo.Something SET (atSmbol)someCount = [SomeCount] = [SomeCount]+ 1 WHERE ID = 123 In my usage (atSmbol)someCount is an output parameter. If so, would this not be violating the isolation level? DECLARE (atSmbol)someCount INT; UPDATE dbo.Something SET [SomeCount] = (atSmbol)someCount = [SomeCount]+ 1 WHERE ID = 123 Note, it thinks I'm trying to tag somebody for notification when I use an at symbol. – Wes Mar 09 '16 at 00:07
4

You have two nested TransactionScope objects??

And no try catch block.

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

I think you'll find the specific answer is that you cannot complete a transaction that hasn't begun anything, it's in an invalid state. Do you actually have any code where your LINQ comments are? does a connection actually get established?

hollystyles
  • 4,979
  • 2
  • 36
  • 38