Concurrency Control in Databases

Handling multiple users interacting with the same data is a core challenge for any modern application. Concurrency control ensures that operations happen safely and predictably — even when performed simultaneously — by maintaining data consistency and preventing conflicts.

In this article, we’ll break down key concepts like shared and exclusive locks, deadlocks, and two-phase locking, using real-world SQL examples. We’ll also cover connection pooling and the pitfalls of SQL pagination to help you build scalable and efficient database-backed systems.

Shared vs Exclusive Locks

Locks are mechanisms that help ensure consistency and isolation in databases, especially when multiple users or processes are reading and writing the same data at the same time.

Exclusive Lock (Write Lock) is acquired when a transaction wants to write (or read & write) to a piece of data. Only one transaction can hold an exclusive lock on a given piece of data, and no one else can read or write that data until the lock is released.

Shared Lock (Read Lock) is acquired when a transaction wants to read a piece of data and make sure it doesn’t change during the read. Multiple transactions can hold shared locks on the same data at the same time, but if a shared lock is present, no transaction can write to that data

Shared and exclusive locks have strict rules about how they can coexist.

• Shared locks are compatible with other shared locks, meaning multiple transactions can read the same data at once without blocking each other. However, shared locks are not compatible with exclusive locks. If a shared lock exists, an exclusive lock (which needs full access) cannot be acquired until the shared lock is released.
• Exclusive locks are not compatible with any other locks — if one transaction holds an exclusive lock, no other transaction can read or write that data until the lock is released. This strict compatibility ensures that data remains consistent and avoids conflicts during concurrent transactions.

Locks are essential for maintaining data consistency and transaction isolation in a multi-user database environment. When multiple transactions access the same piece of data simultaneously, locks help control who gets to read or write. They prevent problems like dirty reads (reading uncommitted changes), non-repeatable reads (getting different values in the same transaction), and phantom reads (new rows appearing mid-transaction). By using locks effectively, databases ensure that each transaction can run as if it were the only one interacting with the system.

Despite their benefits, locks can introduce challenges.

• One major downside is reduced concurrency. When too many transactions are waiting on locks, the system slows down, and fewer operations can happen in parallel.
• There’s also the risk of deadlocks, where two or more transactions are stuck waiting for each other’s locks, resulting in a standstill that needs to be resolved by the database engine.
• Additionally, lock contention — when many transactions compete for the same data — can lead to performance degradation. Therefore, while locks are powerful for ensuring correctness, they must be used carefully to avoid hurting overall system performance.

Deadlocks

A deadlock occurs when two or more transactions are waiting on each other to release locks — and none of them can proceed. Consider the following scenario of a deadlock:

1. Transaction A locks Resource 1 and waits for Resource 2.
2. Transaction B locks Resource 2 and waits for Resource 1.
3. Now both are stuck waiting forever — deadlock.

Most modern databases, including PostgreSQL, detect these automatically and resolve them by aborting one of the transactions (usually the one that entered the deadlock last).

To prevent deadlocks

Consider following practical example where we’ve bank accounts:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance NUMERIC
);

Two users are performing money transfers between two accounts.

• Account 1: Alice’s account
• Account 2: Bob’s account

Scenario 1: Deadlock Detected (Transfer Between Two Accounts)

Two transfers are happening simultaneously:

• Client 1: Transfers ₹500 from Alice (1) to Bob (2)
• Client 2: Transfers ₹300 from Bob (2) to Alice (1)

-- Client [1]
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Locks Alice's account

-- Client [2]
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 2;  -- Locks Bob's account

-- Client [2]
UPDATE accounts SET balance = balance + 300 WHERE id = 1;  -- Blocked: waiting on Client 1

-- Client [1]
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- DEADLOCK

So what happens here is:

1. Client 1 locks Alice’s account, then tries to access Bob’s, but it’s locked by Client 2.
2. 2. Client 2 locks Bob’s account, then tries to access Alice’s, but it’s locked by Client 1.
3. 3. Now both are waiting on each other → Deadlock!

PostgreSQL automatically detects this and aborts one of the transactions:

ERROR: deadlock detected

Scenario 2: One Client Rolls Back

-- Client [1]
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Client [2]
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 2;
UPDATE accounts SET balance = balance + 300 WHERE id = 1;  -- Blocked (waiting on Client 1)

-- Client [1]
ROLLBACK;  -- Releases lock on Alice’s account

-- Client [2]
-- Now completes the update: Alice’s balance increases by ₹300

So what happens here is:

1. Client 2 was waiting to update Alice’s account.
2. Client 1 rolled back, releasing the lock.
3. Client 2’s transaction continued successfully.

Scenario 3: One Client Commits First

-- Client [1]
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Client [2]
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 2;
UPDATE accounts SET balance = balance + 300 WHERE id = 1;  -- Blocked

-- Client [1]
COMMIT;  -- Lock released

-- Client [2]
-- Update continues — Alice gets ₹300

So what happens here is:

1. Client 2 was waiting to access Alice’s account.
2. Once Client 1 committed, the lock was released.
3. Client 2 successfully continued its transfer.

