TRANSACTIONS

Database Transactions 101: The Essential Guide

intro

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

Copy
        
1 2 -- initializing the transaction 3 START TRANSACTION; 4 -- adding 100 points to user 1 and 5 5 UPDATE users 6 SET points = points + 100 7 WHERE id IN (1, 5); 8 -- removing 200 points from user 4 9 UPDATE users 10 SET points = points - 200 11 WHERE id = 4; 12 -- commiting the change (or rolling it back later in case of failure) 13 COMMIT;
Running the transaction query in DbVisualizer.
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:

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

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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

StateTransaction types
ActiveThis 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 CommittedA transaction enters this state after performing its final operation/query.
CommittedA 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.
FailedA transaction enters this state if any transaction fails or if the transaction is aborted before completing. A failed transaction cannot proceed further.
TerminatedIt is the final state of each transaction. It means that the transaction is finished, either successfully or not.
State transition diagram for a database transaction.
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!

Dbvis download link img

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 FoundationDBMarkLogic, 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.

About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Outer Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 11 min 2024-02-19
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL Views: A Comprehensive Guide

author Bonnie tags VIEWS 5 min 2024-02-12
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

Dangerous Big Data - Big Data Pitfalls to Avoid

author Lukas Vileikis tags BIG DATA min 2024-02-01

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