Please, reply to the list as well, as any information may be useful to others.
Inline... On Tue, 23 Nov 2004, Alexander Jordanov wrote: >First thank you for the fast reply >Well i already have such a log that logs all the queries that are run >by my php scripts and there was never a BEGIN TRANSACTION or COMMIT >but you might be right for the activity because Saturday and Sunday i >was constantly monitoring if the database is locked by running an >UPDATE query about every hour and it didn't lock at all so i suppose >i'll run a cron to run me one update query and will see if it helps >Unfortunatelly the scripts are not locally (i have a local copy but >never managed to lock it) I wonder if the PHP backend is locking it? Not an area of expertise for me, unfortunately. >What is lsof? lsof means "list open files". It's a free utility, shipped with most Linux distros, and available for most UNIX platforms from source. Get it from: ftp://lsof.itap.purdue.edu/pub/tools/unix/lsof Typical output for a quiescent sqlite shell (interesting files only listed below): [EMAIL PROTECTED] csmith]$ lsof | grep sqlite ... sqlite 29546 csmith 3u REG 0,13 3072 2667735 /home/csmith/tmp/db (netapp:/vol/home/csmith) sqlite 29546 csmith 4u REG 8,6 0 115375 /var/tmp/sqlite_RLChwRJBRKUaXLK (deleted) File 3 in this case is the main database file. File 4 is a temporary database file, used, I think, for intermediate results. Creating a transaction in the shell: [EMAIL PROTECTED] csmith]$ lsof | grep sqlite ... sqlite 29546 csmith 3uW REG 0,13 3072 2667735 /home/csmith/tmp/db (netapp:/vol/home/csmith) sqlite 29546 csmith 4uW REG 8,6 0 115375 /var/tmp/sqlite_RLChwRJBRKUaXLK (deleted) sqlite 29546 csmith 5u REG 0,13 20 2667736 /home/csmith/tmp/db-journal (netapp:/vol/home/csmith) sqlite 29546 csmith 7u REG 8,6 20 115380 /var/tmp/sqlite_RLChwRJBRKUaXLK-journal (deleted) In the second listing, notice that file descriptors 3 and 4 have uW flags. This means the files are open for update, and both have write locks on the whole file (as a result of the open transaction.) Files 5 and 7 are the rollback journals for the main and temporary databases respectively. >Unfortunatelly I have no choise about how to unlock (at least until >somebody tells me a better way to unlock it) because this site is the >official site of the company I work in and we are shipping a new >product these days and a lot of people will be visiting the site so it >has to be running. You really need a test server to debug on! >What do you mean by "the copy is not atomic"? Copying the file is not done in a single action relative to everything else. The file can be updated while it is being copied, with the resulting copy not being a consistent file. Consider: Copy Process Update Process ------------ -------------- Copy block 0 Copy block 1 Update block 1 Update block 2 Copy block 2 ... At the end of the copy, the copy has the updated block 2, but not the updated block 1. Corrupt database. >Since the database has been locked for several hours and there is no >script writing to it i thought it is safe enought (and that's the >reason i keep a backup copy - if the copy becomes corrupted somehow) >Thank you again i'll see if the constant activity will solve the things. Christian > >On Tue, 23 Nov 2004 15:18:42 +0000 (GMT), Christian Smith ><[EMAIL PROTECTED]> wrote: >> My guess is that you have a script that opens a transaction, waits for >> something to happen, updates the database, then commits the changes. >> >> This may be why you are seeing the locking at night when there is little >> activity. Something may be beginning a transaction (thus locking the >> database) and waiting patiently for some activity. During the day, the >> constant activity ensures that there is little to no waiting between >> opening a transaction and an update. >> >> I'd check your scripts. Log to a file when you create a transaction, then >> log when you update the database and commit the transaction. >> >> You may be able to check the lock condition of the file using lsof if all >> the scripts run locally. This will at least point you at the offending >> script if any. >> >> Christian >> >> PS. I'd recommend NOT unlocking the file in the manner you describe! You >> can corrupt the database, as the copy is not atomic. >> >> >> >> >> 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 >> > >> >> -- >> /"\ >> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL >> X - AGAINST MS ATTACHMENTS >> / \ >> > -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
