Let's implement a basic hotel reservation system with 2PC using pessimistic and optimistic locks in Go

Photo by Georg Bommeli on Unsplash

Hello all! In this article, I will implement 2PC (Two-Phase Commit) with pessimistic and optimistic locking in Go within a basic hotel reservation system. In this system, we will focus on creating a reservation process and will use the PostgreSQL database. You can find the source code here!

Figure: Problem space

To understand the problem and its solutions better, first, we will start with monolith architecture (only one database) and then microservice architecture (multiple databases). Let's get started!

We have two tables named as room_type_inventory and reservation

• room_type_inventory table includes hotel room reservations and rooms. There are some important points for its data model. First, users reserve a type of room in a given hotel (standard room, king-size room, two-bedroom, etc.), and room numbers are given only at user check-in. Second, we keep one row per date. In this way, managing reservations with a given date range and queries is easy.

Figure: room_type_inventory table

(hotel_id, room_type_id, and date) are used as composite primary keys. It has some example records, as shown below.

• reservation table includes user reservation data.

Its field is self-explanatory. Status indicates reservation status. It can be pending , cancelled , paid , refunded and rejected .

Implement Create Reservation in the Monolith

Let's start with monolith architecture. It has only one database.

Figure: Create reservation flow

In the implementation step, all we can do is use transactions for room_type_inventory and reservation table. I implemented this like

Figure: use local transaction

You can find implementation here.

Okay, fine 😌. What about the user clicks the "Create Reservation" button multiple times? If we don't solve this problem, at least two reservations will be made 😢. We must implement some idempotency (applying the operation several times always produces the same result) mechanism here! 🤔

There are a couple of solutions to this problem. One of them is making reservation_id a primary key. In this way, if the user tries to insert multiple records with the same reservation ID, it gets pq: duplicate key value violates unique constraint "reservation_pk" error.

Figure: trying to make the same reservation multiple times

What about multiple users trying to book the same room simultaneously? 😕. We need to solve that one, too. To understand the problem better, assume that the database isolation level is not serializable, and as ACID requires database transactions, they must complete their tasks independently from other transactions. As a result of this, a race condition occurs in the following.

Figure: Race condition occurs

We need to implement some locking mechanism here. There are a couple of techniques. We will explore two of them. Pessimistic Locking and Optimistic Locking.

Pessimistic and optimistic concurrency control is the difference between apologizing and asking permission. Pessimistic strategies request access to the resource and will only act based on the response, while optimistic strategies assume they have access to the resource, and when they don’t, they apologize by acting accordingly. [1]

Pessimistic Locking

Pessimistic concurrency strategies lock the resource upfront. The services request access to the resource; if it is available, the application locks it, and it becomes unavailable for every other access that might happen until the application frees the resource. Suppose the resource is unavailable (another consumer or thread already has a lock on it). In that case, the access will fail, and the application will either wait to release the lock or fail the operation. [2]

To implement pessimistic locking in PostgreSQL, we can use the FOR UPDATE statement. All we need to do is lock the row we want to change.

checkInventoryQuery := `
   SELECT date, total_inventory, total_reserved
   FROM room_type_inventory
   WHERE hotel_id = $1 and room_type_id = $2 AND date between $3 AND $4
   FOR UPDATE`

Figure: Pessimistic Locking Example

You can find implementation here.

There is a couple of good documentation on using it; I strongly recommend this to check it out.

We can also achieve pessimistic concurrency with distributed locks by resorting to an external tool like Consul or ZooKeeper.

Unfortunately, pessimistic locking is not a silver bullet that fits everywhere perfectly. Let's review the pros and cons of it.

Pros

• Prevents conflicts between concurrent business transactions by allowing only one business transaction at a time to access data.
• It is appropriate when the chance of conflict is high, or the expense of a conflict is unacceptable.

Cons

• Deadlock may occur. You must do deadlock-free implementation, like providing lock time or implementing a deadlock detection mechanism.
• It affects database performance if transactions lock the resource long scalability may hurt.

Optimistic Concurrency Control

Optimistic concurrency strategies assume there is no concurrency and act when concurrency occurs. Typically, there is no locking involved; the flow of the application runs without synchronization. When the application is about to persist the changes, it validates if anything has changed since the start of the operation. If it did, the application aborts or retries the operation. [3]

A good way of thinking about this is that Optimistic lock is about conflict detection, while a pessimistic lock is about conflict prevention.

Optimistic locks usually base their conflict detection on a version marker on the data. This can be a timestamp or a sequential counter. I implemented it with an integer version column in the room_type_inventory table. First, we can get the latest version value with the Select query; second, use it to update the query's where clause.

