System.Data.SQLite

I have an easy to repeat situation. This might not be a bug in SQLite.net per se, but it's important to resolve the situation nonetheless.

My test app deletes data from a few tables on the startup, not using the explicit transaction (auto-commit mode). Then it inserts a couple more rows, again, no explicit transactions. Then it starts the explicit transaction (I start the transaction calling BeginTransaction with no parameters).  Then it does several INSERTs (in my case, just a dozen or so). I put a breakpoint on the Commit call and stop the application in the debugger when the time comes to commit (thus, simulating an app crash). I end up with a DBName.db3-journal file next to the DBName.db3 (assume my db is named DBName). The journal is supposed to help the SQLite engine to restore the state of the DB file on the next run. So far so good.

On the next run, I do the same thing. This time I let it run. On the Commit call I get the exception with the text from the subject. Obviously, SQLite is not able to recover, despite the journal file. I highly doubt that the problem is that the journal file is not completely written to the disk as its size is identical across several  invocations.

Opening the db from SQLiteSpy, Visual Studio 2008 or my test app, after the simulated crash, results in the deletion of the journal file, as if some kind of recovery was done.

Reading the Apress book "The definitive guide to SQLite" my understanding is that SQLite, assuming that journal file is fully written to the disk, is able to automatically recover its state, no user action necessary. Still, it appears I am doing something wrong, or I am not doing something I should be, because as soon as I am about to commit the data, but only after the simulated crash, I get the exception from the subject. If I never simulate the crash, the commit call succeeds, every time (I ran into the problem by actually crashing my application due to the bug in my code, I'm not just mindlessly testing SQLite error recovery).

Any hints? My configuration: release version of SQLite, debug version of the test app, fully patched 32-bit Windows XP machine, Visual Studio 2008, SQLite 3.5.4 (that is, SQLite.net 1.0.48).

Thanks in advance,

Drazen

I have a little bit more information that could explain what's going on. I still don't know how to resolve the issue though.

The problem seems to be that there are a couple of SELECT queries that by chance execute inside this transaction. If I deliberately avoid executing them, everything works like a charm.

I vaguely remember reading somewhere something about avoiding to do SELECTs inside transactions, but I don't recall where or why. If such a restriction exists, it's a bit silly to move the SELECT code out of the transaction as it is related to the code inside the transaction and might not be necessary to run at all.

Anyone? Thanks in advance for any pointer.

Regards,

Drazen 

What kind of SELECT statement is it?  Does it return rows?  Are you calling .Dispose() on the SQLiteCommand and SQLiteDataReader?  If you leave them to the garbage collector, then there's no guarantee they'll be cleaned up by the time you call COMMIT.

Robert

Hi Robert,

thanks for a very quick reply.

There are one or two SELECTs, depending on the first I either execute second or not. The first one does return rows, the second just returns a scalar value ("SELECT COUNT(*)...").

I am calling Dispose on the DbCommand, by way of "using" statement. In fact, we use our own wrappers to avoid having to remember to call Dispose, so each call is guaranteed (assuming no bugs in our wrapper code) to call Dispose.

Let's assume that I am not calling Dispose (i.e. we have a bug). But then, regardless of the crash, my code should not work (it's actually very rare that GC would collect the command and the reader in such a short time between these two SELECTs and the call to Commit, because the app allocates very little memory).

This only happens if I deliberately crash the app just as it wants to do the Commit, then run again, let it do this one Commit (of the transaction that contains SELECTs). All of the other calls that do NOT have SELECTs inside (and I have several DELETEs, INSERTs, UPDATEs and such), even after the crash, still execute fine. It's only on this one that I get the exception. What's worse, it looks like sometimes I do not get the exception, but it's rare (this is really irritating).

Any other ideas?

Thanks,

Drazen 

ugh.

Ok ... well, I guess I'm going to have to try and reproduce this here.  This looks like it'll be fun.  Not.

Hi Robert,

I think I have narrowed down the problem a bit more.

Forget about the story about the crashes and such. It doesn't make a difference any more - whatever I do, even if I cleanly exit the application, on the next run, but only in this one transaction, I get the exception. I have also triple checked that I am not sharing instances of DbConnection, DbProviderFactory and such across the threads (most of the code is executing on a manually created thread) and that I always close/dispose DbXXX when used (the wrapper code is short and works), so it's definitely something I do in this one particular transaction.

In the problematic transaction, besides from a couple of SELECTs, I also have many INSERTs and one table where I first DELETE all data from a table, then INSERT many rows. I will try to narrow down the problem a bit more, to see exactly which pattern of execution is causing the exception, then post my findings here.

Thanks,

Drazen 

Robert Simpson:

ugh.

Ok ... well, I guess I'm going to have to try and reproduce this here.  This looks like it'll be fun.  Not.

I know :( That's why I said I'll try to narrow things down a bit more. If possible, I'll try to make a small test app that is stripped down of all our business logic that only exercises the problem and send it to you. 

I'm still not 100% sure this is a bug with SQLite.net, it might be something non-obvious, yet illegal, that I am doing.

Regards,

Drazen 

My problem seems closely related to yours, Drazen. Did you try to insert more than some dozen, maybe some hundred? In my case things start working with a higher amount of inserts. Would be interesting if our topics correlate in that way.

pacolov:

My problem seems closely related to yours, Drazen. Did you try to insert more than some dozen, maybe some hundred? In my case things start working with a higher amount of inserts. Would be interesting if our topics correlate in that way.

Hi,

it never occured to me to test for that. Still, the number of INSERTs here is actually always the same, as I am "replaying" some data normally coming from network, yet the data actually comes from the file. So overall, I'd say there are no more than 25 inserts.

I'll keep you posted on anything I find.

Drazen

I have resolved the problem. Good news, Robert: it's not a bug in your code.

Remember when I said that it's very unlikely we have a bug in our wrapper code that made sure that Dispose was called? Well, I was right, but only partially. There was a subtle bug in another piece of code that used the wrapper.

Allow me to sketch the problem. If you're not a fan of LINQ and extension methods, feel free to skip the rest of this post.

So, our wrapper looks like this:

void ExecuteQuery(...) {
  using(DbCommand = ...) {
    using(DbDataReader reader = ...) {
      while (reader.Read())
        yield return ...;
    }
  }
}

It looks fine, doesn't it? Two usings, both making sure that command and data reader are disposed. This code is being called from many, many places in our app and works great. But in this particular case, instead of calling foreach on the result of this method (or something similar), I called an extension method FirstOrDefault. Since our app is still .NET 2.0, we have to develop our own IEnumerable extension methods, can't use the ones from System.Core. FirstOrDefault (look it up in MSDN) should return first element from a sequence, or default(T) for some type T. Quite a useful method, in fact. In this case, if I get back default (null for reference types), then there are no elements in the sequence (that is, query returned 0 rows), but if there is one, I get that back.

Unfortunately, there was a bug in that method (our implementation of that method, .NET framework version is correct). I forgot to dispose the Enumerator, thus the "loop" would never end, thus the Dispose would not get called on data reader, hence the exception.

Phew, glad it's over. Not only that I've fixed the problem in this particular case, but also many, many yet unseen problems in God know how many other places will be avoided.

Btw Robert, thanks for a complete implementation of ADO.NET interfaces. I was able to completely (more or less, there are differences in the SQL code unfortunately) isolate our storage related code from the specifics of the concrete DB implementation (as you can see in the snippet above, we use DbXXX classes everywhere).

Regards,

Drazen