Blog

Using TransactionScope with SQLite

July 2, 2010

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 :-).

If you and your team want to learn more about how to write maintainable unit tests and get the most out of TDD practices, make sure to have look at our trainings and workshops or check out the books section. Feel free to reach out at infonull@nullprincipal-itnull.be.

Profile picture of Jan Van Ryswyck

Jan Van Ryswyck

Thank you for visiting my blog. I’m a professional software developer since Y2K. A blogger since Y2K+5. Provider of training and coaching in XP practices. Curator of the Awesome Talks list. Past organizer of the European Virtual ALT.NET meetings. Thinking and learning about all kinds of technologies since forever.

Comments

About

Thank you for visiting my website. I’m a professional software developer since Y2K. A blogger since Y2K+5. Author of Writing Maintainable Unit Tests. Provider of training and coaching in XP practices. Curator of the Awesome Talks list. Thinking and learning about all kinds of technologies since forever.

Contact information

(+32) 496 38 00 82

infonull@nullprincipal-itnull.be