After inserting a row whose primary key was produced by the database's generator, how do you reliably obtain the value assigned to your row when many sessions insert concurrently?
answer
- RETURNING / OUTPUT / getGeneratedKeys
- currval = my session only
- same physical connection under pooling
- allocate first for object graphs
- never MAX(id), never the generator's global state
basics
~20 sGet it from the insert itself — a RETURNING/OUTPUT clause or the driver's generated-keys API — or from the session-scoped last-value call for your own session. Never query MAX(id) or read shared sequence state: those see other sessions' rows.
solid answer
~60 sThree safe options, in order of preference. 1. **Ask the insert.** Have the statement return the generated column (`RETURNING`, `OUTPUT`, or the driver's generated-keys facility). One round trip, no ambiguity, works for multi-row inserts. 2. **Read the session-scoped last value.** The `currval`-style call returns the value *your session* most recently obtained from that generator, so concurrent sessions cannot interfere. It is session-local state, so it errors if this session has not used the generator, and connection pooling means you must read it on the same physical connection as the insert. 3. **Allocate first.** Take the value from the sequence explicitly, use it to build parent and child rows in memory, then insert with the key already known. This is the cleanest option for object graphs and for writing the id into an outbox message. Unsafe: `SELECT MAX(id)` and reading the sequence's global state. Both observe other sessions' allocations and are a race even inside a transaction, because sequence allocation is not transactional and other sessions' rows can appear between statements.
code
sql · 6 linesINSERT INTO orders (customer_id, total)
VALUES (42, 99.50)
RETURNING id;
-- or allocate first, then insert parent and children with a known key
SELECT NEXT VALUE FOR order_id_seq AS new_id;go deeper
Know that you return the key from the insert (RETURNING/OUTPUT or the driver's generated-keys API) and that MAX(id) is wrong.
Explain the session scoping of currval, the connection-pool caveat, and pre-allocating the value for parent/child inserts.
Cover batch inserts, ORM key-generation strategies that pre-allocate blocks, and why the number is yours before commit even though the row is not visible.
Treat identifier acquisition as an API design point: who owns the id, whether it is needed before persistence (outbox, external calls), and the round-trip cost of each strategy at scale.
## Why this is a real question The generated key is chosen by the server, but the application needs it — to insert child rows, to return a resource id to a caller, to publish an event. Under concurrency the naive ways of finding it are wrong, and they are wrong intermittently, which makes them survive review and testing. ## The safe mechanisms **Return it from the insert.** The best answer: the statement that generated the value tells you what it generated. Standard SQL and every major engine offer some form (`RETURNING`, `OUTPUT`, `INSERT ... RETURNING`), and JDBC-style drivers expose it as generated keys. Properties: one round trip, correct for multi-row inserts (you get one value per row), and immune to pooling issues because nothing is stored in session state. **Session-scoped last value.** `currval`-style calls return the last value obtained *by this session* from a named generator. Because it is per-session, another session's concurrent `nextval` cannot corrupt your read. Caveats: it fails if your session has not called `nextval` in this session yet; it is per-generator, so you must name the right one; and with connection pooling or a framework that may hand your next statement to a different connection, you must guarantee the same physical connection. Vendor "last inserted id" functions are the same idea with the same session scoping — note some of them report only the *first* value of a multi-row insert. **Allocate before inserting.** Call the generator yourself, then insert with an explicit key. This inverts the problem: you never have to discover the id because you chose it. It shines when you must build a graph (order plus items) in one pass, or write the id into a message or a file before the transaction commits. It requires a standalone sequence, or an identity column declared in the form that accepts supplied values. ## The unsafe ones and exactly why **`SELECT MAX(id) FROM t`** — reads the whole table's state, including other sessions' committed rows. It can return an id that is not yours, and even under a serialisable isolation level it is answering the wrong question: the maximum id is not "the id I just used". It also invites a table or index scan on every insert. **Reading the sequence's global last value** — many engines expose the generator's current state. That value moves whenever any session allocates, so it is a shared counter, not your receipt. Reading it right after your insert may already reflect three other sessions. **Re-selecting by natural key** — sometimes proposed as "select the row I just inserted by its business fields". It works only if those fields are truly unique, costs a round trip and an index lookup, and fails outright when the business columns are not unique. ## Batch and framework concerns For multi-row inserts, prefer returning the generated column per row; "last inserted id" style functions differ across engines on whether they report the first or last value of a batch, and building on that difference is fragile. ORMs typically use generated-key retrieval or an explicit pre-allocation strategy; the pre-allocation strategies that grab a block of values per application instance trade guaranteed gaps for far fewer round trips, which is usually a good deal precisely because gaps are acceptable. ## Transaction visibility Your generated value is yours as soon as the insert executes, before commit — you may use it inside the transaction to insert children. Other sessions cannot see the row until you commit, but the *number* is already consumed globally, so nobody else will get it even if you roll back. ## The interview answer Name the returning-clause mechanism first, mention session-scoped `currval` and its connection-pool caveat, mention pre-allocation for object graphs, and explicitly reject `MAX(id)` with the reason (it observes other sessions and answers the wrong question). That sequence of points is the complete answer.
- Why can a session-scoped "last generated value" call break under connection pooling?The value lives in the state of a physical database connection. If the framework returns the connection to the pool between the insert and the read, or runs the two statements on different connections, the read either errors or reports a value from an unrelated insert. Keep both statements inside one transaction on one connection, or avoid the problem entirely by returning the key from the insert.
- Is SELECT MAX(id) safe if you run it inside the same transaction as the insert?No. Sequence allocation is not transactional, so other sessions keep allocating and committing rows regardless of your transaction, and depending on isolation level you may see them. Even when you cannot see them, MAX(id) answers "the largest id in the table", which is not "the id my insert consumed" — those coincide only by luck on an idle system.
saying these in an interview costs you the question
- Using SELECT MAX(id) after an insert
- Reading the sequence's shared last-value as if it were your receipt
- Assuming currval is global rather than session-scoped
- Ignoring that a pooled connection may change between statements
- Relying on a "last inserted id" function for a multi-row insert without checking whether it reports the first or last value