Postgres advisory lock within function allows concurrent execution
As soon as PERFORM pg_advisory_unlock(eventId)
is executed, another session can grab that lock, but as the INSERT of session #1 is not yet commited, it will not be counted in the COUNT(*)
of session #2, resulting in the over-booking.
If keeping the advisory lock strategy, you must use transaction-level advisory locks (pg_advisory_xact_lock
), as opposed to session-level. Those locks are automatically released at COMMIT time.