On Tue, 23 Nov 2004, Alexander Jordanov wrote:
Hi i have an SQLite database file that gets locked from time to time and some parts of the site that uses the sqlite cannot be accesed. For what i know the SQLite database uses the OS locking mechanism. So my question is: Is there any way to unlock it (preferabbly by using PHP)? The most interesting part is that the file locks most often at night when there is almost no activity but is behaving very well during the day when there is a lot of activity - and that's the reason why i think it's not my script locking it otherwise it would lock mostly during the day when there is a lot of queries to the database (if it wasn't for that i would seek the problem in my scripts). I have made a log file for the queries that are run by the script and i see there just a few INSERT or UPDATE queries and a lot of SELECT queries and nothing that (at least i think so) could lock the database. And another question is: What could lock the database? I have succeded to lock it on linux when a timeout for the script occured (the script was just doing a lot of SELECT-s and very little INSERT or UPDATE -s) but i don't beleive that that is how the database gets locked because I had put a timeout to my scripts of 3600 s and it still gets locked and i don't beleive that any of my scripts will need that much time to finish (i don't remember doing something infinite anywhere and if i do then the guys from the hosting would've called me by now :) ) and especially the scipts that are being run around the time when the locking occurs. The current problems occured recently, before that the site was working very stable... Is it possible a corrupt database to have such a behaviour? Currently i unlock the database by a script i made that basically copies the file renames the old wan to '.bak' and renames the copy to the database file name, it works but i do not dare to make this automatic because if something happens during the copy i will lose the entire database... I almost forgot: SQLite version is 2.8.14, php is 5.0.2 on Apache release 10331100 API version 19990320 . I know that versions prior to 2.8.9 had some problems with locking under linux is it possible that thay are still there? Sorry if this is told too confusing the reason is that this problem is pretty urgent and i wanted to say everything i have tried and any information that may be relevant so i don't be asked for more information...
Shortly: is there any way to unlock the database right there on the hosting server? and what could cause the locking?
Please help me. Thank you
almost certainly it's your script that is holding the lock. the next time this happens you need to determine which processes are holding the lock. do a 'man fuser' to see how to do this. after determining which processes are holding the lock (using the sqlite database file) you should then see what they are doing using strace or something similar. the solution lies in here somewhere - if you just copy the db as you are doing it's possible that you'll copy it midwrite and destroy your data. an alternatice method, and one that i always use, it to use a sentianl file which to do the locking in advance on, eg.
# write operation
lockfile = open 'db.lock', 'a+'
lockfile.flock LOCK_EX
db.execute sql
lockfile.flock LOCK_UN
lockfile.close
# read operation
lockfile = open 'db.lock', 'a+'
lockfile.flock LOCK_SH
db.execute sql
lockfile.flock LOCK_UN
lockfile.close
this will hamper performance, but probably not greatly. the method of busy polling while the database is busy also hampers performance. if you use a sentinal file in this way you should never, ever have a locked database error happen. obviously you need to understand when you should be a write lock and when you should get a read lock.
cheers.
-a -- =============================================================================== | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
