The choice of using Linq queries as the default syntax was not an accident. If you look at how Couch DB is doing things, you can see that the choice of Javascript as the query language can cause some really irritating imperative style coding. For example, look at this piece of code:

function(doc) {

  if (doc.type == "comment") {

    map(doc.author, {post: doc.post, content: doc.content});

  }

}

This works, and it allows for some really complicated solutions, but it comes with its own set of problems. Unlike Couch DB, I actually want to enforce a schema for the views, and I need to be able to tell that schema at view creation time. This is partly because of the storage engine choice, and partly because the imperative style means that it is very easy to violate some of the map reduce required behaviors, such as repeatability of the results (by querying a separate data source, for example).

Linq queries are not imperative, they are a good way of expressing set based logic in a really nice way, while still allowing for an almost embarrassingly complex set of problems to be expressed with them. More than that, Linq queries are strongly typed, provide me with a whole bunch of information and allow me to do some really interesting things along the way, some of which we will talk about later. There is also the issue of how easy it would be to utilize such things as PLinq, or that the extensibility story for the DB becomes much easier with this scenario, or that at least in a theoretical perspective, the performance that we are talking about here should be much better than a Javascript based solution. 

Another property of Linq that I considered, much as I am loath to admit it in such a public forum is the marketing aspect of it. A linq-driven database is sure to get a lot of attention, you only have to look at the number of comment on the previous posts in this topic, compare those with linq queries to those without the linq queries. The difference is quite astounding.

All in all, it sounds like an impressive amount of reason to go with Linq.

The problem, of course, is that Linq implies C#, and I don’t really think that C# is the best language for doing language oriented programming. This time, however, we have the major advantage that the domain concepts that we want are already built into the language, so we don’t really need a lot of tweaking here to get things exciting.

I posted about the syntax before, but I don’t think that a lot of people actually got what I meant. Here is the entire view definition:

It is not a snippet, and it is not a part of something larger that I am not showing. This is the view. And yes, it is not compliable on its own. Nor do I imagine that we will see people writing this code in Visual Studio. Or, at least, I imagine that it will be written there, but it will not stay there.

Much like in Couch DB today, you are going to have to create the view on the server, and you do that by creating a specially named document, which will contain this syntax as its content.

Internally, we are going to do some interesting things to it, but I think that I can stop now by just showing your the first stage, what happens to the view code after preprocessing it:

Readers of my book should recognize the pattern, I am using the notion of Implicit Base Class here to get us an executable class, which we can now compile and execute at will. Note that the query itself was modified, to make it compliable. We can now proceed to do additional analysis of the actual query, generate the fixed schema out of it, and start doing the really interesting things that we want to do.

But I have better leave those for another post…

One of the more interesting problems with document databases is the views, and in particular, how are we going to implement views that contain aggregation. In my previous post, I discussed the way we will probably expose this to the users. But it turn out that there are significant challenges in actually implementing the feature itself, not just in the user visible parts.

For projection views, the actual problem is very simple, when a document is updated/removed, all we have to do is to delete the old view item, and create a new item, if applicable.

For aggregation views, the problem is much harder, mostly because it is not clear what the result of adding, updating or removing a document may be. As a reminder, here is how we plan on exposing aggregation views to the user:

Let us inspect this from the point of view of the document database. Let us say that we have 100,000 documents already, and we introduce this view. A background process is going to kick off, to transform the documents using the view definition.

The process goes like this:

Note that the process depict above is a serial process. This isn’t really useful in the real world. Let us see why. I want to add a new document to the system, how am I going to update the view? Well… an easy option would be this:

I think you can agree with me that this is not a really good thing to do from performance perspective. Luckily for us, there are other alternative. A more accurate representation of the process would be:

We run the map/reduce process in parallel, producing a lot of separate reduced data points. Now we can do the following:

We take the independent reduced results and run a re-reduce process on them again. That is why we have the limitation that map & reduce must return objects in the same shape, so we can use reduce for data that came from map or from reduce, without caring where it came from.

This also means that adding a document is a much easier task, all we need to do is:

We get the single reduced result from the whole process, and now we can generate the final result very easily:

