Re: PostgreSQL not ACID compliant?

Heikki Tuuri kirjutas P, 21.09.2003 kell 12:51:
> Tom,
>
> ----- Original Message -----
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: "Heikki Tuuri" <Heikki(dot)Tuuri(at)innodb(dot)com>
> Cc: <pgsql-hackers(at)postgresql(dot)org>
> Sent: Sunday, September 21, 2003 10:32 AM
> Subject: Re: [HACKERS] PostgreSQL not ACID compliant?
>
>
> > "Heikki Tuuri" <Heikki(dot)Tuuri(at)innodb(dot)com> writes:
> > > if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB,
> > > then InnoDB uses next-key locking in every SELECT, and transactions
> really
> > > are serializable in the mathematical sense.
> >
> > My understanding is that next-key locking only helps when all your
> > predicates are point or range searches against an available b-tree
> > index.
>
> all SQL queries are performed through index searches.

Does that mean that an index is allways needed for predicate column, or
is this an abstract "index" in some realational algebra sense ?

> That is why next-key
> locking enforces serializability. IBM researcher C. Mohan has written papers
> about next-key locking. Next-key locking is an approximation of predicate
> locking. We simply lock more to make sure the 'predicates' themselves are
> locked.
>
> > While that might cover many practical cases, it can hardly
> > be called a complete solution to the problem of serializability.
>
> It is a complete solution.

Is this solution complete only for MAX() case, or is this a general
solution woking for things line AVG or STDDEV and perhaps all
user-defined aggregates as well ?

> Another approximation of predicate locking is
> table level locking, a solution which Oracle used some 15 years ago, if you
> switched it on the SERIALIZABLE isolation level.

Table level locking seems to be a complete solution indeed, just not
concurrent at all. It may be that we have to forget concurrency to get
complete and general concurrency ;(

Or is "next key locking" something more than a solution for getting
continuous nextval() 's ?

------------------
Hannu