Sorry – another slightly technical post. This is yet again another visit to the subject of locking, which I briefly described the problems with a while ago, and then wrote some new code to help alleviate the problems.
One thing I didn’t explain at all, looking back at those posts, is the different reasons for locking. We can simplify this down to two major reasons:
- Internal database consistency. SQL databases are usually “ACID compliant“, and SQLite is no different. This means that every reader and writer gets a consistent view of the database; for example, if you’re writing data to the database, you can’t ever run a query which gets back partially-written data – you either see the data in its complete state, or you see nothing at all.
- External store consistency. A bit like internal views, we don’t want clients accessing the store to see “inconsistent” data – we don’t want one client to be writing a mail to a user’s Inbox, and have another client read the half-written data and think it has the whole mail.
Those problems seem very similar, and indeed, our previous issues are mostly to do with the fact that those problems were conflated: the SQLite locking was used to ensure external consistency in the store. That’s actually a really common – and often desirable – way of doing things. The way it works is you wrap all your SQL operations in an SQL transaction, and whether or not your operation (e.g., writing a file) succeeds stands or falls on whether or not the transaction succeeds. The database guarantees that when you use a transaction, you can make many alterations at once but no other database client gets an intermediate view of the partially-written data.
