In MySQL, transactions are SQL statements that are grouped together under a single unit.
The SQL statements that you grouped under a transaction will then be executed one by one.
When all of the SQL statements are executed successfully, you can commit the changes and make them permanent using the COMMIT statement.
But if one of the statements fails, you can roll back the changes that have been done by other statements to undo those changes using the ROLLBACK statement.
A transaction is very useful when you have multiple statements that need to run successfully for your business to work.
Let’s see an example that shows you the power of MySQL transactions.
Suppose you have two tables named products and orders as follows:
CREATE TABLE `products` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`product_name` varchar(70) DEFAULT NULL,
`stock` int unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `orders` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`product_id` int unsigned NOT NULL,
`quantity` int unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);
The orders.product_id column will have a foreign key reference to the products.id column.
Now that the two tables are created, let’s start with inserting data to the products table:
INSERT INTO products VALUES (NULL, "Basic T-Shirt", 5);
Imagine that sometime later, you got an order for the Basic T-Shirt with a quantity of 10 inserted into the orders table:
INSERT INTO orders VALUES (NULL, 1, 10);
After saving the order to the orders table, you need to update the stock column from the products table:
UPDATE products SET stock = stock - 10 WHERE id = 1;
But since the stock value is only 5 from the beginning, MySQL will throw an error saying that the column value is out of range!
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range
in '(`test_db`.`products`.`stock` - 10)'
Whoops! Turns out you forgot that you only have 5 pieces of the shirt. Now you need to cancel the order and remove it from the database using the DELETE statement:
DELETE FROM orders WHERE id = 1;
The problem is solved, but IF only MySQL doesn’t permanently save the new order to the orders table before the transaction is validated, you can avoid having to run the DELETE statement above.
Rolling back MySQL transaction changes
This is where a MySQL transaction can help you. Multiple SQL statements executed within a transaction won’t be permanently saved to the database until you run a COMMIT statement.
To start a transaction, you need to run the START TRANSACTION statement first:
START TRANSACTION;
Every single statement that you run after the START TRANSACTION statement won’t be saved permanently to the database.
This time, let’s insert another record to the orders table as follows:
START TRANSACTION;
INSERT INTO orders VALUES (NULL, 1, 8);
UPDATE products SET stock = stock - 8 WHERE id = 1;
The UPDATE statement above still fails because you don’t have 8 pieces of shirt:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO orders VALUES (NULL, 1, 8);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE products SET stock = stock - 8 WHERE id = 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range
in '(`test_db`.`products`.`stock` - 8)'
If you query the orders table, you’ll have a new order record as follows:
mysql> SELECT * FROM orders;
+----+------------+----------+
| id | product_id | quantity |
+----+------------+----------+
| 2 | 1 | 8 |
+----+------------+----------+
Rather than running a DELETE statement, you can simply run the ROLLBACK statement to undo the insertion:
ROLLBACK;
Now your orders column will be empty again.
Committing MySQL transaction changes
Now imagine that sometime later, a third order came for the Basic T-Shirt with a quantity of 3.
This time, you try to run the INSERT and UPDATE statements under a transaction again as follows:
START TRANSACTION;
INSERT INTO orders VALUES (NULL, 1, 3);
UPDATE products SET stock = stock - 3 WHERE id = 1;
Now the UPDATE statement works because you still have 5 pieces of the shirt.
With the statements successfully executed, you save the changes permanently to the database using the COMMIT statement:
COMMIT;
-- Query OK, 0 rows affected (0.00 sec)
Both changes to the orders and products table are now saved permanently.
Conclusion
MySQL transaction allows you to run multiple SQL statements step by step. When one of your statements fails, you can undo all previous changes from the start of the transaction just by running the ROLLBACK statement.
The example above is simplified to help you understand MySQL transactions. Now imagine you have many SQL statements that make changes to multiple related tables.
When you have 10 related SQL statements and the 9th statement fails, you need to undo any change made by the previous 8 statements manually!
Using MySQL transaction feature will save you from doing exactly that.
You can make use of the ROLLBACK and COMMIT statements to either undo or save all related changes at once.
Great job on learning about MySQL transaction feature! 👍
