@Ara.T.Howard:
Thanks for the suggestion i will try it and hope the database will not
lock again...
Here is what i did:
clearstatcache ();
if ($sql[0]=='S'){
$fp=fopen('koral.db.lock','a+');
while (!flock($fp,LOCK_SH)){};
} else {
$fp=fopen('koral.db.lock','a+');
while (!flock($fp,LOCK_EX)){};
}
$result = sqlite_query($this->handle, $sql);
flock($fp,LOCK_UN);
fclose($fp);
Please tell me if that is what you meant. My database file is
koral.db. This is a part of a class file i use to run the queries
$this->handle is the handle returned by
sqlite_open('koral.db',0666,$sqliteerror). koral.db.lock is empty i
don't write or read from it just check if it's locked or not and
according to that determine what i can do. $sql is the query that will
run and i check if it's first letter is S from SELECT and if it is i
do a shared lock if not Exclusive lock.
Thank you for the help.
On Tue, 23 Nov 2004 09:15:22 -0700 (MST), Ara.T.Howard
<[EMAIL PROTECTED]> wrote:
>
>
> 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
> ===============================================================================
>
