SQLite gets a shared read or exclusive write lock on a given file at a time. Does “file” mean a whole database or a single table?
A file refers to an entire SQL database (tables, indexes, everything), so SQLite usually does database-wide locking. The locking situation is more complex when SQLite is used in shared cache mode, where some operations lock a table rather than the whole file, but only relative to threads in the same process sharing the cache. What other locking implications are there? For example, a large table scan requires BDB to acquire and release read locks on every page, but SQLite native only gets/releases a single lock. How much of an impact does this have on performance and what types of queries are most/least affected by it? Locking has a significant impact on some queries: we have seen up to 30% of the runtime for some queries connected with locking (and latching). While scans do lock each leaf page in a table, they generally map to a bulk get, and the locking cost is often not significant. The most costly queries are ones that involve many random lookups, since each individual lookup acquir
A file refers to an entire SQL database (tables, indexes, everything), so SQLite usually does database-wide locking. The locking situation is more complex when SQLite is used in shared cache mode, where some operations lock a table rather than the whole file, but only relative to threads in the same process sharing the cache.
