> Two groups of friends try to book seats ranges 5–8 and 7–9 in the same row, for the same show, at the same time. One of them must fail. An ACID database can help enforce this rule. The question is: how much collateral damage does your concurrency control cause for everyone else?
In modern development, we tend to put the business logic in the application first. Still, enforcing business invariants in the database with integrity constraints may provide better performance when it avoids the need for the serializable isolation level required when the invariant concerns multiple rows. This is the case for foreign keys and unique constraints, and here is another example.
In a previous post of this series, we saw how Oracle SQL Assertions can work around the limitations of Oracle's Snapshot Isolation (improperly called SERIALIZABLE). We explored the rule: "each shift must always have at least one doctor on call", which is an example of a table-level invariant — a constraint that requires at least one row satisfying a given condition to exist at all times. This goes beyond what traditional row-pair constraints can express.
Most databases provide only the unique constraint, which enforces that no two rows share the same value(s) for a set of columns. PostgreSQL extends this with exclusion constraints, which generalize the concept: rather than just preventing duplicate values, they prevent any two rows from satisfying a specified pairwise condition — for example, preventing two time ranges from overlapping.
Here is an example of such a business rule: we manage a movie theater where customers can book consecutive seats in a row for themselves and their friends.
Oracle Database: assertion
The booking table identifies the seats for a session (SHOWING_ID) by the row (ROW_NUMBER) and the seat number range (SEAT_START, SEAT_END):
Name Null? Type
______________ ___________ ______________________________
BOOKING_ID NOT NULL RAW(16 BYTE)
SHOWING_ID NOT NULL RAW(16 BYTE)
ROW_NUMBER NOT NULL NUMBER(38)
SEAT_START NOT NULL NUMBER(38)
SEAT_END NOT NULL NUMBER(38)
CUSTOMER_ID NOT NULL RAW(16 BYTE)
CREATED_AT TIMESTAMP(6) WITH TIME ZONE
Typically, customers query look at the free seats in their preferred row, and book an available range that is free and can fit the number of seats they need. A serializable transaction would be needed to be sure that what is read is not booked by another in the meantime, but Oracle's SERIALIZABLE isolation level does not provide true serializability — it implements Snapshot Isolation, which permits write skew anomalies and doesn't prevent a new booking from being committed that overlaps what was read. With SQL assertions, you can take another approach: create an integrity constraint that raises an error in case of overlap:
CREATE ASSERTION no_overlapping_seats
CHECK (
NOT EXISTS (
SELECT 1
FROM bookings b1
WHERE EXISTS (
SELECT 1
FROM bookings b2
WHERE b1.showing_id = b2.showing_id
AND b1.row_number = b2.row_number
AND b1.booking_id <> b2.booking_id
-- Intersection logic:
AND b1.seat_start < b2.seat_end
AND b2.seat_start < b1.seat_end
)
)
);
The rule says that there cannot exist two bookings within the same show for the same row with overlapping start/end seat ranges.
The assertion uses the existential negation form (NOT EXISTS). The universal quantifier version, which may be easier to understand, says that for any two different bookings on the same show and row, the seat ranges must not overlap (one ends before the other starts):
∀ b1, b2 ∈ Bookings : (
b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ (
b1.start ≥ b2.end ∨ b2.start ≥ b1.end
)
This is equivalent, by De Morgan's law (x ∨ y ≡ ¬(¬x ∧ ¬y)), to saying it cannot be the case that both ranges extend into each other:
∀ b1, b2 ∈ Bookings : (
b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
) ⇒ ¬ (
b1.start < b2.end ∧ b2.start < b1.end
)
Now, applying quantifier duality (∀x : P ⇒ ¬Q ≡ ¬∃x : P ∧ Q), it is easy to understand the existential negation version that maps directly to the NOT EXISTS assertion:
¬∃ b1, b2 ∈ Bookings : (
b1.show = b2.show ∧ b1.row = b2.row ∧ b1.id ≠ b2.id
∧
b1.start < b2.end ∧ b2.start < b1.end
)
With this, any concurrent inserts for the same show and same row will wait, and after the wait, the seat ranges will be checked and ORA-08601: SQL assertion (NO_OVERLAPPING_SEATS) violated will be raised if they overlap. This works but is not ideal because it will wait even if the seats do not overlap. However, two non-overlapping bookings in the same show and row will still block before being accepted becaise assertions lock on exact column values (here, the combination of showing_id and row_number), not on ranges within those values.
It is possible to get better concurrency for that rule with PostgreSQL exclusion constraints, but first, let's check with serializable as PostgreSQL supports true Serializable Snapshot Isolation (SSI).
PostgreSQL: serializable
To get a fully reproducible example, I create all necessary tables for the theater's screens, movies, showings, and bookings:
CREATE TABLE screens (
screen_id UUID PRIMARY KEY,
name TEXT NOT NULL,
rows INT NOT NULL,
seats_per_row INT NOT NULL
);
INSERT INTO screens (screen_id, name, rows, seats_per_row) VALUES
('a1b2c3d4-0001-4000-8000-000000000001', 'Alpha Station', 25, 36),
('a1b2c3d4-0001-4000-8000-000000000002', 'Big Market', 18, 28),
('a1b2c3d4-0001-4000-8000-000000000003', 'Mül Paradise', 12, 20),
('a1b2c3d4-0001-4000-8000-000000000004', 'Kirian Corridor', 15, 24);
CREATE TABLE movies (
movie_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
year INT NOT NULL,
duration_minutes INT NOT NULL,
director TEXT NOT NULL DEFAULT 'Luc Besson'
);
INSERT INTO movies (movie_id, title, year, duration_minutes) VALUES
('b0b0b0b0-0001-4000-8000-000000000001', 'Subway', 1985, 104),
('b0b0b0b0-0001-4000-8000-000000000002', 'The Big Blue', 1988, 168),
('b0b0b0b0-0001-4000-8000-000000000003', 'Nikita', 1990, 117),
('b0b0b0b0-0001-4000-8000-000000000004', 'Léon: The Professional', 1994, 110),
('b0b0b0b0-0001-4000-8000-000000000005', 'The Fifth Element', 1997, 126),
('b0b0b0b0-0001-4000-8000-000000000006', 'Angel-A', 2005, 90),
('b0b0b0b0-0001-4000-8000-000000000007', 'Lucy', 2014, 89),
('b0b0b0b0-0001-4000-8000-000000000008', 'Valerian and the City of a Thousand Planets', 2017, 137),
('b0b0b0b0-0001-4000-8000-000000000009', 'Anna', 2019, 119),
('b0b0b0b0-0001-4000-8000-000000000010', 'Dogman', 2023, 114);
CREATE TABLE showings (
showing_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
movie_id UUID NOT NULL,
screen_id UUID NOT NULL,
start_time TIMESTAMPTZ NOT NULL
);
INSERT INTO showings (showing_id, movie_id, screen_id, start_time) VALUES
-- Friday
('c0c0c0c0-0001-4000-8000-000000000001', 'b0b0b0b0-0001-4000-8000-000000000005', 'a1b2c3d4-0001-4000-8000-000000000001', '2025-01-24 19:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000002', 'b0b0b0b0-0001-4000-8000-000000000004', 'a1b2c3d4-0001-4000-8000-000000000003', '2025-01-24 20:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000003', 'b0b0b0b0-0001-4000-8000-000000000008', 'a1b2c3d4-0001-4000-8000-000000000002', '2025-01-24 21:00:00+01'),
('c0c0c0c0-0001-4000-8000-000000000004', 'b0b0b0b0-0001-4000-8000-000000000007', 'a1b2c3d4-0001-4000-8000-000000000004', '2025-01-24 22:00:00+01')
;
CREATE TABLE bookings (
booking_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
showing_id UUID NOT NULL REFERENCES showings(showing_id),
row_number INT NOT NULL,
seat_range INT4RANGE NOT NULL, -- e.g., [5,9) means seats 5,6,7,8
customer_id UUID NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO bookings (booking_id, showing_id, row_number, seat_range, customer_id) VALUES
('d0d0d0d0-0001-4000-8000-000000000001', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[15,19)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000001'),
('d0d0d0d0-0001-4000-8000-000000000002', 'c0c0c0c0-0001-4000-8000-000000000001', 12, '[19,21)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000002'),
('d0d0d0d0-0001-4000-8000-000000000003', 'c0c0c0c0-0001-4000-8000-000000000001', 13, '[10,16)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000003'),
('d0d0d0d0-0001-4000-8000-000000000004', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[1,5)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000004'),
('d0d0d0d0-0001-4000-8000-000000000005', 'c0c0c0c0-0001-4000-8000-000000000001', 14, '[30,36)'::int4range, 'e0e0e0e0-0001-4000-8000-000000000005');
INSERT INTO bookings (
showing_id,
row_number,
seat_range,
customer_id
) VALUES (
'c0c0c0c0-0001-4000-8000-000000000002',
12,
'[ 1, 3)'::int4range,
gen_random_uuid()
)
;
PostgreSQL has a built-in range datatype. A range like [5,8) represents a half-open interval — it includes 5, 6, and 7 but not 8. The [ means inclusive, ) means exclusive. This is the standard representation for discrete ranges in PostgreSQL, and it means I can store the seat range in a single column instead of two columns like in Oracle Database. PostgreSQL also provides operators on ranges, notably && (overlaps) which returns true when two ranges share any points in common.
Typically a service that checks the available seats for a show and books a range of seats will use a serializable transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- query the show's booked seats
SELECT row_number,seat_range,now()
FROM bookings
WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002';
-- find a range available and book it
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid())
RETURNING customer_id, booking_id, created_at;
-- wait time added to hold the transaction open long enough for others
-- to start and attempt to commit concurrently
select pg_sleep(10);
-- commit the transaction or fail if not serializable
COMMIT;
For this show and row, only seats in range [ 1, 3) are currently booked. I run my transactions in parallel for ranges [ 5, 8), [25,28), [ 7,10) so I expect only one of [ 5, 8) and [ 7,10) to succeed, in addition to [ 5, 8) which do not overlap with anyone:
\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
\! psql -ec '\timing on' -c "begin isolation level serializable; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
Note: the \! commands launch background shell processes with &, staggered by 1-second sleeps, to simulate concurrent users hitting the database at nearly the same time.
As it was first, [5,8) succeeded:
postgres=# SELECT row_number,seat_range,created_at
FROM bookings
WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'
ORDER BY created_at
;
row_number | seat_range | created_at
------------+------------+-------------------------------
12 | [1,3) | 2026-05-14 10:12:58.322202+00
12 | [5,8) | 2026-05-14 10:15:26.063663+00
(2 rows)
Both [25,28) and [ 7,10) have waited for the first one to commit and have failed, not because of overlap, as both viewed the seats free from their query (the first transaction wasn't committed yet), but because of serialization:
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
Time: 10004.080 ms (00:10.004)
Serializable Snapshot Isolation (SSI) uses predicate locks to track what each transaction has read. Here, all three transactions read the same set of rows for the showing — SSI records a predicate lock on the showing_id filter condition. When the first transaction commits its insert, the other transactions have a read-write dependency: they read a state that no longer exists (the showing now has a new booking). SSI detects this (a "pivot" in the dependency graph) and aborts the transactions that cannot be guaranteed to produce a serializable outcome. The granularity of SSI predicate locks means the entire showing's bookings are effectively locked — not just the specific seat range. This is why [25,28), which doesn't overlap with anything, still fails.
For better concurrency, we need to lower the isolation level and enforce the overlap prevention as an in-database integrity constraint. In short, we enforce the logic using the C instead of I from ACID properties.
PostgreSQL: EXCLUDE constraint
PostgreSQL's exclusion constraints generalize unique constraints: instead of checking only equality between rows, they can check any combination of operators. The constraint is backed by an index (typically GiST) that efficiently finds conflicting rows at insert or update time. The key insight is that the constraint acts as a materialized conflict check — it evaluates against the current committed state of the table at the moment of the insert, independent of what the transaction previously read. This is why we can safely drop down to Read Committed isolation.
Exclusion constraints require a GiST index on the range for the overlap operator &&. Since I also have equality operators = on non-range columns ("showing_id", "row_number"), I need to enable the btree_gist extension — it's shipped with PostgreSQL and adds GiST operator classes for scalar types like UUID and integer, allowing them to be mixed with range types in a single GiST index. Then I can declare the exclusion constraint that will raise an error on seat range overlap for the same show and row:
-- Need the btree_gist extension for mixing = and && operators
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD
CONSTRAINT no_overlapping_seats
EXCLUDE USING GIST (
showing_id WITH =,
row_number WITH =,
seat_range WITH && -- && = "overlaps" operator
)
;
The EXCLUDE USING GIST declaration reads: "no two rows may exist where showing_id equals, row_number equals, AND seat_range overlaps." The GiST index makes this check efficient — PostgreSQL doesn't scan the whole table, it uses the index to find only potentially conflicting rows.
If you forget to create the btree_gist extension, you'll encounter the following error:
ERROR: data type uuid has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
The reason is that GiST indexes natively support only geometric and range types (with operators like &&, @>, <<). Scalar types like uuid, integer, or text have no built-in GiST operator class — they normally live in B-tree indexes. The btree_gist extension adds GiST operator classes for these scalar types, enabling them to participate in exclusion constraints alongside range operators. The error is misleading because it doesn't say "you need btree_gist" but says "this is not supported by GiST alone".
Here are some inserts to test it:
-- This should FAIL (overlaps with seats [15,19) in row 12, showing ...0001)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[17,22)'::int4range, gen_random_uuid());
-- This should SUCCEED (adjacent, seats [21,25) in row 12)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000001', 12, '[21,25)'::int4range, gen_random_uuid());
-- This should SUCCEED (same row 12 but different showing)
INSERT INTO bookings (showing_id, row_number, seat_range, customer_id)
VALUES ('c0c0c0c0-0001-4000-8000-000000000004', 12, '[15,19)'::int4range, gen_random_uuid());
You can validate that overlapping ranges of seats are not permitted for the same row in the same show. However, this doesn't show the concurrency advantages of it like a parallel testing.
I delete the booking for [5,8) that succeeded before, so that I can run my parallel test again:
DELETE FROM bookings
WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'
AND row_number=12 AND seat_range='[ 5, 8)'::int4range
;
Now, I can run my transactions in Read Committed isolation level because the constraint prevents the overlap, regardless of what was read initially. The exclusion constraint checks for conflicts against committed data at insert time — even if the transaction's earlier SELECT didn't see the conflicting row:
\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 5, 8)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[25,28)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
\! psql -ec '\timing on' -c "begin isolation level read committed; SELECT row_number,seat_range,now() FROM bookings WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'; INSERT INTO bookings (showing_id, row_number, seat_range, customer_id) VALUES ('c0c0c0c0-0001-4000-8000-000000000002', 12, '[ 7,10)'::int4range, gen_random_uuid()) returning customer_id, booking_id, created_at; select pg_sleep(10) ; commit;" & sleep 1
Two bookings have succeeded, [5,8) and [25,28), and from the creation time you can see that they didn't have to wait on each other:
postgres=# SELECT row_number,seat_range,created_at
FROM bookings
WHERE showing_id='c0c0c0c0-0001-4000-8000-000000000002'
ORDER BY created_at
;
row_number | seat_range | created_at
------------+------------+-------------------------------
12 | [1,3) | 2026-05-14 10:12:58.322202+00
12 | [5,8) | 2026-05-14 10:33:25.935444+00
12 | [25,28) | 2026-05-14 10:33:26.936793+00
(3 rows)
Only the conflicting range [7,10) waited on the first transaction to commit and then detected the overlap:
ERROR: conflicting key value violates exclusion constraint "no_overlapping_seats"
DETAIL: Key (showing_id, row_number, seat_range)=(c0c0c0c0-0001-4000-8000-000000000002, 12, [7,10)) conflicts with existing key (showing_id, row_number, seat_range)=(c0c0c0c0-000
1-4000-8000-000000000002, 12, [5,8)).
Time: 8009.347 ms (00:08.009)
Note that the wait time (~8 seconds instead of ~10) corresponds to the 1-second stagger between the sessions: the third transaction started 2 seconds after the first, so it waited the remaining duration until the first committed. During this wait, PostgreSQL holds a lightweight lock on the potentially conflicting index entry — only transactions inserting an actually overlapping range will block.
Indexes are often used to avoid broader table locks. For example, unique constraints in most databases rely on an index to detect conflicts, and in Oracle, creating an index on a foreign key helps avoid locks for referential integrity checks. PostgreSQL does not need that because it can use share row locks, whereas Oracle implements only exclusive row locks.
The application must interpret this error (SQLSTATE 23P01 — exclusion_violation) as: "those seats are not available" and it should start again the transaction with a fresher seat map. This is similar to how applications handle serialization failures (SQLSTATE 40001) with SSI, but with an important difference: with the exclusion constraint, only actual overlaps trigger the error, so retries are far less frequent.
Summary
| Approach |
Blocking Behavior |
Concurrency |
Constraint Expressiveness |
| Oracle 26ai Assertion |
Blocks all inserts for same show+row, even non-overlapping |
Medium (wait even non-conflicting seats) |
Very high (arbitrary SQL predicates) |
| PostgreSQL Serializable (SSI) |
Aborts transactions that read the same predicate range, even non-overlapping inserts |
Low (requires retries) |
N/A (application logic) |
| PostgreSQL EXCLUDE constraint |
Blocks only actually overlapping ranges |
High (fails only on conflict) |
Pairwise operators supported by an index |
The exclusion constraint with GiST index provides the best concurrency: non-conflicting inserts proceed in parallel without waiting or aborting, while true overlaps are precisely detected and rejected. Serializable isolation is more conservative — it must protect against any possible anomaly from the read set, not just overlaps. Oracle assertions are the most expressive (they can enforce arbitrary multi-row predicates) but operate at the granularity of exact value locks, which causes unnecessary blocking when the actual conflict depends on range overlap.
The tradeoff is clear: if your invariant can be expressed as a pairwise exclusion condition with indexable operators, PostgreSQL's EXCLUDE constraint gives you the best of both worlds — declarative integrity with fine-grained concurrency.
by Franck Pachot