Why doesn't this transactionscope work?

Question

Playing with transactions for the first time I thought I'd get the following code to work:

namespace database
{
    class Program
    {
        static string connString = "Server=ServerName;Database=Demo;Trusted_Connection=True;";
        SqlConnection connection = new SqlConnection(connString);
        static Random r = new Random();


        static void Add()
        {
            try
            {
                Thread.Sleep(r.Next(0, 10));
                using (var trans = new TransactionScope())
                {
                    using (var conn = new SqlConnection(connString))
                    {
                        conn.Open();

                        var count = (int)new SqlCommand("select balance from bank WITH (UPDLOCK) where owner like '%Jan%'", conn).ExecuteScalar();
                        Thread.Sleep(r.Next(0, 10));
                        SqlCommand cmd = new SqlCommand("update bank set balance = " + ++count + "where owner like '%Jan%'", conn);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        static void Remove()
        {
            try
            {
                Thread.Sleep(r.Next(0, 10));
                using (var trans = new TransactionScope())
                {
                    using (var conn = new SqlConnection(connString))
                    {
                        conn.Open();

                        var count = (int)new SqlCommand("select balance from bank WITH (UPDLOCK) where owner like '%Jan%'", conn).ExecuteScalar();
                        Thread.Sleep(r.Next(0, 10));
                        SqlCommand cmd = new SqlCommand("update bank set balance = " + --count + "where owner like '%Jan%'", conn);
                        cmd.ExecuteNonQuery();

                    }
                    trans.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }


        static void Main(string[] args)
        {
            for (int i = 0; i < 5; i++)
            {
                Thread t = new Thread(new ThreadStart(Add));
                t.Start();
            }
            for (int i = 0; i < 5; i++)
            {
                Thread t = new Thread(new ThreadStart(Remove));
                t.Start();
            }
            Console.ReadLine();
        }
    }
}

I assumed that at the end after 100 adds and 100 subtractions my balane would be the same as my starting point - 100, however it keeps changing up and down every time I run the script. Even with isolationlevel serializable. Could anyone tell me why? O_o

EDIT: Moved connection opening and closing to inside the transaction scope. The problem now is that I get "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction"

---

Like Marc Gravell Said: Putting the connection inside the transaction scope and adding UPDLOCK to the select query combined with changing isolationlevel to repeatableRead did the trick :)

        static void Add()
        {
            try
            {
                Thread.Sleep(r.Next(0, 10));
                using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.RepeatableRead }))
                {
                    using (var conn = new SqlConnection(connString))
                    {
                        conn.Open();

                        var count = (int)new SqlCommand("select balance from bank WITH (UPDLOCK) where owner like '%Jan%'", conn).ExecuteScalar();
                        Thread.Sleep(r.Next(0, 10));
                        SqlCommand cmd = new SqlCommand("update bank set balance = " + ++count + "where owner like '%Jan%'", conn);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

Answer 1

You have to open the connection inside the TransactionScope block.

Instead of

var conn = new SqlConnection(connString);
conn.Open();
using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable }))
{ 
    // do stuff
}

use it like this

using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable }))
using (var conn =  new SqlConnection(connString))
{ 
    conn.Open();
    // do stuff
}

This way opening the connection automatically enlists it in the TransactionScope as a lightweight transaction.

You can always look at the examples.

Answer 2

1: currently the TransactionScope might be redundant and unused; try changing the transaction to wrap the connection, not the other way around (oh, and use using):

using (var trans = new TransactionScope(TransactionScopeOption.Required,
      new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable }))
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    //...
    trans.Complete();
}

this way, the connection should enlist correctly inside the transaction (and be cleaned up properly if something bad happens)

I think the above is the main problem; i.e. not enlisting in the transaction. That means that there can be lost changes, since the read/write operation is not actually being raised to a higher isolation level.

2: however, if you do that by itself, I expect you'll see deadlocks. To avoid deadlocks, if you know you're going to update, you might want to use (UPDLOCK) on that select - this will take a write lock at the start, so that if there is a competing thread you get a block rather than a deadlock.

To be clear, this deadlock scenario is caused by:

• thread A reads the row, getting a read lock
• thread B reads the row, getting a read lock
• thread A tries to update the row, and is blocked by B
• thread B tries to update the row, and is blocked by A

Adding the UPDLOCK, this becomes:

• thread A reads the row, getting a write lock
• thread B tries to read the row, and is blocked by A
• thread A updates the row
• thread A completes the transaction
• thread B is able to continue, reads the row, getting a write lock
• thread B updates the row
• thread B completes the transaction

3: but querying to do a trivial update is silly; better just to issue an in-place update without selecting, i.e. update bank set balance = balance + 1 where ...