Understanding transactions

A transaction is a sequence of SQL statements that are grouped into a single logical operation. Its purpose is to guarantee the integrity of data. If a transaction fails, no change will be applied to the databases. If a transaction succeeds, all statements will succeed.

Why is this so important? Consider the following example:

START TRANSACTION;
SELECT quantity FROM product WHERE id = 42;
UPDATE product
  SET quantity = quantity - 10
  WHERE id = 42;
UPDATE customer
  SET money = money -
  (SELECT price FROM product WHERE id = 42)
  WHERE id = 512;
INSERT INTO product_order
  (product_id, quantity, customer_id)
  VALUES (42, 10, 512);
COMMIT;

We haven't yet discussed some of the statements used in the preceding example. However, they are not transactions we need to understand. This sequence of statements happens when a customer (whose id is 512) orders a product (with id 42). As a consequence, we need to execute the following sub-operations in our database:

• Check...