Sqlite simultaneous access from multiple computers

[jguinch]

Hi everybody.

I'd like to know if a SQLite database can be accessed from multiple computers at the same time (just to retrieve values, no insert and no update)
The idea is to put an AutoIt compiled script on a large number of computers (~4000), which uses a SQLite database stored on a network share...

 

 

[Earthshine]

Should be fine. It only locks for a few milliseconds when doing write operations. However it is not multi threaded

[Gianni]

Here a nice example by @jchd on how to read and write concurrently on an sqlite DB

... and here (https://www.autoitscript.com/forum/topic/173055-keeping-several-sqlite-databases-in-sync/?do=findComment&comment=1251581) a note where @jchd warns on risks by using sqlite in a network where more clients access concurrently to write to and SQLite DB.

 

Edited January 29, 2018 by Chimp

[jchd]

As long as there are no DB writes, that will work fine.

Be sure to use the latest DLL available from SQLite.org as there have been much speed improvements and (dark corners) fixes.

[jpm]

@jchd

not sure I understand "no DB writes" as the user wants write/read

another question does the .dll I recompile https://www.autoitscript.com/autoit3/pkgmgr/sqlite/ are OK?

Cheers

[jchd]

14 hours ago, jguinch said:

(just to retrieve values, no insert and no update)

To me that means only SELECTs, then no writes. Or am I missing something?

I can't test anything right now, will do at some later time. BTW, it would be very helpful to include the json1 extension (there is a simple declarative to do so).

Edited January 29, 2018 by jchd

[Skysnake]

what @jchd is stating here is that SQLite is a file based database.  It is suitable for multiple simultaneous read transactions (SELECT) but due to its design, not suitable for multiple simultaneous reads (INSERT / UPDATE).  You will end up with locks.  See the SQLite website for this.

If this is running on a web page, with few inserts (by admin) and many reads, it should be fine.

For a LAN type of deployment where multiple users will be connecting simultaneously, you need to deal with (a) the mapping issues at the file system level and (b) again the read / write issues above.

See something like this for ideas compare RDBMs

Skysnake

[jchd]

Well, SQLite is suitable for many concurrent R/W accesses without any problems with locking, provided the apps' SQL are coded correctly (using timeout and suitable transactions).

The only problem with concurrent R/W accesses thru a network is with the questionable OS support for file locking, which SQLite uses extensively. SQLite can't do anything about that. For heavy R/W concurrency (say dozens of users) either implement a remote DB server (using SQLite or any RDBMS available) and use whatever protocol of your choice, or use a ready-made client-server.

There have been reports of R/W issues with remote SQLite DBs in some past, but AFAICT current OSes seem to behave better as no reports of wreckage have surfaced recently. Yet the warning remains so pick your own choice.

 

[jguinch]

Thank you all for your answers.
The goal is to make a tool to help users, by automatically install the closest printers using the location of the computer, or manually install some desired printers from a list.
The SQLite database will contain some informations about all available printers, like "PrinterName", "PrinterIP", "PrinterShare", "PrinterServer", "Comment", "Location", "Model", and informations about computers like "ComputerName", "ComputerIP", "Location"...
So, only one computer will make changes in the SQLite Database (with a scheduled task, every day), and all computers will access to it at startup and/or on demand.

I think, with your answers, that I will be able to use a SQLite Database (I didn't want to use a SQLServer database just for a small tool)
Now, let's go ! I just have to make the job !

Thanks

 

 

 

[Earthshine]

you can always use the free SQLServer for small jobs

https://www.microsoft.com/web/platform/database.aspx

 

why bother with nonsense

Edited January 29, 2018 by Earthshine

[jguinch]

SQLServer Express can be used for big apps (up to 10GB), but for me, it seems disproportionate given what I want to do with it (3-4 tables, less than 10000 entries).
Its positive point would be to definitely answer the question of concurrency access by thousand of computers...

[willichan]

You can look at using my "Cooperative Semaphore" UDF.  It will give you a way to "lock" the SQLite database when any writes are being done.