Transaction and Concurrency on SQL Server 2005

Ayende recently posted an article about Transaction and Concurrency, and yeah, I read his blog very often, and if you are a .Net developer you should do it as well.
He had noticed that using the ReadCommitted isolation level something was not working as he had expected. I have to confess that when I first read his post and some of the comments, I had superficially concluded that could be related to the emergence of the Phantom phenomenon.

The SQL standard (at last SQL-92 and SQL-99) is defining the Phantom phenomenon as such:

P3 (‘‘Phantom’’): SQL-transaction T1 reads the set of rows N that satisfy some <search>.
SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search>, it obtains a different collection of rows.

Today I tried to test Ayende's code, and I finally understood the issue is far more interesting than what I was supposing (lecture 1: run the code you are trying to understand).

Under the hypothesis of Ayende (which are far from being exotic, but for a little point), the ReadCommitted isolation level doesn't work as expected on Microsoft SQL Server 2005! Kudos to Stuart Carnie, who had grokked this well before me, and pointed out a very interesting article from Tony Rogerson.

Rogerson's conclusions are that on Microsoft SQL Server 2005 the ReadCommitted isolation level (and its ReadCommittedSnapshot sibling) "does not give a point in time view of your data", so when the need arises, the safe way to go may be to use the Snapshot isolation level (which incidentally seems to have been a playtoy of Microsoft Research since 10 years).

Today I played a bit with Ayende's code, and I wrote a little console application I called TransactionsAndConcurrency.exe:
TransactionsAndConcurrency mode ilp ilc [iterations [records]]
The mode argument can be one of "aye", "aye-run" or "ale", ilp and ilc one of "ch", "rc", "rr", "ru", "se", "sn" (respectively Chaos, Read Committed, Repeatable Read, Read Uncommitted, Serializable and Snapshot) while iterations and records should be self-explanative ints.
In example you may call this little console application as such:
TransactionsAndConcurrency aye rc rc
or as such:
TransactionsAndConcurrency ale rc rc 20 500
You will notice that when called with aye, this application is probably working as Ayende's code, with aye-run with a slightly different behaviour (doesn't stop the first time the consumer fetch an "unexpected" amount of rows) instead with ale unexpectedly works (I tested it until with rc and ru until 5000 various times, not a single glitch).
What is the difference? The exotic point on Ayende's hypothesis, that is that its table doesn't have a primary key. If the same exercise is done with a table with a primary key (using a surrogate key through the T-SQL Identity column) everything goes fine.

I have to confess that I am still wondering if this is a bug of Microsoft SQL Server 2005, because those are obviously uncommitted phantom rows that are showing up because something in the range lock of the tables without primary keys is obviously not working as most of us would expect.

If you wish to play as well, here is the code:

using System;
using System.Data.SqlClient;
using System.Threading;
using System.Data;

namespace TransactionsAndConcurrency
{
public class Program
{
   #region Vars and Consts
   private const int DEFAULT_ITERATIONS = 20;
   private const int DEFAULT_RECORDS = 500;

   private static int records = DEFAULT_RECORDS;
   private static int iterations = DEFAULT_ITERATIONS;
   private static IsolationLevel isolationLevelProducer = IsolationLevel.Unspecified;
   private static IsolationLevel isolationLevelConsumer = IsolationLevel.Unspecified;
   private static string mode;

   private static string connectionString = "Data Source=myDB;Initial Catalog=test;User=sa;Pwd=ICannotSay1!;";
   #endregion

   #region Setup
   private static void Setup()
   {
       SqlConnection connection = new SqlConnection(connectionString);
       connection.Open();
       SqlTransaction sqlTransaction = connection.BeginTransaction();
       for (int i = 0; i < records; i++)
       {
           SqlCommand sqlCommand = connection.CreateCommand();
           sqlCommand.Transaction = sqlTransaction;
           if (mode != "ale")
           {
               sqlCommand.CommandText = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND type in (N'U')) DROP TABLE [dbo].[t]";
               sqlCommand.ExecuteNonQuery();
               string create = "CREATE TABLE [dbo].[t]("
                   + " [id] [int] NOT NULL"
                   + " ) ON [PRIMARY]";
               sqlCommand.CommandText = create;
               sqlCommand.ExecuteNonQuery();
           }
           else
           {
               sqlCommand.CommandText = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s]') AND type in (N'U')) DROP TABLE [dbo].[s]";
               sqlCommand.ExecuteNonQuery();
               string create = "CREATE TABLE [dbo].[s]("
                   + " [id] [int] IDENTITY(1,1) NOT NULL,"
                   + " [field] [int] NULL,"
                   + " CONSTRAINT [PK_s] PRIMARY KEY CLUSTERED"
                   + " ([id] ASC) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]"
                   + ") ON [PRIMARY]";
               sqlCommand.CommandText = create;
               sqlCommand.ExecuteNonQuery();
           }
           sqlCommand.Dispose();
       }

