Locking during DMLs on tables with referential integrity constraints

When a transaction inserts a row in a table with a foreign key constraint, it performs a scan at isolation level 2 on the table with the primary key constraint (which is referenced by the table with the foriegn key constraint). Adaptive Server performs this scan so the row is not updated or deleted until the transaction commits. Any updates and deletes on the table being scaned block on the referenced key of the row being inserted but is not yet committed.

Similarly, when a transaction attempts to delete a row from a table with a primary key constraint, it performs a scan at isolation level 3 on tables with foreign key constraints that reference this table. Adaptive Server does not allow a row insert into these tables until the transaction deleting the row commits.