PostgreSQL Row Versioning: Optimistic Locking, Lost Update Prevention, and Version Columns

The lost update problem happens when two users edit the same record simultaneously: user A reads version 3, user B reads version 3, user A saves (version 4), user B saves (overwrites A's changes with stale data). Optimistic locking prevents this by including the version in the WHERE clause of the UPDATE — if the version changed between read and write, the update affects 0 rows and you can detect and handle the conflict.

Schema: Version Column Pattern

CREATE TABLE documents (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  title       TEXT NOT NULL,
  content     TEXT NOT NULL DEFAULT '',
  author_id   UUID NOT NULL REFERENCES users(id),
  version     INTEGER NOT NULL DEFAULT 1,  -- Increments on every update
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Trigger to auto-increment version on update
CREATE OR REPLACE FUNCTION increment_version()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.version   := OLD.version + 1;
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_documents_version
BEFORE UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION increment_version();

Optimistic Locking Query Pattern

-- Read: return document with current version
SELECT id, title, content, version
FROM documents
WHERE id = $1 AND workspace_id = $2;

-- Write: include version in WHERE clause
-- If version changed since read, UPDATE affects 0 rows
UPDATE documents
SET title   = $1,
    content = $2
    -- version and updated_at handled by trigger
WHERE id        = $3
  AND workspace_id = $4
  AND version   = $5;  -- ← The optimistic lock check

-- Rows affected = 0 → conflict detected
-- Rows affected = 1 → success

TypeScript Implementation

// lib/documents/update.ts
import { prisma } from "@/lib/prisma";

export class OptimisticLockError extends Error {
  constructor(
    public readonly resourceType: string,
    public readonly resourceId:   string,
    public readonly expectedVersion: number,
  ) {
    super(`${resourceType} ${resourceId} was modified by another user (expected version ${expectedVersion})`);
    this.name = "OptimisticLockError";
  }
}

export async function updateDocument(
  id:            string,
  workspaceId:   string,
  currentVersion: number,
  updates: {
    title?:   string;
    content?: string;
  }
): Promise<{ id: string; title: string; content: string; version: number }> {
  // Prisma doesn't natively support optimistic locking WHERE clauses,
  // so we use $executeRaw for the conditional update and a follow-up SELECT

  const result = await prisma.$executeRaw`
    UPDATE documents
    SET
      title   = COALESCE(${updates.title ?? null}::text,   title),
      content = COALESCE(${updates.content ?? null}::text, content)
    WHERE id           = ${id}::uuid
      AND workspace_id = ${workspaceId}::uuid
      AND version      = ${currentVersion}
  `;

  // 0 rows affected = version conflict (or document not found)
  if (result === 0) {
    // Distinguish "not found" from "version conflict"
    const doc = await prisma.document.findFirst({
      where: { id, workspaceId },
      select: { version: true },
    });

    if (!doc) throw new Error("Document not found");
    throw new OptimisticLockError("Document", id, currentVersion);
  }

  // Fetch the updated document
  return prisma.document.findUniqueOrThrow({
    where:  { id },
    select: { id: true, title: true, content: true, version: true },
  });
}

Server Action with Conflict Handling

// app/actions/documents.ts
"use server";

import { auth } from "@/auth";
import { updateDocument, OptimisticLockError } from "@/lib/documents/update";
import { revalidatePath } from "next/cache";

interface UpdateResult {
  success: boolean;
  error?:  "conflict" | "not_found" | "unauthorized" | "unknown";
  version?: number;   // On success, return new version to client
}

export async function saveDocument(
  documentId:     string,
  currentVersion: number,
  updates:        { title?: string; content?: string }
): Promise<UpdateResult> {
  const session = await auth();
  if (!session?.user) return { success: false, error: "unauthorized" };

  try {
    const updated = await updateDocument(
      documentId,
      session.user.workspaceId,
      currentVersion,
      updates
    );

    revalidatePath(`/documents/${documentId}`);
    return { success: true, version: updated.version };
  } catch (err) {
    if (err instanceof OptimisticLockError) {
      return { success: false, error: "conflict" };
    }
    if (err instanceof Error && err.message === "Document not found") {
      return { success: false, error: "not_found" };
    }
    console.error("Document save error:", err);
    return { success: false, error: "unknown" };
  }
}

Client-Side Conflict Resolution UI

// components/document-editor.tsx
"use client";

import { useState, useCallback, useTransition } from "react";
import { saveDocument } from "@/app/actions/documents";
import { AlertTriangle, RefreshCw } from "lucide-react";

interface DocumentEditorProps {
  document: { id: string; title: string; content: string; version: number };
}

export function DocumentEditor({ document: initial }: DocumentEditorProps) {
  const [title, setTitle]     = useState(initial.title);
  const [content, setContent] = useState(initial.content);
  const [version, setVersion] = useState(initial.version);
  const [conflict, setConflict] = useState(false);
  const [isPending, startTransition] = useTransition();

  const save = useCallback(() => {
    setConflict(false);
    startTransition(async () => {
      const result = await saveDocument(initial.id, version, { title, content });

      if (result.success && result.version) {
        setVersion(result.version);  // Update to new version from server
      } else if (result.error === "conflict") {
        setConflict(true);
      }
    });
  }, [initial.id, version, title, content]);

  return (
    <div className="space-y-4">
      {/* Conflict warning */}
      {conflict && (
        <div className="flex items-start gap-3 bg-amber-50 border border-amber-200 rounded-xl px-4 py-3">
          <AlertTriangle className="w-5 h-5 text-amber-500 flex-shrink-0 mt-0.5" />
          <div className="flex-1 text-sm">
            <p className="font-semibold text-amber-900">
              Conflict: this document was modified by someone else
            </p>
            <p className="text-amber-700 mt-1">
              Your changes were not saved. Reload to see the latest version, or continue editing to overwrite.
            </p>
            <div className="flex gap-2 mt-3">
              <button
                onClick={() => window.location.reload()}
                className="flex items-center gap-1.5 px-3 py-1.5 bg-white border border-amber-300 text-amber-800 text-xs font-semibold rounded-lg hover:bg-amber-50"
              >
                <RefreshCw className="w-3.5 h-3.5" />
                Reload latest
              </button>
              <button
                onClick={() => {
                  // Force save: update version to match server's current version
                  // by refetching the document first
                  setConflict(false);
                }}
                className="px-3 py-1.5 text-amber-800 text-xs font-semibold"
              >
                Dismiss
              </button>
            </div>
          </div>
        </div>
      )}

      <input
        value={title}
        onChange={(e) => setTitle(e.target.value)}
        className="w-full text-2xl font-bold border-0 outline-none bg-transparent"
        placeholder="Untitled"
      />

      <textarea
        value={content}
        onChange={(e) => setContent(e.target.value)}
        rows={20}
        className="w-full text-base border-0 outline-none bg-transparent resize-none font-mono text-sm"
        placeholder="Start writing…"
      />

      <div className="flex items-center justify-between">
        <span className="text-xs text-gray-400">Version {version}</span>
        <button
          onClick={save}
          disabled={isPending}
          className="px-4 py-2 bg-blue-600 text-white text-sm font-semibold rounded-lg hover:bg-blue-700 disabled:opacity-50"
        >
          {isPending ? "Saving…" : "Save"}
        </button>
      </div>
    </div>
  );
}

PostgreSQL xmin: Built-In Row Version

PostgreSQL has a built-in system column xmin — the transaction ID of the last write. You can use it instead of an explicit version column:

-- Read with xmin
SELECT id, title, content, xmin
FROM documents
WHERE id = $1;

-- Write with xmin check
UPDATE documents
SET title   = $1,
    content = $2
WHERE id   = $3
  AND xmin = $4::xid;  -- Cast the stored xmin back to xid type

// Using xmin instead of explicit version
// Advantages:
// - No trigger needed
// - Always in sync (PostgreSQL manages it)
// Disadvantages:
// - xmin wraps around after 2^31 transactions (rare but possible)
// - Less human-readable in debugging
// - Can't be set manually for testing

// For most apps: explicit version column is more predictable and testable

Batch Optimistic Locking

-- Update multiple rows with per-row version checks
-- Use a CTE with RETURNING to get affected count per row

WITH updates AS (
  SELECT * FROM UNNEST(
    ARRAY[$1, $2]::uuid[],       -- ids
    ARRAY[$3, $4]::text[],       -- new titles
    ARRAY[$5, $6]::integer[]     -- expected versions
  ) AS t(id, title, expected_version)
),
result AS (
  UPDATE documents d
  SET title = u.title
  FROM updates u
  WHERE d.id      = u.id
    AND d.version = u.expected_version
  RETURNING d.id
)
SELECT
  u.id,
  (r.id IS NOT NULL) AS updated
FROM updates u
LEFT JOIN result r ON r.id = u.id;
-- updated=false → that row had a version conflict

Optimistic vs Pessimistic Locking

Cost and Timeline Estimates

See Also

• PostgreSQL Triggers and Audit Logging
• PostgreSQL Advisory Locks
• Prisma Advanced Patterns and Extensions
• PostgreSQL Schema Design for SaaS
• React Optimistic Updates

---

Working With Viprasol

The lost update problem is invisible until it causes data corruption — and by then it's too late. Our team implements optimistic locking as standard practice for any collaborative editing or concurrent write scenario: version column + trigger, $executeRaw conditional update, OptimisticLockError class, and a conflict resolution UI that gives users a clear choice between reloading or overwriting.

What we deliver:

• version INTEGER DEFAULT 1 column + increment_version() trigger (auto-increments on UPDATE)
• updateDocument function: $executeRaw WHERE version=$N, rowcount check, not-found vs conflict distinction
• OptimisticLockError class with resourceType/resourceId/expectedVersion
• saveDocument Server Action: auth guard, error classification, revalidatePath
• DocumentEditor with conflict banner, reload button, and version tracking

Talk to our team about your collaborative editing architecture →

Or explore our SaaS development services.