DevHeads.net

Postfix with sqlite - Database becomes locked

Dear all,

I am running postfix 3.3 and recently migrated all my virtual domains from MySQL to Sqlite for performance reasons. So far, everything works fine however up to 5-8 times a day, I am seeing an error message in my mail.log saying that the Sqlite 3 database may be locked. Here is an example:

Comments

Re: Postfix with sqlite - Database becomes locked

By Wietse Venema at 01/10/2018 - 15:15

Sebastian Wolfgarten:
So what is locking the database? The query as shown does not attempt
to modify the database. Note that the query fails immediately, there
is no attempt to wait for a lock to be released.

Wietse

Re: Postfix with sqlite - Database becomes locked

By sebastian at 01/10/2018 - 15:28

Hi,

I am uncertain as to what’s causing the DB to get locked - I am also running Roundcube (webmail) on the server, maybe that’s the reason. I will check the permission roundcube uses to access the db, I think it can be read-only which will hopefully fix the locking issue.

As a feature request, would it maybe make sense to add a waiting period to be able to wait for the lock for 1-2 seconds and then retry with the database query?

Thanks.

Kind regards
Sebastian

Re: Postfix with sqlite - Database becomes locked

By Wietse Venema at 01/10/2018 - 19:45

Sebastian Wolfgarten:
It certainly looks like a problem specific to your setup. The Postfix
sqlite client has been around since Postfix 2.8 and it has hardly
changed.

The only lock that can prevent sqlite from reading is a write lock,
and there is nothing in Postfix that generate an sqlite write request.

Look at the file modification time. Did the file change recently?

Let's find out more about the error first. See John Fawcett's suggestion.

Wietse

Re: Postfix with sqlite - Database becomes locked

By Viktor Dukhovni at 01/10/2018 - 20:15

SQLite is designed primarily for embedded access and writers acquire
exclusive locks when making updates, or merging the write-ahead-log
into the database ... Read-only users need to be willing to retry
database operations when it is locked by a writer. The easiest way
to do that is by making the first raw SQL command at the start of a
connection a pragma to set a busy timeout:

<a href="https://www.sqlite.org/pragma.html#pragma_busy_timeout" title="https://www.sqlite.org/pragma.html#pragma_busy_timeout">https://www.sqlite.org/pragma.html#pragma_busy_timeout</a>

PRAGMA busy_timeout = milliseconds

For a database with email-related info large transactions
and long write-locks should be rare, so 1000ms or so should
be enough.

Re: Postfix with sqlite - Database becomes locked

By John Fawcett at 01/10/2018 - 16:02

On 01/10/2018 09:28 PM, Sebastian Wolfgarten wrote:
Could be useful to see the return code from sqlite3_step. If this is
different to SQLITE_DONE or SQLITE_ROW then the warning message is
triggered. When using sqlite3_step() after the sqlite3_prepare_v2()
function the return code can contain extended error information.

<a href="https://www.sqlite.org/rescode.html" title="https://www.sqlite.org/rescode.html">https://www.sqlite.org/rescode.html</a>

If you're able to compile from source:

--- dict_sqlite.c    2015-01-11 17:52:40.000000000 +0100
+++ dict_sqlite_new.c    2018-01-10 21:55:12.149559110 +0100
@@ -244,8 +244,8 @@
     }
     /* Fix 20100616 */
     else {
-        msg_warn("%s: %s: SQL step failed for query '%s': %s\n",
-             myname, dict_sqlite->parser->name,
+        msg_warn("%s: %s: SQL step failed with result %d for query
'%s': %s\n",
+             myname, dict_sqlite->parser->name,status,
              vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
         dict->error = DICT_ERR_RETRY;
         break;

John