Double Booking Problem

Booking.com, Airbnb, Calendly, and 42Mentors all serve a common purpose: they allow users to book:

• Appointments
• Accommodations, meals, and other services

One of the most common yet critical issues these platforms face is the double booking problem.

The Double Booking Problem

Imagine two users accessing your system at the same time. Both of them select the same slot—April 18 at 8:00 PM—and click the Book button simultaneously. If your system does not have a mechanism to handle this, it may confirm the booking for both users. This results in a major issue! 😢

Thankfully, databases like PostgreSQL and MySQL provide built-in solutions to tackle this problem efficiently.

Using SELECT ... FOR UPDATE

SELECT id, name, school_number FROM schools;

Most developers are familiar with a standard SELECT query like the one above. However, not everyone is aware of the FOR UPDATE clause, primarily because they haven't encountered the need for it yet.

PostgreSQL provides an extensive explanation of the SELECT statement in its documentation. The FOR UPDATE clause is specifically discussed in the Locking Clause section.

SELECT ... FOR UPDATE not only retrieves a row but also locks it, preventing concurrent modifications. This helps prevent race conditions. The process follows three key steps:

1. Row Locking – The selected row is locked, ensuring no other operation can modify it.
2. Transaction Scope – The lock remains active until the transaction is either committed or rolled back.
3. Concurrent Modification – While the transaction is in progress, other transactions attempting to modify the same row are blocked.

Implementation

To demonstrate this in practice, let’s use JavaScript with PostgreSQL. Before we begin, create a table and insert some sample data into your database:

CREATE TABLE slots (
	id SERIAL PRIMARY KEY,
	name VARCHAR(150),
	isbooked BOOLEAN
)

Start your PostgreSQL server and create a route in Express.js to retrieve all available slots:

app.get('/slots', async (req, res) => {
  // Fetch all slots from the database
  const result = await pool.query('SELECT id, name, isbooked FROM slots');
  // Return the rows as a response
  res.send(result.rows);
});

Handling Slot Booking

Now, let's create a route to book a slot based on id and name:

app.put('/:id/:name', async (req, res) => {
  try {
    const id = req.params.id;
    const name = req.params.name;

    const conn = await pool.connect();
    // Fetch the slot to check if it's available
    const sql = 'SELECT id, name, isbooked FROM slots WHERE id = $1 AND isbooked = 0';
    const result = await conn.query(sql, [id]);

    // If no rows are returned, the slot is already booked
    if (result.rowCount === 0) {
      res.send({ error: 'Slot is already booked!' });
      return;
    }

    // If available, update the slot status to booked
    const sqlU = 'UPDATE slots SET isbooked = 1, name = $2 WHERE id = $1';
    const updateResult = await conn.query(sqlU, [id, name]);

    conn.release();
    res.send(updateResult);
  } catch (ex) {
    console.log(ex);
    res.sendStatus(500);
  }
});

At this stage, the system appears functional. However, there is still a major flaw: we haven’t used FOR UPDATE yet! If two users book a slot simultaneously, both will receive a success response, causing a double booking issue.

Preventing Double Booking with Transactions & FOR UPDATE

To solve this, we modify the previous implementation by using transactions and FOR UPDATE:

app.put('/:id/:name', async (req, res) => {
  try {
    const id = req.params.id;
    const name = req.params.name;

    const conn = await pool.connect();
    // Start transaction to isolate the process
    await conn.query('BEGIN');
    
    // Lock the selected slot to prevent race conditions
    const sql = 'SELECT id, name, isbooked FROM slots WHERE id = $1 AND isbooked = 0 FOR UPDATE';
    const result = await conn.query(sql, [id]);

    // If no rows are returned, the slot is already booked
    if (result.rowCount === 0) {
      res.send({ error: 'Slot is already booked!' });
      return;
    }

    // If available, update the slot status to booked
    const sqlU = 'UPDATE slots SET isbooked = 1, name = $2 WHERE id = $1';
    const updateResult = await conn.query(sqlU, [id, name]);

    // Commit the transaction before releasing the connection
    await conn.query('COMMIT');

    conn.release();
    res.send(updateResult);
  } catch (ex) {
    console.log(ex);
    res.sendStatus(500);
  }
});

Final Thoughts

As you can see, by making a small but crucial modification—using transactions and FOR UPDATE—we successfully prevent double booking.

Of course, this approach also has its drawbacks, such as potential performance overhead when handling a high number of concurrent requests. To fully optimize your solution, it’s essential to read more documentation and experiment with different database locking mechanisms.

If you found this post helpful, make sure to explore further and share this knowledge with others! 🚀