#1684 FK constrain violation when parent record locked in another transaction (MVCC mode)

[Mindaugas Plukas]

Scenario:
Database in MVCC mode.
TABLE_A (ID, COLV) and TABLE_B (ID, A_ID). TABLE_B has foreign key constraint: A_ID references TABLE_A (ID) (on delete cascade, on update cascade) .

1. Transaction 1 starts
2. Transaction 1 executes: update TABLE_A SET COLV = 'some val' where ID = '1'
3. Transaction 1 stays in progress (TABLE_A row with ID=1 is locked)
4. Transaction 2 starts
5. Transaction 2 executes: insert into TABLE_B (ID , A_ID) values ('22', '1'); an error "integrity constraint violation: foreign key no parent" is received immediately.

Expected behaviour is that step 5 succeeds immediately. (Semantically acceptable behaviour would be if step 5 had been blocked until transaction 1 commits or rollbacks and then succeeds, but it seems that for MVCC mode blocking can and should be avoided) .

Note: If database is in LOCKS mode, then step 5 blocks until transaction 1 commits/rollbacks and then succeeds.

May be related to the issue [https://sourceforge.net/p/hsqldb/bugs/1627/]

[Fred Toussi]

Thanks for reporting. Fixed and committed to SVN for version 2.7.3.