The database file is locked

Are you having problems with using or developing a plugin? Let me know here.
Post Reply
jfm
Posts: 82
Joined: Thu May 10, 2012 4:09 pm

The database file is locked

Post by jfm » Thu May 09, 2013 3:16 am

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.

agw
Posts: 2241
Joined: Fri Feb 17, 2012 3:20 am

Re: The database file is locked

Post by agw » Fri May 10, 2013 12:20 am

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.

jfm
Posts: 82
Joined: Thu May 10, 2012 4:09 pm

Re: The database file is locked

Post by jfm » Wed May 15, 2013 4:14 am

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.

agw
Posts: 2241
Joined: Fri Feb 17, 2012 3:20 am

Re: The database file is locked

Post by agw » Fri May 17, 2013 12:27 am

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

jfm
Posts: 82
Joined: Thu May 10, 2012 4:09 pm

Re: The database file is locked

Post by jfm » Tue May 21, 2013 3:17 pm

Thanks. I'm running with that version now. So far, no issues to report.

Post Reply