Can VACUUM rollback the db?
(1) By Florian (Flohack) on 2020-06-29 15:30:23 [link] [source]
We have an auto-compacting feature enabled for our database, so if the user closes the application or the workspace, an external process will be kicked off that simply calls VACUUM on the Db and then terminates. If the user wants to open the same workspace again, we detect that the compact is still running and present him with an option to terminate this process.
So far so good, this works for 99% of users. Still we have one customer that reported that sometimes changes in his workspace are reverted the next time he opens the Db. Turns out if we turn off this feature all is fine.
Now I can understand if the db was closed before the compacting and a transaction was lingering then this would make perfect sense, except it does not: Wouldn´t any new connection to the db roll back anything thats in the journal? Why the disabling of the vacuum helps to fix that issue? Just brainstorming here, could the vacuum kick in while still things are not flushed correctly and cut off the log?
(2) By Rowan Worth (sqweek) on 2020-06-30 02:53:55 in reply to 1 [link] [source]
VACUUUM is just a regular transaction, so assuming a sane environment/filesystem which provides the expected locking/sync semantics this shouldn't be possible.
If this is the only concurrent access to the DB performed by your app, then it's possible that assumption doesn't hold on this customer's machine but the problem doesn't manifest in normal usage.
Now I can understand if the db was closed before the compacting and a transaction was lingering then this would make perfect sense
This scenario doesn't really make sense, because sqlite cleans up when closing a DB (assuming sqlite3_close is called and returns SQLITE_OK). The only time you should end up with a lingering transaction (aka hot journal) is if sqlite is unceremoniously killed or the machine crashes, and in that case you are correct that any connection to the DB will roll back said transaction.
If the DB was not actually closed (eg. sqlite3_close returns SQLITE_BUSY because there are still unfinalized statements associated with the connection), then the connection would remain open and would still be holding locks that should prevent VACUUM from proceeding.
(3) By Florian (Flohack) on 2020-06-30 07:13:22 in reply to 2 [link] [source]
I did some more investigations, so it turns out we are not reusing connections at all, means throwing away the connection together with the eol of the transaction (be either committed or rolled back). So I would say that if the connection gets closed there, the thread would block until any I/O has finished, or am I missing something?
We have a guard in place that allows only one transaction to exist at at time so I also do not see any concurrency being an issue. It´s such a strange thing for that easy usecase :)
(4) By Keith Medcalf (kmedcalf) on 2020-06-30 09:11:17 in reply to 3 [link] [source]
You should be explicitly finalizing ALL statements when you are done with them.
You should be explicitly closing ALL connections when you are done with them.
Throwing them away and hoping/praying for the best is often an error-prone strategy.
(6) By Florian (Flohack) on 2020-06-30 22:13:24 in reply to 4 [link] [source]
Well our project utilizes the .NET wrapper, so I do not have full control over everything, but I think we are having using statements in place, so disposal should be automatic.
(5) By Rowan Worth (sqweek) on 2020-06-30 09:27:13 in reply to 3 [source]
I'm not sure what you mean by "eol of the transaction" but you might be interested in this documentation:
If an sqlite3 object is destroyed while a transaction is open, the transaction is automatically rolled back.