Concurrency Control in Go with Ent ORM and MySQL

In this article, we'll delve into the world of concurrency control in Go, specifically focusing on the optimistic locking approach. We'll explore its implementation using Ent ORM to illustrate how to manage data consistency when multiple users interact with the same resource. Keep in mind that this example serves as a simplified illustration, and real-world booking systems involve a many of additional complexities. However, the core concepts presented here provide a solid foundation for understanding optimistic locking in Go applications. Feel free to explore the complete source code in my GitHub repository for a more in-depth look at the implementation.

Corbin was incredibly excited for his long vacation. He'd been looking forward to exploring Europe, especially the city of Rome in Italy, and witnessing the Colosseum alongside other historical landmarks. He even booked his plane tickets through JustLetGo.com, splurging on a business-class seat for a comfortable and unforgettable flying experience.

However, fate had other plans. On his departure day, Corbin hit a snag at the security checkpoint. The staff informed him that his assigned seat was already occupied by another passenger named Miles. Corbin's disappointment was undeniable. His meticulously planned vacation suddenly turned chaotic.

Upon investigation, it turned out there was a data error in JustLetGo.com's system. Both Corbin and Miles had booked the same seat and departure time, resulting in boarding passes being printed for both passengers. JustLetGo.com sincerely apologized and offered Corbin a free flight to Italy with a first-class seat as compensation.

While Corbin did end up with a higher class, his disappointment as a customer due to JustLetGo.com's unreliable booking system remained. This incident could negatively impact JustLetGo.com in several ways, such as:

• Bad customer experience
• Negative reviews from customers
• Financial loss
• Potentially losing customers forever

Corbin's story serves as a valuable lesson for JustLetGo.com to improve their booking system and ensure their customers' comfort. Corbin's disappointment could turn into loyalty if JustLetGo.com handles such situations effectively and provides satisfying solutions.

The Double Booking Problem

The incident you just read about is known as The Double Booking Problem. It occurs when two people reserve the same accommodation service at the same time. This problem is not limited to flight booking systems; it can also occur in hotel booking systems, concert tickets, personal trainer appointments, doctor appointments, and so on. The main causes of this problem are not limited to unreliable booking systems but it's also possible because manual errors or miscommunication between different parties involved in the accommodation.

Let's assume we are the engineering team of the JustLetGo.com website and we use Go, Ent ORM, and MySQL for our booking system. Let's start discussing how double booking occurs by looking at the schema and tables before any passenger has booked a flight:

// ent/schema/seat.go
func (Seat) Fields() []ent.Field {
    return []ent.Field{
        field.Int("id"),
        field.Bool("is_booked").Default(false),
        field.String("passenger_name").Optional().Nillable(),
    }
}

mysql> select * from seats;
+----+-----------+----------------+
| id | is_booked | passenger_name |
+----+-----------+----------------+
|  1 |         0 | NULL           |
|  2 |         0 | NULL           |
|  3 |         0 | NULL           |
|  4 |         0 | NULL           |
|  5 |         0 | NULL           |
|  6 |         0 | NULL           |
|  7 |         0 | NULL           |
|  8 |         0 | NULL           |
|  9 |         0 | NULL           |
| 10 |         0 | NULL           |
| 11 |         0 | NULL           |
| 12 |         0 | NULL           |
| 13 |         0 | NULL           |
| 14 |         0 | NULL           |
| 15 |         0 | NULL           |
| 16 |         0 | NULL           |
| 17 |         0 | NULL           |
| 18 |         0 | NULL           |
| 19 |         0 | NULL           |
| 20 |         0 | NULL           |
+----+-----------+----------------+
20 rows in set (0.00 sec)

The field is_booked indicates whether the airplane seat has been reserved by the passenger_name. The example above shows when no seats on the plane have been reserved yet.

When a visitor to the JustLetGo.com website purchases a plane ticket, the system will book a seat by sending a request as follows:

POST /seats HTTP/1.1
Host: api.justletego.com
Content-Type: application/json

{
  "seatID": 2,
  "passengerName": "Corbin"
}

The system will then process the request with the following flow:

// domain/seats/usecaases/book_seat.go
func (us *UseCases) BookSeat(ctx context.Context, in InBookSeat) (*OutBookSeat, error) {
    // Start Ent's transaction
    tx, err := us.db.Tx(ctx)
    if err != nil {
        return nil, err
    }

    // Find and check seat availability
    requestedSeat, err := tx.Seat.Query().Where(seat.ID(in.SeatID)).Only(ctx)
    if err != nil {
        tx.Rollback()
        return nil, err
    }

    if requestedSeat.IsBooked {
        tx.Rollback()
        return nil, ErrSeatIsBooked
    }

    // Reserve a seat
    _, err = tx.Seat.
        Update().
        Where(seat.ID(in.SeatID)).
        SetIsBooked(true).
        SetPassengerName(in.PassengerName).
        Save(ctx)

    if err != nil {
        tx.Rollback()
        return nil, err
    }

    // Return the updated data from the transaction.
    updatedSeat, err := tx.Seat.
        Query().
        Where(seat.ID(in.SeatID)).
        Only(ctx)
    if err != nil {
        tx.Rollback()
        return nil, err
    }
    tx.Commit()

    out := OutBookSeat{}
    err = mapstructure.Decode(updatedSeat, &out)
    return &out, err
}

