On Mon, 2 Aug 2004, Oliver Harvey wrote:
hello all,
we have many thousands of sqlite databases reporting 'database is locked'.
then your access is occuring at a moment when another process is also using the database in a way that prevents aquiring the correct type of lock. sqlite uses non-blocking locks and simply reports this if it cannot aquire the lock it needs. personally i think the default should always be blocking locks as people generally want this behaviour.
we are in a production environment with a large volume of data coming through our systems - and need access to those databases!
there are no stale nfs locks, to it must be locking at some other level.
i assume then, that you are accessing your databases from an nfs mounted file system - this is always subject to more problems than a local filesystem, but i'm doing the same, as are others. the locks should all simply be fcntl (nfs safe) based locks. in this case, and also if the databases are local, you need to coordinate access to the database somehow in a way that will will put the process to sleep (block) until the lock becomes available rather than simply reporting that it was not. the simplest way to do this is to create a file for each db, say db.lock, and to apply a blocking read/write to this file depending on the intent of your operation. the contents of this file are not important - it is merely used as a mutex that processes can coordinate access to the database with. the key is to ask for the locks in a blocking fashion - this should ensure that only one process is accessing the database at a given moment and prevent sqlite from ever needing to return 'database is locked'.
obviously there are performance issues here, not least of which that nfs lockd is absolutely terrible at distributing locks. i suspect that, if you monitor the lock owner hostname.pid somehow, you'll see a single host obtain the lock hundreds of time in a row before another host gets it. in essence lockd does starved processes quite badly. to remedy this you can implement a smarter lock request (more that just the fcntl call), but that's getting a bit OT.
is there any way to force these out of a locked state?
not without upsetting the process that has the lock!
I appreciate that they may be corrupt, but rebuilding them is going to be tough.
sounds like they are locked - not corrupt.
-a
--
===============================================================================
| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE :: 303.497.6469
| A flower falls, even though we love it;
| and a weed grows, even though we do not love it. | --Dogen
===============================================================================
