How to implement a seat reservation system in a cinema without double sales? What type of locking to choose?

For a seat reservation system in a cinema, the key task is to avoid double booking of the same seat. To achieve this, it is necessary to ensure the atomicity of the booking operation and proper synchronization of data access.

Locking type:

• In most cases, optimistic locking using record versioning or timestamps is suitable. This helps avoid long-term locks and improves performance.
• If high contention for the same seats is expected, pessimistic locking at the transaction level (e.g., SELECT ... FOR UPDATE) can be used to lock the seat record during booking.

Example of an optimistic locking approach:

1. When loading seat information, the client receives the current record version.
2. A booking request is sent with the version number.
3. The database checks that the version has not changed since reading.
4. If the version matches, the booking is confirmed, and the version is incremented.
5. If the version has changed, the operation is rolled back, and the client is prompted to retry.

Example of a SQL with pessimistic locking:

BEGIN TRANSACTION;
SELECT * FROM seats WHERE seat_id = ? AND show_id = ? FOR UPDATE;
-- Check if the seat is free
-- If free, update the status to booked
COMMIT;

In Java, using JPA, you can use the @Version annotation for optimistic locking or explicitly manage transactions and locks via EntityManager.

Thus, the choice of locking depends on the load and performance requirements, but optimistic locking is often preferred for reservation systems.