PostgreSQL: Playing a little bit with race conditions, SQL and isolation levels

buying_new_tickets.sql

--
-- Trying to buy a new ticket for an event
--
BEGIN;
    UPDATE events e
       SET updated_at = now()
     WHERE e.id = :event_id
       AND e.max_tickets > (SELECT count(*)           -- (does this logic work with READ_COMMITTED??)
                              FROM tickets t
                             WHERE t.event_id = e.id); 

    if ROW_COUNT == 0 then
        RAISE EXCEPTION 'sorry, there is no more tickets to sell'
    end if;

    INSERT INTO tickets 
         VALUES (:event_id :customer_id, :code, now());
COMMIT;

I asked this question in twitter:
https://twitter.com/rponte/status/1552648187860594688?s=20&t=xaPz946ZIwSToujPWlrblQ

@FranckPachot @vlad_mihalcea
do you think this kind of logic in the UPDATE can avoid inserting more tickets than the max allowed?
I mean, when using the default READ_COMMITTED isolation level.
My guess is that we must have at least a REPEATABLE_READ there, right?

Some interesting article about this topic that can be helpful:

• https://vladmihalcea.com/write-skew-2pl-mvcc/
• https://vladmihalcea.com/a-beginners-guide-to-read-and-write-skew-phenomena/
• https://vladmihalcea.com/spring-transaction-best-practices/
• https://franckpachot.medium.com/read-committed-and-pessimistic-locking-in-distributed-sql-databases-211af3fdcfca
• https://dev.to/aws-heroes/read-committed-anomalies-in-postgresql-1ieg

https://franckpachot.medium.com/read-committed-and-pessimistic-locking-in-distributed-sql-databases-211af3fdcfca

There is another isolation level, between Read Committed and Serializable. It is called Repeatable Read by database using pessimistic locking for it, and Snapshot Isolation by those using optimistic locking. It ensures that the rows are read from a state at the start of the transaction, but still allows some other intermediate state for new rows. In my hotel example, if there is one row per room, with a free/booked flag, Repeatable Read allows only one of the two transactions to commit. However, if this is modeled with the presence or absence of a booking row, the absence of a booking is not part of the initial state, and the isolation level doesn’t prevent the conflict. However, a unique constraint may prevent it.

• Postgres Repeatable Read vs Serializable
• Postgres Oficial Doc: 13.2.2. Repeatable Read Isolation Level

⭐️ The answer to this question:

I've made some integration tests, and this code avoids race conditions when executed inside a transaction that uses the following isolation levels:

• SERIALIZABLE;
• REPEATABLE READ;

Any level less strict than those above, as READ_COMMITED, is susceptible to race conditions!