I'm working on an application where we have to make sure to only have a limited number of rows inserted into a table. We are using postgresql.

It's for a ticketing system where there are only limited seets available and when the user is asking for a seet, I first have to SELECT to make sure that there is one more seet available and if so, I have to INSERT into this table to "reserve" the seet.

But what if between the SELECT and the INSERT statement is another one inserting into the same table?

I have read about LOCKING but only found examples for one statement at a time and not about locking between multiple statements.

My questions are:

1. How do I lock a table for multiple statements? Or: What is the best strategy to prevent faulted data?
2. If one user has locked the table, will the process of another user wait for the other process to finish or will it fail?
3. If I'm completely wrong, how is the correct way to solve this kind of problem?

CodePudding user response：

I wouldn't lock the table at all, no need for. I think. When I have 500 tickets, I would create 500 records (tickets). When someone buys a numbers of tickets, you have to update these tickets as SOLD. Use a SELECT FOR UPDATE statement in combination with SKIP LOCKED to get the number of tickets you need and then UPDATE these selected records. Multiple customers can buy tickets at the same time, without issues.

The only thing left, is what to do when someone wants to buy 10 tickets when you don't have 10 tickets anymore.