SQLite with TransactionScope and Increment Identity Generator

Question

When trying to use SQLite with System.Transactions TransactionScope with the identity generator as Increment, i noticed that i was getting an exception (given below along with code) when NHibernate was trying to retrieve the next Identity number.

This seems to be because the new SQLite connection is doing a auto enlist of the current transaction. From what i have heard SQLite only support single write transaction, but should support multiple read's, so i am surprised that i am getting a Database locked exception for a read operation. Did anybody use SQLite with Transaction Scope in this manner.

The same Code works fine if i use a NHibernate Transaction instead of TransactionScope

Code Block:

           using (var scope = new TransactionScope()) 
            { 
                var userRepository = 
container.GetInstance<IUserRepository>(); 
                var user = new User(); 
                userRepository.SaveOrUpdate(user); 
                scope.Complete(); 
            } 

Exception:

19:34:19,126 ERROR [   7] IncrementGenerator [(null)]- could not get 
increment value 
System.Data.SQLite.SQLiteException: The database file is locked 
database is locked 
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) 
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() 
   at System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection 
connection, Boolean deferredLock) 
   at System.Data.SQLite.SQLiteConnection.BeginTransaction(Boolean 
deferredLock) 
   at System.Data.SQLite.SQLiteConnection.BeginTransaction() 
   at System.Data.SQLite.SQLiteEnlistment..ctor(SQLiteConnection cnn, 
Transaction scope) 
   at 
System.Data.SQLite.SQLiteConnection.EnlistTransaction(Transaction 
transaction) 
   at System.Data.SQLite.SQLiteConnection.Open() 
   at NHibernate.Connection.DriverConnectionProvider.GetConnection() 
   at NHibernate.Id.IncrementGenerator.GetNext(ISessionImplementor 
session) 
19:34:20,063 ERROR [   7] ADOExceptionReporter [(null)]- The database 
file is locked 
database is locked

Answer 1

There are two things at play here.

As you mentioned, System.Data.SQLite will auto-enlist in distributed transactions. This is on by default and can turn it off by adding Enlist=no.

The second is that System.Data.SQLite by default creates transactions with an automatic write lock. This is done based on the assumption that if a transaction is started, writes will be done. This can be overridden by starting the transactions with Serializable.ReadCommitted.

The default can also be specified in the connection string using the DefaultIsolationLevel key. Valid values are ReadCommitted and Serializable only. Other isolation levels are not supported by SQLite. ReadCommitted defers the write lock whereas Serializable obtains the write lock immediately.

Unspecified will use the default isolation level specified in the connection string. If no isolation level is specified in the connection string, Serializable is used. Serializable transactions are the default. In this mode, the engine gets an immediate lock on the database, and no other threads may begin a transaction. Other threads may read from the database, but not write.

With a ReadCommitted isolation level, locks are deferred and elevated as needed. It is possible for multiple threads to start a transaction in ReadCommitted mode, but if a thread attempts to commit a transaction while another thread has a ReadCommitted lock, it may timeout or cause a deadlock on both threads until both threads' CommandTimeout's are reached.