All we have to do is run the reduce on the final result and the new result. The answer from that would be identical to the answer running the full process on all the documents. Things get more interesting, however, when we talk about document update or document removal. Since update is just a special case of atomic document removal and addition, I am going to talk about document removal only, in this case.

Removing a document invalidate the final aggregation results, but it doesn’t necessarily necessitate recalculating the whole thing from scratch. Do you remember the partial reduce results that we mentioned earlier? Those are not only useful for parallelizing the work, they are also very useful in this scenario. Instead of discarding them when we are done with them, we are going to save them as well. They wouldn’t be exposed to the user at any way, but they are persisted. They are going to be useful when we need to recalculate. The fun thing about them is that we don’t really need to recalculate everything. All we have to do is recalculate the batch that the removed document resided on, without that document. When we have the new batch, we can now reduce the whole thing to a final result again.

I am guessing that this is going to be… a challenging task to build, but from design perspective, it looks pretty straightforward.

I said that I would speak a bit about aggregations. On the face of it, aggregation looks simple, really simple. Continuing the same thread of design from before, we can have:

The problem is that while this is really nice, it doesn’t really work.

The problem is that using this approach, we are going to have to recalculate the view for the entire document set that we have, a potentially very expensive operation. Now, technically I can solve the problem by rewriting the Linq statement. The problem is that it wouldn’t really work. While it is possible to do so, it wouldn’t really work because the following code assume that it knows all the state, and there is no way to regenerate that state in an incremental fashion.

Let us try a better approach:

Thanks for Alex Yakunin, for helping me simplify this.

What do we have now? We split the problem into two sections, the Map and the Reduce. Note that to simplify things, map and reduce must return objects in the same shape. That means that we don’t need an explicit re-reduce phase.

That is much easier to reason about, and it allow us to perform aggregation in a very easy manner, allowing us to do aggregation in a manner that is simple to partition. I am probably going to have another post regarding the actual details of handling aggregations.

Here is an interesting one. Can you write code that would take the first piece of text and would turn it into the second piece of text?

First (not compiling)

Second (compiling):

Hint, you can use NRefactory to do the C# parsing.

One of the more interesting problems with document databases is how you handle views. But a lot of people already had some issues with understanding what I mean with document database (hint, I am not talking about a word docs repository), so I have better explain what I mean by this.

A document database stores documents. Those aren’t what most people would consider as a document, however. It is not excel or word files. Rather, we are talking about storing data in a well known format, but with no schema. Consider the case of storing an XML document or a Json document. In both cases, we have a well known format, but there is not a required schema for those. That is, after all, one of the advantages of document db’s schema less nature.

However, trying to query on top of schema less data can be… problematic. Unless you are talking about lucene, which I would consider to be a document indexer rather than a document DB, although it can be used as such. Even with lucene, you have to specify the things that you are actually interested on to be able to search on them.

So, what are views? Views are a way to transform a document to some well known and well defined format. For example, let us say that I want to use my DB to store wiki information, I can do this easily enough by storing the document as a whole, but how do I lookup a page by its title? Trying to do this on the fly is a receipt for disastrous performance. In most document databases, the answer is to create a view. For RDMBS people, a DDB view is often called a materialized view in an RDMBS.

I thought about creating it like this:

Please note that this is only to demonstrate the concept, actually implementing the above syntax requires either on the fly rewrites or C# 4.0

The code above can scan through the relevant documents, and in a very clean fashion (I think), generate the values that we actually care about. Basically, we now have created a view called “pagesByTitleAndVersion”, index by title (ascending) and version (descending). We can now query this view for a particular value, and get it in a very quick manner.

Note that this means that updating views happen as part of a background process, so there is going to be some delay between updating the document and updating the view. That is BASE for you :-)

Another important thing is that this syntax is for projections only. Those are actually very simple to build. Well, simple is relative, there is going to be some very funky Linq stuff going on in there, but from my perspective, it is fairly straightforward. The part that is going to be much harder to deal with is aggregation. I am going to deal with that separately, however.

In a previous post, I asked about designing a document DB, and brought up the issue of replication, along with a set of questions that effect the design of the system:

• How often should we replicate?
  • As part of the transaction?
  • Backend process?
  • Every X amount of time?
  • Manual?

