On Tue, 30 Nov 2004, Alexander Jordanov wrote:
I am 100% sure that my all my queries go trought that class. Unfortunatelly
it is too late to make such changes ('forces the user to specify whether the
transaction is write or read') - i have too many scripts to change.
make every single sql execution follow this logic
die 'nested lock' if global_recursion_flag
global_recursion_flag = true
ret = flock 'db.lock', LOCK_EX
die 'lock failed' unless ret
result = execute sql
ret = flock 'db.lock', LOCK_UN
die 'unlock failed' unless ret
global_recursion_flag = false
if all access is through this one class you CANNOT experience a locked
database using this logic. if you are then:
0) some other process is locking the database
1) there is some database access NOT though this class
2) your app is doing some weird thread things fcntl based locks are process
based so all thread 'get the lock'
About NFS i asked the hosting and they said they dont use NFS.
good.
And can the lack of threadsafe lead to such behaviour? (i suppose it can...)
i don't know - but seriously doubt it unless you're access is multithreaded.
is it?
I must say also that the database seems to get locked during a select query
because i can read normally from the database but can't write in it.
then some other process holds the lock.
i can't get the database to lock (exept when the script times out and i only
noticed that on a linux machine and i can't confirm that it also applies to
the server - probably will test that tommorow but even if it applies i have
put one hour timeout and my scripts are not that heavy i have 2 places if i
recall correctly where i use recursion and for the first i am 100% sure it
is ok and the second has been used for -4-5 months without problems and i
haven't changed anything on it)
this smells fishy. obviously SOMETHING changed right? recursion and
locking
sounds like a great thing NOT to do! another thing you might try removing your
database - seriously, if you coordinated ALL access through something like
ret = flock 'db.lock', LOCK_EX
mv '.db', 'db'
result = execute sql
mv 'db', '.db'
ret = flock 'db.lock', LOCK_UN
i would expect that the offending code (the secret locker you cannot determine
with lsof or fuser) would begin to blow up. does that make sense? what i am
saying is that if database never exists unless the lock is held then code that
is not respecting the lockfile will fail because the database will not be there
(tiny race condition i realize but this would certainly make offending code
fail).
Can a query get the database locked? because i can't imagine a query that
can do that...
i don't think so.
And what can lock a database?
probably nothing but your own code ;-(
I tried not to commit a transaction but the database didn't lock... any
ideas?
something is very, very wrong then:
harp:~ > rm db; sqlite db 'create table foo(bar)'
harp:~ > strace sqlite db 'begin;end;' 2>&1 | grep -i lck
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7b30) = 0
fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7b40) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7b30) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7b40) = 0
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76b0) = 0
fcntl64(3, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7710) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76b0) = 0
fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0},
0xbfff7710) = 0
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76f0) = 0
fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76c0) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76f0) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff76c0) = 0
harp:~ > strace sqlite db 'begin; insert into foo values(42); end;' 2>&1 |
grep -i lck
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ed0) = 0
fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ee0) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ed0) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ee0) = 0
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a50) = 0
fcntl64(3, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ab0) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a50) = 0
fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9ab0) = 0
fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a90) = 0
fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a60) = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a90) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfff9a60) = 0
-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
===============================================================================
