Using TransactionScope with SQLite

Earlier this week I ran into a little quirk with SQLite. Take a look at the following code snippet:

using(var transaction = new TransactionScope(TransactionScopeOption.Required))
{
    using(var connection1 = new SQLiteConnection(_connectionString))
    {
        connection1.Open();
        
        ... // Do stuff with the open connection
        
    } // Closes the connection (so we think …)
    
    using(var connection2 = new SQLiteConnection(_connectionString))
    {
        // This line of code throws a SQLiteException with an error 
        // code 'Busy' after a certain timeout has been expired
        connection2.Open();          
        
        ... 
    } 
}

Everything works just fine with the first connection, but the second connection forms a bigger problem. Calling the Open method on the second connection throws a SQLiteException with the message “The database file is locked” and an error code ‘Busy’ after a particular timeout has been expired.

At first I didn’t have a clue, largely because the TransactionScope was nicely tucked away at a much higher level (NServiceBus deals with transactions for me). But after reading more about the intrinsic behavior of SQLite it all started to make sense. Apparently SQLite supports only one writer at a time. The TransactionScope holds an exclusive writer lock, even when disposing the first connection. Because the first connection cannot fully close itself, opening the second connection results in a lock error.

Hope this helps explain why the obvious isn’t obvious :-).