I think that we can assume that the faster we replicate, the better it is. However, there are cost associated with this. I think that a good way of doing replication would be to post a message on a queue for the remote replication machine, and have the queuing system handle the actual process. This make it very simple to scale, and create a distinction between the “start replication” part an the actual replication process. It also allow us to handle spikes in a very nice manner.

• Should we replicate only the documents?
  • What about attachments?
  • What about the generated view data?

We don’t replicate attachments, since those are out of scope.

Generated view data is a more complex issue. Mostly because we have a trade off here, of network payload vs. cpu time. Since views are by their very nature stateless (they can only use the document data), running the view on source machine or the replicated machine would result in exactly the same output. I think that we can safely ignore the view data, treating this as something that we can regenerate. CPU time tend to be far less costly than network bandwidth, after all.

Note that this assumes that view generation is the same across all machines. We discuss this topic more extensively in the views part.

• Should we replicate to all machines?
  • To specified set of machines for all documents?
  • Should we use some sharding algorithm?

I think that a sharding algorithm would be the best option, given a document, it will give a list of machine to replicate to. We can provide a default implementation that replicate to all machines or to secondary and tertiaries.

In a previous post, I asked about designing a document DB, and brought up the issue of attachments, along with a set of questions that needs to be handled:

• Do we allow them at all?

We pretty much have to, otherwise we will have the users sticking them into the document directly, resulting in very inefficient use of space (binaries in Json format sucks).

• How are they stored?
  • In the DB?
  • Outside the DB?

Storing them in the DB will lead to very high database sizes. And there is the simple question if a Document DB is the appropriate storage for BLOBs. I think that there are better alternatives for that than the Document DB. Things like Rhino DHT, S3, the file system, CDN, etc.

• Are they replicated?

Out of scope for the document db, I am afraid. That depend on the external storage that you wish for.

• Should we even care about them at all? Can we apply SoC and say that this is the task of some other part of the system?

Yes we can and we should.

However, we still want to be able to add attachments to documents. I think we can resolve them pretty easily by adding the notion of a document attributes. That would allow us to add external information to a document, such as the attachment URLs. Those should be used for things that are related to the actual document, but are conceptually separated from it.

An attribute would be a typed key/value pair, where both key and value contains strings. The type is an additional piece of information, containing the type of the attribute. This will allow to do things like add relations, specify attachment types, etc.

This is actually a topic that I haven’t considered upfront. Now that I do, it looks like it is a bit of a hornet nest.

In order to have authorization we must first support authentication. And that bring a whole bunch of questions on its own. For example, which auth mechanism to support? Windows auth? Custom auth? If we have auth, don’t we need to also support sessions? But sessions are expansive to create, so do we really want that?

For that matter, would we need to support SSL?

I am not sure how to implement this, so for now I am going to assume that magic happened and it got done. Because once we have authorization, the rest is very easy.

By default, we assume that any user can access any document. We also support only two operations: Read & Write.

Therefore, we have two pre-defined attributes on the document, read & write. Those attributes may contain a list of users that may read/write to the document. If either read/write permission is set, then only the authorized users may view it.

The owner of the document (the creator) is the only one allowed to set permissions on a document. Note that write permission implies read permission.

In addition to that, an administrator may not view/write to documents that they do not own, but he is allowed to change the owner of a document to the administrator account, at which point he can change the permissions. Note that there is no facility to assign ownership away from a user, only to take ownership if you are the admin.

There is a somewhat interesting problem here related to views. What sort of permissions should we apply there? What about views which are aggregated over multiple documents with different security requirements? I am not sure how to handle this yet, and I would appreciate any comments you have in the matter.

In my previous post, I asked about designing a document DB, and brought up the issue of concurrency, along with a set of questions that effect the design of the system:

• What concurrency alternatives do we choose?

We have several options. Optimistic and pessimistic concurrency are the most obvious ones. Merge concurrency, such as the one implemented by Rhino DHT, is another. Note that we also have to handle the case where we have a conflict as a result of replication.

I think that it would make a lot of sense to support optimistic concurrency only. Pessimistic concurrency is a scalability killer in most system. As for conflicts as a result of concurrency, Couch DB handles this using merge concurrency, which may be a good idea after all. We can probably support both of them pretty easily.

