It can't be done without fs specific snapshots - otherwise how would it distinguish between a cp/rsync needing consistent reads vs another sqlite client wanting the newest data?
https://man7.org/linux/man-pages/man2/ioctl_ficlone.2.html
> Clones are atomic with regards to concurrent writes, so no locks need to be taken to obtain a consistent cloned copy.
I'm not aware of any of the filesystems that use it (Btrfs, XFS, Bcachefs, ZFS) that deviate from expected atomic behavior, at least with single files being the atom in question for `FICLONE` operation.
The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata.
To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot.
PRAGMA wal_checkpoint(FULL);
BEGIN IMMEDIATE; -- locks out writers
. /* trigger your LVM snapshot here */
COMMIT;
sudo fsfreeze -f /mnt/data # (A) flush dirty pages & block writes
lvcreate -L1G -s -n snap0 /dev/vg0/data
sudo fsfreeze -u /mnt/data # (B) thaw, resume writes
sqlite3 mydb-snapshot.sqlite "PRAGMA integrity_check;"Whether a transaction had started and gets transparently rolled back, or you had prevented from starting, what is the difference to you? Either way, you have a point-in-time snapshot, that time is the latest commit before the LVM snapshot.
You're discussing this in terms of "safety" and that doesn't seem right to me.
Here's a scenario for a note-taking app backed by sqlite:
1. User A is editing a note and the app writes the changes to the database.
2. SQLite is in WAL mode, meaning changes go to a -wal file first.
3. You take the LVM snapshot while SQLite is:
- midway through a write (to the WAL or the main db file)
- or the WAL hasn’t been checkpointed back into the main DB file
- a partial write to notes.db
- or a notes.db and notes.db-wal that are out of sync
- Cause sqlite3 to throw errors like database disk image is malformed
- Lose recent edits
- Require manual recovery or loss of WAL contents
- Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes
- Or, flush and block all writes to the filesystem using fsfreeze
If you can corrupt a SQLite database by pulling the power or simulating it by taking a block-device snapshot, this is a serious SQLite bug and you should report it.
https://sqlite.org/transactional.html & https://sqlite.org/atomiccommit.html have more details
If it will convince you, I went and asked ChatGPT like you recommended and it agrees:
> If you want to use snapshots: > Use a filesystem or block-level snapshot tool that guarantees a point-in-time, atomic snapshot (e.g., LVM snapshots, ZFS snapshots, Btrfs snapshots, or VSS on Windows).
If you were going to "Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes" as you suggest is necessary, why even bother with a snapshot at that point?
Since decades, lvm triggers a freeze before snapshot creation and an unfreeze afterwards, if the filesystem supports it.
> fsfreeze is unnecessary for device-mapper devices. The device-mapper (and LVM) automatically freezes a filesystem on the device when a snapshot creation is requested. For more details see the dmsetup(8) man page.
https://man7.org/linux/man-pages/man8/fsfreeze.8.html
It is part of the VFS:
> freeze_fs > > called when VFS is locking a filesystem and forcing it into a consistent state. This method is currently used by the Logical Volume Manager (LVM) and ioctl(FIFREEZE). Optional.
https://docs.kernel.org/filesystems/vfs.html
All relevant filesystems implement it.
> List of these filesystems include (2016-12-18) btrfs, ext2/3/4, f2fs, jfs, nilfs2, reiserfs, and xfs. Previous list may be incomplete, as more filesystems get support.
Using LVM snapshots is absolutely fine for creating consistent and complete copies of sqlite databases. You have to to copy the rollback-journal or WAL, too: they are part the db, it will checkpoint to the db file when you open it or its copy. Every committed transaction before the snapshot is in it. It is complete.
And even if you do use a mode below PRAGMA synchronous=FULL ( https://sqlite.org/pragma.html#pragma_synchronous ) -- FULL is needed to ensure durability as it fsync()s the WAL on every commit -- you are better of than with a system crash: the freeze writes out every dirty page to disk, which is not guaranteed in case of a crash, where in NORMAL mode, you might lose the transactions written to the WAL file, but not to synced to disk yet.