Ayende @ Rahien

Refunds available at head office

SQL CE Transaction Handling

Update: Yes, I am crazy! Turn out that I forgot to do "command.Transaction = tx;" and then I went and read some outdated documentation, and got the completely wrong picture, yuck! I still think that requiring "command.Transaction = tx;" is bad API design and error prone (duh!).

Someone please tell me that I am not crazy. The output out this program is:

Wrote item
Wrote item
Wrote item
3
Wrote item
Wrote item
4
Wrote item
Wrote item
5
Wrote item
Wrote item
6
Wrote item

This is wrong on so many levels...

public class Program
{
	const string connectionString = "Data Source=test.dsf";

	public static void Main(string[] args)
	{
		File.Delete("test.dsf");
		var engine = new SqlCeEngine(connectionString);
		engine.CreateDatabase();

		using (var connection = new SqlCeConnection(connectionString))
		{
			connection.Open();
			SqlCeCommand command = connection.CreateCommand();
			command.CommandText = @"CREATE TABLE Test(Id INT IDENTITY PRIMARY KEY, Name NVARCHAR(25) NOT NULL)";
			command.ExecuteNonQuery();
		}

		ThreadPool.QueueUserWorkItem(ReadFromDb);

		using (var connection = new SqlCeConnection(connectionString))
		{
			connection.Open();
			using(IDbTransaction tx = connection.BeginTransaction(IsolationLevel.Serializable))
			{
				while(true)
				{
					using (SqlCeCommand command = connection.CreateCommand())
					{
						command.CommandText = @"INSERT INTO Test(Name) VALUES('A');";
						command.ExecuteNonQuery();
					}
					Console.WriteLine("Wrote item");
					Thread.Sleep(500);
				}
			}
		}
	}

	private static void ReadFromDb(object state)
	{
		Thread.Sleep(1000);
		using (var connection = new SqlCeConnection(connectionString))
		{
			connection.Open();
			using (IDbTransaction tx = connection.BeginTransaction(IsolationLevel.Serializable))
			{
				while (true)
				{
					using (SqlCeCommand command = connection.CreateCommand())
					{
						command.CommandText = @"SELECT COUNT(*) FROM Test;";
						Console.WriteLine(command.ExecuteScalar()); 
					}
					Console.WriteLine("Wrote item");
					Thread.Sleep(500);
				}
			}
		}
	}
}

Comments

Rik Hemsley
03/31/2008 05:54 PM by
Rik Hemsley

You never set the commands' transactions to the transactions you create. Could that be it?

Ayende Rahien
03/31/2008 05:57 PM by
Ayende Rahien

Nope.

I checked, and it looks like SQL CE only supports ReadCommited, yuck!

Aaron Carlson
03/31/2008 06:06 PM by
Aaron Carlson

It looks like you have a extra Console.WriteLine("Wrote item") in the ReadReadFromDB function that is misleading.

Ayende Rahien
03/31/2008 06:08 PM by
Ayende Rahien

You are correct, but still, this was surprising.

Lars Wilhelmsen
03/31/2008 07:38 PM by
Lars Wilhelmsen

What!? It doesn't support Serializable? That is really insane... :-O

--larsw

Frans Bouma
03/31/2008 08:18 PM by
Frans Bouma

Let's summarize it as: SqlCE is broken at so many levels. ;)

I mean: in 3.5 they introduced named parameter binding, but ... if you now send a query with anonymous parameters ("?") which works in 3.1, it might not work due to wrong order of binding in CE 3.5....

The Other Steve
03/31/2008 08:28 PM by
The Other Steve

The thread reading shouldn't see the records that are being written until the write transaction is committed, because it's operating on a different connection.

Alex Simkin
03/31/2008 10:05 PM by
Alex Simkin

I have added (see Rick's note)

command.Transaction = tx

and everything works as expected. Read is waiting for writes to complete until timeout expires.

Ayende Rahien
03/31/2008 10:15 PM by
Ayende Rahien

And now I feel like a total idiot.

Thanks guys!

Comments have been closed on this topic.