Why I don't like FireBird: Part II
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