How do we implement MySQL transactions?

We know that in a transaction, statements are executed as a unit. If any operation within the transaction fails, the entire transaction fails and should be rolled back; otherwise, any changes made by the statement are saved to the database. In order to implement transactions, MySQL provides the following statement -

START TRANSACTION

As the name suggests, the transaction starts from this statement. Basically, it informs MySQL that subsequent statements should be treated as a single unit of work until the end of the transaction.

COMMIT

The COMMIT statement commits changes to the database. In other words, when a transaction completes successfully, a COMMIT command should be issued to make changes to all involved tables effective.

ROLLBACK

The ROLLBACK command undoes any changes made by the statement and returns the database to its previous state, which was the state when the transaction began.

Example

The following is an example showing MySQL transaction implementation -

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Aarav','Maths',50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Marks Values(2, 'Harshit','Maths',55);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

In this example, the transaction is started by the START TRANSACTION statement. Then execute two INSERT statements, followed by a COMMIT statement. The COMMIT statement saves the changes to the database, which can be observed from the following result set, which shows that the values ​​have been inserted into the table -

mysql> SELECT * FROM Marks;
 +------+---------+---------+-------+
| Id   | Name    | Subject | Marks |
+------+---------+---------+-------+
| 1    | Aarav   | Maths   | 50    |
| 2    | Harshit | Maths   | 55    |
+------+---------+---------+-------+
2 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Aarav','History',40);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Harshit','History',48);
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)

In this example, the transaction is started by the START TRANSACTION statement. Then execute two INSERT statements, followed by a ROLLBACK statement. The ROLLBACK statement will undo the changes made to the database, which can be observed from the following result set, which shows that no new values ​​were inserted into the table -

mysql> SELECT * FROM Marks;
+------+---------+---------+-------+
| Id   | Name    | Subject | Marks |
+------+---------+---------+-------+
| 1    | Aarav   | Maths   | 50    |
| 1    | Harshit | Maths   | 55    |
+------+---------+---------+-------+
2 rows in set (0.00 sec)

The above is the detailed content of How do we implement MySQL transactions?. For more information, please follow other related articles on the PHP Chinese website!