lundi 24 août 2015

Using TransactionScope with SQLite causes database locked exception

I am trying to adapt a c# code that uses TransactionScope and works with Oracle to work also with SQLite. My code is structured in a way that every method called within the transaction scope that accesses the SQLite database creates its own SQLiteConnection object.

Now I have run into a problem when trying to open a second connection within the TransactionScope block. I am taking care to properly dispose of all the unused SQLiteConnection objects by either wrapping them in a using statement or calling Dispose in a finally block. No matter what I do the second call to SQLiteConnection.Open times out after a while with database is locked exception.

Example code:

using(var transaction = new TransactionScope(TransactionScopeOption.Required))
{
    using(var connection1 = new SQLiteConnection(_connectionString))
    {
        connection1.Open();
        ... // Do stuff with the open connection

    } // Closes the connection 

    using(var connection2 = new SQLiteConnection(_connectionString))
    {
        // database locked exception
        connection2.Open();          
        ... 
    } 
}

All I have found out about the issue so far is in this article:http://ift.tt/1KHhAgw which says:

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.

Unfortunately the article doesn't offer any workaround. I'd very much like to stick to using TransactionScope since having to pass SQLiteConnection and SQLiteTransaction objects to every method in my code would be a lot of rewrite effort not to mention the code would look much more messy. Any advice?

Aucun commentaire:

Enregistrer un commentaire