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


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]

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]

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]

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 [link]

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.