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