Understanding Database Locking and Concurrency

You've probably seen it happen: two users try to edit the same record at the same time, and one of them overwrites the other's changes. Or worse, you've experienced a transaction that hangs indefinitely because another transaction is holding a lock. Database locking and concurrency control are fundamental concepts that every developer working with databases needs to understand. They determine how multiple users can safely access and modify data simultaneously without corrupting it or losing updates.

This article explains how database locking works, why it matters, and how to design your applications to work with it effectively. You'll learn about different locking mechanisms, isolation levels, and practical strategies for avoiding common concurrency issues. By the end, you'll have a solid understanding of how to build applications that handle concurrent access gracefully.

What is Database Locking?

Database locking is a mechanism that prevents multiple transactions from modifying the same data at the same time. When a transaction wants to read or write data, it requests a lock on that data. If another transaction already holds a lock on the same data, the new transaction must wait until the lock is released. This ensures that data remains consistent and that no transaction can see partially completed changes from another transaction.

Think of locking like a library book. When you check out a book, it becomes unavailable to others. When you return it, others can check it out. Database locking works similarly: when a transaction "checks out" a row or table, no other transaction can modify it until the first transaction is finished. The database manages these locks automatically, but understanding how they work helps you write better queries and avoid performance problems.

Locks come in different types and scopes. A row-level lock prevents other transactions from modifying a specific row. A table-level lock prevents any transaction from modifying any row in the table. Some locks are shared, allowing multiple transactions to read the same data simultaneously. Others are exclusive, meaning only one transaction can hold the lock at a time. The database chooses the appropriate lock type based on the operation you're performing.

Shared vs Exclusive Locks

Shared locks (also called read locks) allow multiple transactions to read the same data simultaneously. This is efficient because it doesn't block concurrent reads. When a transaction requests a shared lock, the database grants it as long as no other transaction holds an exclusive lock on the same data. Shared locks are automatically released when the transaction completes.

Exclusive locks (write locks) prevent any other transaction from accessing the data. When a transaction requests an exclusive lock, the database waits until all shared locks on the data are released. Only then does it grant the exclusive lock. Exclusive locks are also automatically released when the transaction completes. This is why writing to a database can sometimes be slower than reading: the exclusive lock blocks other transactions from accessing the data.

-- Example: PostgreSQL transaction with shared and exclusive locks
BEGIN;
 
-- This request a shared lock on the user with ID 1
SELECT * FROM users WHERE id = 1 FOR SHARE;
 
-- Other transactions can now read this row, but cannot modify it
-- This query will succeed because it only reads data
SELECT * FROM users WHERE id = 1;
 
-- This query will wait until the shared lock is released
-- because it needs to modify the data
UPDATE users SET email = 'new@example.com' WHERE id = 1;
 
COMMIT;

The FOR SHARE clause in PostgreSQL explicitly requests a shared lock. Without it, PostgreSQL automatically applies the appropriate lock based on the operation. Reading a row without FOR SHARE still acquires a lock, but it's a weaker lock that doesn't prevent other transactions from acquiring shared locks. The key difference is that FOR SHARE prevents other transactions from acquiring exclusive locks, which is important when you need to ensure the data doesn't change while you're working with it.

Isolation Levels: Controlling Visibility

Isolation levels determine how much one transaction can see of another transaction's changes. They control the visibility of uncommitted data and the strength of locks that are acquired. Different isolation levels trade off between data consistency and performance. Choosing the right isolation level for your application is a critical design decision.

The most common isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. READ UNCOMMITTED allows a transaction to read uncommitted changes from other transactions, which can lead to dirty reads. READ COMMITTED (the default in many databases) only allows reading committed data, but a transaction can see different data on each read if other transactions commit in between. REPEATABLE READ ensures that a transaction sees the same data throughout its execution, even if other transactions commit changes. SERIALIZABLE is the strictest level, ensuring that transactions appear to execute sequentially, which provides the highest consistency but can lead to significant performance overhead.

-- Example: Setting isolation level in PostgreSQL
BEGIN;
 
-- Set the isolation level to REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- Now this transaction will see the same data throughout
-- even if other transactions commit changes
SELECT * FROM accounts WHERE id = 1;
 
-- Perform some operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
-- Even if another transaction commits a change to this row,
-- this transaction will still see the original data
SELECT * FROM accounts WHERE id = 1;
 
COMMIT;

The SET TRANSACTION ISOLATION LEVEL command in PostgreSQL changes the isolation level for the current transaction. The isolation level affects which locks are acquired and which data is visible. REPEATABLE READ is a good default for many applications because it prevents dirty reads and non-repeatable reads without being as restrictive as SERIALIZABLE. However, it can still suffer from phantom reads, where new rows appear in a query due to other transactions.

Deadlocks: When Transactions Block Each Other

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a cycle. For example, Transaction A holds a lock on row 1 and wants to lock row 2. Transaction B holds a lock on row 2 and wants to lock row 1. Neither can proceed, and both must wait indefinitely. Deadlocks are a fundamental problem in concurrent systems, and databases have mechanisms to detect and resolve them.

