RE: Revisited: Transactions, insert unique.

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?

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

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?

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.

> P.S. Note that the default isolation level of PostgreSQL is READ
> COMMITTED.

I see. This is another important point to keep in mind. Thanks for
pointing this out (another minor SQL92 non-compliance :-)

Joachim

--
private:  joachim@kraut.bc.ca    (http://www.kraut.bc.ca)
work:     joachim@mercury.bc.ca  (http://www.mercury.bc.ca)