4

I'm having trouble suppressing part of a transaction using Sql Server CE 4 with Entity Framework and System.Transactions.TransactionScope.

The simplified code below is from a unit test demonstrating the problem.

The idea is to enable the innerScope block (with no transaction) to succeed or fail without affecting the outerScope block (the "ambient" transaction). This is the stated purpose of TransactionScopeOption.Suppress.

However, the code fails because it seems that the entire SomeTable table is locked by the first insert in outerScope. At the point indicated in the code, this error is thrown:

"SQL Server Compact timed out waiting for a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default lock timeout can be increased in the connection string using the ssce: default lock timeout property. [ Session id = 2,Thread id = 2248,Process id = 13516,Table name = SomeTable,Conflict type = x lock (x blocks),Resource = PAG (idx): 1046 ]"

[TestMethod()]
[DeploymentItem("MyLocalDb.sdf")]
public void MyLocalDb_TransactionSuppressed()
{
    int count = 0;

    // This is the ambient transaction
    using (TransactionScope outerScope = new TransactionScope(TransactionScopeOption.Required))
    {
        using (MyObjectContext outerContext = new MyObjectContext())
        {
            // Do something in the outer scope
            outerContext.Connection.Open();
            outerContext.AddToSomeTable(CreateSomeTableRow());
            outerContext.SaveChanges();
            try
            {
                // Ambient transaction is suppressed for the inner scope of SQLCE operations
                using (TransactionScope innerScope = new TransactionScope(TransactionScopeOption.Suppress))
                {
                    using (MyObjectContext innerContext = new MyObjectContext())
                    {
                        innerContext.Connection.Open();
                        // This insert will work
                        innerContext.AddToSomeTable(CreateSomeTableRow());
                        innerContext.SaveChanges(); // ====> EXCEPTION THROWN HERE
                        // There will be other, possibly failing operations here
                    }
                    innerScope.Complete();
                }
            }
            catch { }
        }
        outerScope.Complete();
    }

    count = GetCountFromSomeTable();
    // The insert in the outer scope should succeed, and the one from the inner scope
    Assert.AreEqual(2, count);
}

So, it seems that "a transaction in a transaction scope executes with isolation level set to Serializable", according to http://msdn.microsoft.com/en-us/library/ms172001

However, using the following code snippet to change the isolation level of the TransactionScope does not help:

public void MyLocalDb_TransactionSuppressed()
{
    TransactionOptions opts = new TransactionOptions();
    opts.IsolationLevel = IsolationLevel.ReadCommitted;
    int count = 0;

    // This is the ambient transaction
    using (TransactionScope outerScope = new TransactionScope(TransactionScopeOption.Required, opts))
    ...

The same exception is thrown at the same location.

It seems the only way to avoid this is to call outerScope.Complete() before entering the innerScope block. But this would defeat the purpose.

What am I missing here? Thanks.

user1425515
  • 41
  • 1
  • 3

2 Answers2

1

AFAIK SQL Server Compact does not support nested transactions.

josemiguel.torres
  • 632
  • 4
  • 6
  • Thanks for the reply. But, my example above works perfectly if, for example, the initial insert is into another table. If I insert into `SomeOtherTable` in `outerScope`, the code works as expected. So the problem is with the table-level lock. – user1425515 May 30 '12 at 10:52
  • 1
    These are parallel transactions - which are supported by SQLce, but your original test code creates nested, and as Jose said, they are not supported. – Vanja Nov 19 '12 at 07:32
0

And why do you do that this way? If I look at your code there is no difference between running the second transaction scope inside the first one and running them in sequence.

IMHO this is not a problem of SQL Compact, TransactionScope or isolation level. This is a problem of your wrong application logic.

Each SaveChanges runs in transaction - either outer transaction defined by TransactionScope or inner DbTransaction. Even if it would not create transaction every database command has its own implicit transaction. If you use Suppress on the inner code block you are creating two concurrent transactions which are trying to insert into same table and moreover the first cannot complete without completing the second and the second cannot complete without completing the first => deadlock.

The reason is that insert command always locks part of the table not allowing new inserts until it is committed or rolled back. I'm not sure if this can be avoided by changing transaction isolation level - if it does, you will most probably need Read.Uncommitted.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • As I explicitly stated, this is not the actual application logic. It is "simplified code [...] from a unit test demonstrating the problem". The problem is that the table is locked, and I asked how to avoid that. – user1425515 May 30 '12 at 10:44
  • 1
    *The problem is that the table is locked, and I asked how to avoid that* You will avoid that only if those two transactions will not wait to each other. This is simply the way how database works. – Ladislav Mrnka May 30 '12 at 11:09
  • So, you are saying that there is *NO* way to prevent a single-row insert from performing a Table-Level lock in SQL CE? – user1425515 May 31 '12 at 01:50
  • I'm saying that I don't know about the way how to achieve it because it is conceptually wrong - it breaks core database behavior. What you are trying to achieve is allowing concurrent inserts without locking and that can result in data loss or inconsistency. If you want to know for sure if it is possible, create a new question just about SQL CE (skip .net and EF which are completely unrelated to the problem). Also if you find the way you will have to execute it in direct SQL, not in EF. You should change your application logic and execute two transactions in sequence. – Ladislav Mrnka May 31 '12 at 09:11
  • Thanks for your help. I will try a more succinct question. However, your claim -- that concurrent inserts into the same table MUST introduce a table-lock to enforce consistency -- is incorrect. It happens in databases ALL THE TIME, and is definitely not "conceptually wrong". – user1425515 May 31 '12 at 09:20