When a transaction inserts a row whose foreign key column references a parent row, what lock does the engine take on that referenced parent row, and why not simply an exclusive row lock?
answer
- snapshot read is not enough — current read + lock
- key-preserving shared lock (PG FOR KEY SHARE, InnoDB shared record lock)
- conflicts with parent DELETE / key UPDATE only
- exclusive lock would serialise all child inserts
- shared→exclusive upgrade = classic deadlock
basics
~20 sIt takes a shared, key-preserving lock on the parent row — enough to stop the parent being deleted or its key changed before commit, but weak enough that many children can be inserted concurrently. An exclusive lock would serialise every child insert under the same parent.
solid answer
~50 sThe child-side check must be durable against concurrency: between "parent exists" and commit, nobody may delete that parent or change its key, or the transaction would commit an orphan. A snapshot read cannot provide that, so the engine does a current read plus a lock on the parent row. The lock is deliberately weak and *key-preserving*: Postgres takes `FOR KEY SHARE`, InnoDB a shared record lock. It conflicts with parent DELETE and with UPDATEs to the referenced key columns, but not with other child inserts referencing the same parent, and in Postgres not with UPDATEs to non-key parent columns either. Using an exclusive lock instead would make every child insert under a hot parent (a popular product, a big tenant) serialise, destroying write throughput. The practical fallout: long transactions that insert children pin parent rows and block parent deletes; and mixed access orders — one transaction updating a parent's key while another inserts children — deadlock. Order writes consistently and keep child-inserting transactions short.
go deeper
Know that inserting a child locks the referenced parent row lightly so it cannot vanish before commit.
Name the lock mode as a shared, key-preserving lock and explain why an exclusive lock would serialise child inserts.
Explain why the snapshot read is insufficient, and diagnose the real symptoms: parent deletes blocked by long child transactions and shared-to-exclusive upgrade deadlocks.
Weigh fan-in and hot-parent lock-manager pressure in the write path, set transaction-scope and access-order conventions, and treat removing the constraint as an explicit correctness trade rather than a tuning knob.
## The concurrency hole the lock closes Enforcing a foreign key on child INSERT means asking "does a parent with this value exist?". Under snapshot isolation (MVCC) the naive implementation reads the parent through the transaction's snapshot and answers yes. But another transaction may already be deleting that parent and may commit first; the child transaction then commits an orphan and the invariant is broken with no error anywhere. So the constraint check cannot be an ordinary snapshot read. Engines perform a *current* read (latest committed version) and take a lock that survives to commit. ## What the lock must and must not do Requirements: - Block deletion of the referenced parent row until the child transaction ends. - Block updates that change the referenced key columns (a key change would make the child dangle just as a delete would). - **Not** block other transactions inserting different children under the same parent — otherwise every child write under a popular parent is serialised. - Ideally not block harmless updates to unrelated columns of the parent (renaming a customer while orders are being inserted). That is precisely a *key-preserving shared* lock. Postgres names it `FOR KEY SHARE`: it conflicts with `FOR UPDATE` and `FOR NO KEY UPDATE`-style key changes and with DELETE, but is compatible with other `FOR KEY SHARE` holders and with updates that leave key columns alone (which take the weaker `FOR NO KEY UPDATE`). InnoDB takes a shared record lock (conceptually `LOCK IN SHARE MODE`) on the matching parent index record; it is compatible with other shared holders, and conflicts with the exclusive lock a parent delete or key update needs. Oracle historically achieved the same effect with a short lock on the parent plus, in older releases, table-level locks on the child when the referencing columns were unindexed — the origin of much foreign-key lock folklore. An exclusive lock would be correct but catastrophic for throughput: inserting order lines under one popular product, or rows under one large tenant, would become a single-file queue. ## Consequences you will actually observe **Parent deletes block behind child writers.** A long-running transaction that inserted a child row holds the key-share lock until commit. Any attempt to delete that parent waits — sometimes appearing as an unexplained lock wait on a table nobody is writing. Fix by shortening transactions, not by weakening the constraint. **Deadlocks from shared-to-exclusive upgrades.** Two transactions each insert children of parent P (both hold shared/key-share on P). One then tries to update P's key or delete it, requiring exclusive access; it waits on the other's shared lock, and vice versa. Classic shared-lock upgrade deadlock. The remedy is consistent access ordering: touch the parent in its final mode first, or avoid mutating parents inside transactions that also insert children. **Hot-parent contention under high write rates.** Even a shared lock has cost: in InnoDB the locks are recorded per index record, and thousands of concurrent inserters under one parent inflate lock-manager work. Where that becomes the bottleneck, the structural answers are to reduce fan-in (avoid a single "global" parent row), or to accept the cost, not to silently drop the constraint. **Repeated inserts under the same parent are cheap after the first.** The lock is already held by the transaction, so a batch of children under one parent takes the lock once. ## Symmetry on the parent side The reverse check — deleting a parent — must likewise prevent a concurrent transaction from inserting a new child that the delete's search did not see. It takes locks on the child rows it examines (which, with no index on the referencing columns, can be far more rows than expected) and relies on the child inserters' key-share locks to serialise correctly. That interplay is why unindexed referencing columns show up as both a performance problem and a deadlock problem.
- Why can't the engine just check the parent using the transaction's MVCC snapshot?A snapshot read reports the state as of the transaction start, so it could see a parent that another transaction is concurrently deleting and will commit before you. The child would commit as an orphan with no error raised. Enforcement therefore uses a current read of the latest committed version plus a lock held to commit, so the parent cannot disappear underneath the check.
- You see deadlocks between two transactions that each insert child rows and also update the parent. What is happening and how do you fix it?Both transactions take a shared, key-preserving lock on the same parent when inserting children, then each tries to upgrade to exclusive access to update or delete the parent; each waits on the other's shared lock. Fix by making the access order consistent — take the parent in its strongest mode first, or move the parent mutation out of transactions that insert children — and by keeping those transactions short.
saying these in an interview costs you the question
- Saying a child insert takes an exclusive lock on the parent row
- Claiming the foreign key check is lock-free because MVCC readers never block
- Believing a child insert blocks other inserts under the same parent
- Thinking a long child-inserting transaction cannot block a parent DELETE
- Assuming updating any parent column conflicts with child inserts