Ingres 10 MVCC, foreign key constraints and locking

[john_howell]

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?

[rhann]

Can anybody think of a situation requiring the exclusive lock on the parent table?

I've been thinking about this overnight and nothing much is coming to me. Avoiding deadlocks is about all I've come up with, and I've not thought that through yet. Or maybe there's some benefit to avoiding a thundering herd when a shared lock is released?

[daryl.monge]

As I recall, the issue with referential integrity is that it cannot wait to take place at a later time. The verification of the reference has to be immediate, even in an MVCC installation. What I find slightly unusual is that it has an exclusive vs. shared (read) lock. Perhaps the underlying non-MVCC locking is defaulting to exclusive?

[john_howell]

The DDS specifies an exclusive lock. Maybe MVCC can only do exclusive row locks...? As stated, I really couldn't find any way to get shared locks in MVCC.

BTW, the RI verification takes place using rules and procedures that execute at the end of the statement. The underlying rules and procedures are quite nice in that they accumulate values to be checked and do an outer join to the parent for the accumulated rows so the check is done at once rather than row by row. Also the QEPs are calculated when the procedure is compiled so we don't get the slow QEP calculation that Ingres is prone to for outer joins.
Anyway, it's pretty much ruled out Ingres MVCC for our application as far as the customer is concerned. I like its application except for this little point. I particularly like that you can't update a row that was updated since the beginning of the transaction - "a leading database" (unquote) uncomplainingly lets you update rows that are no longer the same as at the beginning of the transaction.
We will probably have to trace the SQL and tune the concurrency manually using row locking. We prefer to use serializable isolation level since it's a complicated interrelated financial app. At least there's shared locking on the parent for row level locking...

[kschendel]

An an FYI, I believe that this is being changed to take a shared row lock during constraint checking.

[jahowell]

That's great news. Thanks. I am tracking it through my support call.