I am working on the backend of a game. Players first purchase their tickets, which are stored into the database, then they play and can win certain prizes. Each player can buy a max of 60 tickets.
A new requirement appeared, setting an upper bound for the total number of tickets per game - 10 000 tickets. The implementation would involve adding to the purchase endpoint a test checking that the total number of purchased tickets at that time + number of tickets required on current purchase is less or equal to the max number of tickets for that game, in order for a purchase to be accepted.
The problem is that, calculating the current number of purchased tickets by using count on the tickets table, the returned value may be out-of-date because in the meantime other players could have purchased tickets.
A possible solution is to lock the tickets table during the purchase, but this can have a negative impact on performance and user experience. Please let me know if you have a different idea.
If yours is a hard constraint then you must avoid multiple purchases being conducted concurrently. Probably an appropriate table lock in the DB is the best way to accomplish such serialization. There could be other alternatives (i.e. performing the serialization somewhere in the front end), but they are likely to be messier to implement, more prone to bugs, and worse-performing.
In fact, it may be difficult to make your game exhibit consistent behavior at all without appropriate locking in the DB. On the other hand, you probably don't need explicit locking. If you configure your database with an appropriate transaction isolation level then it should do all necessary locking for you.
Since there's no clear win to be had here, I recommend shelving the performance question until you can actually test your performance. If it turns out not to be good enough then you can determine from actual measurement what parts of the system can most benefit from tuning.