0

Background, I have a booking system that allows users to book a seat for the concert.

Table: ERP_MovieTickets

Scenario: Two users could book the same seat at the same time, let's say 1-July-2022 at 00:00:50

From the link below, I know these issues could be solved by 'BEGIN TRANSACTION' in SQL Server,

Question:

  1. When users book the same seat in the scenario above, only one transaction will be triggered?
  2. The other request will not proceed although they are received by SQL Server at the same time?
  3. The other request will be canceled?

Link Reference

Wei Chen Chen
  • 88
  • 8
  • 3
    Transactions by themselves won't prevent an issue. Neither transaction will be cancelled - one will proceed, the other be blocked until the first finishes, then it will proceed. Consider what happens if user A books a seat , then user B books the same seat immediately afterward? User A had the seat, now user B has it. User A will get angry when they paid for a seat and then can't use it because user B is sitting in it. Read up on concurrency control and how [rowversion](https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql) helps in dealing with situations like this. – AlwaysLearning Jul 19 '22 at 05:20
  • 2
    I'm surprised the world needs another booking system. However the issues of concurrency are very complex and not able to be addressed in a single question here. You'll notice that on most commercial booking systems they place a hold on the requested seats and then give the use a time period to pay for them after which time the seats revert back to being available again. All things you need to consider. – Dale K Jul 19 '22 at 06:07
  • @AlwaysLearning I got your idea, thanks for the sharing. If we add "IsBooked" column for checking, User A seat won't be taken by User B , right? – Wei Chen Chen Jul 19 '22 at 06:43
  • Again, concurrency is a huge topic. Look up "isolation level" and `UPDLOCK` if you are using SQL Server. – Charlieface Jul 19 '22 at 09:04
  • Also search "race condition" for more information. One such discussion [here](https://sqlperformance.com/2020/09/locking/upsert-anti-pattern) though with a different focus. – SMor Jul 19 '22 at 11:03

0 Answers0