System.Data.SQLite

Something in 1.0.45.0 starting causing me to get a "The database file if locked error".  It continues with 1.0.46.0.  The problem does not occur with 1.0.44.0.

The error is being called from:

   at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

I Close() my connection, and call Dispose() (don't think this is needed but I was trying to make the above error go away), and set the connection variable to null.

This is not the first place in my code that I call ExecuteNonQuery() but the previous calls were all in the same previous connection, which was closed.

If I can provide more information please let me know what else you would like to know.

-Mont

 

1. Do multiple threads or processes access the database at the same time?
2. Do you make any other kinds of calls other than ExecuteNonQuery()?  (Such as ExecuteReader, etc)
3. What are the statement(s) being executed and which one does it throw an error on?

 

Multiple threads do not access the database at the same time.

 

Before the statement fails: 

    ExecuteReader will have been called

    ExecuteNonQuery will have been called already

    Connections to the database will have been opened and closed

 

The statement it is failing on is: "UPDATE configuration SET repository_version = '2.0.2.0'
 

1.0.45..0+ could be exposing an error I already had but just to be clear I get this error simply by changing the library version (no code changes).

 

Thanks,
-Mont
 

Have you disposed of your data reader?  Prior to 1.0.45, when a connection was closed the program forcibly closed open datareaders and reset commands.  This cleanup, depending on how someone's code was written, could get ugly.  So I rewrote the code so that closing a connection queue's the connection for closure but doesn't actually close until the last statement(s) are cleaned up either by the garbage collector or by explicit close/dispose.

I may need to revisit this because its a bit tricky to deal with.

Robert

 

 Hi, I think i can confirm your thinking here. Take the sample code we use below ;

 
Public Overrides Function GetDataSourceProperty(ByVal sDataSourceName As String, ByVal sProp As String, Optional ByVal sDefault As String = "") As String
            Dim sValue As String = sDefault
            Dim oVal As Object
            Dim oCn As System.Data.SQLite.SQLiteConnection = Nothing
            Dim cm As SQLite.SQLiteCommand
            Dim sSQL As String

            Try
                If Connection <> "Data Source=" Then
                    ' Create it
                    oCn = New SQLite.SQLiteConnection(Connection)
                    sSQL = "SELECT VALUE FROM [" & PROPS_TABLE & "] WHERE DATASOURCENAME = ? AND PROPERTY = ?"
                    cm = New SQLite.SQLiteCommand(sSQL, oCn)
                    cm.Parameters.AddWithValue("@p1", sDataSourceName)
                    cm.Parameters.AddWithValue("@p2", sProp)
                    cm.Parameters.AddWithValue("@p3", sValue)

                    ' execute it
                    oCn.Open()
                    oVal = cm.ExecuteScalar
                    If Not IsNothing(oVal) Then sValue = CStr(oVal)
                End If

            Catch ex As Exception
                Throw New DataSourceManagerException(ex.Message)
            Finally
                If Not IsNothing(oCn) Then oCn.Close()
            End Try

            Return sValue
        End Function

 

 Notice the line If Not IsNothing(oCn) Then oCn.Close() in Version 1.0.46 this leaves an unwanted locked on the SQLite Database file!!! If I add a line that explicitly 'Disposes' the Command object, the lock does not remain.

This behavior was not present in 1.0.44. I much prefer the way it worked in the earlier version, rather than explicitly having to dispose of objects referencing the Connection.

 

Hope that helps. 

 

I'll look into changing the behavior (bleh) back, but you really should dispose of those objects.  They are holding locks on unmanaged resources and should be disposed of in a timely manner.

Robert

 

Thanks for the quick follow up.

I understand that a DataReader might hold the connection open, but not simply a Command that merely references the Connection. It would seem inconsistent to have to explicitly call Command.Dispose in SQLite when it is not needed elsewhere in ADO.NET. If that ere the case, the Command would have a Close method like the DataReader and the Connection.

Sorry if I am being annoying ;)

Cheers 

It is important to Dispose() of any objects you use that implment IDisposable.  IDisposable is there for a reason, and objects that implement it usually do so for good reason.

In the case of a DbCommand, the statement(s) in the CommandText in SQLite (as well as Sql Server and other providers) are prepared the first time they are executed and stay prepared until you dispose of the command.

Robert

 

So, then my code that current looks like:

 

            IDbCommand command = Connection.CreateCommand();
            command.CommandText = commandText;
            return command.ExecuteNonQuery();

Should look like:

            IDbCommand command = Connection.CreateCommand();
            command.CommandText = commandText;
            int returnVal = command.ExecuteNonQuery();
            command.Dispose();
            return returnVal

If that is the case it would definitely be my problem with the newer releases.

I don't have a problem making this change, I've apparently just gotten away with it thus far.

Thanks,

-Mont

 

 

All disposable objects should be wrapped in try/catch or Using blocks.  Your code above works fine unless ExecuteNonQuery() throws an error, in which case your command would not be disposed.

Robert

 

 Good point.  Once I get all instances of Command disposed I'll re-try the newer release and report back if my issue is resolved.

 
Thanks,
-Mont 

I don't mean to keep pushing the issue, and I understand that Dispose can be called, but if you look at nearly all Books, Articles, Samples that teach people how to use ADO.NET I have not, to this day, seen anyone suggest that you must be calling Dispose on the Command object. If I am closing the Connection (and theres no open readers) then the the connection should be closed, no question.

We absolutely love this product because it is identical (nearly) to any other provider. Thats the whole point .

 
Cheers.
 

I have to second gpt's opinion, DbCommand's are often cached for performance reason and not disposed directly after execution, so I'd also appreciate if non-disposed DbCommands would not keep the db file locked and could be reused when the connection is re-opened, even if the prepared state is lost.

 

I have to agree with Gpt. None of the books I own, and I own quite a few on the subject, mentions this.
I'm not saying the books aren't wrong, I'm just saying people won't think of that and simply assume it to be a bug
(just as I did before i found this in the forum).

 

 

I agree as well (for what it's worth!).

I started using this SQLite version in a project earlier this week and everything was going swimmingly until I started to get database lock errors that I couldn't resolve. In the end, I ripped all the SQLite code out and used a nasty XML file instead. I stumbled across this thread today (I did searches earlier in the week and missed it). Errors like this quickly put off noobs from using this wrapper, so it would be good to either make it work as people expect, or to clearly post the *right* way to do it in the sample code... with comments explaining why it should be done that way.

The two main errors that I found were:
1. Inserting a record to a table, then finding that a subsequent read did not find the record. I *thought* I'd resolved the issue by closing the database connection after each insert/update statement.
2. Hitting database locked errors when the close connection statement was executed after an update.

Am I right in thinking that I should be able to resolve both of these issues by performing a Dispose() on the command, and making sure that I Close() readers?

James