MySQL 101 - Transactions

We've now come a long way since our first steps at creating our online bookshop database. Now we need to start to think about how to sell the books and store details about the sales.  This is the time we need to start understanding database transactions.

Database transactions are very similar to real world transactions. They define a set of steps required to happen together in order for a transaction to be complete.  A real-world example might be that you buy a trinket from a store.  You find the trinket, then take it to the counter, find out the price, hand over the cash and receive your trinket.  That is a completed transaction.  Should you not have the available cash, the transaction would not be able to be completed and you would need to return the item, effectively rolling back that transaction.  It is not a great deal different in the database, except that the transaction can be controlled to a greater degree.

Before going too far, we need to build a few more tables for our database to enable us to sell our books.  To make life easier I've created these tables in this downloadable file.  You can use your favourite MySQL client to create the database for the purposes of following this episode.  The tables I've created are customer, purchase_order and order_item.  We'll see how they work in handling a typical transaction - we can use SHOW CREATE TABLE to see how they are defined:

SHOW CREATE TABLE `customer`\G
*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 NOT NULL,
`address1` varchar(80) CHARACTER SET latin1 NOT NULL,
`address2` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
`postcode` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
`city` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
`country` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
`state` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SHOW CREATE TABLE `purchase_order`\G
*************************** 1. row ***************************
Table: purchase_order
Create Table: CREATE TABLE `purchase_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer` int(11) NOT NULL DEFAULT '0',
`order_date` datetime NOT NULL,
`ship_date` datetime DEFAULT NULL,
`order_total` decimal(9,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_order_customer` (`customer`),
CONSTRAINT `fk_order_customer` FOREIGN KEY (`customer`) REFERENCES `customer` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SHOW CREATE TABLE `order_item`\G
*************************** 1. row ***************************
Table: order_item
Create Table: CREATE TABLE `order_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`purchase_order` int(11) NOT NULL,
`book` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`price` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_item_order` (`purchase_order`),
KEY `fk_item_book` (`book`),
CONSTRAINT `fk_item_order` FOREIGN KEY (`purchase_order`) REFERENCES `purchase_order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_item_book` FOREIGN KEY (`book`) REFERENCES `book` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

You'll notice a few things that may not immediately make sense.  In most cases you don't want duplicate data in different tables.  This is why we break out things like publisher from book, for instance.  However in purchase_order we have an order_total value that could be derived from the order_item records, and similarly we have a price field in order_item that is the same as the price field in book.  There is a lot of sense in doing this however.  Because we are dealing with financial transactions we must ensure they can stand alone.  So we need to ensure that the price of the book is not whatever the current price is, but the price it was at the  time it was sold.  This is why we have the price field in two places as they are not exactly the same.  The order_total field is a summary data field.  It means that we don't necessarily have to have a complicated multi-table join if we want to find how much a customer has bought.  Summary data fields are often used to speed up common queries by simplifying them.

One other field I've added is the stock_on_hand value in the book table.  This will allow us to set a count of books available for sale and decrement them when we sell something.

If we are to sell a book to a customer, we can create the customer record at any time because it won't matter if they don't buy straight away, so customer is not tied directly to a sale, so we can keep it out of our transaction.  When they purchase a book, however, we need to do a number of steps that have to be kept together.   If we don't we could end up with inconsistent data.

Lets go with an example.  I'll create a new customer and set a stock_on_hand value for Peter Temple's The Broken Shore:

INSERT INTO `customer` SET `name` = 'Adam Donnison',
 `address1` = '41 Bendigo Street',
 `city` = 'Collingwood',
 `state` = 'VIC', `postcode` = '3066', `country` = 'Australia';
Query OK, 1 row affected (0.00 sec)

UPDATE `book` SET `stock_on_hand` = 5 WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

It is important to realise at this point that every single INSERT, UPDATE or other data manipulation statement that we have performed so far has been a transaction in its own right.  This implicit transaction is as a result of the default value of the MySQL server variable 'autocommit', which is normally turned on.  If this is turned off we need to explictly make all queries part of a transaction.  More confusingly if you issue a statement that changes data, a transaction will be automatically started, but you need to commit the transaction for it to be permanently recorded in the database.  But let us look at the anatomy of a simple transaction.

In this example we need to ensure the following happen at effectively the same time:

  • Decrement the stock on hand value of the book sold by the quantity sold
  • Create a purchase order entry
  • Create an entry for each book in the order_item table
  • Ensure the purchase order total is the total of the items in the order
START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

INSERT INTO `purchase_order` SET `customer` = 1, `order_date` = NOW();
Query OK, 1 row affected (0.00 sec)

SET @po_number = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

INSERT INTO `order_item` SET `purchase_order` = @po_number,
 `book` = 1,
 `price` = (SELECT `price` FROM `book` WHERE `id` = 1),
 `quantity` = 1;
Query OK, 1 row affected (0.00 sec)

UPDATE `purchase_order` SET `order_total` =
 ( SELECT SUM(`price` * `quantity`) FROM `order_item` WHERE `purchase_order` = @po_number )
 WHERE `id` = @po_number;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1

UPDATE `book` SET `stock_on_hand` = `stock_on_hand` - 1 WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

COMMIT;
Query OK, 0 rows affected (0.00 sec)

There are a few constructs here that are potentially confusing, so we need to go through each one and make sure everything is clear.

First, the SET @po_number = LAST_INSERT_ID() uses a MySQL function to find the ID of the record that we just inserted (the id field for the purchase_order record) and assigns it to a variable, @po_number, that we can use in subsequent queries. The reason we need to do this is to ensure we get an order_item record correctly associated with our newly created purchase order.

We've also used sub-selects. These are stand-alone select queries that are used to provide values for other queries.  In the first case it is a simple select to get the price field from the book table and insert it as the selling price for the order_item.  The second one uses an aggregation function, SUM(`price` * `quantity`) to find all of our order_item records (in this case, one) and determine what the purchase order total is.  You are more likely to be doing this from an application so you probably wouldn't do a sum up.  Alternatively you could use a separate variable that you updated as you added order_item records.  Regardless, what we have done is a set of queries that together make up a transaction.

If at any point in the transaction we decided that we had an error and we couldn't continue (perhaps we found the customer record was incorrect, or the application received an error) we can use ROLLBACK to abort the transaction and wind the database back to the point it was before we issued the START TRANSACTION.  By default if we were to drop the connection to the database (by closing the connection or a terminal failure in our application) or if the database server failed, any pending transaction, that is one that was STARTed but never COMMITted, would be rolled back.

Disclaimer

I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.

MySQL is a registered trademark of Oracle Corporation,  MariaDB is a trademark of MontyProgram, and Drizzle is a trademark.

No feedback yet

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
This is a captcha-picture. It is used to prevent mass-access by robots.
Please enter the characters from the image above. (case insensitive)