Difference between revisions of "Transactions Airline"

From SQLZOO
Jump to: navigation, search
(Book 20 seats for alice and 20 for becky)
Line 87: Line 87:
 
You can set of two processes at the same time.
 
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 bob.
+
This single bash line will attempt to book 20 seats for alice and 20 seats for becky.
 
<source lang=bash>
 
<source lang=bash>
 
for i in `seq 20` ; do php book_seat.php alice; done & for i in `seq 20` ; do php book_seat.php becky; done
 
for i in `seq 20` ; do php book_seat.php alice; done & for i in `seq 20` ; do php book_seat.php becky; done
Line 93: Line 93:
  
 
They cannot both get 20 seats and so half of these should fail.
 
They cannot both get 20 seats and so half of these should fail.
 
==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 twice
 
*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.
 
  
 
==What can go wrong==
 
==What can go wrong==
Line 167: Line 153:
 
2 rows in set (0.00 sec)
 
2 rows in set (0.00 sec)
 
</pre>
 
</pre>
 +
 +
==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 twice
 +
*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.

Revision as of 23:07, 2 November 2016

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: Book seat.png

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. Alice 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)

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 twice
  • 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.