RE: Revisited: Transactions, insert unique.

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Joachim Achtzehnter
>
> In a message to pgsql-general, Hiroshi Inoue wrote:
> >
> > Strictly speaking SERIALIZABLE isolation level of PosgreSQL isn't
> > SERIALIZABLE.
>
> Thanks for clarifying this.
>
> > It's same as Oracle.
>
> So, even the latest versions of Oracle still have this problem?
>

I don't know recent versions of Oracle.
However truly SERIALIZABLE isolation level seems very hard to
implement if read-only queries acquire no lock.

> > AFAIK,there's no way to block (logically) non-existent row(key) except
> > unique constraint.
>

Sorry,my above comment lacks the words *in current PostgreSQL*.

> A couple of comments:
>
> There is, of course, a way to achieve this with a concurrancy mechanism
> that is based on locking, rather than a multi-version system. Locking
> systems in serializable mode would use shared locks for reads and hold the
> locks until the end of the transaction. The trick with the non-existent
> rows is that the locks must be placed on the access path rather than just
> individual rows.
>
> For example, if the select query is executed using a hash index, it would
> place a shared lock on the hash bucket where the non-existing row would
> have been. If the second transaction does its select before the first one
> upgrades its lock to exclusive mode the two transactions will deadlock,
> otherwise the second transaction's select blocks. Either way, the problem
> is avoided.
>
> Clearly, if a table has no index the lock would have to be placed on the
> table because the access method is a full table scan.
>
> The 1,000,000 dollar question is whether there is some way to achieve the
> same with a multi version concurrancy system without giving up its
> advantages?
>

There might be some discussion about dirty read though I don't
remember well.  Unique constraint stuff has to read dirty data to
check uniqueness.  I don't know other code using *dirty read*
in current PostgreSQL. It's what I meant by "except unique
constraint".

> My second comment is about the remark "except unique constraint": It is
> true that a unique constraint would have stopped the incorrect second
> insert in the example under discussion. Furthermore, a unique constraint
> or primary key is definitely the way to go in such a case because
> performance would be really bad with large tables.

> However, a unique
> constraint does not prevent all serialization violations caused by
> non-existent row effects.
>

You are right. So Oracle 8.1.5's manual seems to refer FOR UPDATE,
Referential Integrity etc.  as to serializability.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp