Re: PostgreSQL not ACID compliant?

Hannu,

----- Original Message -----
From: "Hannu Krosing" <hannu(at)tm(dot)ee>
To: "Heikki Tuuri" <Heikki(dot)Tuuri(at)innodb(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Monday, September 22, 2003 10:00 PM
Subject: Re: [HACKERS] 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 ?

in InnoDB, all tables have a clustered index, where rows are stored.
Normally, it is the primary key of the table. All searches to the table go
through indexes, even table scans.

> > 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 ?

It works for all queries. An AVG(), for example, does a table scan through
the clustered index, and consequently locks the whole table, if you use the
SERIALIZABLE isolation level.

> > 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 ;(

No, not at all. If you want SERIALIZABLE execution, you can do the
following:

1) Report transactions, like AVG(), are often read-only, and you can use the
default multiversioning concurrency control method of InnoDB to perform them
without setting any locks. The database simply reads a snapshot of the
database, and that snapshot is the serialization point of the read-only
transaction.

2) Update transactions are usually smaller, they will not scan whole tables.
For them you can use the SERIALIZABLE isolation level and next key locks.
The serialization point of the transaction is the COMMIT time of the
transaction.

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

Yes.

> ------------------
> Hannu

Best regards,

Heikki