0

I'm using a simple sqlite DB as persistent msg-queueing mechanism between processes. To reduce the file size after exceeding a certain limit I wanted to use the "vacuum" command. Generally all this works nicely, only that every now and then I get a "database is locked" error when vacuuming.

After reading through various resources on the web I understand that there is nothing I can do on sqlite-level.

However, besides the side-questions "Why is that the case? What would be the problem with retrying to obtain the required lock using the regular busyHandler mechanism?" I came up with the idea to just implement the very same busyHandler mechanism just on application level.

Now the essential question: Anything wrong with this?

Many thx!!

tge
  • 91
  • 9
  • SQLite automatically reuses freed database pages, so the file size will stay constant eventually. You don't need to `VACUUM` unless you actually know that the total amount of data will be smaller *and* will not increase in the future. – CL. Oct 10 '13 at 08:16
  • true, but in my scenario (as a msg queue) I have to support something like a max queue size. So if I exceed this, I cannot write into the DB never again - even if the DB is completely empty meanwhile (because the reader now has consumed all msgs). Hmm, unless I could determine the actual real amount of data (rather than just the file size) ... will think about that ... – tge Oct 10 '13 at 13:39

2 Answers2

0

There isn't really much of a difference between using SQLite's built-in retrying mechanism, and implementing it yourself. However, writing code that you do not need to write is useless effort and increases the chance of introducing bugs.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Agree. But based on my initial design I still think (but will re-think - actually not needing it were much more elegant) that I DO need it, and it's not there - hence I do it myself. So, maybe the question "why is it not there" might be more important. Did the sqlite people leave it out on purpose? Most likely. Because it adds tech. problems or just because they did not want a special solution just for that case? – tge Oct 10 '13 at 13:49
  • What exactly do you think is missing? – CL. Oct 10 '13 at 13:53
  • well, retrying to acquire the lock for the vacuuming operation (e.g. by using the busyHandler or anything like that), or better, having the possibility to enable such a mechanism as for other operations – tge Oct 10 '13 at 19:27
  • Exactly. Generally yes, but not when vacuuming – tge Oct 11 '13 at 11:46
  • Works for me. (There is no difference in how SQLite tries to acquire the lock.) – CL. Oct 11 '13 at 11:49
  • Hmm, strange ... I'll check whether there is a newer version. Regarding the topic of how it acquires the lock: yes, but looking into the C code, there seem to be differences depending on which type of lock is needed (there is a comment at beginning of function "sqlite3PagerSetBusyhandler") – tge Oct 16 '13 at 13:07
0

After working on this for another while, I changed my application logic by removing the vacuuming altogether but rather use "pragma max_page_count" to limit the DB size. The only thing to watch out for is that this seems to be session-specific, i.e. the pragma must be set again after each DB connect.

Regarding the original problem: I found that @CL was mostly right - vacuuming indeed involves the standard busy handler. And on Linux this also works nicely, just on Windows it does not reliably (might be by accident though and rather caused by system speed, ...). Using a little printf() in my custom busyHandler I can see that it is invoked in most cases, however sometimes it is not and the "pragma vacuum" just returns "DB locked". Possibly caused by concurrent processes trying to vacuum at the same time (?) ... anyway, the reworked design is much cleaner/easier anyway.

tge
  • 91
  • 9
  • Busy handlers are not invoked [when there is a deadlock](http://www.sqlite.org/c3ref/busy_handler.html). – CL. Nov 05 '13 at 15:21