Database Transactions 101: The Essential Guide

Let’s learn everything you need to know about database transactions. In this guide, you will have the opportunity to see what database transactions are, what properties they have, how they work, and how to define one.

MySQL, MariaDB, PostgreSQL, Oracle Database, and Microsoft SQL Server are the most popular relational database technologies. All these RDBMSs, Relational Database Management Systems, share one common aspect: they are all transactional databases. In other words, they all rely on database transactions.

In detail, database transactions play a key role in the functioning of a relational database. This is why it is so important to know what a database transaction is and how it works. Follow this guide and become an expert on database transactions!

What is a Database Transaction?

A database transaction is a logical unit that generally includes several low-level steps. If one step of the transaction fails, the whole transaction fails. A database transaction is used to create, update, or retrieve data.

Think of a database transaction as a series of operations performed within a DBMS. The transaction system ensures that the data in the database always remains in a reliable and consistent state.

If a transaction is successful, the data in the database is updated as described in the instructions contained in the transaction. This is called a “commit.”
If a transaction fails, all transaction steps performed prior to the step that led to the failure are reversed. The data in the database returns to its initial state as if the transaction had never been executed. This operation is called a “rollback.”

In other terms, a database transaction ends with a commit or rollback. This ensures that database transactions are Atomic, Consistent, Isolated, and Durable. These are commonly known as the ACID properties. Let’s learn more about them.

ACID Properties in DBMS

The ACID acronym defines the four properties every database transaction must have to ensure data integrity in case of errors or failures. In a DBMS, these properties are:

Atomicity: This property ensures that all operations within a transaction complete successfully, or do not complete at all. This means that a transaction is indivisible. Atomicity prevents a transaction from stalling and prevents partial database updates.
Consistency: Any transaction leaves the database in a consistent state, regardless of the outcome of the transaction. If the database was in a consistent state before the transaction, it must remain consistent after the transaction is executed.
Isolation: Each transaction has access to an isolated version of the database. Data used in transactions that have not yet been completed cannot be modified by other transactions.
Durability: Once a transaction has been successfully completed, its effect will remain in the database even if the database fails. Thus, if a transaction is completed but the database crashes before writing data to disk, the data will be updated when the system returns to service.

Keep also in mind that ensuring ACID properties comes at a performance cost for a DBMS. So, not all database management systems fully support ACID. Plus, some DBMSs, such as MySQL, allow you to disable ACID for speed by changing a parameter from 0 to 1.

Now that we know what ACID properties are and learned a little about database transactions, you will see some of them in action.

How To Define a Database Transaction

A database transaction is generally defined by a set of instructions wrapped by two keywords. This is especially true when it comes to transactions in SQL. These two keywords mark the beginning and end of the transaction, respectively. For example, in PostgreSQL and MySQL these are: START TRANSACTION and COMMIT. In SQL server these are: BEGIN TRANSACTION and COMMIT TRANSACTION.

Let’s assume you want to remove 200 points from user 4, and distribute them equally among user 1 and 5. This is how you can achieve this with a PostgreSQL database transaction:

-- initializing the transaction
START TRANSACTION;

-- adding 100 points to user 1 and 5
UPDATE users
SET points = points + 100
WHERE id IN (1, 5);

-- removing 200 points from user 4
UPDATE users
SET points = points - 200
WHERE id = 4;

-- commiting the change (or rolling it back later in case of failure)
COMMIT;

Running the transaction query in DbVisualizer

This is a simple example of a transaction in SQL. What this database transaction does is perform two different operations on the table. In detail, user 4 loses 200 points, which are transferred to users 1 and 5. If the database crashes after the first update query, then the database data would be inconsistent. Users 1 and 5 would find themselves with 100 more points than they should have. Luckily, the transaction ensures that the entire logical operation is performed atomically. So, the database data will remain consistent even in case of failures.

Now you know how to write a transaction in SQL, but you still have to understand how transactional databases execute transactions. Let’s learn more about this!

States of a Database Transaction

In the case of a transactional database, the life cycle of a transaction can be described by the following four steps:

The transaction begins: The transactional database prepares everything required to execute the transaction.
The queries defined in the transaction are executed: This is when data manipulation takes place.
If no errors occur, the transaction is committed: The transaction ends successfully.
If an error occurs, it rolls back the transaction: The transaction ends with failure and any query executed before it failed is reversed.

Specifically, a transaction in DBMS can have the following five states:

State
Transaction types

Active
This is the initial state of every transaction in a database. This state means that the transaction is being executed and can perform read and write operations.

Partially Committed
A transaction enters this state after performing its final operation/query.

Committed
A transaction is in this state if it has performed all its operations successfully. The effects of the transaction are now permanent in the database system.

Failed
A transaction enters this state if any transaction fails or if the transaction is aborted before completing. A failed transaction cannot proceed further.

State transition diagram for a database transaction

Conclusion

Here you have learned everything you need to know about database transactions. In detail, you have had the opportunity to see what a database transaction is, how to define one through an example, and what ACID properties are.

Database transactions are a powerful tool that allows you to build an atomic query composed of several sub-queries and sub-operations. This means that you should use transaction queries carefully. Also, you need to optimize them for good performance. In other words, such a powerful tool requires an advanced database client that supports you with query optimization features and much more, such as DbVisualizer. Try DbVisualizer for free!

FAQ about Database Transactions:

Let’s answer some questions about database transactions.

1. What are the types of database transactions?

There are several types of database transactions. The most important to know are: non-distributed transactions, distributed transactions, online transactions, batch transactions, two-step transactions, flat transactions, and nested transactions.

2. What is the difference between a transaction and a query

You can think of queries as single operations to CRUD, Create, Read, Update, Delete, data in a table. On the other hand, a database transaction is an atomic that involves multiple statements, queries, and operations.

3. When should you commit a database transaction?

The COMMIT instruction should be placed at the end of the transaction. Executing too many operations in a single transaction can make it very slow. So, try to commit only the operations that your transaction really needs.

4. Does NoSQL support database transactions?

Database transactions are a concept that is independent of database type. Although database transactions are usually associated with relational databases, there are also NoSQL technologies that support them. NoSQL technologies like FoundationDB, MarkLogic, and Oracle NoSQL support database transactions.

5. What is a transactional database?

A transactional database is a database that supports ACID transactions. Specifically, a transactional database is a DBMS technology that can commit or roll back data with a database transaction system.