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?
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).trejan wrote: ↑Fri Nov 08, 2019 10:07 pmTransactions would solve the problems you listed. Read https://www.sqlite.org/lockingv3.html#t ... on_control
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.
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.
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.pfletch101 wrote: ↑Fri Nov 08, 2019 10:48 pmIf 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.