At first glance, the flow of the BookSeat function above looks good, right? Inside the transaction, there is a query to get information about the airplane seat requested by the client, then there is a check whether the seat has been booked or not, and if it has been booked, an error will occur with the status that the seat has been booked.

Have you found the bug yet? If not, let's discuss it further by looking this code and query result.

requestedSeat, err := tx.Seat.Query().Where(seat.ID(in.SeatID)).Only(ctx)

mysql> select * from seats where id = 2;
+----+-----------+----------------+
| id | is_booked | passenger_name |
+----+-----------+----------------+
|  2 |         0 | NULL           |
+----+-----------+----------------+
1 row in set (0.05 sec)

When there are more than one requests at the same time, the query above will return the same data for each request. This is quite clear because there is no transaction that has updated the data and committed it. Therefore, the check of the requestedSeat.IsBooked condition will always return false in each request.

// This check might incorrectly evaluate to false due to potential race conditions when multiple requests occur simultaneously. 
// Even if a seat is booked by a previous request, this request might still see it as available because the data hasn't been updated yet.
if requestedSeat.IsBooked {
  tx.Rollback()
  return nil, ErrSeatIsBooked
}

The following is a sequence diagram of the case of Corbin and Miles booking an airplane seat at the same time:

The sequence diagram shows the steps that Corbin and Miles take to book seat No. 2 on an airplane.

Corbin starts by sending a request to the system to book seat No. 2. The system checks the availability of seat No. 2 and responds to Corbin that the seat is available. Corbin then books the seat.

At the same time, Miles also sends a request to book seat No. 2. The system, unaware of Corbin's ongoing transaction, checks the availability of seat No. 2 and responds to Miles that the seat is available. This happens because Corbin's booking information hasn't been written to the database yet. Miles then confirms his booking for seat No. 2.

The system then updates the database to show that seat No. 2 is booked. Therefore, the transaction that commits last will be stored as the latest data in the database table.

Optimistic Concurrency Control to the Rescue

One way to handle the double booking case, as explained in the previous section, is to use the Optimistic Locking method (also known as Optimistic Concurrency Control). As the name suggests, this method assumes that transaction conflicts will be rare and that if a conflict does occur, further action will be needed ("I am confident that this transaction will succeed because there should be no conflict, but if there is a conflict, please roll back the transaction immediately and let me know.").

To implement Optimistic Locking, we need to update the schema of the seats table by adding a version field as follows:

// ent/schema/seat.go
func (Seat) Fields() []ent.Field {
    return []ent.Field{
        field.Int("id"),
        field.Bool("is_booked").Default(false),
        field.String("passenger_name").Optional().Nillable(),
        field.Uint64("version").Default(0).Optional(),
    }
}

ALTER TABLE `seats` ADD COLUMN `version` bigint unsigned NULL DEFAULT 0;

The version field will be an indicator of when the row was last updated and also a reference for whether there has been a data update when we want to save the data back to the table. Generally, the value of the version field is a sequential number (e.g. 1, 2, 3 and so on) or a timestamp like Unix timestamp.

We need to make a few changes to the BookSeat() function:

1. Add the version value from the previous data to the WHERE clause
   This will ensure that we are only updating the row that we have read.

2. Update the version field with a Unix timestamp
   This will ensure that the version field is unique for each row.

3. Check for conflicts
   If the version field in the database is different from the version field that we read, then there has been a conflict. In this case, we should roll back the transaction and notify the user that the seat is no longer available.
   

// domain/seats/usecaases/book_seat.go
nextVersion := time.Now().UnixNano()
n, err := tx.Seat.
    Update().
    Where(seat.ID(in.SeatID), seat.Version(requestedSeat.Version)). // add `version` in our where clause
    SetIsBooked(true).
    SetPassengerName(in.PassengerName).
    SetVersion(uint64(nextVersion)). // update `version` value
    Save(ctx)

if err != nil {
    tx.Rollback()
    return nil, err
}

// check for conflicts
// no record is updated, it means there's a conflict.
if n != 1 {
    tx.Rollback()
    return nil, ErrDataUpdatedByAnotherProcess
}

-- the value of @previousVersion is from `requestedSeat.Version`
UPDATE seats
SET is_booked = 1, passenger_name = "Corbin", version = unix_timestamp()
WHERE id = 2 AND version = @previousVersion;

The main purpose of checking the version value against the previous value is to prevent dirty data. Dirty data is data that is inconsistent or inaccurate. By checking the version value, we can ensure that the data we are about to save is the most up-to-date version.

