In MySQL, a sequence of queries can be wrapped inside transaction. Transaction can be useful for some use cases.
Create a transaction
This is the query structure to create a transaction. The QUERIES_SAMPLE
can be filled with a sequence of queries. The COMMIT
clause is used to execute or commit a transaction that already created.
START TRANSCATION;
QUERIES_SAMPLE;
COMMIT;
This is the transaction example, this transaction contains a sequence of queries:
- Add the product into the cart.
- Update the product's quantity that inserted into the cart.
- Retrieve the product's data after being updated.
START TRANSACTION;
-- 1. add the product with id equals 1 into the cart
INSERT INTO cart VALUES (0,1);
-- 2. update the product's quantity that inserted into the cart
SELECT @quantity := quantity FROM shop WHERE id = 1;
UPDATE shop SET quantity = @quantity - 1 WHERE id = 1;
-- 3. retrieve the product's data after being updated
SELECT * FROM shop WHERE id = 1;
-- commit the transaction
COMMIT;
Rollback a transaction
Rollback a transaction is a mechanism to undo or roll back the changes that affected by a transaction. To rollback a transaction can be done using ROLLBACK
query.
This is the example of rollback mechanism, in this case a transaction is a created to delete all data inside shop table.
-- transaction sample
START TRANSACTION;
DELETE FROM shop;
Notice that the transaction is not committed so the rollback mechanism is available to use. To rollback a transaction use the ROLLBACK
query.
ROLLBACK;
After the ROLLBACK
is executed, all data inside the shop table is still exists.
Notes
- Other MySQL transaction example can be checked here.
This is the final part of the MySQL tutorial series, I hope this series is helpful for learning MySQL. 😀
I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.
Top comments (0)