SQLite Forum

Is pragma integrity_check safe during app working?
Login

Is pragma integrity_check safe during app working?

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

Hi!

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?

BR

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