A Brief Introduction to MySQL Transactions
Tags: mysql, mysqltransactions, guide
Published: 2021-10-29 20:18:45

## 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:
![](https://lh3.googleusercontent.com/EXlInMuIvbbYyoqF9HLdGKSQLuDdptjYDeuW90n__HyUDYAmrEG0VddvGjeKzyRVpuJXUkJrhEAhdyVhl-146VRYlelZKO60igcmK9QRju-DTbTTZXfurKjt194upkbxZnpbPoEA=s0)

```
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.
![results after committing delete](https://lh4.googleusercontent.com/hGW9y--oVFxxyjsG0D0P2G2WB3eYm1Jur_PkNnvX9gF-RWLTBLaWjsehNmBEaMo123KV5iAvr_YCrjWooyQiTTjt8uPyHc5ydQfpHr-uzms60GzDaKCl0-xveQ8UkFnCy3RzMD40=s0)

### 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.
![](https://lh4.googleusercontent.com/BPxxN7zDXFymQdjukyojZZuKWuCfKlQW5ZTgjtXve8OGP4fRTiiFaByQRrLYP2rb-2q0PHe2-AdoRUJV3cEu1T18uGWQxMOyKqUQnAerTb0ZVa0eGtnIc7-ZVP4eLvo8ZzgGGFAO=s0)

```
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.
![](https://lh4.googleusercontent.com/BPxxN7zDXFymQdjukyojZZuKWuCfKlQW5ZTgjtXve8OGP4fRTiiFaByQRrLYP2rb-2q0PHe2-AdoRUJV3cEu1T18uGWQxMOyKqUQnAerTb0ZVa0eGtnIc7-ZVP4eLvo8ZzgGGFAO=s0)

### 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.
![](https://lh6.googleusercontent.com/kO7bqIrn6pM2xygc42tnytbCkOBdVJJmUKG5qxJCpHYqejDgjvb_bI8wnvsutEVH7aVq-QAj_sX-1pqUpanms9sr5Gz9zBBCL2uV8OJaskIFjHpC_klQsMRsAPHXkO_cgaDbsvoh=s0)

```
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 `SAVEPOINT`s; `SP1` AND `SP2`. We then rolled back the transaction to the first `SAVEPOINT` —`SP1`.
![](https://lh4.googleusercontent.com/BPxxN7zDXFymQdjukyojZZuKWuCfKlQW5ZTgjtXve8OGP4fRTiiFaByQRrLYP2rb-2q0PHe2-AdoRUJV3cEu1T18uGWQxMOyKqUQnAerTb0ZVa0eGtnIc7-ZVP4eLvo8ZzgGGFAO=s0)

### 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](https://twitter.com/LordChuks3).