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.