Why can the order your code touches objects differ from the order a deferring data-access layer actually acquires their row locks?
answer
- changes are buffered, not sent
- the flush picks its own statement order
- grouped for batching and dependencies
- locks land when statements run
- explicit lock requests execute where written
basics
~20 sBecause changes are buffered and sent at flush, and the layer orders those statements by its own rules rather than by the order your code made them. Locks are taken when statements run, so the flush order is the acquisition order.
solid answer
~50 sA unit of work accumulates changes in memory and emits them at flush, and it is free to reorder: grouping statements by table so they can be batched, running inserts before updates before deletes, and sorting by referential dependencies. Row write locks are taken when each statement executes, so the sequence the database sees is the flush's sequence, not your code's. Two paths that both 'touch A then B' can therefore acquire in opposite orders if their pending sets group differently, which quietly defeats an ordering convention written as the order of business calls. Explicit lock requests behave differently: they are reads issued immediately, so they do execute where you put them. That is what gives you control — claim the rows you need up front, in a fixed order, before any buffered work exists.
go deeper
Remember that changed objects are written at flush, not when you change them, so the database sees a different sequence from your code.
Explain why the layer reorders — batching by table, insert-update-delete phases, dependency sorting — and that a write lock is taken when its statement runs.
Demonstrate the fix in practice: claim the rows up front in a deterministic order with explicit lock requests, and read the statement log rather than the code to confirm it.
Name the trade you are making — earlier serialisation for deterministic acquisition — and set the codebase-wide rule that makes every path claim in the same order.
A convention like "always take the rows in a fixed order" is easy to write in application code and easy to believe. In a layer that defers writes, it can be untrue of the statements that actually run, and the gap between the two is worth understanding precisely. ## Why the layer reorders at all A unit of work does not send a statement each time you change an object. It records the change and sends everything at **flush** — before a query that might be affected, and at the boundary. When it flushes, it decides the statement order itself, for good reasons: - **Batching.** Statements of the same shape against the same table are grouped so they can be sent together; that grouping is worth far more than preserving call order. - **Operation phases.** Inserts, then updates, then deletes is a common ordering, so that rows exist before they are referenced and referencing rows are gone before their targets. - **Referential dependencies.** Where the mapping knows one row must exist before another, the layer sorts to satisfy that. None of these rules is "the order the application called the setters", and none of them is documented as a contract you may lean on. ## Where locks enter A write statement takes its row's write lock when it executes. Combine that with the above and the consequence is direct: **the acquisition order is the flush order.** Two more wrinkles make it less predictable still: 1. A plain read early in your code may take no lock at all under a versioned read scheme, so the object your code "touched first" might be locked last, or never. 2. The first lock in the boundary is often taken at a moment you did not choose — an automatic flush triggered by a query that happens to run before it. So a unit of work that reads A, reads B, changes both, and commits does not reliably lock A before B, and a second unit of work following the same written convention can arrive at the opposite order because its pending set groups differently. Each path is internally consistent and the pair is not. | What the code expresses | What the database sees | |---|---| | Order of business calls | Nothing; the calls only mutate memory | | Order of changes made to objects | Discarded in favour of the layer's grouping | | An explicit lock request | A statement executed at that point | | An explicit flush | Everything pending, in the layer's order, at that point | ## What restores control An explicit lock request is a **read**, not a buffered change, so it is issued when you make it. That is the lever: 1. At the top of the use case, before any changes accumulate, request locks on the rows the unit of work is going to write, in a deterministic order — sorted by identifier, or by a fixed rule every path in the codebase shares. 2. Then do the work. Whatever the flush later reorders, the write locks are already held, so the flush cannot introduce a new acquisition order. 3. Where locking up front is impractical, an explicit flush at a chosen point at least makes the moment of acquisition deliberate rather than incidental. The trade is real and worth saying out loud: locking up front holds the rows for the whole boundary and serialises earlier than strictly necessary. It buys determinism, and determinism is what an ordering convention needs to be worth anything. ## Related habits that pay off here - **Keep the write set small.** Fewer rows written per boundary means fewer chances for two paths to overlap in different orders, and it is the cheapest mitigation available. - **Do not spread one logical update across two boundaries** to "reduce lock time"; you get two acquisition orders instead of one and lose atomicity as well. - **Look at the statements, not the code**, when reasoning about acquisition. A statement log for the boundary is the only honest record of the order the database experienced. - **Write the convention where it is enforceable.** "Lock accounts in ascending identifier order" is a rule about explicit lock requests. As a rule about which service method is called first, it is not enforceable, because the calls do not reach the database in that order. The compact version, worth saying in an interview: **in a layer that defers writes, code order is not statement order, and locks follow statement order.**
- How do you make an ordering convention hold in a layer that defers writes?Express it as explicit lock requests, not as the order of business calls. Claim every row the boundary will write at the start, in a deterministic order such as ascending identifier, before any changes accumulate. The later flush can then reorder freely without changing which locks are already held.
- What does an automatic flush before a query do to this picture?It moves the moment of acquisition somewhere you did not choose. Pending changes are written — and their locks taken — because an unrelated query needed to see them, so the first write lock of the boundary can land in the middle of a read path. An explicit flush at a chosen point makes that timing deliberate.
- Why isn't 'read A before B' enough to fix the order?A plain read may take no lock at all, so the object your code touched first can be locked last or never. Only a locking read or the write itself acquires anything, which is why the convention has to be built from lock requests rather than from the sequence of ordinary loads.
saying these in an interview costs you the question
- Assumes statements reach the database in the order the code made the changes
- Writes a lock-ordering rule as the order of service calls
- Believes a plain read already takes the row's lock
- Thinks the first lock of a boundary is always taken where the code says
- Splits one update across two boundaries to shorten lock time