Page 1 of 1

The database file is locked

Posted: Thu May 09, 2013 3:16 am
by jfm
Hi agw,

I run a PHP script occasionally to update basestation.sqb with updated aircraft info. I do this while VRS and the DB Writer plugin are running. Sometimes I get this error:

[2013-05-08 14:15:22.907 UTC] [t16] Database writer plugin caught exception on message processing: System.Data.SQLite.SQLiteException: The database file is locked
database is locked
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock)
at System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction()
at VirtualRadar.Database.TransactionHelper.StartTransaction(IDbConnection connection)
at VirtualRadar.Database.BaseStation.Database.StartTransaction()

This makes sense since the plugin and my script may both try to update the DB at the same time. I'm wondering what DB Writer does after it hits this error? Does it try its updates again after x amount of time? Does it stop doing anything until I restart the plugin?

Thanks.

Re: The database file is locked

Posted: Fri May 10, 2013 12:20 am
by agw
It depends on exactly where the exception took place. I suspect that in this case it was trying to create a new aircraft and flight record, if this is true then it would retry the next time a message comes in from the aircraft. You shouldn't need to restart the program (you can't restart plugins, they're started with the application).

I think more recent versions of SQLite do a better job of locking with multiple writers, you can try giving the most recent versions a try if you've got multiple things writing to the database. You'd need to download the ADO.NET provider and replace the System.Data.SQLite.dll in VRS' program folder and also replace whatever your script is using. I had trouble with a 3rd party program when I used a later version of SQLite a year or so back, hence the old version that ships with VRS, but if only VRS and your program are updating the database then you should be alright.

Re: The database file is locked

Posted: Wed May 15, 2013 4:14 am
by jfm
Thanks for the info - I'd like to give it a try. Can you tell me which of these I should download?

http://system.data.sqlite.org/index.htm ... loads.wiki

I'm using the PHP pdo_sqlite driver version 3.7.7.1, if that matters.

Re: The database file is locked

Posted: Fri May 17, 2013 12:27 am
by agw
Sorry for the delay - I would go for "Precompiled Binaries for 32-bit Windows (.NET Framework 3.5 SP1)" (quite a way down the page):

http://system.data.sqlite.org/downloads ... 0.85.0.zip

Re: The database file is locked

Posted: Tue May 21, 2013 3:17 pm
by jfm
Thanks. I'm running with that version now. So far, no issues to report.