Two-Phase Locking

Two-Phase Locking is a concurrency control method that ensures transactions acquire all the locks before modifying data, and then release all locks only after the operations are complete.

Get AkashSDas’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

As the name suggests, there’re two phases:

1. Growing Phase — Acquiring locks (no release allowed).
2. Shrinking Phase — Releasing locks (no new locks allowed).

Without this mechanism, we risk race conditions like double booking of the same movie seat.

Double Booking Without Two-Phase Locking

Imagine two users trying to book Seat #13 for the same movie at the exact same time. The following the shape of the seats table:

CREATE TABLE seats (
  id INT PRIMARY KEY,
  is_booked BOOLEAN,
  booked_by TEXT
);

The following implementation results if booking the same seat for two users (who are booking in parallel).

-- User 1
BEGIN;

-- User 2
BEGIN;

-- User 2 checks seat
SELECT * FROM seats WHERE id = 13;
-- Seat is available

-- User 1 checks seat
SELECT * FROM seats WHERE id = 13;
-- Seat is also available

-- User 2 books seat
UPDATE seats SET is_booked = true, booked_by = 'Alice' WHERE id = 13;
-- UPDATE 1

-- User 1 also books seat
UPDATE seats SET is_booked = true, booked_by = 'Bob' WHERE id = 13;
-- PostgreSQL allows this, but waits (blocks) for User 2 to finish

-- User 2
COMMIT;

-- User 1
-- Now proceeds and overwrites booking
COMMIT;

-- Final state: Seat #13 is booked by Bob. Alice's booking was overwritten.

Both users saw the seat as available and both ended up booking it — but the last commit wins, silently overwriting the earlier one.

Avoiding Double Booking with Two-Phase Locking

PostgreSQL supports row-level locks using SELECT ... FOR UPDATE, which prevents others from reading the same row for updates until the transaction is complete.

The following implementation applies row-level lock on the seat a user is trying to book.

-- User 1
BEGIN;
-- Lock seat #14 exclusively
SELECT * FROM seats WHERE id = 14 FOR UPDATE;

-- User 2
BEGIN;
-- Tries to lock seat #14 → Blocked (waiting for User 1)
SELECT * FROM seats WHERE id = 14 FOR UPDATE;

-- User 1 books the seat
UPDATE seats SET is_booked = true, booked_by = 'Alice' WHERE id = 14;

-- User 1
COMMIT;
-- Now User 2 gets unblocked

-- User 2 re-runs SELECT ... FOR UPDATE
-- Sees seat is already booked → doesn't proceed with booking

Only one user can book the seat. The other waits until the transaction is complete and sees the updated state — avoiding double booking.

Alternate: Conditional Update (Not Reliable Across DBs)

Some systems try to do an optimistic update:

-- User 1
BEGIN;
UPDATE seats SET is_booked = true, booked_by = 'Alice'
WHERE id = 15 AND is_booked = false;
-- UPDATE 1

-- User 2
BEGIN;
UPDATE seats SET is_booked = true, booked_by = 'Bob'
WHERE id = 15 AND is_booked = false;
-- Blocks or fails, depending on DB implementation

-- User 1
COMMIT;

-- User 2
-- Gets unblocked → PostgreSQL re-evaluates WHERE clause
-- Sees `is_booked = true` → UPDATE 0 rows → No double booking

But we not a good solution (even though it would work with PostgreSQL) and the reason is it depends heavily on the database engine’s isolation level and internal mechanics. Not all databases re-evaluate the WHERE clause after blocking (some use cached values). Also, it’s not as predictable as explicit locking.

PostgreSQL tracks row lock info in the tuple itself (in the heap). When you try to update a row:

1. If it’s already locked → PostgreSQL blocks your transaction.
2. 2. Once it’s unblocked → PostgreSQL re-reads the tuple from the heap.

This is why even optimistic updates can be safe in Postgres — but not in all databases (e.g., MySQL or Oracle may behave differently).

Connection Pool

Connection pooling is a technique used to manage and reuse a set of pre-established database connections to improve performance and resource usage. Instead of opening and closing a new database connection for every request — which is slow and expensive — a pool of connections is maintained and shared among multiple clients.

Establishing a new database connection involves:

• TCP handshake
• Authentication
• SSL setup (for secure connections)

These steps are resource-intensive and time-consuming, especially under high load. So, rather than doing that for every query, we: Create a pool of connections once and reuse them as needed.

Two main usage patterns for connection pool:

• Stateless (Single Query): It’s useful for quick, one-off queries. The app sends a query. The pool automatically assigns an available connection. The query runs and the connection is returned to the pool.
• Stateful (Transactions): It’s useful for transactions or multiple queries that must use the same connection. The app borrows a dedicated connection from the pool. Runs multiple queries in sequence (like in an ACID transaction). Releases the connection back to the pool after completion.

Without pooling, every client request creates a new connection which results in high latency, wastage of resources, and can exhaust database limits (e.g., PostgreSQL has a default max connections limit).

With pooling, connections are reused. You can control max connections, idle time, and timeouts. Efficient handling of thousands of queries with just a limited set of active DB connections.