Understanding Database Locking in an Airline Seat Booking System

In this blog, we explore database locking mechanisms in an airline seat booking system using Go and PostgreSQL/YugabyteDB. Learn how to handle concurrent transactions with various locking strategies for data consistency.

locking in db
locking in db

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

Related Posts

48 Comments

  1. When I originally commented I clicked the -Notify me when new comments are added- checkbox and now each time a comment is added I get four emails with the same comment. Is there any way you can remove me from that service? Thanks!

  2. I needed to send you the tiny note just to say thanks a lot over again on the exceptional techniques you have documented on this site. It is really seriously generous with people like you to make easily precisely what many individuals could possibly have distributed for an ebook to help make some dough for their own end, specifically considering the fact that you might well have tried it if you ever considered necessary. These pointers as well worked to provide a easy way to fully grasp the rest have similar passion similar to my personal own to know very much more related to this problem. I am certain there are many more pleasurable opportunities up front for people who browse through your blog post.

  3. Simply desire to say your article is as astounding. The clearness in your post is simply nice and i can assume you are an expert on this subject. Well with your permission let me to grab your feed to keep up to date with forthcoming post. Thanks a million and please carry on the rewarding work.

  4. Приветствуем вас на нашем веб-сайте!

    Здесь вы найдёте всё необходимое для успешного
    управления своими финансами. Мы предлагаем широкий спектр финансовых продуктов, которые помогут вам достичь
    ваших целей и обеспечить стабильность в будущем.

    В нашем ассортименте представлены различные виды банковских продуктов, инвестиции,
    страхование, кредиты и многое другое.
    Мы постоянно обновляем нашу базу данных, чтобы
    вы всегда были в курсе последних тенденций
    и инноваций на финансовом рынке.

    Наши специалисты помогут вам выбрать наиболее подходящий продукт, учитывая ваши индивидуальные потребности и предпочтения.

    Мы предоставляем консультации и рекомендации, чтобы вы могли
    принять обоснованное решение и избежать возможных рисков.

    Не упустите возможность воспользоваться нашими услугами и
    откройте для себя мир финансовых возможностей!
    Заходите на наш сайт, ознакомьтесь с каталогом продуктов и начните свой путь к финансовой
    стабильности прямо сейчас!
    займ для многодетных семей

  5. Wonderful blog! I found it while surfing around on Yahoo News. Do you have any suggestions on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Thanks

  6. Great post, Uday! Your explanation of database locking with real-world airline seat booking examples is super practical. The breakdown of FOR UPDATE strategies really helps understand concurrency control. For SaaS teams building complex systems like this, clear communication is key—Creamy Animation’s SaaS video production is a great resource to simplify and showcase such concepts. Thanks for the valuable insights!

  7. I think this is one of the most important information for me. And i’m glad reading your article. But should remark on some general things, The website style is wonderful, the articles is really great : D. Good job, cheers

  9. Greetings from California! I’m bored at work so I
    decided to check out your website on my iphone during lunch break.
    I really like the information you present here and can’t wait to take
    a look when I get home. I’m amazed at how quick your blog loaded on my mobile ..
    I’m not even using WIFI, just 3G .. Anyhow, great site!

  11. I have been exploring for a bit for any high quality articles or
    weblog posts in this kind of space . Exploring in Yahoo I ultimately stumbled upon this web site.
    Reading this info So i’m glad to express that I’ve an incredibly just right uncanny
    feeling I found out just what I needed. I such a lot undoubtedly will make certain to do not
    forget this web site and give it a look on a relentless basis.

  12. I was suggested this web site by my cousin. I am not sure
    whether this post is written by him as nobody else know such
    detailed about my problem. You are amazing! Thanks!

  13. An outstanding share! I have just forwarded this onto a coworker who was
    doing a little homework on this. And he in fact ordered me dinner due to the fact that I
    found it for him… lol. So allow me to reword this….
    Thank YOU for the meal!! But yeah, thanks for spending some time to talk about this topic here on your web site.

  14. I’ve read a few just right stuff here. Certainly value bookmarking for
    revisiting. I surprise how a lot attempt you
    place to create the sort of magnificent informative site.

  16. Thanks for your marvelous posting! I actually enjoyed
    reading it, you happen to be a great author.I will ensure that
    I bookmark your blog and definitely will come back later on. I want to encourage you to ultimately continue
    your great posts, have a nice evening!

  18. Hey There. I found your blog using msn. This is a very well written article.

    I will be sure to bookmark it and return to read more of your
    useful information. Thanks for the post. I’ll definitely comeback.

  19. Hey there! I know this is somewhat off topic but I was wondering if
    you knew where I could locate a captcha plugin for my comment form?
    I’m using the same blog platform as yours and I’m having difficulty finding one?

    Thanks a lot!

  22. Hello There. I found your blog using msn. This is a very well written article.
    I’ll make sure to bookmark it and come back to
    read more of your useful info. Thanks for the post. I’ll certainly return.

  24. Wonderful article! This is the kind of information that should be shared across the web.
    Disgrace on the search engines for now not positioning this submit higher!
    Come on over and visit my site . Thank you =)

  25. Right here is the perfect blog for anybody who wants to understand
    this topic. You understand a whole lot its almost hard to argue with
    you (not that I really will need to…HaHa). You definitely put a brand new spin on a subject which has
    been discussed for decades. Great stuff, just great!

  29. Can I just say what a relief to find somebody who truly knows what they are discussing over the internet.
    You definitely realize how to bring a problem to light and make it important.

    More people should check this out and understand this side of your story.

    I was surprised you are not more popular since you most certainly possess the gift.

  36. Today, I went to the beach with my kids. I found a sea shell and gave it to
    my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the
    shell to her ear and screamed. There was a hermit crab inside and
    it pinched her ear. She never wants to go back! LoL I know this is totally
    off topic but I had to tell someone!

  37. I like the valuable information you provide in your articles.

    I will bookmark your weblog and check again here regularly.
    I’m quite certain I will learn many new stuff right here!

    Good luck for the next!

  38. Thanks for another wonderful article. The place else may anyone get that
    kind of information in such an ideal method of writing?
    I have a presentation next week, and I am at the search for
    such info.

  39. My coder is trying to persuade me to move to .net from PHP.
    I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using WordPress
    on a number of websites for about a year and am worried about switching to another platform.

    I have heard excellent things about blogengine.net.
    Is there a way I can import all my wordpress content into it?
    Any help would be greatly appreciated!

  41. Hello i am kavin, its my first occasion to commenting anyplace, when i read this
    article i thought i could also create comment due to this
    sensible paragraph.

  42. What’s up to all, for the reason that I am truly eager of
    reading this weblog’s post to be updated regularly. It includes pleasant information.

  43. What’s up to every one, for the reason that I am actually keen of reading this web site’s post to be updated regularly.

    It includes pleasant material.

  45. Good day I am so happy I found your webpage, I really found you by accident, while I
    was browsing on Askjeeve for something else, Anyways I am
    here now and would just like to say thank you for a incredible post and a all round interesting blog (I also love the theme/design), I don’t have time
    to look over it all at the moment but I have bookmarked it and also included
    your RSS feeds, so when I have time I will be back to read a
    great deal more, Please do keep up the excellent work.

  47. Hi, I do think this is an excellent web site. I stumbledupon it
    😉 I’m going to return once again since I bookmarked it. Money
    and freedom is the greatest way to change, may you be rich and continue to help other people.

  48. Very great post. I simply stumbled upon your weblog and wished to say that I’ve truly loved surfing around
    your weblog posts. In any case I will be subscribing on your rss feed and I’m hoping you write once more very soon!

Leave a Reply to xem ngay sex không giới hạn full hdCancel Reply

Your email address will not be published. Required fields are marked *