sqlite file format, anyone?

[Christopher Browne]

Matthew Vanecek wrote
> On Mon, 2003-06-23 at 19:25, Derek Neighbors wrote:
> > Matthew Vanecek said:
> > > You lock a table in order to perform updates on the table, in the
> > > absence of row or page level locks.  Standard DBMS practice.  Not a
> > > design flaw--it's a feature, to prevent corruption of data.  Lock
> > > tables, perform updates, unlock tables.  Anyone else will get a
> > > contention error, and either retry their transaction or do other error
> > > handling.
> > 
> > Well in my world locking a table causes grief beyond belief.  Page locks
> > are ok, row locks preferred.  I think it is a design flaw to lock a table
> > to update a row.  Thus I think in general MySQL as a choice is a design
> > flaw, but I wanted to avoid that debate.  (See Mr. Browne's email earlier
> > as I share his opinion on MySQL)
> > 
> > Please read I never said it was silly to 'lock' records, only silly to
> > 'lock' tables.  There is a very significant difference.
> 
> Indeed, and point well made.  However, if a DBMS only offers
> table-level locking, then that's pretty much what you're stuck with.
> MySQL has table locks, according to the book, but I don't know about
> row level locking.  Postgresql has the "SELECT FOR UPDATE" command
> that can be used for explicit row locking.  The current PG back end
> uses table locks--I want to replace those with row locks.  That will,
> of course, be something that is pretty far removed from the
> engine->back end and middle-ware layers.

PostgreSQL supports MultiVersion Concurrency Control, which eliminates
most of the typical needs for locking.

http://www.cs.umb.edu/~serl/odiedu/alllabs.htm
http://www.linuxgazette.com/issue68/mitchell.html
http://wombat.doc.ic.ac.uk/foldoc/foldoc.cgi?MVCC

This is something that is increasingly being supported by DB makers;
such notables as DB/2, Oracle, and Firebird also support something
similar.

The basic idea is that queries hold on to the records that they are
using.  If you modify data, this leads to making a fresh copy; the old
copy remains, but can be discarded once the queries that were working
with the old data are done.  No lock is needed because the multiple uses
can coexist as long as they are not actually updating the same data.

  "Any sufficiently complicated C or Fortran program contains an ad hoc
  informally-specified bug-ridden slow implementation of half of Common
  Lisp."  -- Philip Greenspun

A major  problem with creating some new "embedded DBMS" is that by the
time you get finished dealing with the concurrency challenges, you'll
probably have gone off and recreated an ad hoc, informally-specified,
bug-ridden slow implementation of about half of PostgreSQL.  And what is
even more unfortunate is that the resulting code will probably be
_bigger_ than PG...
--
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www.ntlug.org/~cbbrowne/finances.html
It is  in the tranquillity of  decomposition that I  remember the long
confused emotion which was my life.