CODEWITHSUNDEEP
sqlite
Vytautas Leonavičius
Vytautas Leonavičius

Reputation: 45

SQLite VACUUM in DB opened from multiple processes

I’ve two questions about SQLite VACUUM (and possibly WAL):

• If multiple processes have DB open, do all SQL statements in all processes need to be finalized for VACUUM to succeed?

• Why would VACUUM sometime not have effect (no space reclaimed) but Sqlite return SQLITE3_OK?

A bit more details about my problem:

I’ve database in WAL mode accessed by 2 processes. At some point, user has a choice of dropping the data from the database. Because database can be opened by multiple processes, I delete the records and then run VACUUM to reclaim the disk space (instead of closing connection and deleting the file).

The problem is that if 2 processes have DB connection opened, VACUUM from one of the processes returns OK, but does not reclaim the space really.

I think what happens is that VACUUM won’t succeed till there’s any outstanding SQL statement from any process. The problem is that I do not want to make those two processes aware of each other.

What I am considering is doing VACUUM from both processes, so that whichever closes the connection last (upon user request to drop the data), takes care of space reclamation. I am also considering auto_vacuum (I am aware of its limitations, but DELETEs are not very frequent on this database.

Upvotes: 2

Views: 1421

Answers (1)

CL.
CL.

Reputation: 180210

The documentation says:

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

Just as with any other kind of database modification, a writable transaction requires that no other read or write transaction is active; this usually requires that all statements are reset or finalized.

When in WAL mode, a write transaction is not blocked by other read transactions, but this requires that old data be kept. You should initiate a truncate checkpoint in addition to the vacuum.

The easiest way to handle this would be to not run VACUUM at all; the free space will be reused later.

Upvotes: 1

Related Questions