SQLite Forum

Is pragma integrity_check safe during app working?

Is pragma integrity_check safe during app working?

(1) By gguinea on 2021-07-19 15:50:03 [link] [source]


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?


(2) By Richard Hipp (drh) on 2021-07-19 16:10:26 in reply to 1 [link] [source]

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.

(3) By gguinea on 2021-07-19 17:19:21 in reply to 2 [link] [source]

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?

(4) By Richard Hipp (drh) on 2021-07-19 17:39:47 in reply to 3 [link] [source]

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.

(5) By gguinea on 2021-07-19 18:06:27 in reply to 4 [source]

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.