Re: [sqlite] force unlocking?

[Ara.T.Howard]

On Mon, 2 Aug 2004, Tito Ciuro wrote:
Hello Ara,
On 2 ago 2004, at 9:59, Ara.T.Howard wrote:
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'.
A word of caution to Mac OS X SQLiters. The following statement applies to
Jaguar (Mac OS X 10.2) and earlier. I'm not sure it it's still the case with
Panther (10.3) and Tiger (10.4) though:
Creating a file is not an atomic operation and may produce a race condition
if two or more processes attempt to create such lock file at the same time,
on the same place. The solution in this case is to create a directory
instead. Using this route, there won't be race conditions.
There is a small performance hit, but considering that a file lock may cause
you trouble, I would think that it's better to bite the bullet and go for
the safest solution.
Again, I'm not sure this applies to Panther or Tiger.
Regards,
-- Tito
hello tito-
i'm speaking of using a pre-existing file and appying an fcntl based lock to
this file, not creating a lockfile, so it does not matter that the creation is
not atomic, for example:
  ~ > sqlite db 'create table foo(bar)'
  ~ > touch lock
now in code you will do either
  blocking request for fcntl read lock on 'lock'
    access db in usual way
  release fcntl lock
or
  blocking fcntl request for write lock on 'lock'
    access db in usual way
  release fcntl lock
the lock requests must use the F_SETLKW flag - so they are blocking
procedures.  if all processes accessing the db using this protocol then you
can be guarenteed to never get a 'database is locked' error because you'll
only access the database (which applies it's own fcntl locks to the db in
non-blocking fashion) after getting a valid lock.  it's critical to use fcntl
based locks because only they work on NFS and it's critical to use another
file because a blocking lock applied to the db would be superceeded by the
first fcntl lock the sqlite api requested.
regarding atomic creation of lockfiles: this can be done using link(2).   i
have a library and command line tool for doing so, but have never tested it on
a mac.  it can be found at
  http://raa.ruby-lang.org/project/lockfile/
cheers.
-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
===============================================================================