It does cause problems with the API, however. A better approach might be to fail reads of documents with multiple versions, and force the user to resolve them using a different API. I am not sure if this is a good idea or a time bomb. Maybe returning the latest as well as a flag that indicate that there is a conflict? That would allow you to ignore the issue.

• What about versioning?

In addition to the Document ID, each document will have an associated version. The Document Id is a UUID, which means that it can be generated at the client side. Each document is also versioned by the server accepting it. The version syntax follow the following format: [server guid]/[increasing numeric id]/[time].

That will ensure global uniqueness, as well as giving us all the information that we need for the document version.

In my previous post, I asked about designing a document DB, and brought up the issue of scale, along with a set of questions that effect the design of the system:

• Do we start from the get go as a distributed DB?

Yes and no. I think that we should start from the get go assuming that a database is not alone, but we shouldn’t burden it with the costs that are associated with this. I think that simply building replication should be a pretty good task, which mean that we can push more smarts regarding the distribution into the client library. Simpler server side code usually means goodness, so I think we should go with that.

• Do we allow relations?
• Joins?
• Who resolves them?

Joins are usually not used in a document DB. They are very useful, however. The problem is how do we resolve them, and by whom. This is especially true when we consider that a joined document may reside on a completely different server. I think that I am going to stick closely to the actual convention in other document databases, that is, joins are not supported. There is another idea that I am toying with, the notion of document attributes, which may be used to record this, but that is another aspect all together. See the discussion about attachments for more details.

• Do we assume data may reside on several nodes?

Yes and no. The database only care about data that is stored locally, while it may reference data on other nodes, we don’t care about that.

• Do we allow partial updates to a document?

That is a tricky question. The initial answer is yes, I want this feature. The complete answer is that while I want this feature, I am not sure how I can implement this.

Basically, this is desirable since we can use this to reduce the amount of data we send over the network. The problem is that we run into an interesting issue of how to express that partial update. My current thinking is that we can apply a diff to the initial Json version vs. the updated Json version, and send that. That is problematic since there is no standard way of actually diffing Json. We can just throw it into a string and compare that, of course, but that expose us to json format differences that may cause problems.

I think that I am going to put this issue as: postphoned.

In a previous post, I asked about designing a document DB, and brought up the issue of storage, along with a set of questions that needs to be handled:

• How do we physically store things?

There are several options, from building our own persistent format, to using an RDMBS. I think that the most effective option would be to use Esent. It is small, highly efficient, require no installation and very simple to use. It also neatly resolve a lot of the questions that we have to ask in addition to that.

• How do we do backups?

Esent already has the facilities to do that, so we have very little to worry about it here.

• How do we handle corrupted state?

See above, Esent is also pretty good in doing auto recovery, which is a nice plus.

• Where do we store the views?
• Should we store them in the same file as the actual data? 

I think not, I think that the best alternative is to have a file per view. That should make things such backing up just the DB easier, not to mention that it will reduce contention internally. Esent is built to handle that, but it is better to make it this way than not. All the data (include logs & temp dirs) should reside inside the same directory.

Crash recovery on startup should be enabled. Transactions should probably avoid crossing file boundaries.It is important the the files will include a version table, which will allow to detect invalid versions (caused a whole bunch of problems with RDHT until we fixed it).

• Are we transactional?

Yes, we are transactional. But only for document writes. We are not transactional for document + views, for example, since view generation is done as a background service.

• Do we allow multi document operation to be transactional?

Yes, depending on the operation. We allow submittal of several document writes / deletes at the same time, and they would succeed or fail as a single unit. Beyond that, no.

A while ago I started experimenting with building my own document DB, based on the concepts that Couch DB have. As it turn out, there isn’t really much to it, at a conceptual level. A document DB requires the following features:

• Store a document
• Retrieve document by id
• Add attachment to document
• Replicate to a backup server
• Create views on top of documents

The first two requirements are easily handled, and should generally take less than a day to develop. Indeed, after learning about the Esent database, it took me very little time to create this. I should mention that as an interesting limitation to the DB, I made the decision to accept only documents in Json format. That makes some things very simple, specifically views and partial updates.

