SQLite Forum

Help to solve database locked
Login

Help to solve database locked

(1) By Riguz Lee (riguzlee) on 2020-11-12 09:02:10 [link] [source]

Hi there,

I'm using Sqlite3(sqlcipher) with flutter ffi, the database get locked after application hot-restart, ie. the connection is not properly closed (see Database is locked after hot restart and sometimes in production for more details).

However, pragma lock_status actually shows that database is unlocked,

sqlite> pragma lock_status;
main|unlocked
temp|closed
sqlite> select * from note;
1|1605168690261|1605168690261|111|111
2|1605168692461|1605168692461|2222|2222
3|1605168695966|1605168858667|33333xxx2222|33333xxx2222
sqlite> delete from note where id=1;
Error: database is locked
sqlite> pragma lock_status;
main|unlocked
temp|closed
sqlite> pragma integrity_check;
ok
sqlite>

Is this by design or is a bug? How can I figure out which connection locked the database?

Thanks!

(2) By Keith Medcalf (kmedcalf) on 2020-11-12 18:18:36 in reply to 1 [link] [source]

According to my reading of the code, pragma lock_status returns the lock status of the database file (bTree) for the specified connection on which the command is executed, not the interplanetary lock status of the database file (bTree).

It would therefore be entirely possible for a particular connection to not have a lock on the database file (bTree) on the connection on which you are asking for the lock status, yet at the same time for SOME OTHER CONNECTION to have a lock which precludes the connection which does not have a lock from acquiring one.

(4) By Riguz Lee (riguzlee) on 2020-11-12 18:33:11 in reply to 2 [link] [source]

Thanks for your reply, I got it.

(3) By Keith Medcalf (kmedcalf) on 2020-11-12 18:24:45 in reply to 1 [source]

How can I figure out which connection locked the database?

Since you are being precluded from obtaining a RESERVED lock, then the connection which holds a RESERVED lock is the one which is preventing a different connection from obtaining a RESERVED lock.

The solution would be to search the entire universe for connections to the database file and then on those connections find out which one of them is holding the lock.

Use the appropriate command for your Operating System to enumerate the processes which have the associated file open and go see which of those is holding the lock. This requires information which you have not provided.

(5) By Gunter Hick (gunter_hick) on 2020-11-13 07:01:47 in reply to 1 [link] [source]

On linux and other unix type OS it is quite simple to write a program that
- opens a supected SQLite file for read
- checks the file header
- attempts to obtain the file locks that SQLite would use
- output the pid of any process that is preventing the lock from being granted

This would eliminate the guesswork about "who is locking the database"