On 12 Nov 2010, at 4:44pm, Dennis Suehr wrote:

> Firstly, a bit more background information.  The system will be running on 
> Linux (kernel > 2.6.0) and all accesses to the database will be via local 
> disk (ext3 or similar).

This allows me to be a little more precise about my answers, I think.

> 1) I appreciate your explanation about any any open connections to the file 
> still being able to write to it until they close it.  I suppose that 'fuser' 
> could be used to send a SIGTERM or similar to the processes holding open file 
> handles.  I will have to give some additional thought to how I go about this 
> 'delete' operation. 

Yes, you can certainly kill any processes holding the file open.  I assume your 
processes will be intelligent about how they respond to SIGTERM, issuing 
sqlite3_close() if but only if appropriate.  Otherwise you will have trouble 
with journal files and/or WAL files being left behind.
> 2) Regarding, SQLite and open handles to a database, that is fair enough that 
> it does not have a mechanism send alerts to all processes connected to a 
> given DB.  However, I am surprised that it does not at least possess a list 
> of all open handles to a given database.  If I had that, then I could close 
> all DB connections either before the delete or after.  Are you sure that such 
> a list does not exist?  Wouldn't it use it for mechanisms like 'unlock 
> notify' and 'busy handler', etc?

SQLite is an unusual SQL engine in that it has no server/client architecture.  
In most SQL engines, for example MySQL, all the real work is done by a server 
process, and all applications just communicate with this process: they have no 
contact with the files on the disk.  In SQLite, all applications are peers: 
they actually handle the disk file and don't know about one-another.  No one 
process runs the show, and they communicate only by using the file system's 
locking routines.  All one process knows is that some part of the disk file is 
locked.  It doesn't know what locked it or even whether it was locked by a 
SQLite library call or by some arbitrary application treating that file as 

> 3) Finally, am I correct in thinking that if I used 'pragma locking 
> exclusive' that I can lock the entire database even after closing my 
> connection?

Closing your connection releases the lock.  (The mechanism used is just the 
normal file locking one and it's unusual for an OS to let you lock a file you 
don't have open.)   For more information see


Just in passing, all locks in SQLite are locks of the entire database.  SQLite 
doesn't lock individual rows or ranges of rows.  So that's a reason not to use 
locking when you don't need to.

If you are doing serious concurrent multi-process access, then you might want 
to explore other solutions.  You might want to use a SQL engine with a 
server/client architecture.  Or instead of deleting the disk file you might 
want to not shuffle files at all: issue a 'DELETE FROM tableName' which is very 
fast, and use the form of INSERT that copies your data from another table in 
another database you are accessing using ATTACH.

sqlite-users mailing list

Reply via email to