Why 20 Database Connections Don’t Corrupt Your Ticket Count ?

Imagine you are building a ticket booking application. You have 30 tickets available for a popular concert. Tomcat can accept 200 web requests at the same time. HikariCP is configured with a maximum pool of 20 database connections.

When ticket sales open, hundreds of users click “Book Now” at the exact same millisecond. A question many developers ask is: If only 20 database transactions can run at the exact same time, how do we stop the system from selling more than 30 tickets?

Let’s try to break it down, problem is the “Naive” Code

// 1. Read current ticket count from the database
Event event = repository.findById(id);
// 2. Subtract 1 ticket in Java memory
event.setAvailableTickets(event.getAvailableTickets() - 1);
// 3. Save the updated count back to the database
repository.save(event);

Above code seems to be correct on paper, but under heavy traffic, it breaks completely. If multiple transactions read the row before any of them commits, they can all read the same ticket count and overwrite each other’s updates. So, all 20 threads read the database before any thread finishes saving and creates a Lost Update Problem and it leads to massive overbooking.

Every thread subtracts 1 from 30 in Java memory, and every thread writes 29 back to the database. Even though 20 users bought tickets, the ticket counter only goes down from 30 to 29!

Thread 1 - -> Reads 30
Thread 2 - -> Reads 30
Thread 3 - -> Reads 30
…
Thread 20 - -> Reads 30

So, what do we do now ?

1. Pessimistic Locking (SELECT FOR UPDATE)

To fix this, we can tell the database engine: “I am reading this row because I plan to update it soon. Do not let another transaction acquire a conflicting lock or modify this row until my transaction finishes..”

By using SELECT ... FOR UPDATE (or @Lock(LockModeType.PESSIMISTIC_WRITE) in Spring Data JPA), the database puts an exclusive lock on the ticket row

Thread 1 - -> Reads row & Locks it - -> Updates to 29 - -> Commits & Releases Lock
Thread 2 - -> (Waits for Lock) - -> Reads 29 - -> Updates to 28 - -> Commits & Releases Lock
Thread 3 - -> (Waits for Lock) - -> Reads 28 - -> Updates to 27…

• Pros: 100% data consistency. Zero overbooking.
• Cons: Because every other thread must wait in line for the lock to release, performance slows down under high traffic.

2. Atomic SQL Update

A simpler and faster way is to let the database perform the calculation inside a single SQL statement instead of pulling the data into Java memory first.

UPDATE event 
SET available_tickets = available_tickets - 1 
WHERE id = 101 AND available_tickets > 0;

This single query is atomic. The database engine handles the row update safely one by one. The WHERE available_tickets > 0 condition acts as a built-in safety guard. Once the count hits 0, the query simply affects 0 rows. Your application checks this result and safely returns a "Sold Out" message to the user.

• Pro: Extremely fast, simple, and avoids keeping long-running locks in Java code.
• Con: Ideal for standard database loads, but if thousands of users fight over 1 single row, database lock contention can still occur.

3. Redis as a Gatekeeper

It is not necessary to query the relational database to verify ticket count on every click. Instead, they store the ticket inventory counter in Redis an in-memory storage.

30 — -> 29 — -> 28 — -> … — -> 0 — -> REJECT (-1)

• When a user clicks “Book Now”, the application calls an atomic decrement (DECRBY) in Redis.
• Because Redis processes commands one at a time on a single thread, making operations like DECRBY atomic, the first 30 requests get valid numbers (29, 28, 27... 0).
• Requests 31 through 200 receive negative numbers (-1, -2...). They fail fast at the application code check in 1ms and never touch the database!

@Transactional(rollbackFor = Exception.class) // Protects multi-table DB saves
public BookingResponse bookTicket(Long userId, Long eventId) {
   String redisKey = "ticket_count:" + eventId;
   // Step 1: Atomic Decrement in Redis
   Long remainingTickets = redisTemplate.opsForValue().decrement(redisKey);
   // Step 2: Handle Sold-Out Condition
   if (remainingTickets == null || remainingTickets < 0) {
     redisTemplate.opsForValue().increment(redisKey);
     throw new SoldOutException("Sorry, tickets are completely sold out!");
   }

   // Step 3: Attempt Database Operations
   try {
      Booking booking = new Booking(userId, eventId, BookingStatus.CONFIRMED);
      // If this throws an exception (e.g., DB down, Constraint Violation):
      return bookingRepository.save(booking);
   } catch (Exception e) {
      // Step 4: REVERT REDIS COUNTER IF DB SAVE FAILS!
      // Give the ticket back to Redis so another user can buy it!
      redisTemplate.opsForValue().increment(redisKey); 
      throw new BookingFailedException("Failed to reserve ticket. Please try again.", e);
   }
  }
}

The 30 winning requests don’t even run an UPDATE query on the database—they simply execute an INSERT statement to append a receipt into a bookings table.

• Pro: Easily handles tens of thousands of requests per second while protecting the relational database from crashing.
• Con: Adds architecture complexity by introducing Redis alongside your SQL database.

Final Thoughts:

• Pessimistic Locking is safe and works well for low-concurrency applications.
• Atomic SQL Updates are the best standard choice for most relational database apps.
• Redis Inventory Counters are the enterprise standard for high-throughput platforms.

The most important takeaway is that data consistency depends on choosing the right strategy.