When a deadlock is detected, the database chooses one transaction to roll back and releases its locks. The other transaction can then proceed. This is usually transparent to the application, but it can cause unexpected failures if your application doesn't handle the rollback gracefully. The key to avoiding deadlocks is to always acquire locks in a consistent order and to keep transactions as short as possible.

-- Example: A deadlock scenario in PostgreSQL
BEGIN;
 
-- Transaction A: Locks user 1 first
SELECT * FROM users WHERE id = 1 FOR UPDATE;
 
-- Transaction B: Locks user 2 first
SELECT * FROM users WHERE id = 2 FOR UPDATE;
 
-- Transaction A tries to lock user 2
-- This waits because Transaction B holds the lock
UPDATE users SET email = 'a@example.com' WHERE id = 2;
 
-- Transaction B tries to lock user 1
-- This waits because Transaction A holds the lock
-- Now both transactions are waiting for each other
-- PostgreSQL detects the deadlock and rolls back one transaction

The FOR UPDATE clause in PostgreSQL explicitly requests an exclusive lock. Without it, PostgreSQL still applies locks automatically, but FOR UPDATE makes the locking behavior explicit and predictable. In the example above, the two transactions are trying to lock different rows in different orders, which creates a deadlock. PostgreSQL will detect this situation and roll back one of the transactions, typically the one that has been waiting the longest.

Optimistic vs Pessimistic Concurrency Control

Concurrency control strategies fall into two main categories: optimistic and pessimistic. Pessimistic concurrency control assumes that conflicts are likely and prevents them by acquiring locks immediately. This is the traditional approach used by most databases. Optimistic concurrency control assumes that conflicts are rare and only checks for conflicts when committing the transaction. This can be more efficient in low-contention scenarios but requires additional logic to handle conflicts.

Pessimistic locking is simpler to implement because the database handles most of the work. You just write your queries, and the database ensures that no conflicts occur. However, it can lead to contention and deadlocks, especially in high-concurrency scenarios. Optimistic locking requires you to check for conflicts manually, typically by comparing a version number or timestamp with the current data. If a conflict is detected, you retry the transaction or notify the user.

-- Example: Optimistic locking with a version column
BEGIN;
 
-- Read the current data with the version number
SELECT id, name, balance, version FROM accounts WHERE id = 1;
 
-- Assume the application modifies the balance and prepares an update
-- The update includes the current version number
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 2;
 
-- If the version number has changed, the update affects 0 rows
-- This indicates that another transaction modified the data
-- The application can then retry or notify the user

The optimistic locking pattern uses a version column to detect conflicts. When you update a row, you include the current version number in the WHERE clause. If another transaction has modified the row in the meantime, the version number will be different, and the update will affect 0 rows. This is a simple and effective way to handle optimistic concurrency. The application can then retry the transaction or notify the user that the data has changed.

Practical Strategies for Handling Concurrency

Designing applications that handle concurrency well requires careful consideration of your data access patterns. One common strategy is to keep transactions short and focused. Long-running transactions hold locks for extended periods, increasing the likelihood of deadlocks and reducing concurrency. Another strategy is to always acquire locks in a consistent order. If all transactions lock rows in the same order (e.g., by ID), deadlocks are much less likely.

For applications that need high concurrency, consider using optimistic locking instead of pessimistic locking. This can reduce contention and improve performance in low-contention scenarios. However, you'll need to implement conflict detection and retry logic in your application code. For applications that require strong consistency, pessimistic locking with appropriate isolation levels may be necessary.

-- Example: Using advisory locks to coordinate between applications
BEGIN;
 
-- PostgreSQL advisory locks are database-specific and can be used
-- to coordinate between different applications or sessions
-- This lock is identified by a specific integer key
SELECT pg_advisory_xact_lock(12345);
 
-- Now this transaction has a lock that no other transaction can acquire
-- This is useful for coordinating complex operations across multiple tables
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE transactions SET status = 'completed' WHERE id = 1;
 
COMMIT;

PostgreSQL's advisory locks are a powerful tool for coordinating transactions across different applications or sessions. They're database-specific, which means they won't work with other databases, but they're very flexible because you can use any integer as the lock key. This makes them useful for coordinating complex operations that span multiple tables or even multiple database connections.

Conclusion

Database locking and concurrency control are essential concepts for building reliable and performant applications. Understanding how locks work, the different isolation levels, and the strategies for avoiding deadlocks will help you design applications that handle concurrent access gracefully. Remember that the database manages most of the locking automatically, but being aware of these mechanisms allows you to make informed decisions about your data access patterns.

The key takeaways are: always keep transactions short, acquire locks in a consistent order, choose the appropriate isolation level for your needs, and consider optimistic locking for low-contention scenarios. When conflicts do occur, handle them gracefully by retrying transactions or notifying users. With these principles in mind, you can build applications that are both consistent and performant in concurrent environments.

Platforms like ServerlessBase handle many of these concurrency concerns automatically through managed database services, but understanding the underlying concepts is still valuable for troubleshooting and optimization. When you encounter locking issues or performance problems, a solid understanding of database locking will help you diagnose and resolve them effectively.