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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
