Hello,
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 (.., ..,
..)'
My interpretation is that the acquisition of a lock on the row in
question is due to the enforcement of the foreign key constraint, and
that, combined with the fact that this locking is performed on a
per-row level, this creates a conflict with any concurrent transaction
updating that row in othertable, regardless of whether 'otherid' is
touched.
First off, is this correct?
If yes:
To me, it would be advantegous if "bogus" conflicts were not generated
like this. Although I realize that serializable transactions are
subject to retries, one still tends to design transactions
specifically to avoid generating conflicts. It is useful to know that
a particular transaction is guaranteed to not generate serialization
failures. And if that is not possible, than at least minimizing the
risk is useful. Normally, an INSERT is conflict-free and it would be
nice to keep it that way.
Unfortunately, the introduction of enforced referential integrity has
this negative side effect.
If my interpretation above is correct; is the use of row-level locking
due to:
(1) it being mandated by a standard?
(2) "cell"-level beinginefficient?
(3) no one having implemented "cell"-level locking?
(4) there being a problem with having a DELETE correctly
conflict with a "cell"-level lock?
(*) something else?
In short, I am wondering whether this behavior is intended or a
side-efffect of implementation details.
--
/ Peter Schuller
PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org
