Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 10 | Comments: 35

filter by tags archive

SQL CE Transaction Handling

time to read 2 min | 338 words

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

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

Ayende Rahien

Nope.

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

Aaron Carlson

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

Ayende Rahien

You are correct, but still, this was surprising.

Lars Wilhelmsen

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

--larsw

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

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

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

And now I feel like a total idiot.

Thanks guys!

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Production postmortem: The case of the memory eater and high load - 2 days from now
  2. Production postmortem: The case of the lying configuration file - 3 days from now
  3. Production postmortem: The industry at large - 4 days from now
  4. The insidious cost of allocations - 5 days from now
  5. Find the bug: The concurrent memory buster - 6 days from now

And 4 more posts are pending...

There are posts all the way to Sep 10, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    14 Aug 2015 - The case of the man in the middle
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats