Ingres 10 MVCC, foreign key constraints and locking
We are evaluation Ingres 10 MVCC to replace an existing 9.2 installation and found that if there is a foreign key constraint on a table, when inserting into that (child) table, the relevant parent table row is exclusively locked. This would result in considerable concurrency problems. In an extreme case (not representative of ours though), if all tables in a database had an 'active' flag enforced using a foreign key to an 'active' table, inserts into such tables would exclusively lock the 'active = Yes' row and prevent any other activity against these tables for the duration of the transaction.
It also exhibits this behaviour when you are updating a column with a foreign key - the parent row is also exclusively locked. Depending on the coverage of the value in the column, this effectively locks out considerable amounts of the child table.
Anyway, this behaviour is documented in the MVCC DDS and its purported purpose is to prevent orphaned child records. My thoughts are that a shared lock instead of an exclusive lock against the parent record would prevent that child record from being altered and would give the same level of protection without the concurrency issues. I have been experimenting and trying to think of a situation where exclusive lock is required, but have failed so far. Can anybody think of a situation requiring the exclusive lock on the parent table?
I have thought up a workaround in that we extract the rules and procedures that Ingres creates to enforce the constraint, rename them and create them as regular rules and procedures, then drop the foreign key constraint. This works OK but does not lock the parent rows at all and risks orphaned records (just like readlock=nolock using non-MVCC locking) and complicates maintenance of the schema.
So is the exclusive lock warranted? Can anybody think of a reason why the parent table lock is exclusive? Can anybody think of a way to get a shared row lock in MVCC to help make the workaround more robust?