System.Data.SQLite

I am using the following code:

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable }))
{
        EntitiesSQLite myContext = new EntitiesSQLite();

        string strSQLSongDB = "select * from Songs where IDSong = 38";
        Songs mySongDB = myContext.Songs.SqlQuery(strSQLSongDB).FirstOrDefault<Songs>();

        string strSQLAuthors = "select * from Authors";
        List<Authors> lstAuthors = myContext.Authors.SqlQuery(strSQLAuthors).ToList<Authors>();

        scope.Complete();
}

When I get the song, I have not problems, but when I try to get the authors, the application waits about 10 seconds and the I get the error "The underlying provider failed on Open".

Also, when I get the song, I try to do a quary with an external application that is connected to the database, and I can get the registers, so the register is not blocked. I want to block the song because I don't want that other usr can read it while I am working with this record.

How can I use transactions with SQLite?

I am using SQLite version 1.0.83.0 and C# 4.0.



Thanks.
Daimroc.

Can you provide the database schema associated with this issue?

I attach a small program that reproduce the problem.

The sample you attached is using a different isolation level than the
code snippet in the original portion of the ticket.  Also, the sample
leaves out the "scope.Complete()" call.

Changing the connection string in the sample 'App.Config' file to the following
prevents the issue:

<add name="transactionsEntities" connectionString="metadata=res://*/Model.Transactions.csdl|res://*/Model.Transactions.ssdl|res://*/Model.Transactions.msl;provider=System.Data.SQLite;provider connection string=&quot;data source=.\database\transactions.db;foreign keys=True;Default IsolationLevel=ReadCommitted&quot;" providerName="System.Data.EntityClient" />

Note that I also had to modify the sample project to copy the test database to
the output directory.

This root cause of this issue appears to be a transaction deadlock caused by a
combination of the default isolation level and the use of the SqlQuery method
that results in more than one transaction being used by the sample code.

Test case for this ticket is here.

I try to change the connection string in the app.config, and the problem is solved if I use readCommitted isolation level, but if I use serializable the problem persists.

is it possible to use serializable isolation level?



Thanks.

It does not seem possible to use the Serializable isolation level with the
SqlQuery method, as that method seems to always internally open and close a new
connection, which also causes a new transaction to be created.