There are several topics here that are worth discussion, because they represent non trivial issues. I am going to raise them here as questions, and answer them in future posts.

Storage:

• How do we physically store things?
• How do we do backups?
• How do we handle corrupted state?
• Where do we store the views?
• Should we store them in the same file as the actual data? 
• Are we transactional?
• Do we allow multi document operation to be transactional?

Scale:

• Do we start from the get go as a distributed DB?
• Do we allow relations?
• Joins?
• Who resolves them?
• Do we assume data may reside on several nodes?
• Do we allow partial updates to a document?

Concurrency:

• What concurrency alternatives do we choose?
• What about versioning?

Attachments:

• Do we allow them at all?
• How are they stored?
• In the DB?
• Outside the DB?
• Are they replicated?
• Should we even care about them at all? Can we apply SoC and say that this is the task of some other part of the system?

Replication:

• How often should we replicate?
• As part of the transaction?
• Backend process?
• Every X amount of time?
• Manual?
• Should we replicate only the documents?
• What about attachments?
• What about the generated view data?
• Should we replicate to all machines?
• To specified set of machines for all documents?
• Should we use some sharding algorithm?

Views:

• How do we define views?
• How do we define the conversion process from a document to a view item?
• Does views have fixed schema?
• How often do we update views?
• How do we remove view items from the DB when the origin document has been removed?

There are some very interesting challenges relating to doing the views. Again, I am interested in your opinions about this.

There are several other posts, detailing my current design, which will be posted spaced about a day apart from one another. I’ll post a summary post with all the relevant feedback as well.

This post about how Friend Feed is using schema-less storage for most of their work is fascinating. In the ALT.Net Seattle there was a session about that, which generated a lot of interest.

My next post will have more details about the actual implementation details of doing something like that in a manner easily accessible in .Net, but just reading the post is very interesting. Another item that I found that was an interesting read, although it is far harder to read is: http://highscalability.com/how-i-learned-stop-worrying-and-love-using-lot-disk-space-scale

I am now in an argument where I am in support for stored procedures. A piece of the dialog:

Team Member #1: We have to do something about this, we don’t even have any stored procedures for this.

Me: I will write the stored procedure for you.

I just noticed that for the last few months I have been consistently denying the existence of a database. I use the term persistent storage when asked, and when asked I usually say: “There is no database”.

It has gotten to the point that this is how I draw the DB on most whiteboard sessions:

Did you know that Windows came with an embedded database?

Did you know that this embedded database is the power behind Active Directory & Exchange?

Did you know that this is actually part of Windows' API and is exposed to developers?

Did you know that it requires no installation and has zero administration overhead?

Did you know there is a .Net API?

Well, the answer for all of that is that you probably didn't know that, but it is true!

The embedded database is called Esent, and the managed library for this API was just released.

This is an implementation of ISAM DB, and I have been playing around with it for the last few days. It isn't as nice for .Net developers as I would like it to be (but Laurion is working on that).

I think making this public is a great thing, and the options that this opens up are quite interesting. I took that for a spin and came up with this tiny bit of code that allow me to store JSON documents:

https://rhino-tools.svn.sourceforge.net/svnroot/rhino-tools/branches/rhino-divandb

It is not done, not nearly done, but the fact that I could rely on the embedded DB to do so made my life so much easier. I wish I knew about that when I played with Rhino Queues, it would have made my life so much simpler.

public class DivanDatabaseTest
{
	[Fact]
	public void Can_add_document_to_database()
	{
		using (var instance = new Instance("test"))
		{
			instance.Init();

			using (var sesion = new Session(instance.JetInstance))
			{
				var database = new DivanDatabase(instance, sesion, "test.divan");
				DocumentId[] add = database.Add(
					JObject.Parse("{'name': 'oren', 'email': 'ayende@ayende.com'")
					);

				using (var view = database.OpenDocumentsView())
				{
					var doc = view.FindById(add[0].Id);

					Assert.Equal("oren", (string) doc["name"]);
					Assert.Equal("ayende@ayende.com", (string) doc["email"]);
				}
			}
		}
	}
}

I was asked to comment on the use of DB schemas, so here it is. The first thing that we need to do is decide what a schema is.

