Data Modeling hard
You are doing high-concurrency inserts into a `Child` table with a Foreign Key to `Parent`. Why might this cause deadlocks on the `Parent` table?
- A To verify the constraint, the database must acquire a Shared (Read) Lock on the Parent row; if multiple transactions then attempt to update that Parent simultaneously, deadlocks occur.
- B The database hardware is forced to acquire an exclusive lock on the entire Parent table for every single child insert operation to ensure that no data corruption can take place.
- C The presence of a Foreign Key automatically disables the database's ability to perform row-level locking, forcing the system to revert to a less efficient, page-level locking model.
- D Foreign Keys strictly limit the maximum total volume of the child table, and once this threshold is crossed, the database enters a global block state until old records are purged.
Answer: A. To verify the constraint, the database must acquire a Shared (Read) Lock on the Parent row; if multiple transactions then attempt to update that Parent simultaneously, deadlocks occur.
This is a common performance bottleneck in high-concurrency systems. Every `INSERT` into a table with a Foreign Key requires the database to verify that the parent exists, which effectively performs a `SELECT ... FOR SHARE` on the parent row. If many concurrent processes are inserting records for the same parent (and some are updating it), the conflicting Shared and Exclusive locks can easily lead to a deadlock cycle.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?