Switching between EXCLUSIVE and NORMAL lock mode for backup
(1.1) By dkr (daveryderoxford) on 2022-03-03 11:28:53 edited from 1.0 [link] [source]
I have a database that Im accessing in EXCLUSIVE mode (for proven performance reasons) that I want to backup.
From https://sqlite.org/pragma.html#pragma_locking_mode it looks possible to change the database locking mode between NORMAL (shared) while the backup is in progress only.
From my backup process, I was proposing to perform the following:
- PRAGMA locking_mode = NORMAL
Perform backup
- sqlite3_backup_init()
- sqlite3_backup_step(-1)
- sqlite3_backup_finish
PRAGMA locking_mode = EXCLUSIVE
The relevant documentation section is:
In NORMAL locking-mode (the default unless overridden at compile-time using SQLITE_DEFAULT_LOCKING_MODE), a database connection unlocks the database file at the conclusion of each read or write transaction. When the locking mode is set to EXCLUSIVE, the database connection never releases file locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.
Database locks obtained by a connection in EXCLUSIVE mode may be released either by closing the database connection or by setting the locking mode back to NORMAL using this pragma and then accessing the database file (for read or write). Simply setting the locking mode to NORMAL is not enough - locks are not released until the next time the database file is accessed.
Do people see issues with this approach?
(2) By Rowan Worth (sqweek) on 2022-03-03 17:58:50 in reply to 1.1 [source]
I don't think your proposal is complete. Locking mode is a connection-level setting, so starting another process and changing the locking mode isn't going to have any effect on the existing process.
I'm assuming your desire is to keep the production process up and running while the backup is taken; this would have to be a cooperative protocol along these lines:
Production process: PRAGMA locking_mode = NORMAL -- (release exclusive lock)
Backup process: sqlite3_backup_init()/step()/finish()
Production process: PRAGMA locking_mode = EXCLUSIVE -- (resume fast mode)
However the other thing to note is that sqlite3_backup_step(-1) will copy all pages to the destination in a single transaction. This transaction is read-only on the source database, but the implication is that if the production process wants to write anything to the database it has to wait for the backup to finish.
You can pass a finite page limit through to sqlite3_backup_step() to limit the transaction size, but the downside of this approach is that if the production process changes the database the backup process has to start over from scratch. ie. your choice is between starving production or starving the backup.
You might be able to get the best of both worlds if you implement the backup as another thread in the existing process. IIRC sqlite has some extra smarts at the library layer so that if a backup is in progress and another connection in the same address space changes the source DB, the changed pages are flagged and the next backup iteration just has to copy the updates over (instead of starting over).
(3) By dkr (daveryderoxford) on 2022-03-07 11:03:51 in reply to 2 [link] [source]
Many thanks. I will go with performing the backup as a separate thread in the same process.
(4) By anonymous on 2022-03-07 12:07:57 in reply to 3 [link] [source]
There is also the option of using vacuum into
This also requires giving up the exclusive lock but database access will continue normally. If you are in WAL mode then you should end up with a snapshot of the db at the end of the vacuum process.
(5) By Rowan Worth (sqweek) on 2022-03-10 03:40:24 in reply to 4 [link] [source]
The journal mode is an interesting consideration. I should clarify that my response is based on ROLLBACK journal mode; it's possible there are other approaches available in WAL mode to allow concurrent writes while a backup proceeds.