[MySQL](EN) Database manipulation with the use of TRANSACTION
Environment and Prerequisite
- MySQL
Background
- Thinking about ways to avoid making mistakes while using a database, I came up with the following ideas
START TRANSACTION, COMMIT, and ROLLBACK Statements
START TRANSACTION
- Start a new transaction.
- Remember the queries until COMMIT or ROLLBACK.
COMMIT
- Commits the current transaction, making its changes permanent.
- The cases that trigger implicit commits can be found in the link.
ROLLBACK
- Rolls back the current transaction, canceling its changes.
- Statements that cannot be rolled back can be referred to in the link.
Usage
- An example of mistakenly modifying the values of the row with id 2 in the example_table and then correcting it by updating the values of the row with id 3.
- In addition, TRANSACTION can be used effectively when many changes are needed in the database.
START TRANSACTION;
UPDATE example_table SET status = 'family' WHERE id = 2;
ROLLBACK;
START TRANSACTION;
UPDATE example_table SET status = 'family' WHERE id = 3;
COMMIT;