What is a Database Transaction and Why is it Important?

Written by: Innocent Kanayo

What is a Database Transaction and Why is it Important?

A database transaction is a single area of the database where multiple data operations are carried out and written as a whole. These operations can be create, read, update, or delete operations.During the process of a transaction, the database is in an inconsistent state because there are ongoing operations that are making changes to the database. The DB returns to a more consistent state when the operations have been committed.For a transaction to be successful, it means that every operation carried out has been committed. Database transactions are very important in ensuring the consistency of your database when multiple operations are being performed at the same time. It also gives you a way to recover changes that may have occurred due to the failure or accidental misuse of an operation.

Overview of MySQL and its Transaction Support

MySQL databases offer support for database transactions by providing statements to initiate these transactions. It gives us the following in-built queries:

"START TRANSACTION / BEGIN": this query triggers the start of a transaction.

"COMMIT": this query allows the changes made to the database to become permanent. You can set your database to auto-commit changes by using the following query:

SET autocommit = 1;

"SET": this query allows you to set your commit by enabling the operations to commit automatically or disabling the auto-commit. That is, your operations won't commit automatically until you call the "commit" query.

/*Disabling the auto-commit */

SET autocommit = 0;

/* OR */

SET autocommit = OFF;

/* Enabling the operations to automatically commit every operation*/

SET autocommit = 1;

/* OR */

SET autocommit = ON

"ROLLBACK": this query allows you to undo the changes you have made to the database, therefore returning the database to its previous (last commit) state.

ACID Properties of Transactions

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. Let's go through each term to understand how they relate to transactions.

Atomicity

Atomicity in a database transaction means that all the changes made during that transaction are treated as one "bundle" of changes. This means that when you are trying to modify your database, it's either all of the changes happen at the same time, or none of them happen at all.

It's like when you and your teammates are building an application. If one person writes a line of code, and then another person takes it off, it's like nothing happened. But if everyone keeps adding different lines of code and nobody takes any off, then the code base keeps getting bigger.

Consistency

Consistency in databases means that the data stored in the database is always in a valid and consistent state. For example, if the database contains any constraints such as primary keys, foreign keys, and so on, it should always conform to the rules surrounding the constraint. For example, let's say a table has a rule that says a specific column must be an integer value. Consistency ensures that this rule is always followed and data inserted into the column can only be of the integer value data type.

Isolation

The ability of multiple transactions to execute without interfering with one another is known as Isolation. The isolation level of a transaction determines how the changes made by that transaction are visible to other transactions.

MYSQL supports the following isolation levels:

i. READ UNCOMMITTED: In the READ UNCOMMITTED level, which is also the lowest isolation level, a transaction can read data that is yet to be committed by other transactions. This means that other transactions can alter the data that another transaction is currently reading, but these changes may not be visible until the operation is complete.

ii. READ COMMITTED: This is the second-to-the-lowest isolation level. Here, a transaction is only able to read data that has already been committed by other transactions. This means that other transactions can alter the data that a transaction is currently reading but these changes will not be visible until the other transaction has been committed.

iii. REPEATABLE READ: This is a higher level of isolation. A transaction at this level is only able to read data that has already been committed by other transactions, and it also restricts other transactions from altering the data that is currently being read. This means that even though other transactions have committed changes, if a transaction executes a SELECT statement again, it will always see the same data.

iv. SERIALIZABLE: This is the highest level of isolation. At this level, a transaction is only able to read data that has already been committed by other transactions. It also prevents other transactions from altering the data that the transaction is reading and from adding new rows that would be visible to the current transaction.MySQL uses the READ COMMITTED isolation level by default. However, it is possible to change the isolation level by using the "SET TRANSACTION ISOLATION LEVEL" statement.

Durability

Durability ensures that your data remains safe, even in the event of unforeseen circumstances. When a transaction is committed, its changes must remain in the database, even if it experiences a malfunction or a power outage.But how does MySQL ensure durability? It makes use of write-ahead logging. This technique involves writing a log of the transaction to disk before making any changes to the database.The log acts as a road map for the database and contains information about the changes that will be made in case of an unexpected system failure. In the event of this, the database can be recovered from the log, and the changes made in the transaction will be replayed to makesure that the database is still in a consistent state.It's important to keep in mind that while write-ahead logging can have a performance impact, it's a small price to pay for the peace of mind that comes with knowing your data is safe.