</>
sqlintermediate

Optimistic Locking with Version Column

Prevent lost updates in concurrent environments using a version column for optimistic concurrency control.

sql
-- Add version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

-- Update with optimistic lock check
UPDATE products
SET
  price = 29.99,
  version = version + 1
WHERE
  id = 42
  AND version = 3;  -- Expected version

-- Check if update succeeded (0 rows = conflict)
-- In application code:
-- if (result.rowCount === 0) throw new Error('Concurrent modification detected');

Use Cases

  • Multi-user editing
  • Cart checkout
  • Inventory management

Tags

#concurrency#locking#transactions#data-integrity

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlintermediate

Deferred Foreign Key Constraints

Defer constraint checking to transaction commit for circular references and batch operations.

#constraints#transactions
pythonintermediate

asyncio.gather Concurrent Tasks

Run multiple async operations concurrently with asyncio.gather and proper error handling.

#asyncio#concurrency
sqlintermediate

Keyset Pagination vs Offset

Efficient keyset (cursor) pagination pattern compared to traditional OFFSET for large datasets in PostgreSQL.

#pagination#performance
sqlintermediate

UPSERT with ON CONFLICT

Insert or update rows atomically using PostgreSQL ON CONFLICT clause with partial indexes and conditions.

#upsert#postgresql