#784 SQLite3 Database is not properly locked, breaking concurrent access to the DB

Reporter woffs
Owner Nobody
Created
Updated
Stars ★ (1)  
Tags
  • Priority-Medium
  • Type-Defect
  • Status-CantReproduce
  1. woffs on

    What steps will reproduce the problem? - storage = "sql" - restart prosody - sqlite3 /var/lib/prosody/prosody.sqlite "pragma journal_mode=wal" - restart prosody - lsof -p `pgrep -f prosody` What is the expected output? What do you see instead? I would expect /var/lib/prosody/prosody.sqlite and /var/lib/prosody/prosody.sqlite-shm having a read lock. But I see no lock. When accessing the DB from another process (e.g. sqlite3 or prosodyctl), the *-shm and *-wal are deleted because of the missing lock, leading to data loss. What version of the product are you using? On what operating system? prosody-trunk 1nightly724-1~jessie on Debian Jessie amd64. lua-dbi is 0.5.hg5ba1dd988961-2 (official Debian package). Please provide any additional information below. I have already filed this as https://github.com/mwild1/luadbi/issues/37 but I'm not sure if it is a luadbi bug, because a small lua test script locks a DB as expected.

  2. woffs on

    create the test db: $ sqlite3 example.sqlite3 sqlite> CREATE TABLE schn (eins,zwo); sqlite> INSERT INTO "schn" VALUES(1,2); sqlite> INSERT INTO "schn" VALUES(2,3); sqlite> pragma journal_mode=wal; sqlite> ^D the "small lua test script" here: local DBI = require('DBI') local dbh = assert(DBI.Connect('SQLite3','example.sqlite3')) local sth = assert(dbh:prepare('select * from schn')) print (sth:execute()) for row in sth:rows(true) do print (row.eins,row.zwo) end os.execute('lsof -p $PPID') print (sth:close()) os.execute('lsof -p $PPID') print (dbh:close())

  3. Zash on

    Did you test with this script running as different users?

  4. woffs on

    Zash, the missing lock can be seen without a second concurrent process. And I tested with a "sleep 20" instead of the lsof, and a concurrent sqlite3 cmdline access to the database, and the locking and access worked fine. Same UID. Different UIDs are not the problem here. The user has write access to the directory (like prosody has on /var/lib/prosody).

  5. woffs on

    Illustrating with lsof output: $ lsof -p `pgrep -f prosody` | grep sody.sqlite lua5.1 32181 prosody mem REG 254,1 32768 131264 /var/lib/prosody/prosody.sqlite-shm lua5.1 32181 prosody 10u REG 254,1 3433472 131105 /var/lib/prosody/prosody.sqlite lua5.1 32181 prosody 11u REG 254,1 1532208 131118 /var/lib/prosody/prosody.sqlite-wal lua5.1 32181 prosody 12u REG 254,1 32768 131264 /var/lib/prosody/prosody.sqlite-shm $ lua test.lua | grep ample.sqli lua 26275 woffs mem-r REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs 3ur REG 0,21 8192 101254 /home/woffs/sqlite3/example.sqlite3 lua 26275 woffs 4u REG 0,21 0 102638 /home/woffs/sqlite3/example.sqlite3-wal lua 26275 woffs 5ur REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs mem-r REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs 3ur REG 0,21 8192 101254 /home/woffs/sqlite3/example.sqlite3 lua 26275 woffs 4u REG 0,21 0 102638 /home/woffs/sqlite3/example.sqlite3-wal lua 26275 woffs 5ur REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm Notice the additional r for "read lock".

  6. Zash on

    Try with dbh:autocommit(false); and explicit BEGIN TRANSACTION and COMMIT, which is what Prosody does.

  7. Zash on

    Looks fine to me: lua5.1 22734 zash 15ur REG 8,6 32768 1441878 /home/zash/src/prosody/trunk/data/prosody.sqlite lua5.1 22734 zash 16u REG 8,6 98912 1444683 /home/zash/src/prosody/trunk/data/prosody.sqlite-wal lua5.1 22734 zash 17ur REG 8,6 32768 1444687 /home/zash/src/prosody/trunk/data/prosody.sqlite-shm On: Debian 9.0 (stretch) libsqlite3-0 3.16.2-3 lua-dbi-sqlite3 0.5.hg5ba1dd988961-4

    Changes
    • tags Status-CantReproduce
  8. woffs on

    I suspect a mod which is interfering here in my case. I remember testing this with and without some modules and I noticed different behaviour and looked into the code and then gave up. This has to be repeated and documented properly.

New comment