SQLite Question

[Trax]

   Long ago I wrote a AutoIT program that reads temperatures from a device and saves them in a SQLite database 24/7/365. Now they want to access that data. I was very unclear about  record locking in SQLite. Can several programs access that SQLite data as long as it is read only without messing up the one that is writing to it?

[Nine]

https://www.sqlite.org/lockingv3.html

[jchd]

@Traxif you switch your database to WAL mode, using a pragma, you can have many readers and one writer concurrently.

[Gianni]

2 hours ago, Trax said:

   Long ago I wrote a AutoIT program that reads temperatures from a device and saves them in a SQLite database 24/7/365. Now they want to access that data. I was very unclear about  record locking in SQLite. Can several programs access that SQLite data as long as it is read only without messing up the one that is writing to it?

...following this nice example (https://www.autoitscript.com/forum/topic/154901-two-processes-sharing-a-file/?do=findComment&comment=1119112) posted by @jchd some time ago, I was able to write some programs with simultaneous reading and writing on the same DB from multiple scripts at the same time. I hope that example can be useful to you too .....

 

Edited March 5, 2022 by Gianni

[Trax]

OK. I must admit that was a little more involved then I was hoping for. It looks like I am using SQLite 3.31.1 in whatever its default mode is. The database is on a computer on the network and the reading will take place across the network based on a share. The only one writing to the database is the program running on that computer. So I would want to implement WAL?

[jchd]

Yes, but there's one caveat: network support may behave unexpectedly. The problem is that SQLite relies on file locking for proper operation. Many networks don't like that many locking/unlocking requests occuring constantly from several source on the network and may get confused.

In practice, I've long used a similar setup as yours and never experienced any problem. As long as remote "clients" don't harrass the host, you should be safe. In any case code defensively by using transactions when required and checking every call for error.

You just have to issue the relevant pragma only once since the setting is database-persistent.

Edited March 5, 2022 by jchd

[Trax]

Many thanks @jchd and all

[Nine]

Of course you could make a copy of the DB (once every day like during backup procedure), and use that copy to DW purposes for extracting and sending requests..

Edited March 5, 2022 by Nine

[jchd]

A quick note: an SQLite DB is just a file, BUT when connections are active there may be journaling files created. In WAL mode, there are 2 files for journaling (*.wal and *.shm). To make a complete working copy of a database, use either the backup API or vacuum into.

Important warning: I said yes to using a low-concurrency DB over a network with recent Windows OSes. BUT I forgot to explicitely state that WAL mode is incompatible with a network filesystem since it uses memory-mapped journal files. For remote acces you have to use the rollback journal mode.

In this mode you can have either many readers OR one writer at a given time. Use short immediate transactions with a very long timeout delay (several minutes).