A schema is an organization unit inside the database. You can think about it as a folder structure with an allowed depth of 1. (Yes, just like MS-DOS 1.0). Like folders in the real file system, you can associate security attributes to the schema, and you can put items in the schema. There is the notion of the current schema, and that about it.

Well, so this is what it is. But what are we going to use if for?

People are putting schemas to a lot of usages, from application segregation to versioning. In general, I think that each application should have its own database, and that versioning shouldn't be a concern, because when you upgrade the application, you upgrade the database, and no one else has access to your database.

What we are left with is security and organization. In many applications, the model layout naturally fall out into fairly well define sections. A good example is the user's data (Users, Preferences, Tracking, etc). It is nice to be able to treat those as a cohesive unit for security purposes (imagine wanting to limit table access to the Accounting schema). It is nice, but it is not really something that I would tend to do, mostly because, again, it is only the applications that is accessing the database.

Defense in depth might cause me to have some sort of permission scheme for the database users, but that tends to be rare, and only happen when you have relatively different operation modes.

What I would use schemas for is simply organization. Take a look at Rhino Security as a good example, but default, it will tack its tables into their own schema, to avoid cluttering the default schema with them.

In short, I use schemas mostly for namespacing, and like namespaces elsewhere, they can be used for other things, but I find them most useful for simply adding order.

That is pretty amazing, since that was a big pain point for developing for EC2 powered systems. They support both Oracle and MySQL, in addition to SimpleDB, which is a non relational DB.

From the looks of things, however, there is a significant difference between the Oracle and MySQL offerings. MySQL is a DB limited to a single machine. They talk about the ability to dynamically scale the machine (which sounds just awesome) from small to extra large based on requirement, but not about multi instance databases.

Oracle, however, does have this ability, and it is supported on EC2. So you can increase you database horizontally, rather than vertically. At least, that is how I read things.

I found this to be extremely interesting.

I spent significant parts of the last two weeks dealing with embedded databases. I have used, SQL CE, SQLite, FireBird, db4o and Berkeley DB.

My requirements were really simple, or so I thought. I just wanted safe for multi threading and support for transactions.

Let me go over them in order:

SQL CE

This is a really nice DB, syntax is comparable to SQL Server, so it makes a lot of things simpler. It has transaction support and is supposed to be multi threaded safe.

It is not.

It is very easy to get SQL CE into situations where it hang. Usually when it is attempting to open the database. Oh, and there is no lock timeout for this issue, so it literally hang.

Result: overruled.

SQLite

I just love this DB. It is simple, straightforward, and aside from crazy date manipulation support, just works. It has both transactions and multi threading support.

However, multi threading support is gained by locking the entire database. This is an acceptable behavior, in most scenarios, but for my needs, it meant that threads stepped on each other all too often, and that wasn't acceptable.

FireBird

I had a really hard time getting this to work correctly. I got some recommendations for it, so I decided to go for it.

It doesn't handle transaction isolation, so I never actually got to the point of testing multi threading behavior.

db4o

This was my first foray into using db4o, so take anything that I say with a grain of salt.

It looked like it would be a good solution. However, I couldn't figure out how to get two threads share the same file. Each connection lock the file, so they cannot be used concurrently. Using the server version might solve this, but I am looking for embedded solution, not server solution.

Berkeley DB

I spent the most amount of time here. And it is almost there.

I had one critical issue that I managed to overcome, but then BDB totally killed itself when it hang in my tests. After tearing up a lot of hair, I ended up finding out that there is a known bug in version 4.5 (which is the latest version that has a .NET binding) that can cause this.

Conclusion

I start using Dictionary as my data store. Just don't restart the server.

Take a look at this code. It is supposed to give me the earliest message, ensuring that I'll get each message once and only once.

It doesn't work. I am getting some messages twice. The same code (well, simplified) just works on SQLite.