       sqlTransaction.Commit();
       Console.WriteLine("Create table");
       connection.Close();
   }
   #endregion

   #region TearDown
   private static void TearDown()
   {
       SqlConnection connection = new SqlConnection(connectionString);
       connection.Open();
       SqlTransaction sqlTransaction = connection.BeginTransaction();
       for (int i = 0; i < records; i++)
       {
           SqlCommand sqlCommand = connection.CreateCommand();
           sqlCommand.Transaction = sqlTransaction;
           if (mode != "ale")
           {
               sqlCommand.CommandText = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND type in (N'U')) DROP TABLE [dbo].[t]";
               sqlCommand.ExecuteNonQuery();
           }
           else
           {
               sqlCommand.CommandText = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s]') AND type in (N'U')) DROP TABLE [dbo].[s]";
               sqlCommand.ExecuteNonQuery();
           }
           sqlCommand.Dispose();
       }

       sqlTransaction.Commit();
       Console.WriteLine("Drop table");
       connection.Close();
   }
   #endregion

   #region Producer
   private static void Producer()
   {

       SqlConnection connection = new SqlConnection(connectionString);
       int iteration = 0;
       while (true)
       {
           connection.Open();
           SqlTransaction sqlTransaction = connection.BeginTransaction(isolationLevelProducer);
           for (int i = 0; i < records; i++)
           {
               SqlCommand sqlCommand = connection.CreateCommand();
               sqlCommand.Transaction = sqlTransaction;
               if (mode != "ale")
                   sqlCommand.CommandText = "INSERT INTO t (Id) VALUES(@p1)";
               else
                   sqlCommand.CommandText = "INSERT INTO s (field) VALUES(@p1)";
               sqlCommand.Parameters.AddWithValue("@p1", iteration);
               sqlCommand.ExecuteNonQuery();
               sqlCommand.Dispose();
           }

           sqlTransaction.Commit();
           Console.WriteLine("Wrote {0} records in iteration {1}", records, iteration+1);
           iteration += 1;
           connection.Close();
           if (iteration == iterations)
               return;
       }
   }
   #endregion

   #region Consumer
   private static void Consumer()
   {

       SqlConnection connection = new SqlConnection(connectionString);
       int iteration = 0;
       while (true)
       {
           connection.Open();
           SqlTransaction sqlTransaction = connection.BeginTransaction(isolationLevelConsumer);
           SqlCommand sqlCommand = connection.CreateCommand();
           sqlCommand.Transaction = sqlTransaction;
           if (mode != "ale")
               sqlCommand.CommandText = "SELECT COUNT(*) FROM t GROUP BY id ORDER BY id ASC";
           else
               sqlCommand.CommandText = "SELECT COUNT(*) FROM s GROUP BY field";
           SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
           if (sqlDataReader.RecordsAffected != -1)
               Console.WriteLine("Read: {0}", sqlDataReader.RecordsAffected);
           while (sqlDataReader.Read())
           {
               int count = sqlDataReader.GetInt32(0);
               if (mode != "ale")
                   Console.WriteLine("Count = {0} in {1} iteration", count, iteration+1);
               if (count != records)
               {
                   if (mode == "ale")
                       Console.WriteLine("Count = {0} in {1} iteration", count, iteration+1);
                   if (!(mode == "aye-run"))
                       Environment.Exit(1);
               }
           }

           sqlDataReader.Dispose();
           sqlCommand.Dispose();
           sqlTransaction.Commit();
           iteration += 1;
           connection.Close();
           if (iteration == iterations)
               return;
       }
   }
   #endregion

   #region Delete
   private static void Delete()
   {
       SqlConnection connection = new SqlConnection(connectionString);
       connection.Open();
       SqlTransaction sqlTransaction = connection.BeginTransaction();
       for (int i = 0; i < records; i++)
       {
           SqlCommand sqlCommand = connection.CreateCommand();
           sqlCommand.Transaction = sqlTransaction;
           if (mode != "ale")
           {
               sqlCommand.CommandText = "DELETE FROM t";
               sqlCommand.ExecuteNonQuery();
           }
           else
           {
               sqlCommand.CommandText = "DELETE FROM s";
               sqlCommand.ExecuteNonQuery();
           }
           sqlCommand.Dispose();
       }

       sqlTransaction.Commit();
       Console.WriteLine("Delete data from table");
       connection.Close();
   }
   #endregion

   #region Main
   private static void Main(string[] args)
   {
       // string describing the isolation level of the producer
       string ilp = string.Empty;
       // string describing the isolation level of the consumer
       string ilc = string.Empty;

       if ((args.Length > 2) && (args.Length < 6))
       {
           mode = args[0];
           if ((mode != "aye") &&amp;amp;amp;amp; (mode != "aye-run") && (mode != "ale"))
               Environment.Exit(2);
           ilp = args[1];
           ilc = args[2];
       }
       else
           Environment.Exit(3);
       if (args.Length > 3)
           int.TryParse(args[3], out iterations);
       if (args.Length == 5)
           int.TryParse(args[4], out records);

       isolationLevelProducer = getIsolationLevel(ilp);
       isolationLevelConsumer = getIsolationLevel(ilc);

       try
       {
           Setup();
           Delete();
           Thread p = new Thread(Producer);
           Thread c = new Thread(Consumer);
           p.Start();
           c.Start();
           while ((p.IsAlive) || c.IsAlive)
           { }
       }
       finally
       {
           TearDown();
       }
   }
   #endregion

   #region Utils
   private static IsolationLevel getIsolationLevel(string isolationLevel)
   {
       IsolationLevel il = IsolationLevel.Unspecified;

       switch (isolationLevel)
       {
           case "ch":
               il = IsolationLevel.Chaos;
               break;
           case "rc":
               il = IsolationLevel.ReadCommitted;
               break;
           case "ru":
               il = IsolationLevel.ReadUncommitted;
               break;
           case "rr":
               il = IsolationLevel.RepeatableRead;
               break;
           case "se":
               il = IsolationLevel.Serializable;
               break;
           case "sn":
               il = IsolationLevel.Snapshot;
               break;
           default:
               il = IsolationLevel.Unspecified;
               break;
       }
       return il;
   }
   #endregion
}
}