SQLite uses DELETE journaling mode by default. It became a problem on a real-world, actively used network (several hundred subscribers, several thousands SMS per day). Some location updates and SMS delivery marks were not getting written because database file was locked:
2013-12-13_07:26:10.10665 <0002> gsm_subscriber.c:362 Subscriber 334020422525672 ATTACHED LAC=3 2013-12-13_07:26:10.10668 <000d> db.c:170 DBI: 5: database is locked 2013-12-13_07:26:10.10865 <000d> db.c:969 Failed to update Subscriber (by IMSI).
Switching to WAL mode fixed the problem.
WAL journaling mode is persistent; it stays in effect after closing and reopening the database. WAL mode database requires SQLite >= 3.7.0.
All credit belongs Ivan Kluchnikov who diagnosed and fixed this issue. --- openbsc/src/libmsc/db.c | 9 ++++++++- 1 files changed, 8 insertions(+), 1 deletions(-)
diff --git a/openbsc/src/libmsc/db.c b/openbsc/src/libmsc/db.c index e720c78..3a989d4 100644 --- a/openbsc/src/libmsc/db.c +++ b/openbsc/src/libmsc/db.c @@ -232,11 +232,18 @@ static int db_configure(void) dbi_result result;
result = dbi_conn_query(conn, + "PRAGMA journal_mode = WAL"); + if (!result) + LOGP(DDB, LOGL_NOTICE, "Warning: failed to set journal_mode = WAL.\n"); + else + dbi_result_free(result); + + result = dbi_conn_query(conn, "PRAGMA synchronous = FULL"); if (!result) return -EINVAL; - dbi_result_free(result); + return 0; }