System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: e235a52c82098cb165ca73770eb013ff31a9dafa
Title: 'database is locked' error when using a transaction scope
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: LINQ Resolution: Works_As_Designed
Last Modified: 2013-01-29 19:31:17
Version Found In: 1.0.83.0
User Comments:
anonymous added on 2013-01-22 11:30:18:
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.

mistachkin added on 2013-01-23 19:39:28:
Can you provide the database schema associated with this issue?

anonymous added on 2013-01-24 19:06:58:
I attach a small program that reproduce the problem.

mistachkin added on 2013-01-28 23:26:30:
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.

mistachkin added on 2013-01-29 04:52:56:
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.

mistachkin added on 2013-01-29 05:04:01:
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.

mistachkin added on 2013-01-29 05:34:44:

Test case for this ticket is here.


anonymous added on 2013-01-29 15:34:47:
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.

mistachkin added on 2013-01-29 19:31:17:
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.

Attachments: