MySQL tutorial - 9 Transaction

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:

1. Add the product into the cart.
2. Update the product's quantity that inserted into the cart.
3. 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.