A Brief Introduction to MySQL Transactions

Introduction

When managing a database, several operations can execute that may need to be tracked in case of errors. For example, when you want to withdraw money at an atm, you enter the amount you wish to withdraw, and the system checks the database to determine whether you are allowed to withdraw the requested amount. If so, the system deducts the money from the database and sends a success message to you. However, if there was a power outage, the withdrawal process may fail while in progress. Transactions help us keep track of any modifications made to the database and handle them more accurately. For cases like this, the changes can be rolled back or overwritten.

What Is A Transaction?

A transaction is a logical work unit in a database that conducts a single or several actions. Transactions can be made up of a single read, write, delete, or update operation or a mix of all actions.

Properties of Transactions

Now let's briefly highlight and discuss some properties/qualities of a transaction.

Atomicity
Atomicity indicates that the transaction guarantees all actions inside the work unit are completed. Otherwise, the transaction is aborted at the point of failure, and all preceding actions are reverted to their previous state.

Consistency
Consistency means that the transaction guarantees that the database changes states appropriately after a successfully committed transaction.

Isolation
Isolation means that all transactions must be independent of one another and transparent to one another.

Durability
Durability means that the transaction assures that the outcome or impact of a committed transaction is consistent in the event of a system failure.

Transactional Control Commands

Let's now discuss some commands used to control transactions in your database.

COMMIT

COMMIT is a transactional command used to save all the changes made previously by a transaction to the database. This command saves all the changes made since the last commit or rollback command.

As an example, we'll use this sample users table with six rows and use a transaction to delete one:

DELETE 
    FROM users
WHERE 
    name = 'Test';
COMMIT;

In the SQL code above, we deleted the last row from the table and saved the changes using the COMMIT command.

ROLLBACK

ROLLBACK is a transaction command used to undo or overwrite all the changes made previously to the database. This command removes all the changes made since the last commit or rollback command.

BEGIN;
DELETE
    FROM users
WHERE 
    id = 4 
    AND id = 5;
ROLLBACK;

In the SQL code above, we deleted the last row then rolled back the deletion with the ROLLBACK command. The command to be rolled back must start with the BEGIN command.

SET TRANSACTION

The SET TRANSACTION command starts a database transaction. This command can be used to set parameters for the following transaction. You can, for example, make a transaction read-only or read-write.

SET TRANSACTION [READ ONLY |READ WRITE]

SAVEPOINT

The SAVEPOINT command creates a point in the transaction where all the changes before it are saved. You can create a SAVEPOINT for only the changes after the last save point. This command is beneficial when you want to roll back the changes to a particular point in the transaction history.

BEGIN;

DELETE FROM users
   WHERE id =6;

SAVEPOINT SP1;

DELETE FROM users
   WHERE id =2;

SAVEPOINT SP2;

ROLLBACK TO SP1;

In the code above, we initialized the transaction with the BEGIN command, then deleted the rows with id 6 and 2. For each deletion, we created two SAVEPOINTs; SP1 AND SP2. We then rolled back the transaction to the first SAVEPOINT —SP1.

RELEASE SAVEPOINT

The RELEASE SAVEPOINT command is used to delete a previously generated SAVEPOINT. Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to reverse transactions that have occurred since the last SAVEPOINT.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Modes of Transaction in MySQL

Let's look at the modes we can set and how they affect a transaction.

Auto-commit Transaction

MySQL enables auto-commit mode by default. When auto-commit mode is enabled, each SQL statement is assessed as a transaction, and the results determine whether the statement is committed or rolled back. The successful statements are committed, while the unsuccessful ones are promptly rolled back. To manually disable auto-commit mode, simply run the command:

SET @@autocommit := 0;

Implicit Transaction

Adding COMMIT explicitly commits a transaction, however, it is also possible to use transaction controls to implicitly commit transactions. For example:

BEGIN;
SET AUTOCOMMIT = 1;

Conclusion

In this article, you learned what transactions are, why they are important, and how to use them in the MySQL database. In addition, you learned some transactional control commands and the three modes of transactions in MySQL.

If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.