public QueueMessage GetEarliestMessage()
{
	byte[] data = null;
	bool done = false;
	while (done == false)
	{
		Transaction(cmd =>
		{
			/* 
				SELECT FIRST 1 Id, Data FROM IncomingMessages
				ORDER BY InsertedAt ASC
			*/
			cmd.CommandText = Queries.GetEarliestMessageFromIncomingQueue;
			string id;
			using (var reader = cmd.ExecuteReader())
			{
				if (reader.Read() == false)
				{
					done = true;
					return;
				}
				id = reader.GetString(0);
				data = (byte[])reader[1];
			}
			/* DELETE FROM IncomingMessages WHERE Id = @Id	*/
			cmd.CommandText = Queries.DeleteMessageFromIncomingQueue;
			cmd.Parameters.Add("@Id", id);
			try
			{
				var rowAffected = cmd.ExecuteNonQuery();
				// someone else already grabbed and deleted this row, 
				// so we will try again with another one
				if (rowAffected != 1)
					return; // same as continue in this case} 
			}
			catch (FbException e)
			{
				// yuck! it would have been better to compare the error code
				// but FB doesn't exposes it
				if (e.Message == "cannot update erased record")
				{
					return;// same as continue
				}
			}
			done = true;// same as break from the loop
		});
	}
	if (data == null)
		return null;
	return Deserialize(data);
}

protected void Transaction(Action<FbCommand> action)
{
	using (var connection = new FbConnection(connectionString))
	using (var cmd = connection.CreateCommand())
	{
		connection.Open();
		using (var tx = connection.BeginTransaction(IsolationLevel.Serializable))
		{
			cmd.Transaction = tx;
			action(cmd);
			tx.Commit();
		}
	}
}

a

I mentioned before that my main objection to this DB is its utter lack of tooling. I mean, even SQLite has some tools. For Firebird, there seem to be nothing that works on my machine.

I needed to debug something there, and this was the easiest solution.

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);
				}
			}
		}
	}
}

Those two seems to be the most common embedded databases in the .NET world. This is important to me, since I want to do testing against embedded database.

SQL CE can be used with SQL Management Studio, which is nice, but it has three major issues for me so far:

• It doesn't support memory only operations. SQLite does, and it means a difference of 12 seconds vs. 40 seconds in running ~100 tests that hit the DB. This is important, especially kicking up everything seems to take about 10 seconds anyway (using Test Driven.Net)
• It doesn't support paging (WTF!)
• It doesn't support comparing to a sub query, so this is not legal:
  select * from account where 1 = (select allow from permissions)

Right now I am experimenting with just how much I can twist around to get everything to break.

I am on the third NH bug this week, and counting :-)

Recently I was asked how to solve this problem. An external service makes a call to the application, to create / update an entity. This call can arrive in one of few endpoints. The catch is that sometimes the external service send the call to create a new entity to all the end points at the same time. This obviously caused issue when trying to insert the same row twice.

I suggested using serializable isolation level to handle this scenario, but I wasn't sure what kind of guarantees is makes for rows that do not exists. I decided to write this simple test case (warning: test code / quick hack, don't write real code like this!).

private static void InsertToDatabase(object state)
{
    int value = 0;

    while (true)
    {
        value += 1;
        try
        {
            using (SqlConnection connection =
                    new SqlConnection("data source=localhost;integrated security=sspi;initial catalog=test"))
            {
                connection.Open();
                using (SqlTransaction transaction =
                        connection.BeginTransaction(IsolationLevel.Serializable))
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT Id FROM Items WHERE Id = @id";
                    command.Parameters.AddWithValue("id", value);
                    command.Transaction = transaction;
                    if (command.ExecuteScalar() != null)
                        continue;
                    command.CommandText = "INSERT INTO Items (Id) VALUES(@id)";
                    command.ExecuteNonQuery();
                    Console.WriteLine("{1}: Wrote {0}", value, Thread.CurrentThread.ManagedThreadId);

                    transaction.Commit();
                }
            }
        }
        catch (SqlException e)
        {
            if (e.Number == 1205) //transaction deadlock
            {
                Console.WriteLine("{0}: Deadlock recovery", Thread.CurrentThread.ManagedThreadId);
                continue;
            }
            Console.WriteLine(e);
        }
    }
}

As a note, performance for this really sucks if you have contention, and I got a lot of transaction deadlocks when running it with multiply threads.

What I have observed was that it indeed inserted only a single id. All other isolation levels (including snapshot) will produce duplicate rows in this scenario.

Assumption proven, now I only need to find what kind of a lock it takes on a row that doesn't exists.