--- title: "Double Booking Problem" description: "Platforms like Booking.com, Airbnb, Calendly, and 42Mentors all serve a common purpose: they help users book appointments, accommodations, and other services." canonical_url: "https://otabek.io/blogs/double-booking-problem" md_url: "https://otabek.io/blogs/double-booking-problem.md" language: "en" last_updated: "2024-05-02" tags: ["Backend"] --- # Double Booking Problem [Booking.com](https://www.booking.com), [Airbnb](https://www.airbnb.com), [Calendly](https://www.calendly.com), and [42Mentors](https://www.42.uz/mentors) all do the same thing - they help you book stuff: - Meetings with important people - Hotel rooms, meals, and fun activities But these websites all face one BIG problem: the **double booking nightmare**. 😱 ![Booking website example](https://telegra.ph/file/2b90f53ac717de5854909.jpg) ### The Double Booking Problem Picture this: Two people are on the same website at the exact same time. Both want to book a haircut on April 18 at 8:00 PM. Both click the big "Book Now" button at the same moment. Without special protection, the system might say "YES!" to both people. Now the poor hairdresser has two customers showing up at the same time! One happy customer, one angry customer. Not good! 😬 Luckily, smart databases like PostgreSQL and MySQL have secret weapons to fight this problem. ### **Using SELECT ... FOR UPDATE** ```sql SELECT id, name, school_number FROM schools; ``` Most coders know how to write normal SELECT queries like this one. But not everyone knows about the magic **FOR UPDATE** words that can prevent double booking disasters! PostgreSQL explains all about SELECT in its [documentation](https://www.postgresql.org/docs/current/sql-select.html). The powerful **FOR UPDATE** feature is explained in the [Locking Clause](https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE) section. **SELECT ... FOR UPDATE** doesn't just get information - it also **LOCKS** it, preventing others from changing it at the same time. It works in three simple steps: 1. **Row Locking** – The selected row gets a "DO NOT TOUCH" sign on it 2. **Transaction Scope** – The lock stays active until you're done with your changes 3. **Concurrent Modification** – While you're working, anyone else trying to modify the same data has to wait their turn ### **Let's See It In Action** Let's use **JavaScript** with **PostgreSQL** to see how this works. First, create a simple table in your database: ```sql-run CREATE TABLE slots ( id SERIAL PRIMARY KEY, name VARCHAR(150), isbooked BOOLEAN ) ``` Now, let's create a route in **Express.js** to see all available slots: ```js app.get('/slots', async (req, res) => { // Ask the database for all slots const result = await pool.query('SELECT id, name, isbooked FROM slots') // Send the results back to the user res.send(result.rows) }) ``` ### **The Booking Problem** Now, let's create a route to book a slot using its `id` and the customer's `name`: ```js app.put('/:id/:name', async (req, res) => { try { const id = req.params.id const name = req.params.name const conn = await pool.connect() // Check if the slot is 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 returned, the slot is already taken if (result.rowCount === 0) { res.send({ error: 'Sorry! This slot is already booked!' }) return } // If available, mark it as 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) } }) ``` This code looks good at first glance, but it has a HUGE problem: **we haven't used FOR UPDATE yet**! If two people try to book the same slot at the same time, both might see it's available, both will try to book it, and BOOM - double booking disaster! 💥 ### **The Solution: Transactions & FOR UPDATE** Let's fix our code with transactions and the magic **FOR UPDATE** clause: ```js app.put('/:id/:name', async (req, res) => { try { const id = req.params.id const name = req.params.name const conn = await pool.connect() // Start a transaction - think of this like putting up a "Working here!" sign await conn.query('BEGIN') // Lock the row with FOR UPDATE so nobody else can change it 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 returned, the slot is already booked if (result.rowCount === 0) { // Don't forget to end your transaction if you're exiting early! await conn.query('ROLLBACK') conn.release() res.send({ error: 'Sorry! This slot is already booked!' }) return } // If available, mark it as booked const sqlU = 'UPDATE slots SET isbooked = 1, name = $2 WHERE id = $1' const updateResult = await conn.query(sqlU, [id, name]) // Finish the transaction - like saying "I'm done working here!" await conn.query('COMMIT') conn.release() res.send(updateResult) } catch (ex) { console.log(ex) res.sendStatus(500) } }) ``` ### **The Magic Explained** That small change - adding transactions and **FOR UPDATE** - makes all the difference! Now when User A and User B try to book the same slot at the exact same time: 1. User A's query gets there first and LOCKS the row with `FOR UPDATE` 2. User B's query has to wait patiently until User A is done 3. User A books the slot and finishes their transaction 4. User B's query finally runs, but now the slot is already booked, so they get an error message No more double bookings! 🎉 ### **Final Thoughts** This approach works great, but it does have some downsides. If you have LOTS of people trying to book things at once, the `FOR UPDATE` locks might slow your system down a bit. For most apps, this solution is perfect. But for super high-traffic sites (like Airbnb during holiday season), you might need even more advanced techniques. If you found this helpful, please share it with your friends who are building booking systems! And remember, a little SQL magic can save you from big booking headaches! 🧙‍♂️ --- ```quiz { "quiz": { "id": "double-booking-quiz", "title": "Double Booking Problem Quiz", "description": "Test your understanding of database locking", "questions": [ { "id": "q1", "type": "single-choice", "question": "What problem does SELECT ... FOR UPDATE solve?", "options": [ { "id": "a", "text": "Double booking - two users booking the same slot", "description": "" }, { "id": "b", "text": "Slow query performance", "description": "FOR UPDATE is about preventing race conditions, not speed." }, { "id": "c", "text": "Database connection limits", "description": "FOR UPDATE handles locking, not connection limits." }, { "id": "d", "text": "Data backup", "description": "FOR UPDATE is for row locking, not backups." } ] }, { "id": "q2", "type": "drag-drop", "question": "Arrange the steps of using FOR UPDATE correctly:", "items": [ { "id": "begin", "content": "BEGIN transaction" }, { "id": "select", "content": "SELECT ... FOR UPDATE (locks row)" }, { "id": "check", "content": "Check if slot is available" }, { "id": "update", "content": "UPDATE the row" }, { "id": "commit", "content": "COMMIT transaction" } ] }, { "id": "q3", "type": "single-choice", "question": "What happens when User B tries to access a row locked by User A?", "options": [ { "id": "a", "text": "User B gets an error immediately", "description": "User B waits for the lock to be released, not an immediate error." }, { "id": "b", "text": "User B has to wait until User A finishes", "description": "" }, { "id": "c", "text": "Both users can modify at the same time", "description": "That's exactly what FOR UPDATE prevents." }, { "id": "d", "text": "The database crashes", "description": "Locking is normal database behavior, not a crash." } ] }, { "id": "q4", "type": "drag-fill", "question": "Complete the SQL query to lock a row:", "template": "SELECT id, name FROM slots WHERE id = $1 {{b1}} {{b2}}", "options": [ { "id": "opt1", "content": "FOR" }, { "id": "opt2", "content": "UPDATE" } ], "blanks": [ { "id": "b1" }, { "id": "b2" } ] }, { "id": "q5", "type": "single-choice", "question": "What should you do if exiting early from a transaction?", "options": [ { "id": "a", "text": "Just return without doing anything", "description": "You should ROLLBACK to properly end the transaction." }, { "id": "b", "text": "Call ROLLBACK to end the transaction", "description": "" }, { "id": "c", "text": "Call COMMIT anyway", "description": "COMMIT is for successful completion. Use ROLLBACK for early exit." }, { "id": "d", "text": "Delete the row", "description": "Deleting isn't the answer - use ROLLBACK." } ] }, { "id": "q6", "type": "single-choice", "question": "What is a potential downside of using FOR UPDATE?", "options": [ { "id": "a", "text": "It can slow down high-traffic systems due to locks", "description": "" }, { "id": "b", "text": "It doesn't work with PostgreSQL", "description": "FOR UPDATE works great with PostgreSQL." }, { "id": "c", "text": "It requires special hardware", "description": "FOR UPDATE is a software feature, no special hardware needed." }, { "id": "d", "text": "It can only lock one row per database", "description": "You can lock multiple rows in different transactions." } ] } ] }, "answers": { "q1": { "correctOptionIds": ["a"] }, "q2": { "correctOrder": ["begin", "select", "check", "update", "commit"] }, "q3": { "correctOptionIds": ["b"] }, "q4": { "correctPlacements": { "b1": "opt1", "b2": "opt2" } }, "q5": { "correctOptionIds": ["b"] }, "q6": { "correctOptionIds": ["a"] } } } ``` ## Sitemap See the full [Markdown sitemap](/sitemap.md) for all pages.