News for hackers. Stuff that matters.

A comment on Many small queries are efficient in SQLite. Parent comment

Re:Many small queries are efficient in SQLite by lisper on Tuesday February 16, 2021 @01:04PM (#26156804)
I tried to switch from MySQL to SQLite for my Postfix/Dovecot installation but I ran into a very annoying problem: every now and then I lose an email because the "database is locked".

This is a show-stopper for me because the problem happens very rarely, only once every couple of days, but it's catastrophic: when this happens, the incoming message is not bounced, it is actually lost. The only reason I even realized it was happening is because I noticed there were emails in the root account, which is the error-reporting mechanism of last resort.

I've searched the web in vain for a solution. If you have any suggestions, I would love to be able to stick with sqlite, but at the moment I am about to begin migrating back to MySQL. :-(

[ Parent | on HN ]
Re:Many small queries are efficient in SQLite by kevincox on Tuesday February 16, 2021 @01:23PM (#26157025)
This sounds like a problem with Postfix or Dovecot. Postfix shouldn't ack or Dovecot shouldn't delete the email until it is safely stored.

That being said there are many use cases where having high availability is critical and in the face of multiple writers SQLite isn't the best option for that.

[ Parent | on HN ]
Re:Many small queries are efficient in SQLite by lisper on Tuesday February 16, 2021 @01:52PM (#26157347)
I don't think this is a multiple-writer problem. Postfix is only reading. I am running a milter that is writing, but I control the code for that so I have it set up to retry if it fails. So the error is being generated by postfix itself, and so it must be happening on a read (because that is all postfix does).

I was hoping to find some kind of global switch that would make sqlite always wait for locks rather than throwing an error. But I've scoured the web for such a solution without success :-(

[ Parent | on HN ]
Re:Many small queries are efficient in SQLite by samatman on Tuesday February 16, 2021 @05:15PM (#26159442)
There is no such thing, to my knowledge.

What can be done is registering a busy callback:

https://www.sqlite.org/c3ref/busy_handler.html

I don't understand enough about your specific problem to know if this will actually help you, just sharing a tidbit I encountered working on a comparable issue.

[ Parent | on HN ]