ORM pitfalls

Inspect the SQL and transaction settings your ORM actually uses. The examples below demonstrate database behavior, not the defaults or APIs of any ORM.

Pitfall #1: the transaction that outlives the query

An application can update a row and then stop issuing queries while its transaction remains open. The example sets a 500ms idle timeout, observes the idle session, waits 1500ms, and checks termination and rollback:

A opens a transaction and updates a row. A sleep models idle client time; no ORM or external API is executed.

A> SET idle_in_transaction_session_timeout = '500ms';
SET

A> BEGIN;
BEGIN

A> UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE 1

From the outside this is the classic pathology — a session holding locks while doing nothing:

M> SELECT state FROM pg_stat_activity WHERE application_name = 'A';
        state        
---------------------
 idle in transaction 
(1 row)

The modeled idle time exceeds the configured timeout. The next queries check session absence and client failure; no server log is captured.

M> SELECT state FROM pg_stat_activity WHERE application_name = 'A'; -- the backend is gone
(0 rows)

The app finds out only when it finally comes back to commit:

A> COMMIT;
ERROR:  ERR_POSTGRES_CONNECTION_CLOSED: Connection closed

M> SELECT status FROM orders WHERE id = 1; -- the UPDATE was rolled back with the killed session
 status  
---------
 pending 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

No external API call runs here; the sleep models idle client time. PostgreSQL 18's idle timeout contract terminates a session idle in an open transaction beyond the configured interval. The Bun client observes a closed connection on COMMIT; the independent Python driver can report 25P03. The monitor asserts that the session has disappeared and the order remains pending. No server log is captured by this transcript.

This transaction holds the lock acquired by its UPDATE until it ends. Effects on DDL and reclamation depend on the lock modes and snapshot or xid horizon involved. Not every open transaction or await pins every row version. Keep slow external work outside the transaction where the application rule permits it. Choose timeouts for your workload; the values in this demo are test settings, not measured production thresholds.

Pitfall #2: no transaction where you assumed one

A separate read followed by a literal-value update can reproduce the lost-update schedule, whether an ORM or handwritten SQL issues it. An explicit READ COMMITTED transaction alone does not repair that stale write. Verify the ORM's generated SQL and enable the appropriate repair: a relative UPDATE, a locking read in the same transaction, or a checked version predicate. No ORM feature or default is tested here.

Pitfall #3: trusting default isolation

PostgreSQL 18 normally defaults to READ COMMITTED, but the default setting can be changed by configuration or the client. Inspect the session and transaction level. If REPEATABLE READ or SERIALIZABLE is required, also handle 40001 recovery. Whether an ORM restarts application logic depends on that ORM's contract. The database examples here establish no universal ORM retry behavior.

Further reading

• PostgreSQL 18: Client settings
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.