System.Data.SQLite

Hi there,

after Googling for a while I want to ask the community - my app runs perfectly fine but *sometimes* after some time a query (random!) fails with an exception
    "Unable to open the database file"

The really weird thing is that I open the DB file once and that's it... I never close it, my app is long-running and accesses the database every second ~100 times, so there can't be a timeout.

Any ideas how to solve this issue?

Best regards!

Hi,

I have a similar problem. I get the error 'Unable to open the database file" sometimes and randomly.

The file is located on a network drive and the journal file cannot be deleted, I think this is the problem. For some reason the file cannot be deleted automatically by SQLite and I am also unable to delete it via Windows Explorer or a DOS prompt.

Do you have the same situation? Is your file located on a network drive? I have yet to experience this when the database is located on a local drive.

Any help as to ways I can stop this, or get around this problem will be gratefully received. Our app must be able to access a database file located on a network drive.

 

 

If the journal can't be read or deleted, then it must be locked ... and if its locked, the question is -- by who?  Virus scanner maybe?  Or some file monitoring app? 

Does it stay locked even after your app terminates?

Robert

 

Nope,
my database file lives on the Deskop, so on the local machine.... accessing a db-file over a network share does not sound very performant... if multiple users should access the database I would go for a DB server instead.

No, for me it happens for a local file.

Yes the journal file will stay locked (i.e. cant delete it) after my app terminates. I even reboot my machine to make sure I have no processes that have it open. On reboot I still cannot delete the file manually or by opening the database with SQLite.

The journal file may delete itself after a few hours or so. Again a random time. And after that time the database is fully useable again with no indication of data corruption.

I am awaiting a response from our IT department as to why these files may be locked.

Have you made sure you're calling Dispose() on all your ADO.NET objects that support it? 

Robert

 

I dont think I call Dispose() on any ADO.NET object ever. I will go through and make sure I do. However would you mind explaining (or directing me to a web-page) explaining what Dispose() does? i.e. I'd just like to know what this does in case I cannot reproduce the problem after doing so. Great if it fixes it, but I like to know why. ;-)

Thanks.

This could be your problem.  Dispose() is part of the "Disposable Pattern" designed by Microsoft to allow .NET objects that hold references to unmanaged resources a way to dispose of those unmanaged resources.

You can read about it here and that article has several sub-articles regarding IDisposable.

Robert

 

Ok, back to topic :)
How can I avoid that someone else (i.e. virus scanner) is touching the SQLite database file? Is there a way to open the file and keep it completely locked, so noone else can access it?

I noticed something else - if I open the DB with my own application I can still open it using SQLite Admin (http://sqliteadmin.orbmu2k.de/) without a problem.
If I open the DB first using SQLite Admin without even doing anything then opening my application fails with an exception.... so there must be a way to lock the whole DB?

I'm not sure what sqliteadmin does.  It may be starting an exclusive transaction right away to gain an exclusive lock. 

There's no atomic way to open the sqlite database file exclusively -- meaning in one guaranteed success or fail motion.  There's always the possibility the file can be opened and before you execute an exclusive transaction, something else gets there first and locks the file.

Even if you start an exclusive transaction, you still have to commit it eventually, which releases the locks. 

Robert

 

Ok, makes sense.
The problem ist hat my application uses some transactions (multiple inserts -> more speed). So I can hardly create a transaction inside another transaction, or can I do that?

See http://www.sqlite.org/whentouse.html -> Application File Format section

SQLite's engine doesn't support nesting transactions, but the SQLite provider does through the use of simple reference counting.  That probably won't help you, though.

Robert

 

Snoopy,

Do you have Windows Desktop Search running? Or something similar (Google desktop)? What O/S are you running, Vista?

I think we have determined that this problem is that 2 users on our network are running Windows Desktop Search and it is this process that is locking the journal file. It is either one of those two users that have the file locked, and I can run the test application all night without problem, as soon as they log in in the morning, very soon after the journal file is locked by one of them.

I have asked them to exclude the folder the db is located in and will rerun the test. If the test runs ok over the weekend I think this confirms the problem.

Has anyone had a similar experience with these file indexing programs? With SQLite or any other kind of file?

 

Do you have Windows Desktop Search running? Or something similar (Google desktop)? What O/S are you running, Vista?

Hey there, no search is running on my WinXP machine (SP2, all patches). It really happens just sometimes, which is hard to find or track down....

Have a great weekend!

I find this utility very useful to determine what process has a lock on a file.

http://ccollomb.free.fr/unlocker/

There's other ways to find this information, for example using ProcesExplorer from SysInternals (now Microsoft). But this French tool integrates nicely in your Windows Explorer and also has the feature to close any file that is held open by other processes.

hth

/d