Understanding Database Locking in an Airline Seat Booking System

In this post, we will explore various database locking mechanisms using a real-world example: an airline seat booking system. We’ll simulate how different transaction isolation levels and locks can be applied in a concurrent environment. We will use Go for implementing our business logic and PostgreSQL or YugabyteDB for the database. This system simulates multiple users concurrently booking seats on the same flight.

Why Database Locking?

Database locking is critical in multi-transaction environments where concurrent access to shared data occurs. The primary purpose is to maintain data integrity and consistency, ensuring that operations like seat booking don’t result in race conditions or conflicts.

Types of Locks:

1. Pessimistic Locking: A lock is acquired before proceeding with a transaction. This ensures that no other transaction can modify or read the locked data until the lock is released.
2. Optimistic Locking: Rather than locking data upfront, a check is made at the end of a transaction to ensure that no conflicts occurred during the transaction.

In this post, we will focus on pessimistic locking.

Example: Airline Seat Booking System

In our airline seat booking system:

• Each flight has 120 seats.
• Multiple users concurrently attempt to book seats for the same flight.

The system is designed to handle concurrent booking attempts, where each user tries to book the first available seat.

Database Locking Mechanisms Used

We will explore four cases with different database locking strategies:

1. No Locking: No lock is acquired on rows, which can lead to race conditions.
2. FOR UPDATE: A row is locked before modifying, preventing other transactions from modifying or reading it.
3. FOR UPDATE NOWAIT: A row is locked, but if it’s already locked by another transaction, an error is thrown immediately.
4. FOR UPDATE SKIP LOCKED: Skips any locked rows and continues with available rows.

Let’s walk through these cases.

1. Case 0: No Locking

In this case, we fetch the first available seat without applying any locking. This can result in race conditions, where multiple users might attempt to book the same seat concurrently.

// Case 0: No Locking (direct selection)
row := txn.QueryRow(`
    SELECT id, name, trip_id, user_id
    FROM seats
    WHERE trip_id = 1 AND user_id IS NULL
    ORDER BY id
    LIMIT 1`)

Outcome: Multiple users might book the same seat, causing conflicts and data corruption.

2. Case 1: Locking with FOR UPDATE

In this case, we acquire a lock on the row using FOR UPDATE. This prevents other transactions from accessing or modifying the seat until the lock is released.

// Case 1: FOR UPDATE (blocks other transactions)
row := txn.QueryRow(`
    SELECT id, name, trip_id, user_id
    FROM seats
    WHERE trip_id = 1 AND user_id IS NULL
    ORDER BY id
    LIMIT 1 FOR UPDATE`)

Outcome: Other transactions trying to book the same seat will be blocked until the current transaction completes, ensuring consistency.

3. Case 2: Locking with FOR UPDATE NOWAIT

Here, we use FOR UPDATE NOWAIT. This locks the row, but if it’s already locked by another transaction, an error is thrown immediately, and the transaction fails.

// Case 2: FOR UPDATE NOWAIT (fails immediately if row is locked)
row := txn.QueryRow(`
    SELECT id, name, trip_id, user_id
    FROM seats
    WHERE trip_id = 1 AND user_id IS NULL
    ORDER BY id
    LIMIT 1 FOR UPDATE NOWAIT`)

Outcome: If a seat is locked by another transaction, an error is thrown, and the booking process stops for the current user.

4. Case 3: Locking with FOR UPDATE SKIP LOCKED

In this case, we use FOR UPDATE SKIP LOCKED, which skips any locked rows and processes the next available row. This ensures that the system continues processing other seats even when some are locked by other transactions.

// Case 3: FOR UPDATE SKIP LOCKED (skips locked rows)
row := txn.QueryRow(`
    SELECT id, name, trip_id, user_id
    FROM seats
    WHERE trip_id = 1 AND user_id IS NULL
    ORDER BY id
    LIMIT 1 FOR UPDATE SKIP LOCKED`)

Outcome: Locked rows are skipped, and the system continues processing other available seats, ensuring high throughput and minimal contention.

Full Implementation: queries.go

Here’s a sample implementation of the Book function that simulates these locking mechanisms. You can uncomment the locking case you wish to simulate:

package data

import (
	"database/sql"
	"fmt"
	"log"
)

// Book assigns the first available seat to a user.
func Book(db *sql.DB, user User) (*Seat, error) {
	txn, err := db.Begin()
	if err != nil {
		return nil, fmt.Errorf("failed to start transaction: %w", err)
	}
	defer txn.Rollback()

	// Uncomment the case you want to run:

	// Case 0: No locking (direct selection)
	row := txn.QueryRow(`
		SELECT id, name, trip_id, user_id
		FROM seats
		WHERE trip_id = 1 AND user_id IS NULL
		ORDER BY id
		LIMIT 1`)

	// Case 1: FOR UPDATE
	// row := txn.QueryRow(`
	// 	SELECT id, name, trip_id, user_id
	// 	FROM seats
	// 	WHERE trip_id = 1 AND user_id IS NULL
	// 	ORDER BY id
	// 	LIMIT 1 FOR UPDATE`)

	// Case 2: FOR UPDATE NOWAIT
	// row := txn.QueryRow(`
	// 	SELECT id, name, trip_id, user_id
	// 	FROM seats
	// 	WHERE trip_id = 1 AND user_id IS NULL
	// 	ORDER BY id
	// 	LIMIT 1 FOR UPDATE NOWAIT`)

	// Case 3: FOR UPDATE SKIP LOCKED
	// row := txn.QueryRow(`
	//	SELECT id, name, trip_id, user_id
	//	FROM seats
	//	WHERE trip_id = 1 AND user_id IS NULL
	//	ORDER BY id
	//	LIMIT 1 FOR UPDATE SKIP LOCKED`)

	var seat Seat
	err = row.Scan(&seat.ID, &seat.Name, &seat.TripID, &seat.UserID)
	if err != nil {
		return nil, fmt.Errorf("no seat available: %w", err)
	}

	// Update the seat to assign it to the user
	_, err = txn.Exec(`UPDATE seats SET user_id = $1 WHERE id = $2`, user.ID, seat.ID)
	if err != nil {
		return nil, fmt.Errorf("failed to assign seat: %w", err)
	}

	if err := txn.Commit(); err != nil {
		return nil, fmt.Errorf("failed to commit transaction: %w", err)
	}

	return &seat, nil
}

Conclusion

Different database locking mechanisms provide varying levels of concurrency control. Depending on the business requirements and concurrency level, you can choose the most appropriate locking strategy:

• No locking might be suitable in low-concurrency environments where performance is critical.
• FOR UPDATE ensures data consistency but might block other transactions.
• FOR UPDATE NOWAIT provides fast feedback for locked rows.
• FOR UPDATE SKIP LOCKED ensures high throughput by skipping locked rows.

Try implementing this in your next concurrent system and experiment with different cases to see which one fits your needs!

Reference:

https://docs.yugabyte.com/preview/explore/transactions/explicit-locking

https://github.com/DevelopersCoffee/database-concepts-in-action/tree/main/airlinecheckin