NServiceBus will ruin your SQLite operation

NServiceBus is great! Transactionality, automatic retry mechanism and a whole sleuth of guarantees. In a quick glance it is just a wrapper around your favorite messaging queues, but there is a lot underneath. I’ve heard rumors in fact, that if you find a bug in NServiceBus (a lost message in particular) Particular Software will personally visit you to have the situation reproduced and resolved.

When receiving messages through a class implementing IHandleMessages<T>, you can run any code you want. If an exception is thrown in your code. NServiceBus will interpret this as something that might be temporal, such as a connection error (database is down) or resources issue (no memory available). So, any message handling that throws an exception will be retried. Not indefinitely, but for a set number of times, with incremental backup. When the number of retries is high enough, the message will be sent to an error queue, where a human being can decide to retry the message again.

This means that a message can be (partially) handled multiple times. To prevent things like duplicate database record (first try will create record, second try will also create record, and so on) NServiceBus has the notion of Transactions. The whole handling of the message is transactional, which means that anything you do in your code will be rolled back (if possible) whenever an exception is thrown back to NServiceBus. Pretty neat!

Lately we had a problem with handling a message that did some things with an SQLite database. We tried to create a new database and immediately add some rows in it:

public async Task Handle(MyMessage message, IMessageHandlerContext context)
{
    using (var conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=C:\Temp\Test.db; Version=3"))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "CREATE TABLE MyTable (id INT, name TEXT);";
            await cmd.ExecuteNonQueryAsync();
        }
    }

    using (var conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=C:\Temp\Test.db; Version=3"))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO MyTable (id, name) VALUES (1, \"Daan\")";
            await cmd.ExecuteNonQueryAsync();
        }
    }
}

Running this on receiving a message, we get greeted with the next exception:

 System.Data.SQLite.SQLiteException: SQL logic error
 no such table: MyTable
    at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
    at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
    at System.Data.SQLite.SQLiteDataReader.NextResult()
    at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
    at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)

Weirdly, when running the code directly (e.g. having a Console application directly calling the Handle()) does work! So, what gives? It turns out, that NServiceBus wraps each incoming message handling with a transaction. We knew this, of course. What we didn’t knew, is that having multiple operations on the same SQLite database (in this case, creating it and inserting data) does not work nice within a transaction. What can we do about this?

Luckily, there is a way to create a transaction that is independent on the overlying transaction. That is, when the inner transaction is successfully committed, the changes within are complete and will not be rolled back if the overlaying transaction will roll back. This can be done with new TransactionScope(TransactionScopeOption.Suppress):

public async Task Handle(MyMessage message, IMessageHandlerContext context)
{
    using (var ts = new TransactionScope(TransactionScopeOption.Suppress)) 
    {
        using (var conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=C:\Temp\Test.db; Version=3"))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "CREATE TABLE MyTable (id INT, name TEXT);";
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
    using (var ts = new TransactionScope(TransactionScopeOption.Suppress)) 
    {
        using (var conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=C:\Temp\Test.db; Version=3"))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO MyTable (id, name) VALUES (1, \"Daan\")";
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}

Remember kids, NServiceBus is doing transactions for your own good 😉

Leave a Reply Cancel reply