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.