Key Generation & Timing

A **store-assigned key** is a value the database produces at the moment the row is written: the column is declared so that the engine supplies a number when the insert does not name one. The application never chooses it. That single fact drives everything else here — a value the store creates cannot exist in memory before the store has been asked to create it. ## Why the object sits around with an empty key Most mapping layers do not send an insert the instant you hand them a new object. The object joins the **unit of work** — the set of changes the layer is accumulating — as a pending insert, and the statement is deferred to the next **flush**: an explicit call, an automatic flush before a query whose results the pending row could change, or the flush that runs just before commit. Between registration and flush the object is perfectly usable in memory and its key field holds nothing: a null reference, or a sentinel such as zero for a numeric field that cannot be null. ## The sequence, step by step 1. Your code constructs the object. The key field is empty. 2. You register it with the layer. It is recorded as new, but it is *not* filed in the **identity map** — the layer's one-instance-per-row index — because that index is keyed by identifier and there is no identifier yet. A separate set of new objects holds it, keyed by object reference. 3. The layer flushes. The insert is emitted without naming the key column, so the engine supplies the value. 4. The layer reads the value back. Some engines hand it back as part of the same statement; otherwise the connection exposes the value the statement just generated. Either way the read-back is *per statement*. 5. The layer writes the value into the key field and files the object in the identity map under it. Only after step 5 does the object's `key` mean anything to you, to a log line, or to another system. ## What makes the write happen sooner than the layer wanted - **Reading the identifier at all** — the layer must produce a real value, so it flushes that row now. - **Returning a location or an identifier to a caller** before the transaction commits. - **Storing a link as a key value** rather than as an object reference: the child row cannot be written until the parent's key exists. - **Publishing a message, naming a file, or writing an external artefact** after the row. - **Putting the object into a hash-based collection** whose hash reads the key, which forces the write just to get a stable value. Each of those converts a deferred write into a statement sent right now. This is why the timing of the key quietly shapes the whole write path, and — as a consequence only — why writes that could otherwise have travelled together stop travelling together. ## Three timings, compared | when the key is known | what produces it | extra round trip | usable at construction | |---|---|---|---| | at insert | the store, while writing the row | a read-back per statement | no | | ahead of time | a store-side counter, drawn in blocks | one per block, amortised | yes | | at construction | the application computes it | none | yes | The first row is the subject of this question; the other two exist precisely because the first one makes an early identifier expensive. ## The shortcut that is not one A recurring mistake is to write the row and then read the key with a query for the largest value in the key column. It is wrong for a reason that testing rarely exposes: another session inserting at the same time advances the same counter, so the largest value may belong to someone else's row. The value must come from the statement that produced it, through the channel the layer already uses, not from a second query. ## Where layers differ Data-access layers differ here, and it is worth knowing which kind you are in. Some flush automatically before any query whose results a pending row could change, so by the time you look the insert has usually already happened; others write only when told, and the same code sees an empty key for much longer. Some always read the generated value back; others let you skip the read-back deliberately when you do not need the key. Do not carry one layer's timing into your reasoning about another. ## What to take away - The empty key is not a bug; it is the honest state of an object whose row does not exist yet. - The read-back is the layer's only way to learn the value, and it happens per insert. - Any requirement for the identifier before the write is a requirement to change *when* the key is produced, not a requirement to read harder.