3 ms·

You can get effectively the same behavior as an mkdir based lockfile when using BEGIN IMMEDIATE instead of just BEGIN with a WAL based SQLite db. (other modes s

by bhawks 3y ago

You can get effectively the same behavior as an mkdir based lockfile when using BEGIN IMMEDIATE instead of just BEGIN with a WAL based SQLite db. (other modes should use BEGIN EXCLUSIVE). Using this feature statements will immediately fail if another process is writing to the db and you can use the sqlite3_busy_timeout to manage automatic retries for you (if desired). The benefits over the lockfile approach include: - transactional semantics & rollbacks - optional concurrent read only queries in WAL mode if you initiate them with BEGIN DEFERRED that operate on a snapshot of data and don't impede the writing process - simple online backups via VACUUM INTO. Cons are it is heavier weight than a mkdir call and if all you want to do is elect a master process from a group and don't have structured data to operate on transactionaly then it's not worth it.

chasil 3y ago
Oh my, you must understand the limitations of WAL mode. I just don't think that SQLite is an appropriate alternative to mkdir() locking. You cannot use WAL mode with networked filesystems as all processes cannot see the shared memory. Below are well-known limitations of WAL mode. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.” I just don't think that SQLite is an appropriate alternative to mkdir() locking. It's just the wrong tool.

by bhawks 3y ago

chasil 3y ago

Oh my, you must understand the limitations of WAL mode. I just don't think that SQLite is an appropriate alternative to mkdir() locking. You cannot use WAL mode with networked filesystems as all processes cannot see the shared memory. Below are well-known limitations of WAL mode. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.” I just don't think that SQLite is an appropriate alternative to mkdir() locking. It's just the wrong tool.