Theater Reservation

Theater, courtesy of http://en.wikipedia.org/wiki/Roman_theatre_(structure)#/media/File:Plan_Romeins_theater.gif

This schema pattern is based around the concept of buying tickets for a theater. It’s a reservation-based schema where the user can pick their own seats and theater session, and the seats are reserved until the user either checks out the cart or the cart expires.

It’s a variation of the e-commerce shopping cart for reservations, but has some unique twists to it that makes it a useful pattern to know.

Schema Observations

  • Allows for any type of seated venue layout for reservation.

Schema

This schema has different components. We have a collection called theaters that contain documents representing theaters. For simplicity’s sake, each theater only has a single scene. Below is an example document for the theater The Royal.

{
    "_id" : 1
  , "name" : "The Royal"
  , "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
  , "seatsAvailable" : 80
}

The schema is fairly straightforward.

Schema Attribute Description
name Name of the theater
seats A 2d array, representing the available seating in the theater
seatsAvailable The total number of seats in the theater

The most interesting field is the seats field, which contains the map of the theater. It’s worth noticing that although we have picked a completely uniform seat layout with the same number of seats in each row, this schema would allow you to create any layout you wish. For example, you could have the following seat layout.

  , "seats" : [
    , [ 0 ]
    , [ 0, 0 ]
    , [ 0, 0, 0 ]
    , [ 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0 ] ]

Each theater has multiple sessions representing an actual play session. In this schema these are represented by documents in the sessions collection. Below is an example of session for The Royal theater.

{
    "_id" : ObjectId("5500632d2dc02be024ba5c66")
  , "theaterId" : 1
  , "name" : "Action Movie 5"
  , "description" : "Another action movie"
  , "start" : ISODate("2015-03-11T15:45:49.103Z")
  , "end" : ISODate("2015-03-11T15:45:49.103Z")
  , "price" : 10
  , "seatsAvailable" : 80
  , "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
  , "reservations" : [ {
        "_id": ObjectId("5500632d2dc02be024ba5c66")
      , "seats": [ [ 1, 5 ], [ 1, 6 ], [ 1, 7 ] ]
      , "price" : 10
      , "total" : 30
    } ]
}

The schema for a session contains the following fields.

Schema Attribute Description
theaterId The Id of the theater the session belongs to
name The name of the show
description The description of the show
start When does the show start
end When does the show end
price The price of the show
seatsAvailable The number of seats left in this session
seats The 2d array seat map, showing which seats have been booked
reservations Any current reservations for this session that are in a cart

Notice that the seats field mirrors the seating layout of its parent theater document. In a session, it’s used to keep track of what seats have been reserved. The reservations array is an array of current reservations for these sessions before the carts have been checked out.

The shopping cart is fairly straight forward. Let’s look at an example cart with a single reservation.

{
    "_id" : 1
  , "state" : "canceled"
  , "total" : 30
  , "reservations" : [ {
        "sessionId" : ObjectId("5500632d2dc02be024ba5c66")
      , "seats" : [ [ 1, 5 ], [ 1, 6 ], [ 1, 7 ] ]
      , "price" : 10
      , "total" : 30
    } ]
  , "modifiedOn" : ISODate("2015-03-11T15:45:49.105Z")
  , "createdOn" : ISODate("2015-03-11T15:45:49.104Z")
}

The schema for a cart contains the following fields.

Schema Attribute Description
state The current state of the cart, one of active, done, canceled or expired
total The total price of the cart
reservations An array of reservation documents for sessions. Each document includes the sessionId, the seats reserved, the price per seat and the total for that reservation
modifiedOn The date the last time the cart was modified
createdOn The date the cart was created

The most interesting field here is the reservations field, which contains all the current reservations for the cart. Each reservation is for a specific session in a theater.

Operations

Create a new session

Creating a new session for a specific theater requires us to use the original theater template to create a new session. Let’s consider that we have a theater document with _id of 1 and we are creating a new session that will show Action Movie 5.

var theaterId = 1;

var theaters = db.getSisterDB("booking").theaters;
var sessions = db.getSisterDB("booking").sessions;

var theater = theaters.findOne({"_id": theaterId});
sessions.insert({
    , "name" : "Action Movie 5"
    , "description" : "Another action movie"
    , "start" : ISODate("2015-03-11T15:00:00.000Z")
    , "end" : ISODate("2015-03-11T16:00:00.000Z")
    , "price" : 10
    , "seatsAvailable" : theater.seatsAvailable
    , "seats" : theater.seats
    , "reservations" : []
  });

Example 1: Add a new session

Reserving seats

We are going to reserve some seats for a specific session. To do this, we first need to establish if the session has the desired seats available before adding them to the cart. We are going to reserve the 6th and 7th seats on the 2nd row. (Remember that we are 0 indexed in the arrays and that the first row is the first array) Looking at the seat map for the session, we have marked the seats we are trying to reserve with an X.

