Re: [sqlite] Database deleted from file system while other open connections exist.
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
Re: [sqlite] Database deleted from file system while other open connections exist.
> 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? If SQLite was able to obtain such list (not in the same process but among several different processes, remember?) that would be a serious security breach in the kernel. Let alone the possibility of closing file handles in other processes. > Wouldn't it use it for mechanisms like > 'unlock notify' and 'busy handler', etc? It's implemented using simple polling technique: try to lock; if it's unsuccessful sleep for some small time; try to lock again and repeat that until lock is successful or the total waiting time is too long. Pavel On Fri, Nov 12, 2010 at 11:44 AM, Dennis Suehr wrote: > Simon, > > Many thanks for your prompt and thorough response. > > Just a couple of follow-up questions if I may: > > 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). > > 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. > > 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? > > 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? > > Cheers, > > Dennis > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database deleted from file system while other open connections exist.
Simon, Many thanks for your prompt and thorough response. Just a couple of follow-up questions if I may: 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). 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. 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? 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? Cheers, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database deleted from file system while other open connections exist.
On 11 Nov 2010, at 4:05pm, Dennis Suehr wrote: > I need to implement a function which will be able to delete a previously > created database. I plan to do this by obtaining an exclusive lock to the > DB, then closing my connection (which as I understand will still keep the DB > locked) and then removing the DB file (and related SQLite temp files) from > the underlying file system. > > I have a couple of concerns regarding this operation, which are as follows: > > 1) Firstly, I can not seem to ascertain what would happen to another process > which held an open database handle to the now deleted DB if it tried to > perform a subsequent operation on it. If some appropriate error code is > returned upon which I can act, then great. However, it goes without saying > that a seg fault would not be an appropriate outcome > > 2) Ideally, I would like to be able to force close any open connections > before deleting the database file and if that's not possible then at least > being able to clean up any SQLite related memory resources from those other > connection afterwards. However, I can not even find any existing SQLite > mechanism which would provide me with a list of open connection handles to a > given DB. The reason you can't find this in SQLite documentation is that it's done at the OS level. The answer depends on which operating system and which file system you're using. Let us assume that you are accessing these files directly from a hard disk rather than across a network. Then you are using perhaps FAT, NTFS, ext3, HFS+, or some other disk filing system. Under early versions of Windows using FAT or NTFS, if you had a file open without an active lock, the file could be deleted. The application with an open handle would receive an error the next time it tried to do anything with that handle (sometimes including 'close' !). There have been two other changes since early versions of Windows, but I don't remember where they each happened. However, current versions of Windows are POSIX compliant and act the same as Unix systems. POSIX systems (which include all the versions of *n*x you're likely to find including Mac OS X) use a system of hard links: one file on disk may have many hard links to it (appear under different names and in different directories). So a file should be deleted only when the last hard link to it has been deleted. However, a hard link is created each time an application opens the file: that's what the file handle points to. So if two apps have the file open there would be three hard links to the file, and deleting its entry in a disk directory leaves two hard links, so it doesn't delete the file and reclaim the disk space. BUT ... the file will no longer be shown if you list the contents of its directory. There's nothing to stop someone in the meantime creating a new file with the same name, and using an app to open that one. So it's possible for two applications to have open two different files of the same name in the same directory. Other (embedded ?) disk operating systems and can all have their own rules about this. And as I wrote earlier, network filing systems have their own rules about this. To complicate things, one computer might be accessing the file directly from disk, but another might be accessing the same file through a SMB/SAMBA/NFS/whatever network connection. Which is one reason we tell people never to do that. So assuming direct access from a hard disk and a fairly recent OS we get the following answers: 1) The file would not actually be deleted yet. The original file would still exist and the process can continue to read/write it. When the last app closes the file the OS will notice, really delete the file, and reclaim the filespace as unused space. No seg faults because there are no errors. 2) No. There's no way to tell all applications with a certain file open that it's going to disappear. If you write all those apps yourself you could come up with any number of semaphore mechanisms, but most OSen don't provide anything like that. SQLite definitely can't tell what files it really has open because there's no way for the OS to tell it: it works differently for each OS. The above is simplified for brevity (e.g. Mac OS X actually does have a notification mechanism for a file disappearing) but should give you something to go on. If you have specific questions, get back to us. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database deleted from file system while other open connections exist.
Hello, Apologies if an answer to my query exists elsewhere. I have been searching for a fair while without success. I am building an SQLite interface as a C shared library and consequently will have no knowledge or control of concurrent connections to any databases which are created by it. I need to implement a function which will be able to delete a previously created database. I plan to do this by obtaining an exclusive lock to the DB, then closing my connection (which as I understand will still keep the DB locked) and then removing the DB file (and related SQLite temp files) from the underlying file system. I have a couple of concerns regarding this operation, which are as follows: 1) Firstly, I can not seem to ascertain what would happen to another process which held an open database handle to the now deleted DB if it tried to perform a subsequent operation on it. If some appropriate error code is returned upon which I can act, then great. However, it goes without saying that a seg fault would not be an appropriate outcome 2) Ideally, I would like to be able to force close any open connections before deleting the database file and if that's not possible then at least being able to clean up any SQLite related memory resources from those other connection afterwards. However, I can not even find any existing SQLite mechanism which would provide me with a list of open connection handles to a given DB. Any help in either of these regards will be much appreciated. Thank you in advance, Regards, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
