When it comes to FOREIGN KEY constraints, I think that we may first need MDEV-22361. Implementing deferred constraint checks inside InnoDB only would not seem too useful, because that code is not compatible with other features (MySQL Bug #11472 Triggers not executed following foreign key updates/deletes).

I have a vague idea how to implement deferred constraints: Before COMMIT or XA PREPARE, traverse the log of changed rows that might violate some constraints, and perform the checks. Inside InnoDB, we actually already have such a log: the undo log of the transaction. But InnoDB itself does not know anything about CHECK constraints, and it would probably be expensive to iterate each undo log record if violations are rare.

It could be better to maintain a shorter ‘deferred check’ list that would be appended to during the execution of the transaction whenever some constraint is failing during the initial row operation. The storage engine would simply say ‘this operation needs to be checked and may lead to rollback’ and allow the ‘transient violation’.