Optimistic locking checks a version at write time and retries on conflict, while pessimistic locking holds a row lock with SELECT FOR UPDATE. How each works in Postgres, what they cost under contention, how to expose conflicts over HTTP, and a TypeScript example with both.

Two requests that read the same row and write it back will silently overwrite each other unless something stops them. Postgres gives you two ways to do that: lock the row while you work, or let everyone work and reject the write that arrives second.
Both are correct, and the difference is what each costs when requests collide. This guide compares them, shows the SQL, covers conflicts over HTTP and ends with a TypeScript service using both.
Pessimistic locking assumes a conflict is likely and takes the lock first. In Postgres that is SELECT ... FOR UPDATE inside a transaction, which locks the matching rows until the transaction commits or rolls back, as described in the explicit locking documentation. A second transaction that tries to lock or update the same row waits, then sees the committed result.
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- check stock in application code, then:
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;
The lock lives only as long as the transaction, so keep the transaction short and never wait for a user or a slow HTTP call while holding it. Locking several rows in different orders in different transactions is what causes deadlocks, which Postgres resolves by aborting one of them, so lock rows in a consistent order such as ascending ID.
Optimistic locking assumes conflicts are rare and checks at write time. The row carries a version column, the client or service reads it with the data, and the update only succeeds if the version has not changed:
UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;
If the statement updates zero rows, someone else wrote first. The caller reloads and retries, or reports the conflict to the user. Nothing is locked between the read and the write, so the pattern works across HTTP requests and over minutes, which a database lock cannot.
Postgres can also do this without a column: at REPEATABLE READ and SERIALIZABLE isolation, a transaction that updates a row changed by a concurrent transaction fails with a serialization error that the application must retry, per the transaction isolation documentation. The version column is more explicit and works at the default READ COMMITTED level.
Under low contention optimistic locking wins, since a conditional UPDATE is one statement with no lock wait. Under high contention it degrades, because many writers fail and retry, repeating the read and the work each time, while pessimistic writers queue and each succeeds once.
A third option covers the common counter case with neither: UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0 is atomic in one statement. Reach for explicit locking when the decision needs more than one statement.
Send the version to clients as an ETag, and require it back in If-Match on updates, which is what RFC 9110 defines it for. When the precondition fails, answer 412 Precondition Failed. Many APIs return 409 Conflict with the current version in the body instead, which is easier for clients to handle, and either is reasonable if you document it. A pessimistic endpoint needs none of this, since the client never sees a conflict, only a slightly slower response.
Encore is a backend framework with built-in infrastructure, and a service declares its Postgres database in code, so the SQL above runs the same way under encore run and on RDS or Cloud SQL in your AWS or GCP account. The service below uses an optimistic update for document edits and a row lock for stock.
Run in your terminal to get started locally.
-- shop/migrations/1_create_tables.up.sql
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
body TEXT NOT NULL,
version INT NOT NULL DEFAULT 1
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
stock INT NOT NULL CHECK (stock >= 0)
);
// shop/shop.ts
import { api, APIError } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";
const db = new SQLDatabase("shop", { migrations: "./migrations" });
interface EditParams {
id: number;
body: string;
version: number; // the version the client last read
}
// Optimistic: conditional update, zero rows means someone wrote first.
export const editDocument = api(
{ method: "PUT", path: "/documents/:id", expose: true },
async ({ id, body, version }: EditParams): Promise<{ version: number }> => {
const row = await db.queryRow<{ version: number }>`
UPDATE documents SET body = ${body}, version = version + 1
WHERE id = ${id} AND version = ${version}
RETURNING version`;
if (!row) throw APIError.aborted("document changed, reload and retry");
return { version: row.version };
},
);
// Pessimistic: lock the row, decide, update, all in one short transaction.
export const reserveStock = api(
{ method: "POST", path: "/products/:id/reserve", expose: true },
async ({ id }: { id: number }): Promise<{ remaining: number }> => {
await using tx = await db.begin();
const row = await tx.queryRow<{ stock: number }>`
SELECT stock FROM products WHERE id = ${id} FOR UPDATE`;
if (!row) throw APIError.notFound("no such product");
if (row.stock < 1) throw APIError.failedPrecondition("out of stock");
await tx.exec`UPDATE products SET stock = stock - 1 WHERE id = ${id}`;
await tx.commit();
return { remaining: row.stock - 1 };
},
);
APIError.aborted maps to HTTP 409 Conflict in Encore's API errors, so clients see a conflict they can retry. Each request appears as a trace with its queries, which makes lock waits visible as slow database spans.
A coding agent can add a version column and the matching conditional update in one change, run both endpoints concurrently against the local database, and let the deploy apply the migration.
Best fit: Teams building Postgres-backed APIs who want conflict handling written in the service and the database provisioned in their own AWS or GCP account.
Pessimistic locking takes a lock on the row before changing it, so other writers wait until the transaction ends. Optimistic locking takes no lock, reads a version number with the row, and makes the update conditional on that version still matching, so a conflicting writer finds zero updated rows and has to retry or fail.
Use it when conflicts are rare and the work between read and write is slow or crosses requests, for example a user editing a form for several minutes. A version column and a conditional UPDATE cost almost nothing when nobody collides, and nothing stays locked while the user thinks.
Use it when many writers hit the same rows and a failed retry is expensive, such as decrementing stock for a popular product or claiming the next job from a queue. SELECT FOR UPDATE inside a short transaction makes writers queue up instead of failing and retrying.
Yes. An Encore service with a Postgres database runs the same SQL under encore run and in your AWS or GCP account, so a conditional UPDATE or a SELECT FOR UPDATE inside db.begin() behaves the way it does on any Postgres. Encore provisions the database on deploy and starts Postgres locally for development.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.