6

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://elegantcode.com/2010/07/02/using-transactionscope-with-sqlite/ 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?

user2864740
  • 60,010
  • 15
  • 145
  • 220
stambikk
  • 1,175
  • 12
  • 23
  • Did you try using "BeginTransaction" method of SQLiteConnection instead of Transaction Scope? – Thiago Custodio Aug 24 '15 at 13:25
  • 2
    yes, this works but it's also quite inconvenient since all the methods accessing the database would need to share the same connection object. – stambikk Aug 24 '15 at 13:31
  • I agree, but it seems that transaction scope don't work very well with SQLite – Thiago Custodio Aug 24 '15 at 13:52
  • I was having similar issues; see [my answer to this question](http://stackoverflow.com/questions/32721283/transactionscope-and-sqlite-database-gets-locked#36631497). – David Apr 14 '16 at 19:17
  • 1
    After re-reading your question, I'm not sure there is a workaround. As I understand it, attempting to open a second connection promotes the transaction to an MSDTC transaction (and in so doing transfers the lock ownership to MSDTC), so the open will always fail. I guess my question would be, why are you opening a second connection? The Oracle code was doing it that way, yes ... I'm asking *why* was the Oracle code structured thusly? – David Apr 15 '16 at 12:24

0 Answers0