The following is a sequence diagram with an implementation of Optimistic Locking:

We can see that the UPDATE statement executed by Miles did not cause any side effects on the database. This is because the version number in the update query was different from the version number in the database. As a result, no records were updated in the database.

When this happens, we can take appropriate action. For example, we can inform the user that the seat they wanted has already been booked by another passenger and suggest that they book a different seat.

So, with the implementation of Optimistic Locking, we can prevent the double booking problem at the application level without requiring a database lock. In addition to being deadlock-free, this method also provides control when transaction conflicts occur directly without affecting performance. The drawback of this method is that we need to maintain the version value and handle the conflict logic manually.

Utilizing Ent ORM's Mixin and Hooks

The Mixin and Hooks features provided by Ent ORM can help us to maintain the version field automatically. Let's say we are going to "embed" the logic for handling the version field into the schema that we already have. The steps are as follows:

1. Hook registrations

Since we're implementing schema hooks, it's necessary to import Ent's runtime package in the main.go file or the package where you initialize the Ent client. In my case, I need to import _ "go-cc/ent/runtime" in my data package.

// data/data.go
package data

import (
  // ...
  "entgo.io/ent/dialect/sql/schema"

  _ "github.com/go-sql-driver/mysql"
  _ "go-cc/ent/runtime"
)

func NewDB(conf *Database) (*ent.Client, error) {
  // ...
}

2. Create a mixin

We name it VersionMixin and we can put it in the ent/schema package with our other schemas.

// ent/schema/mixins.go

// VersionMixin implements `version` column with unsigned int type.
type VersionMixin struct {
  mixin.Schema
}

func (VersionMixin) Fields() []ent.Field {
  return []ent.Field{
    field.Uint64("version").Default(0).Optional(),
  }
}

3. Define the hooks

Still in the same file, we can add Hooks() method that automatically update the version field value on every create or update mutation.

func (VersionMixin) Hooks() []ent.Hook {
  return []ent.Hook{
    // Hook to automatically update `version` value on every create and update mutation operation.
    func(next ent.Mutator) ent.Mutator {
      return ent.MutateFunc(func(ctx context.Context, m ent.Mutation) (ent.Value, error) {
        switch m.Op() {
        case ent.OpCreate, ent.OpUpdate, ent.OpUpdateOne:
          nextVersion := time.Now().UnixNano()

          err := m.SetField("version", uint64(nextVersion))
          if err != nil {
            return nil, fmt.Errorf("versionmixin failed to set field version: %w", err)
          }
        }

        return next.Mutate(ctx, m)
      })
    },
  }
}

4. Add the VersionMixin{} mixin to our schema

We no longer need to use field.Uint64("version") inside our Fields() method because it's already defined in the VersionMixin{}.

// ent/schema/seat.go

// Seat holds the schema definition for the Seat entity.
type Seat struct {
  ent.Schema
}

// Fields of the Seat.
func (Seat) Fields() []ent.Field {
  return []ent.Field{
    field.Int("id"),
    field.Bool("is_booked").Default(false),
    field.String("passenger_name").Optional().Nillable(),
  }
}

// Edges of the Seat.
func (Seat) Edges() []ent.Edge {
  return nil
}

func (Seat) Mixin() []ent.Mixin {
  return []ent.Mixin{
    VersionMixin{},
  }
}

5. Update our existing BookSeat() flow

n, err := tx.Seat.
  Update().
  Where(seat.ID(in.SeatID), seat.Version(requestedSeat.Version)).
  SetIsBooked(true).
  SetPassengerName(in.PassengerName).
  Save(ctx)

We can now omit the variable nextVersion and SetVersion() method call when we update the record. Our hook will take care of it.

And that's all! By leveraging Ent ORM's Mixin and Hooks features, we've successfully automated version field maintenance for optimistic locking. This combined approach simplifies optimistic locking implementation within your Ent schema.

Conclusions

Thank you for reading this article! I hope the message was easy to understand and you enjoyed reading it. I am always open to discussion if there are any mistakes, as I am still learning.

From what I have discussed, we can take away several important points. First and foremost, as engineers, it is our responsibility to create reliable systems. Reliable systems minimize unwanted business consequences such as customer loss, financial loss, and reputational damage.

One of the challenges in building reliable systems is dealing with concurrency issues. Fortunately, there are various methods and approaches to address this problem. One such approach is Optimistic Locking. As explained in this article, Ent ORM can help us implement Optimistic Locking in Go web applications by leveraging its Mixin and Hook features.

I encourage you to explore the provided code example and experiment with Ent ORM to implement Optimistic Locking in your own projects. By doing so, you can enhance the reliability and consistency of your Go web applications.

Reading References

• IGMS - Double Booking
• StackOverflow - Vlad Mihalcea's explaination and visualization
• Ent ORM's blog - Database Locking Techniques
• Honeypot - Optimistic vs. Pessimistic Concurrency