Jump to content
jguinch

Sqlite simultaneous access from multiple computers

Recommended Posts

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...


 

 

Share this post


Link to post
Share on other sites
Earthshine

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


My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Chimp

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 by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by jchd
  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

 

 

 

Share this post


Link to post
Share on other sites
Earthshine

you can always use the free SQLServer for small jobs

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

 

why bother with nonsense

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×