Key Range Locking when Serializable isolation is used in POSTGRESQL

vivansai

I have an events table where each POST / PUT request is performed under transaction with Serializable Isolation to ensure that no two users tries to books same event spot. But the negative impact of it is, when there are multiple concurrent requests given to event table, one seems to be passing and rest of them are failing with 4001 Postgresql ERROR code. From a general users perspective, having a 4001 doesn't makes sense as they are adding a new POST request to events table.

But due to this key range locking, adding a new row to event table is altogether blocked until the previous request is done?

Lets assume I have the last event_id as 478321

And three users are trying to add a new event data to the events table and each of their individual transactions will take Serializable Isolation and in this case, the one which acquired the Serializable Isolation first will add the entry as 478322 and rest will fail with 4001 Error? Or does the key range includes only immediate row like 478322 alone? How does the key range locking works with Serializable Isolation?

Can someone please explain this use case in detail? One possible solution is to increase the number of retries to ensure that after the first one gets added to event table, with 2 or 3 retries, we can get the other events gets added successfully. But with concurrent heavy system this won't be a scalable approach. How this scenario should be handled efficiently?

Another option to is check if Serializable Isolation level of Isolation is really needed. But if that's really required, what are my options? Any help would be appreciated.