pfletch101
Posts: 527
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Writing to SQLIte database from multiple processes

Thu Nov 07, 2019 11:56 pm

I know that, under normal circumstances, SQLite databases can have multiple readers but only one writer. Can the 'only one writer' rule be relaxed if any potential writers share a semaphore or similar means of interprocess communication in such a way as to prevent multiple writing attempts at the same time?. If so, would it be sufficient to seize the semaphore before calling commit() and release it immediately afterwards, or is it likely to be more complicated than that?
I have an application for which SQLite is ideal except for the necessity for two processes being able to write to one of the data tables - neither very frequently.

deepo
Posts: 197
Joined: Sun Dec 30, 2018 8:36 pm

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 8:30 am

You should safe if you implement access to the database with a mutex.
While you have the mutex you need to perform the SQL statement and then commit (if writing), and then release the mutex.

/Mogens

pfletch101
Posts: 527
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 3:55 pm

deepo wrote:
Fri Nov 08, 2019 8:30 am
You should safe if you implement access to the database with a mutex.
While you have the mutex you need to perform the SQL statement and then commit (if writing), and then release the mutex.

/Mogens
I normally leave a connection to the database and a cursor open while my application is running - only closing them when the application exits (which may be days later, since some of my applications run as services). Do I understand you correctly as saying that the connection and/or the cursor need to be protected by the mutex (if it/they will be used for writing), so that I would need to open it after acquiring the mutex, just before performing the SQL statement, and close it after the commit and before releasing the mutex?

trejan
Posts: 730
Joined: Tue Jul 02, 2019 2:28 pm

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 4:11 pm

The built-in file locking already does this. You don't need to add your own locking layer on top. You only have a single writer but it is at any point in time. It doesn't mean only a single process can write. Read https://www.sqlite.org/faq.html#q5

pfletch101
Posts: 527
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 9:51 pm

Thanks for pointing out that SQLite implements internal file-based locking. For my purposes, that is probably enough, though the FAQ rather glosses over the situations where it is not enough and record-based locking (which SQLite does not implement) is required. If all the multiple writing processes are doing is adding new records and/or deleting old ones, file-based locking should prevent most potential problems, though relationships between Tables may break if a key record is deleted. If one or more process is updating records, however, things become much more 'iffy', since there is then potential for two or more processes to try to update the same record, with the last one to commit the update probably "winning". Unlike trying to update a record that another process has just deleted (which will at least provoke an error), "duelling updates" without record-level locking will almost inevitably silently result in a record containing bad or incomplete data.

trejan
Posts: 730
Joined: Tue Jul 02, 2019 2:28 pm

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 10:07 pm

Transactions would solve the problems you listed. Read https://www.sqlite.org/lockingv3.html#t ... on_control

User avatar
neilgl
Posts: 1108
Joined: Sun Jan 26, 2014 8:36 pm
Location: Near Aston Martin factory

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 10:45 pm

Or maybe use a database that does have row level locking? e.g. postgresql

pfletch101
Posts: 527
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 10:48 pm

trejan wrote:
Fri Nov 08, 2019 10:07 pm
Transactions would solve the problems you listed. Read https://www.sqlite.org/lockingv3.html#t ... on_control
No, I don't think so. If Process A and Process B both read the same record (permitted, because neither has tried to write to the database yet), they can each sequentially proceed to submit and commit updates to that record, based on its original contents, which may be inconsistent with each other. With proper record-level locking, the first of them to access the record would lock it, or at least flag it as being subject to change, and the second could decide either to wait for it to be updated or to return an appropriate exception. I don't mean to diss SQLite, which is an excellent utility, but it is not, and doesn't pretend to be, a full featured database server like Mariadb or SQL Server (or even the Microsoft Access database engine).

pfletch101
Posts: 527
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 10:51 pm

neilgl wrote:
Fri Nov 08, 2019 10:45 pm
Or maybe use a database that does have row level locking? e.g. postgresql
Indeed, but it is overkill for most of the things I do with my Pis. If I want a server-based DB manager, I tend to use Mariadb, because I have worked a lot with MySQL in the past, but if I hadn't, I would probably go to postgresql.

trejan
Posts: 730
Joined: Tue Jul 02, 2019 2:28 pm

Re: Writing to SQLIte database from multiple processes

Fri Nov 08, 2019 11:03 pm

pfletch101 wrote:
Fri Nov 08, 2019 10:48 pm
I don't mean to diss SQLite, which is an excellent utility, but it is not, and doesn't pretend to be, a full featured database server like Mariadb or SQL Server (or even the Microsoft Access database engine).
Yes. It might be possible to kludge it with SQLite but you get to a point where swapping to something else is going to be a much better solution.
pfletch101 wrote:
Fri Nov 08, 2019 10:48 pm
If Process A and Process B both read the same record (permitted, because neither has tried to write to the database yet), they can each sequentially proceed to submit and commit updates to that record, based on its original contents, which may be inconsistent with each other.
It is doable using transactions but there is a hefty performance penalty. If your process needs to do a read-modify-write then it can use BEGIN EXCLUSIVE TRANSACTION which immediately request an EXCLUSIVE lock and would block all other readers/writers. It would basically be the same as your big mutex wrapper.

User avatar
neilgl
Posts: 1108
Joined: Sun Jan 26, 2014 8:36 pm
Location: Near Aston Martin factory

Re: Writing to SQLIte database from multiple processes

Sat Nov 09, 2019 12:10 am

Yes that PostgreSQL has been running ok on my pi3 (pi2) for years.

Return to “Advanced users”