Transactions Airline

Airline seat booking

• Our airline has one aircraft with 20 seats numbered 1 to 20. Each seat has a row in the table seat
• When a customer wants a seat we find the lowest numbered seat and put the customer's name in that location.
• We record how much money our customers owe us in the table charges. Every time a debt is incurred we add a row to that table.

Here is a typical situation. Alice has booked seats 1 and 2. Bob has booked seats 3, 4 and 5

MariaDB [scott]> select * from seat;
+----+-------+
| id | cust  |
+----+-------+
|  1 | alice |
|  2 | alice |
|  3 | bob   |
|  4 | bob   |
|  5 | bob   |
|  6 | NULL  |
|  7 | NULL  |
|  8 | NULL  |
|  9 | NULL  |
| 10 | NULL  |
| 11 | NULL  |
| 12 | NULL  |
| 13 | NULL  |
| 14 | NULL  |
| 15 | NULL  |
| 16 | NULL  |
| 17 | NULL  |
| 18 | NULL  |
| 19 | NULL  |
| 20 | NULL  |
+----+-------+

Each seat costs £100 and the charges table records each sale.

MariaDB [scott]> select * from charge;
+-----+-------+--------+
| tid | cust  | amount |
+-----+-------+--------+
|   1 | alice |    100 |
|   2 | alice |    100 |
|   3 | bob   |    100 |
|   4 | bob   |    100 |
|   5 | bob   |    100 |
+-----+-------+--------+

Using PHP to find and book a seat

The php program book_seat.php can be run form the command line.

• It takes the name of the customer as a parameter
• It finds the first free seat
• If no seat is available it halts with an error message
• Otherwise it
  • Assigns the seat to the customer
  • Adds the charge of £100 for that seat

<?php
$who = $argv[1];
$dbh = new PDO('mysql:host=localhost;dbname=scott','scott','tiger');
#Find the first free seat
$sth = $dbh->prepare("SELECT MIN(id) FROM seat WHERE cust IS NULL");
$sth->execute();
$a = $sth->fetchAll()[0][0];
if ($a==""){
  die("No seats available, sorry.\n");
}
$sth = $dbh->prepare("UPDATE seat SET cust=? WHERE id=?");
$sth->execute(array($who,$a));
$sth = $dbh->prepare("INSERT INTO charge(cust,amount) VALUES (?,?)");
$sth->execute(array($who,100));
echo "$who gets seat $a\n";

Running the program book_seat.php

You can run the program book_seat.php from the command line like this:

Booking 20 seats

You can book 20 seats for alice with a command like this one from the bash command prompt:

for i in `seq 20` ; do php book_seat.php alice; done

If your aircraft was empty beforehand then it will be entirely filled with alice and her entourage.

You can reset the system with the following pair of SQL statements:

delete from charge; update seat set cust=NULL;

Book 20 seats for alice and 20 for becky

You can set of two processes at the same time.

This single bash line will attempt to book 20 seats for alice and 20 seats for becky.

for i in `seq 20` ; do php book_seat.php alice; done & for i in `seq 20` ; do php book_seat.php becky; done

They cannot both get 20 seats and so half of these should fail.

What can go wrong

Here is the output from one attempt - you should expect something slightly different each time. Notice that seat 3 has been assigned twice:

[1] 62586
alice gets seat 1
becky gets seat 2
becky gets seat 3
alice gets seat 3
alice gets seat 4
becky gets seat 5
becky gets seat 6
alice gets seat 7
alice gets seat 8
becky gets seat 9
becky gets seat 10
alice gets seat 11
alice gets seat 12
becky gets seat 13
alice gets seat 14
becky gets seat 15
alice gets seat 16
becky gets seat 17
alice gets seat 18
becky gets seat 19
No seats available, sorry.
alice gets seat 20
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
No seats available, sorry.
[1]+  Done                    for i in `seq 20`;
do
    php book_seat.php alice;
done

You can see that someone has been overcharged with this SQL. Becky got charged for a seat even though her booking got lost:

MariaDB [scott]> select cust,sum(amount) from charge group by cust;
+-------+-------------+
| cust  | sum(amount) |
+-------+-------------+
| alice |        1100 |
| becky |        1000 |
+-------+-------------+
2 rows in set (0.00 sec)

This query shows that alice got 11 seats, becky only secured 9. There can only ever be 20 seats booked, but an error in logic means that sometimes the total charges are more than £2000.

MariaDB [scott]> select cust,count(1) from seat group by cust;
+-------+----------+
| cust  | count(1) |
+-------+----------+
| alice |       11 |
| becky |        9 |
+-------+----------+
2 rows in set (0.00 sec)

Get the error reliably

You may not see the error happening first time. You will have to reset and try again until you get the error.

Increase the number of seats to 100 and keep trying until you can reproduce the error reasonably reliably.

Keep track of the error rate. With 100 seats go through the sequence:

• Clear the database
• Run two lots of 100 bookings
• Check if the total charges is more than it should be

You want to get at least 5 error for each 10 times you go through the sequence. There are plenty of ways to automate this process.

Solve the problem

Use transaction to ensure that customers do not get charged for seats unless the booking was successful.