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 bytes. > 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 http://www.sqlite.org/pragma.html#pragma_locking_mode 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
