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
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
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
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`) );
orders.product_id column will have a foreign key reference to the
Now that the two tables are created, let’s start with inserting data to the
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
INSERT INTO orders VALUES (NULL, 1, 10);
After saving the order to the
orders table, you need to update the
stock column from the
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 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
To start a transaction, you need to run the
START TRANSACTION statement first:
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;
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:
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
This time, you try to run the
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;
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; -- Query OK, 0 rows affected (0.00 sec)
Both changes to the
products table are now saved permanently.
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
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
COMMIT statements to either undo or save all related changes at once.
Great job on learning about MySQL transaction feature! 👍