Re: Foreign keys causing conflicts leading to serialization failures

Peter Schuller wrote:
> Using PostgreSQL 8.2, I have "atable" one of whose columns reference a
> column in "othertable". I see serialization failures as a result of
> *inserts* to atable in the context of:
>
> '"SELECT 1 FROM ONLY othertable x WHERE "otherid" = $1 FOR SHARE OF
> x" ' in 'INSERT INTO atable (otherid, col2, col3) VALUES (.., ..,
> ..)'

A SELECT ... FROM "othertable" ... FOR SHARE won't conflict with a
concurrent update on "atable".

Do I guess right that there was also an UPDATE on the row in
"othertable"?
You may have been misled by a message like:
ERROR: could not serialize access due to concurrent update
CONTEXT: SQL statement "SELECT 1 FROM ONLY "othertable" x WHERE
"otherid" = $1 FOR SHARE OF x"

This message will be displayed although the statement that causes
the conflict is actually the UPDATE, perhaps because this was the
first statement to acquire a lock on that row in this transaction.

What most likely happens is the following:

Serializable transaction 2 starts and SELECTs something.

Transaction 1 starts and gets a RowShareLock on a row of "othertable"
with the SELECT ... FOR SHARE

Transaction 1 UPDATEs the row and now holds a RowExclusiveLock
on the table row and the index row as well. The latter is necessary
because that not yet committed UPDATE has also changed the index
(even if the indexed columns did not change, the index will point
to a new row now).

Transaction 1 COMMITs. A new table row and a new index row are
visible.

Transaction 2 now issues an INSERT on "atable". This requires a
RowShareLock on the index row of the index on "othertable" that
is referenced by the foreign key constraint. But the corresponding
index row has changed since the transaction began, hence the error.

Transaction 2 needs the index entry to verify that the foreign
key constraint is satisfied. It cannot perform the check on the old
version of the index row as this might introduce inconsistencies.
Being serializable, it must not use the new index entry.

One could argue that, as long as the old index entry and the new
index entry have the same values, the transaction could safely proceed.

I guess it is for ease of implementation, design or performance reasons
that this is not done.

Your idea of "cell level locking" will probably not work with
PostgreSQL: since any change in a data row will cause a new row to be
created, there would be no advantage.

Yours,
Laurenz Albe