What is Transaction

A transaction in the realm of databases is a unit of work that is executed as a single, atomic operation. It's a logical sequence of one or more data manipulations, wrapped as a coherent and reliable unit. Transactions follow the ACID properties. The transaction ensures that this sequence of operations is executed all at once or not at all, and changes made in a transaction are saved permanently.

Starting a Transaction: BEGIN or START TRANSACTION

Transactions in MySQL begin with the use of BEGIN or START TRANSACTION commands. The choice between these two is largely one of personal preference and readability in your SQL scripts. When these commands are issued, MySQL starts a new transaction block and all the subsequent commands will be part of this block until it's terminated.

Ending a Transaction: COMMIT

The COMMIT command is used to end a transaction block. This command makes all changes made in the current transaction permanent. After the execution of this command, no rollback can be performed. The use of the COMMIT command signals the successful end of a transaction.

Terminating a Transaction: ROLLBACK

On the other hand, if you want to terminate the transaction without making the changes permanent, you can use the ROLLBACK command. This command undoes all changes that were made in the current transaction. In other words, it's like the transaction never happened. It's useful in cases when you encounter errors or problems in the middle of a transaction and want to revert to the state before the transaction started.

ROLLBACK Operation

I'll explain how the ROLLBACK command functions in a MySQL transaction. To illustrate, we'll use a simple example involving changes to a users table.

State Before a Transaction

Consider the following snapshot of the users table:

mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | Jack |
+----+------+
3 rows in set (0.00 sec)

Here, we have a table with three users: John, Jane, and Jack.

Initiating and Executing a Transaction

Now, let's initiate a transaction with the intent to modify the name of the user with id = 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE `users` SET `name` = "James" WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

At this point, within our transaction, we have updated John's name to James.

Terminating the Transaction with ROLLBACK

Suppose we encounter an issue or change our mind about this update. We can terminate the transaction without committing the change:

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

The ROLLBACK command will undo all the changes made in the current transaction.

Verifying State After ROLLBACK

After the transaction has been rolled back, we can verify that the users table has returned to its original state:

mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | Jack |
+----+------+
3 rows in set (0.00 sec)

As we can see, the user's name has reverted back to John, demonstrating the ROLLBACK command's effect of undoing changes made within a transaction.

COMMIT Operation

I will show you the COMMIT command in MySQL transactions. Let's continue with our users table example to understand this operation better.

State Before a Transaction

Here is the initial state of the users table:

mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | Jack |
+----+------+
3 rows in set (0.00 sec)

We have three users in the table: John, Jane, and Jack.

Initiating and Executing a Transaction

Now, we'll start a transaction with the intention of modifying the name of the user with id = 1:

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

mysql> UPDATE `users` SET `name` = "James" WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Within the scope of our transaction, we have now updated John's name to James.

Ending the Transaction with COMMIT

If we're satisfied with this change and wish to make it permanent, we can end the transaction with a COMMIT:

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

The COMMIT command has the effect of making all changes made in the current transaction permanent.

Verifying State After COMMIT

Now, let's verify the state of the users table after the transaction:

mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | James |
|  2 | Jane  |
|  3 | Jack  |
+----+------+
3 rows in set (0.00 sec)

As we can observe, the user's name has been updated to James. This showcases the effect of the COMMIT operation, which permanently applies changes made within a transaction.