sql / expert
Snippet
Transactional Consistency via Deferred Constraints
Deferred constraints allow for temporary violations of referential integrity within a transaction. This is critical for solving circular dependency deadlocks where two tables reference each other, allowing the final COMMIT to validate the state.
snippet.sql
1
2
3
4
5
6
SET CONSTRAINTS ALL DEFERRED;INSERT INTO teams (team_id, captain_id) VALUES (1, 10);INSERT INTO players (player_id, team_id) VALUES (10, 1);COMMIT;
Breakdown
1
SET CONSTRAINTS ALL DEFERRED;
Instructs the database to delay all constraint checking until the end of the current transaction.
2
COMMIT;
The point at which the database performs all deferred integrity checks. If any fail, the entire transaction is rolled back.