checkInventoryQuery := `
   SELECT date, total_inventory, total_reserved, version
   FROM room_type_inventory
   WHERE hotel_id = $1 and room_type_id = $2 AND date between $3 AND $4`

updateInventoryQuery := `
  UPDATE room_type_inventory
  SET total_reserved = total_reserved + 1, version = version + 1 
  WHERE hotel_id = $1 AND room_type_id = $2 AND date between $3 AND $4 
    AND version = $5`

Figure: Optimistic Locking Example

You can find implementation here.

One of the nice things about the optimistic locking pattern that you can extend it so the client passes the version number that they expect in an If-Not-Match or X-Expected-Version header. In certain applications, this can be useful to help the client ensure they are not sending their update request based on outdated information. [4]

Let's review the pros and cons of it.

Pros

• it shines in environments with low chances of concurrency. They don't involve locking and often require retrying the process when concurrency is detected.

Cons

• it has a performance penalty in high concurrency environments.

Having one database and using transactions with a couple of concurrency controls is nice. What about how we can manage this in a distributed environment like multiple databases in microservices? Let's take a look 👀.

Implement Create Reservation in the Microservices

We have two databases: inventory and reservation.

• The inventory database has only one table, room_type_inventory, with the same fields mentioned above.
• The reservation database has only one table, reservation, with the same fields mentioned above.

Figure: microservice architecture

In this architecture, handling data consistency is very challenging. What about we reserve some inventory but cannot insert any reservation? We must address these failure cases. There are a couple of solutions for handling data inconsistency in the microservice environment: 2PC (two-phase commit) and Saga (Choreographed and Orchestrated variants). In this article, I implement 2PC with PostgreSQL prepared transaction feature. 🚀

2PC (Two Phase Commit)

Two-phase commit is an algorithm for achieving atomic transaction commit across multiple nodes — i.e., to ensure that either all nodes commit or all nodes abort. [4]

We can implement 2PC with pessimistic or optimistic locking, as mentioned above.

Figure: Microservice architecture

Before diving into the implementation, let's look at how to use the prepared transaction feature in PostgreSQL. First of all, you need to pass max_prepared_transactions config to the PostgreSQL server. After that, you can use, for example

BEGIN;
UPDATE room_type_inventory
SET total_reserved = total_reserved + 1
WHERE hotel_id = 100 AND room_type_id = 1 AND date between '2023-10-12' AND '2023-10-13';
PREPARE TRANSACTION 'transaction-id';

COMMIT PREPARED 'transaction-id'; # to commit update transaction
ROLLBACK PREPARED 'transaction-id'; # to rollback update transaction

BEGIN;
INSERT INTO reservation (reservation_id, hotel_id, room_type_id, start_date, end_date, status)
VALUES ('65ac63c6-0f65-42c2-a05c-78a1ac9a384a', '100', '1', '2023-10-12', '2023-10-13', 'pending_pay');
PREPARE TRANSACTION 'transaction-create-id';

COMMIT PREPARED 'transaction-create-id'; # to commit insert transaction
ROLLBACK PREPARED 'transaction-create-id'; # to rollback insert transaction

You should specify the transaction id.

You can also query living-prepared transactions in the pg_prepared_xacts table

SELECT * from pg_prepared_xacts;

In the implementation step, I connect two different database in the same application for simplicity.

func (d *defaultService) CreateReservation(ctx context.Context, reservation *Reservation) error {
   inventoryTxID := uuid.NewString()
   reservationTxID := uuid.NewString()

   _, err := d.inventoryService.UpdatePrepared(ctx, reservation, inventoryTxID)
   if err != nil {
      d.inventoryService.RollbackPrepared(ctx, inventoryTxID)
      return err
   }

   if err = d.reservationService.CreatePrepared(ctx, reservation, reservationTxID); err != nil {
      d.inventoryService.RollbackPrepared(ctx, inventoryTxID)
      return err
   }

   d.inventoryService.CommitPrepared(ctx, inventoryTxID)
   d.reservationService.CommitPrepared(ctx, reservationTxID)

   return nil
}

You can find pessimistic and optimistic implementations. It is the same thing. The only difference is using prepared transactions.

Thank you for reading so far 💛. All feedback is welcome 🙏

References

• System Design Interview — An Insider's Guide: Volume 2
• Practical Event-Driven Microservices Architecture
• Let's Go Further
• Designing Data-Intensive Applications

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn, and YouTube.
• Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.