Application Transactions

A transaction involving multiple statements generally requires a single checked-out connection so all statements execute in the same transaction. Calling pool.query separately for each statement does not guarantee that the same connection is used. Acquire a client, issue BEGIN, perform statements, COMMIT on success, ROLLBACK on failure, and release the client in a finally block.

Avoid making network calls to unrelated services while holding a database transaction open unless the design explicitly requires it. Long transactions hold resources and can worsen contention. For distributed workflows, consider idempotency and patterns such as an outbox rather than assuming a database transaction can atomically commit a remote API request.

Transaction structure

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query(
    "UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2",
    [quantity, productId]
  );
  await client.query("COMMIT");
} catch (error) {
  await client.query("ROLLBACK");
  throw error;
} finally {
  client.release();
}

💡 All statements use the same client. Production code should validate the requested quantity, verify affected row counts, handle rollback errors appropriately, and ensure the operation is idempotent where needed.

Key Takeaways

Use one checked-out client for a multi-statement transaction.
Rollback on failure and always release the client.
Keep transactions short and avoid treating remote calls as part of a local DB transaction.

Knowledge Check

Why should a transaction use the same checked-out client for all statements?

Application Transactions

STAY UPDATED WITH BYTES!