A naukri.com initiative
Dev
5d
246
Image Credit: Dev
Referential integrity In The Absence Of Foreign Key
- Aurora DSQL does not support Foreign Keys. To prevent database corruption, it's essential to verify that the parent key exists and has not been concurrently deleted.
- While the application can see activities of concurrent transactions, it cannot perform due to transaction isolation. Here are four solutions to ensure referential integrity without foreign key.
- Lock the parent key when inserting a row in the child table, one way of achieving this is through SELECT FOR UPDATE which declares an exclusive lock intent that conflicts with a concurrent delete.
- It's possible to use SELECT FOR SHARE instead of SELECT FOR UPDATE to handle shared parent rows if multiple inserts for the same parent should be processed concurrently.
- Write skew is typically prevented by using a serializable isolation level. When not supported, SELECT FOR UPDATE can produce similar results.
- The Doc's On-Call Shift problem and a Normalized Relational Schema to Avoid Write Skew - Franck Pachot for Yugabyte Distributed PostgreSQL Database discusses how to transform write skew issues into repeatable read issues.
- When using SELECT FOR UPDATE to guarantee referential integrity, the application must handle unexpected conflicts and retry transactions without any business justification.
- In Aurora DSQL, referential integrity can be maintained without a foreign key by using row-locking techniques such as SELECT FOR UPDATE.
- Using SELECT FOR UPDATE might reduce the scalability of the database for which one solution is to use SELECT FOR SHARE that allows concurrent transactions if multiple inserts require the same parent.
- The success of row-collision prevention necessitates further data logic addition in business logic while avoiding race conditions that corrupt our database - Franck Pachot for Yugabyte Distributed PostgreSQL Database.
Read Full Article
14 Likes
For uninterrupted reading, download the app