{
  ...
  , "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, X, X, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
    , [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
  ...
}
var sessionId = 1;
var cartId = 1;

var seats = [[1, 5], [1, 6]];
var seatsQuery = [];
var setSeatsSelection = {};

var sessions = db.getSisterDB("booking").sessions;
var session = sessions.find({_id: sessionId});

for(var i = 0; i < seats.length; i++) {
  var seatSelector = {};
  var seatSelection = 'seats.' + seats[i][0] + '.' + seats[i][1];
  // Part of $and query to check if seat is free
  seatSelector[seatSelection] = 0;
  seatsQuery.push(seatSelector);
  // Part of $set operation to set seat as occupied
  setSeatsSelection[seatSelection] = 1;  
}

var result = sessions.update({
      _id: sessionId
    , $and: seatsQuery
  }, {
      $set: setSeatsSelection
    , $inc: { seatsAvailable: -seats.length }
    , $push: {
      reservations: {
          _id: cartId
        , seats: seats
        , price: session.price
        , total: session.price * seats.length
      }
    }
  });

// Failed to reserve seats
if(result.nModified == 0) ...
// Reservation was successful
if(result.nModified == 1) ...

Example 2: Reserve seats in a session

If the result returns a nModified with the value of 1, we’ve successfully reserved seats in the session. If nModified equals 0, we could not honor the reservation request (some seats might have been sold out). At this point, an application might choose to retrieve the session document to show the updated view of available seating.

It might look more complicated than previous schemas, but it’s fairly straightforward when you break it down. The main thing to remember is that we need to ensure that all seats can be reserved at the same time. To achieve this, we use the $and operator to create an array of all the seats that need to be free in order for the update to be successful. If the selector matches, we need to update all those seats using the $set operator, which that takes a document of key value pairs. The actual update statement we create looks like this.

sessions.update({
      _id: 1
    , $and: [{'seats.1.5': 0}, {'seats.1.6': 0}]
  }, {
      $set: {'seats.1.5': 1, 'seats.1.6': 1}
    , $inc: { seatsAvailable: 2 }
    , $push: {
      reservations: {
          _id: 1
        , seats: [[1, 5], [1, 6]]
        , price: 10
        , total: 20
      }
    }
  });

Update cart on successful session reservation

We successfully reserved the seats. Now we need to add those reservations to the shopping cart.

var sessionId = 1;
var cartId = 1;

var seats = [[1, 5], [1, 6]];
var seatsQuery = [];
var setSeatsSelection = {};

var sessions = db.getSisterDB("booking").sessions;
var carts = db.getSisterDB("booking").carts;
var session = sessions.find({_id: sessionId});

var result = carts.update({
    _id: cartId
  }, {
      $push: {
        reservations: {
            sessionId: sessionId
          , seats: seats
          , price: session.price
          , total: session.price * seats.length
        }
      }
      , $inc: { total: session.price * seats.length }
      , $set: { modifiedOn: new Date() }
  })

// Failed to add reservation to cart
if(result.nModified == 0) ...
// Successfully added reservation to cart
if(result.nModified == 1) ...

Example 3: Add reservation to the cart

As in the previous operation where we tried to reserve seats in a specific session, if nModified equals 1, it means we successfully managed to add the reservation to the cart. If nModified equals 0, we failed to add the reservation (maybe the cart got destroyed). If we failed to add the reservation to the cart, we need to release the reservation on the session document.

Release the seat reservation for a session

To release the reservation, we need to set all the seats we marked with 1 back to 0 for the previously reserved seats.

var sessionId = 1;
var cartId = 1;

var seats = [[1, 5], [1, 6]];
var setSeatsSelection = {};

for(var i = 0; i < seats.length; i++) {
  setSeatsSelection['seats.' + seats[i][0] + '.' + seats[i][1]] = 0;
}

var sessions = db.getSisterDB("booking").sessions;
var result = sessions.update({
    _id: sessionId
  }, {
      $set: setSeatsSelection
    , $pull: { reservations: { _id: cartId } }
  });

// Failed to release reservation
if(result.nModified == 0) ...
// Succeded in releasing reservation
if(result.nModified == 1) ...

Example 4: Release all the reservations for a cart

To release the reservation we set all the seats in the reservation to 0 and pull the cart reservation from the reservations array in the sessions documents.

Checkout cart

The checkout process consists of a couple of steps. The first one is to create a receipt document in the receipt collection. After creating the receipt, we commit all the reservations on the session before finally setting the cart to done. Let’s look at how we create a receipt document.

var cartId = 1;

var carts = db.getSisterDB("booking").carts;
var receipts = db.getSisterDB("booking").receipts;

var cart = carts.findOne({_id: cartId});

receipts.insert({
      createdOn: new Date()
    , reservations: cart.reservations
    , total: cart.total
  });

Example 5: Create receipt

Next, we need to remove all the reservations in the cart from the sessions so as to finalize the reservation of the seats.

var cartId = 1;

var sessions = db.getSisterDB("booking").sessions;
session.update({
    'reservations._id': cartId
  }, {
    $pull: { reservations: { _id: id } }
  }, false, true);

Example 6: Commit session reservations

This pulls out all the reservations in all sessions where the reservations._id field matches the cartId.

Finally, we mark the cart as done.

var cartId = 1;

var carts = db.getSisterDB("booking").carts;
carts.update({
    _id: cartId
  }, {
    $set: { state: 'done' }
  });

Example 7: Mark cart as done

Expire carts

If carts are abandoned but contain reservations, we need to release those reservations again so other customers can have the opportunity of reserving them.

var cutOffDate = new Date();
cutOffDate.setMinutes(cutOffDate.getMinutes() - 30);

var cartsCol = db.getSisterDB("booking").carts;
var sessionsCol = db.getSisterDB("booking").sessions;

var carts = cartsCol.find({
    modifiedOn: { $lte: cutOffDate }, state: 'active'
  });

// Process all carts
while(carts.hasNext()) {
  var cart = carts.next();

  // Process all reservations in the cart
  for(var i = 0; i < cart.reservations.length; i++) {
    var reservation = cart.reservations[i];
    var seats = reservation.seats;
    var setSeatsSelection = {};

    for(var i = 0; i < seats.length; i++) {
      setSeatsSelection['seats.' + seats[i][0] + '.' + seats[i][1]] = 0;
    }

    // Release seats and remove reservation
    sessionsCol.update({
      _id: reservation._id
    }, {
        $set: setSeatsSelection
      , $pull: { reservations: { _id: cart._id }}
    });
  }

  // Set the cart to expired
  cartsCol.update({
    _id: cart._id
  }, {
    $set: { status: 'expired' }
  });
}

Example 8: Expiring shopping carts

The first step is to retrieve all the expired carts. Next we return all the reservations in the carts to their respective sessions (setting the released seats to 0 in the session documents). Finally we set the cart to the expired state.

This will correctly release any reservations held by timed out shopping carts.

Indexes

Besides using the _id field to query for carts in the carts collection, we only use the state field for when we are processing expired carts. We should add an index on this field to make the expired carts queries as efficient as possible.

var col = db.getSisterDB("booking").carts;
col.ensureIndex({state:1});

Example 9: Create the state field index

For the sessions collection we look up sessions by the reservations._id field, so we should add a multikey index for this field.

var col = db.getSisterDB("booking").sessions;
col.ensureIndex({"reservations._id":1});

Example 10: Create the reservations._id field index

For the theaters and receipts collection we only access documents by the _id field in this example so no additional indexes are needed.

Scaling

Secondary Reads

All the operations against the cart are write operations so secondary reads are not useful for this schema as they might show out of date carts.

The theaters could be read from secondaries but the sessions should be read from the primary, otherwise they might return an out of date view of the current state of reserved seats.

Sharding

Sharding the carts is fairly straight forward, as each cart is self-contained. The easiest shard key for this is a hashed _id key to allow for writing to be as randomly distributed as possible using all the shards.

var admin = db.getSisterDB("admin");
db.runCommand({enableSharding:'booking'});
db.runCommand({
    shardCollection: 'booking.carts'
  , key: {_id:'hashed'}
});

Example 11: Hashed _id shard key

You could also shard the theater and sessions as they are self-contained documents. A good key here would be the hashed _id key.

Performance

A simple exploration of the performance on a single machine with MongoDb 3.0 shows the difference between MMAP and WiredTiger for a narrow simulation using the schema simulation framework mongodb-schema-simulator.

Scenario

https://github.com/christkv/mongodb-schema-simulator/blob/master/examples/scripts/single_or_replset/theater/theater_reserve_tickets_successfully.js

MongoDb runs locally on a MacBook Pro Retina 2015 with ssd and 16 gb ram. The simulation runs with the following parameters against a single mongodb instance under osx 10.10 Yosemite.

successfully checkout cart parameters

Parameters Value
processes 4
poolSize per process 50
type linear
Resolution in milliseconds 1000
Iterations run 25
Number of users 1000
Number of tickets in each cart 5
Execution strategy slicetime

MMAP

The MMAP engine is run using the default settings on MongoDB 3.0.1.

Successfully Checkout Cart

successfully checkout cart

Statistics Value
Runtime 124.397 seconds
Mean 2292.494 milliseconds
Standard Deviation 1527.972 milliseconds
75 percentile 2916.34 milliseconds
95 percentile 5211.314 milliseconds
99 percentile 7884.857 milliseconds
Minimum 149.741 milliseconds
Maximum 14779.998 milliseconds

WiredTiger

The WiredTiger engine is run using the default settings on MongoDB 3.0.1.

Successfully Checkout Cart

successful cart checkout

Statistics Value
Runtime 112.994 seconds
Mean 2302.15 milliseconds
Standard Deviation 2052.946 milliseconds
75 percentile 3145.921 milliseconds
95 percentile 6451.251 milliseconds
99 percentile 9608.811 milliseconds
Minimum 19.081 milliseconds
Maximum 17122.854 milliseconds

This is an update heavy schema and this impacts WiredTiger more than MMAP, due to the need to rewrite the full document on each update when using WiredTiger. Also note that the total runtime is higher than the 25 seconds of load we apply, pointing to the fact that the system is overloaded for this hardware configuration.

Notes

The difference here is sizable between the WiredTiger and MMAP storage engines reflecting the cost of rewriting the documents in WiredTiger due to the lack of in place update support.