1. SQLite Server
- Posted by euphoric (admin) Jan 04, 2017
- 1536 views
I'm thinking of setting up an SQLite database and have a question about concurrency. I want to make sure I deal with clashes properly so the database doesn't get broken.
I'm setting it up like this:
- Apache receives the request (e.g., "https://mywebsite.com/api_call.ex?param1=3¶m2=Bill¶m3=form_001")
- api_call.ex is run and needs to access the SQLite database. What happens here if the database is unavailable? Can I enable some kind of "wait state" so that it waits until the DB is available before finishing? Can I specify a time-out length?
Will it work this way? Do I need to write a server instead, where each instance of api_call.ex calls a server app that manages database hits? It seems in the setup described above, Apache is acting as the server, so this should work, so long as I can wait for the database to be available.
2. Re: SQLite Server
- Posted by ghaberek (admin) Jan 04, 2017
- 1543 views
Good news! SQLite v3 handles all of this for you. Just carry on doing your thing and the library will automatically lock/wait/unlock between processes as necessary. It's like magic!
You will probably experience delays if you're trying to write from multiple processes at the same time. Multiple processes reading from the same database should be effectively delay-free.
See also:
- File Locking And Concurrency In SQLite Version 3
- Can multiple applications or multiple instances of the same application access a single database file at the same time?
Edit: Sorry, I guess this doesn't happen automatically. You'll have to call sqlite3_busy_timeout() as indicated here: Set A Busy Timeout. Then, as long as your writes stay under the timeout, the library will automatically wait for a slot to open and write the database.
-Greg
3. Re: SQLite Server
- Posted by ChrisB (moderator) Jan 04, 2017
- 1529 views
Hi
When I was using sqlite for my veterinary database it ran for multiple users, but I had to write a 'only one person can write a time' lock.
Sqlite can handle multiple reads on the database as the same time (although there would have to be high volume / long queries for this to happen), but it can only handle one write at a time, so, as I said, the way I handled this was to create a lock file when someone wanted to write, write the data, and delete the lock file. If the lock file was present, then the program had to wait until it had been deleted by the originator. There were one or two occasions where I had to deal with the lock file (glitches / power downs) but these were rare and easily surmountable.
There is an sqlite server out there somewhere (not sure if it's free or paid for) which handles these sort of things.
and this is why I wrote my own write locking system
https://sqlite.org/faq.html#q5
Cheers
Chris
4. Re: SQLite Server
- Posted by euphoric (admin) Jan 04, 2017
- 1483 views
The way I handled this was to create a lock file when someone wanted to write, write the data, and delete the lock file.
Thanks for that info, Chris! That's what I'm going to do... 
5. Re: SQLite Server
- Posted by euphoric (admin) Jan 04, 2017
- 1486 views
Good news! SQLite v3 handles all of this for you. Just carry on doing your thing and the library will automatically lock/wait/unlock between processes as necessary. It's like magic!
You will probably experience delays if you're trying to write from multiple processes at the same time. Multiple processes reading from the same database should be effectively delay-free.
Thanks, Greg! I'm going to have multiple processes possibly trying to even write to the DB, so I'm going to have to write some coordinating process. Chris posted a simple "locked if file exists" algorithm I'm going to try.
