Row Level Lock in PostgreSQL: What It Is and How It Works

A row level lock in PostgreSQL is a mechanism that locks individual rows during a transaction to prevent other transactions from modifying them simultaneously. This lock ensures data consistency by allowing concurrent access to different rows without blocking the entire table.

How It Works

Imagine a busy library where multiple people want to read different pages of the same book at the same time. Instead of locking the whole book, the librarian lets each person read only the pages they want, preventing others from changing those pages while they read. This is similar to how row level locks work in PostgreSQL.

When a transaction modifies or reads a row with a row level lock, PostgreSQL locks just that specific row. Other transactions can still access other rows freely. This fine-grained locking helps avoid delays and conflicts that happen when locking entire tables.

The lock lasts until the transaction ends, ensuring that no other transaction can change the locked row until the first one finishes. This keeps data safe and consistent even when many users work with the database at the same time.

Example

This example shows how to lock a row explicitly using SELECT ... FOR UPDATE to prevent other transactions from modifying it until the current transaction finishes.

sql

BEGIN;
-- Lock the row with id=1
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- Now you can safely update the row
UPDATE users SET balance = balance + 100 WHERE id = 1;
COMMIT;

When to Use

Use row level locks when you want to safely update or read specific rows without blocking access to the whole table. This is common in banking apps where multiple users update their accounts simultaneously, or in inventory systems where stock levels change frequently.

Row level locks help avoid conflicts and keep data accurate by preventing two transactions from changing the same row at the same time. They improve performance by allowing many transactions to work on different rows concurrently.

Key Points

• Row level locks lock only the specific rows involved, not the entire table.
• They prevent concurrent transactions from modifying the same row simultaneously.
• Locks are held until the transaction commits or rolls back.
• Use SELECT ... FOR UPDATE to explicitly lock rows.
• Row level locking improves concurrency and data consistency.

Key Takeaways

Row level locks in PostgreSQL lock individual rows to prevent conflicting changes.

They allow multiple transactions to work on different rows at the same time.

Use SELECT ... FOR UPDATE to lock rows explicitly during a transaction.

Locks last until the transaction ends, ensuring data consistency.

Row level locking improves performance by avoiding full table locks.