How to solve the problem of concurrent booking when switching to a real database?

Problems of concurrent booking when working with a real database are solved using transaction mechanisms and locks to avoid race conditions and double booking.

Main approaches:

• Optimistic locking: a version (version) or timestamp field is added to the table. When updating a record, it is checked that the version has not changed since reading. If it has changed, the operation is rolled back and retried.

• Pessimistic locking: when attempting to book, the row is locked (for example, SELECT ... FOR UPDATE) so that other transactions cannot modify it until the current one is finished.

• Transactions with SERIALIZABLE isolation level: ensure that parallel transactions are executed as sequential, preventing conflicts.

Example using pessimistic locking in Go with PostgreSQL:

func BookSlot(db *sql.DB, slotID int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var available bool
    err = tx.QueryRow("SELECT available FROM slots WHERE id = $1 FOR UPDATE", slotID).Scan(&available)
    if err != nil {
        return err
    }

    if !available {
        return errors.New("slot already booked")
    }

    _, err = tx.Exec("UPDATE slots SET available = false WHERE id = $1", slotID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

This approach guarantees that only one transaction can book a slot at the same time.