Locked SQB Database

Is Virtual Radar Server not behaving itself? If so then please report it here.
Post Reply
subsy
Posts: 2
Joined: Sun Nov 23, 2014 8:28 pm

Locked SQB Database

Post by subsy » Sun Nov 23, 2014 8:36 pm

Hi,

I am using virtual radar since a few weeks with a piaware ADS-B receiver. I have made myself a small app that fills the aircraft infos, so that aicrafts and silhouettes are shown in the virtual radar website.

I have noticed that some times, my app will fails stating that the database is locked. It seems to happen after some network disconnect from my ISP. When this happen, I need to stop and restart virtual server to unlock the database. It so seems that at some point in time a write operation fails, and the connection never gets closed, thus locking the database.

Is there anything I can do so that the dangling connection will get properly closed?

Regards,

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

Re: Locked SQB Database

Post by agw » Tue Dec 02, 2014 11:17 pm

Hello, sorry for the late reply!

SQLite doesn't lock the database file over the duration of a connection, it holds locks over the duration of read and write operations (see http://www.sqlite.org/lockingv3.html).

The first thing to note is that it is unsafe to write to SQLite files across the network - you will need to make sure that VRS and your program are both running on the same machine (see http://www.sqlite.org/lockingv3.html#how_to_corrupt). One symptom of writing SQLite files across a network is that you can end up with weird locking problems.

The current version of VRS ships with an older version of SQLite that was a bit lock-happy. Later versions aren't quite so aggressive with their locks. The reasons for VRS shipping with the old version of SQLite have gone away and the next release will be shipping with the latest version of SQLite, which may help the situation.

In the next release I had planned on using a global mutex so that other applications could, if they wanted to, coordinate their database reads and writes with VRS. However I've since given up on that idea. Instead I've changed the code so that if I get a lock reported by SQLite on any operation then I back off, wait a tenth of a second and try again. I keep trying the operation until either it succeeds or a timeout expires. You might be able to try the same approach.

If you want a preview version of the next release with the later version of SQLite and the "keep trying until it works" approach to lock errors then let me know.

subsy
Posts: 2
Joined: Sun Nov 23, 2014 8:28 pm

Re: Locked SQB Database

Post by subsy » Wed Dec 03, 2014 6:49 am

Hi thanks for the feedback.

I would be happy to try a newer version. I also trapping lock exceptions and just retry after a little wait.

FYI : the app and the server are both running on the same machine. The impact of my app on the VRS database is getting lower every day, since most of the planes I record are not new, and as a consequence do not require updating the database anymore.

Regards,

tutpipos
Posts: 2
Joined: Thu Apr 30, 2015 3:17 pm

Re: Locked SQB Database

Post by tutpipos » Fri May 08, 2015 7:36 am

Hello. I recently wrote a recurrent error writing to the database. Today, I again found that the record was not carried through the night. Spread a slice of the log you to help me identify the cause write errors.
[2015-05-07 04:11:54.824 UTC] [t3] Program started, version 2.1.0.42181
[2015-05-07 04:11:54.847 UTC] [t3] Working folder C:\Users\AleX\AppData\Local\VirtualRadar
[2015-05-07 20:13:49.324 UTC] [t18] Database writer plugin caught exception on message processing: System.Data.SQLite.SQLiteException: database is locked
database is locked
╨▓ System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
╨▓ System.Data.SQLite.SQLiteDataReader.NextResult()
╨▓ System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
╨▓ System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
╨▓ System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
╨▓ System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
╨▓ VirtualRadar.Database.SQLiteExecute.<>c__DisplayClass4.<ExecuteReader>b__3()
╨▓ VirtualRadar.Database.SQLiteExecute.CallActionUntilUnlocked(Action action)
╨▓ VirtualRadar.Database.SQLiteExecute.ExecuteReader(IDbCommand command)
╨▓ VirtualRadar.Database.BaseStation.FlightsTable.GetById(IDbConnection connection, IDbTransaction transaction, TextWriter log, Int32 id)
╨▓ VirtualRadar.Database.BaseStation.Database.GetFlightById(Int32 id)
╨▓ VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.TrackFlight(BaseStationMessage message)
╨▓ VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.MessageQueue_MessageReceived(BaseStationMessageEventArgs args)
Last edited by tutpipos on Sun May 17, 2015 9:57 am, edited 1 time in total.

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

Re: Locked SQB Database

Post by agw » Fri May 15, 2015 7:57 pm

Yes, it's a known problem - the database writer plugin treats database locks as it does every other kind of SQLite exception and stops recording when it sees one.

This will be fixed for the next release of the plugin. I'll probably be releasing that one quite soon but if you want to give it a go ahead of its release then you can download it from here:

http://www.virtualradarserver.co.uk/Fil ... nSetup.exe

Post Reply