Is pragma integrity_check safe during app working?
I would like to setup 'pragma integrity_check' with cron as daily database check. But I wonder if it's safe for incoming transactions since application will be running all the time. Do you have any interesting thoughts about that?
The PRAGMA integrity_check takes a read transaction. This might interfere with simultaneous write operations if you are not in WAL mode. Other than that, there shouldn't be any issues.
Do you know what kind of consequences can be from making write operation and pragma integrity_check? Database corruption? Wrong integrity_check output or wrong write transaction?
In rollback mode, if you try to write while PRAGMA integrity_check is running, you will get an SQLITE_BUSY error. To avoid this error, you can:
Run in WAL mode
Set "PRAGMA busy_timeout=N" to cause the writes to wait for N milliseconds before giving up and returning SQLITE_BUSY.
I really appreciate your response! Thank you Sir.
Unfortunately, I cannot switch to WAL mode and i think, that changing busy_timeout is not the best option for app that I working on.
I see that I will be forced to run pragma integrity_check during hours when the app is not used by users or just simply turn off the app and then check db.