A Beginner’s Guide to PostgreSQL
Locking with SQLAlchemy
Author: Manus AI Date: December 8, 2025
1. Introduction: The Need for Database Locks
In a concurrent application—where multiple users or processes access the database
simultaneously—database locking is a critical mechanism to ensure data integrity
and consistency. A lock is essentially a temporary restriction on a piece of data (a row,
a table, or a custom resource) that prevents other transactions from modifying or even
reading it, depending on the lock type.
PostgreSQL, a highly advanced open-source relational database, provides a
sophisticated and granular locking system. This guide will introduce the main types of
locks and demonstrate how to implement them using SQLAlchemy, a popular Python
Object-Relational Mapper (ORM).
2. PostgreSQL Lock Hierarchy
PostgreSQL organizes its locks into a hierarchy, primarily categorized by the scope of
the lock:
Lock Type Scope Purpose
Row-Level Individual Used by UPDATE , DELETE , and SELECT FOR UPDATE/SHARE to
Locks rows protect specific records.
Used by DDL commands ( ALTER TABLE , DROP TABLE ) and
Table-Level
Entire tables explicit LOCK TABLE statements to protect the table structure
Locks
or all its data.
Advisory Arbitrary Application-defined locks that are not tied to specific data but
Locks resources are used for custom synchronization logic.
3. Row-Level Locks: Protecting Specific Records
Row-level locks are the most common type of lock used in application logic, often
referred to as pessimistic locking. They are acquired when a transaction intends to
modify a row, ensuring no other transaction can interfere until the first transaction
commits or rolls back.
SQLAlchemy provides the with_for_update() method on a SELECT statement to easily
acquire row-level locks.
Example Model Setup
For all examples, we will use a simple Account model:
from sqlalchemy import Column, Integer, String
from [Link] import declarative_base
Base = declarative_base()
class Account(Base):
__tablename__ = 'accounts'
id = Column(Integer, primary_key=True)
username = Column(String, unique=True)
balance = Column(Integer, default=0)
def __repr__(self):
return f"Account(id={[Link]}, balance={[Link]})"
3.1. FOR UPDATE (Exclusive Lock)
The FOR UPDATE lock is an exclusive lock. It prevents other transactions from acquiring
a FOR UPDATE or FOR SHARE lock on the same row, and it blocks any UPDATE or DELETE
operation on that row. This is the standard lock for read-modify-write cycles.
SQLAlchemy Example (FOR UPDATE):
from sqlalchemy import select
from [Link] import Session
# Assume 'session' is an active SQLAlchemy session
def transfer_funds_safely(session: Session, from_id: int, to_id: int, amount: int):
# Acquire FOR UPDATE lock on both rows
stmt = select(Account).where([Link].in_([from_id,
to_id])).with_for_update()
# Execute the query to fetch and lock the accounts
accounts = [Link](stmt).all()
if len(accounts) != 2:
[Link]()
raise ValueError("One or both accounts not found.")
account_from = next(a for a in accounts if [Link] == from_id)
account_to = next(a for a in accounts if [Link] == to_id)
if account_from.balance < amount:
[Link]()
raise ValueError("Insufficient funds.")
# Perform the modification
account_from.balance -= amount
account_to.balance += amount
# The lock is released when the transaction is committed
[Link]()
print(f"Transfer successful. New balances: {account_from.balance},
{account_to.balance}")
3.2. FOR SHARE (Shared Lock)
The FOR SHARE lock is a shared lock. It allows other transactions to also acquire a FOR
SHARE lock on the same row (allowing concurrent reads), but it blocks any transaction
attempting to acquire a FOR UPDATE lock or perform an UPDATE / DELETE . This is useful
when you need to ensure the data you read will not be modified by others before your
transaction finishes.
SQLAlchemy Example (FOR SHARE):
To use FOR SHARE , you pass the argument of=Account (or a list of models) to
with_for_update() , and set read=True .
from sqlalchemy import select
from [Link] import Session
def read_and_hold_balance(session: Session, account_id: int):
# Acquire FOR SHARE lock on the row
stmt = select(Account).where([Link] ==
account_id).with_for_update(read=True)
account = [Link](stmt).first()
if account:
# The lock is held while the transaction is open
print(f"Balance read and locked for sharing: {[Link]}")
# ... perform read-only operations ...
# The lock is released when the transaction is committed
[Link]()
else:
[Link]()
3.3. Handling Lock Conflicts ( NOWAIT and SKIP LOCKED )
When a transaction tries to acquire a lock that is already held by another transaction, it
normally waits. PostgreSQL offers two clauses to change this behavior:
SQLAlchemy
Clause Behavior
Parameter
If the lock cannot be acquired immediately, the query
NOWAIT nowait=True
raises an error instead of waiting.
SKIP If the lock cannot be acquired immediately, the query
skip_locked=True
LOCKED simply skips the locked rows and returns the rest.
SQLAlchemy Example (NOWAIT):
# If the row is already locked, this will raise an exception (e.g.,
OperationalError)
stmt = select(Account).where([Link] == 1).with_for_update(nowait=True)
account = [Link](stmt).first()
SQLAlchemy Example (SKIP LOCKED):
# Useful for background workers processing a queue, where skipping a locked item is
acceptable
stmt = select(Account).where([Link] > 0).with_for_update(skip_locked=True)
unlocked_accounts = [Link](stmt).all()
4. Table-Level Locks: Protecting the Entire Table
Table-level locks are used less frequently in application code but are essential for Data
Definition Language (DDL) operations. They can be explicitly acquired using the LOCK
TABLE command. Since SQLAlchemy’s ORM is primarily designed for row-level
operations, explicit table locks are typically executed using raw SQL via the engine or
connection.
PostgreSQL has eight table-level lock modes, which form a compatibility matrix. The
most restrictive is ACCESS EXCLUSIVE .
Lock Mode Purpose Conflicts With
Acquired by SELECT statements. Allows
ACCESS SHARE ACCESS EXCLUSIVE
concurrent access.
ROW Acquired by UPDATE , DELETE , INSERT . SHARE , SHARE ROW EXCLUSIVE ,
EXCLUSIVE Allows concurrent reads. ACCESS EXCLUSIVE
ACCESS Acquired by DROP TABLE , TRUNCATE .
All other lock modes
EXCLUSIVE Blocks all other access.
SQLAlchemy Example (Raw SQL Table Lock):
To acquire a lock on the entire accounts table in ACCESS EXCLUSIVE mode:
from sqlalchemy import text
from [Link] import Session
def lock_table_exclusively(session: Session):
# Execute raw SQL to lock the table
# Note: This must be done within a transaction
[Link](text("LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE"))
# The table is now locked. No other transaction can read or write until
commit/rollback.
print("Table 'accounts' is now locked in ACCESS EXCLUSIVE mode.")
# ... perform critical, table-wide operation ...
[Link]() # Lock is released on commit
5. Advisory Locks: Application-Defined
Synchronization
Advisory locks are a unique feature of PostgreSQL. Unlike standard locks, they are not
tied to any specific data (rows or tables) but are instead defined by an application
using a user-defined key (a 64-bit or two 32-bit integers).
Advisory locks are useful for implementing application-level synchronization, such as
ensuring only one instance of a background job runs at a time.
Since advisory locks are outside the standard SQL data model, they are typically
acquired using PostgreSQL’s built-in functions via raw SQL.
PostgreSQL Functions (Raw SQL):
pg_advisory_lock(key) : Acquires a session-level exclusive lock. Waits if the lock
is held.
pg_try_advisory_lock(key) : Attempts to acquire the lock immediately. Returns
true or false without waiting.
SQLAlchemy Example (Advisory Lock):
from sqlalchemy import text
from [Link] import Session
# Use a consistent integer key for the resource you want to protect
JOB_LOCK_KEY = 123456789
def run_single_instance_job(session: Session):
# Attempt to acquire the lock immediately
result = [Link](text(f"SELECT
pg_try_advisory_lock({JOB_LOCK_KEY})")).scalar()
if result:
try:
print("Successfully acquired advisory lock. Running job...")
# ... run the critical job logic ...
finally:
# IMPORTANT: Release the lock when done
[Link](text(f"SELECT pg_advisory_unlock({JOB_LOCK_KEY})"))
[Link]()
print("Advisory lock released.")
else:
print("Could not acquire advisory lock. Another instance is running.")
6. Conclusion and Best Practices
Database locking is a powerful tool for concurrency control, but it must be used
judiciously to avoid performance bottlenecks and deadlocks.
Best Practice Description
Always acquire locks as late as possible and release them as early as
Minimize Lock
possible. Locks should be held only for the duration of the critical
Duration
section.
Prefer FOR UPDATE or FOR SHARE (row-level) over LOCK TABLE (table-
Use Row-Level First
level) to maximize concurrency.
To prevent deadlocks, ensure all transactions acquire locks on
Order Lock
multiple resources in a consistent, predefined order (e.g., always lock
Acquisition
Account A before Account B).
Use NOWAIT for High In high-traffic scenarios, use NOWAIT to fail fast instead of blocking,
Concurrency allowing the application to retry or handle the conflict gracefully.
7. References
[1] PostgreSQL Documentation: Explicit Locking [2] SQLAlchemy Documentation:
with_for_update() [3] Medium: SQLAlchemy Database Locks Using FastAPI: A Simple
Guide [4] Stack Overflow: How to SELECT FOR SHARE using SQLAlchemy with
PostgreSQL [5] PostgreSQL Documentation: Advisory Locks