An Introduction to Transactional Databases

Given the increasing reliance on data-based decision making in business and the exponential amount of data each consumer creates daily, organizations are seeking ways to collect, store, secure, and analyze their data assets to maximize insights and value. Transactional databases are a lynch pin in these efforts.

In this article, we'll explore what transactional databases are, how they work, and why they are relevant today.

Table of Contents

• What is a transactional database?

• How do transactional databases work?

• Why are transactional databases relevant today?

• FAQs

What is a transactional database?

In short, a transactional database stores data that results from an interaction between two or more parties. These interactions, or transactions, can be anything from e-commerce sales to banking, insurance claims, or even healthcare orders for patient tests. Regardless of the type of data stored, transactional databases have the following attributes.

Transactional database attributes

• High data integrity: By enforcing data rules and constraints, transactional databases prevent entry of inconsistent or invalid data. Bear in mind that a database transaction can be anything from a business transaction (e.g., an e-commerce sale) to a single document (e.g., an insurance claim). What is important is that the transaction is complete and conforms to the predefined rules in place relating to the database management system (DBMS) governing the transactional database.

• Scalability: Given the operational and fiduciary nature of transactional databases, they must be able to scale as needed to accommodate all organizational data. These databases can scale vertically (e.g., more room on a single machine) or horizontally (e.g., more machines to create a greater amount of data storage).

• Real-time processing: In order to maintain up-to-date, accurate records, transactional databases best practices indicate they should update in real time to keep track of transactional changes and avoid data conflicts.

• Concurrent access: When two users (or more) are uploading database transactions (e.g., a retailer with multiple stores with multiple registers), transactional databases must be able to manage this concurrent traffic while preventing data conflicts. This is also important when collaborative software with multiple users is querying and updating a transactional database. Database operations must be able to keep pace and maintain database performance without system failure.

• Auditability: An audit trail (provided by a database transaction log) is an important aspect of many transactional databases. Whether they be financial records, patient chart updates by specific physicians, or supply chain records, many organizations are required to create and maintain auditable files for compliance and governance purposes.

What type of database is a transactional database?

Transactional databases excel at storing and querying the data required to power an application. However, they are called "transactional" due to the type of data they house (e.g., data that results from an interaction between two or more parties) rather than the type of database they are.

There are different types of databases that can function as transactional databases. They include:

• Relational databases: Relational databases organize data in rows and columns which are used to form tables. There are multiple tables within a relational database where a relationship between the two tables can be created using a foreign key. These foreign keys (e.g., unique identifiers) maintain predefined relationships that exist between the tables. These databases are run using relational database management system (RDMS) software and often use such programming languages as SQL standard, MySQL, or Python when processing transactions. It's important to note that due to its reliance on predefined rules and formats, relational databases are not able to process unstructured data (e.g., text files, photos, videos). These databases are primarily used to support transactions that include structured or semi-structured types of data such as business transactions which are to be queried for data analysis.

• NoSQL databases: NoSQL databases are used to store unstructured data such as text files, photos, videos etc. The databases are interacted with using whichever programming language is supported by that database. This enables databases to write database transactions that include everything from a single document (e.g., a patient test result, insurance photos) to file systems including entire patient histories or insurance case files.

Types of transactions in a transactional database

Within a transactional database, there are different types of transactions stored. These types of transactions include:

• Single transactions: A single transaction within a transactional database refers to a unit of work (e.g., reliable units) consisting of one or more database operations. However, regardless of how many database operations are included, the transaction must be a complete transaction record according to database management system definitions. Records of these transactions can be found in the transaction log. Examples include an e-commerce order placed or money withdrawn from an ATM.

• Multi transactions: A multi transaction, sometimes called a distributed transaction, includes multiple, interdependent transactions that range across a variety of different databases and systems. Within each of these databases or systems, one database operation or many may occur. Records of these transactions may also be found in a transaction log. An example of these transactions include a multi document transaction where customer information is updated and associated invoices and billing must be updated as well, or validating the dependencies throughout an IT project via project management software.

How do transactional databases work?

Transactional databases rely on the Atomicity, Consistency, Isolation, and Durability (ACID) properties, to ensure the reliability and accuracy of the transactional data.

ACID properties

Atomicity: Atomicity simply means that a transaction is treated as a single unit (or unit of work) — either the entire transaction is included or it is not stored in the database and an error occurs if only a partial transaction is available. This helps make sure that transactional data consistency is maintained in case a transaction fails. Some of the ways that atomicity is implemented in a transactional database include:

1. Undo log: In the event a transaction fails, the undo log enables the changes made by the transaction to be reversed. In this way, only database transactions that meet predefined DBMS rules are included in the transactional database.
2. Redo log: When a successful transaction is being written to the transactional database, the redo log keeps track of the changes. This way, if a system failure occurs after a transaction is being ingested but before transaction changes are written, the redo log can be used to reattempt the changes.
3. Two-phase commit: Two-phase commit ensures that when a database is stored across multiple machines (e.g., a distributed database), all the nodes that distributed system either accept (e.g., commit) or abort the transaction together. This not only ensures transactions are executed atomically, but also maintains data in a consistent state for a distributed transaction.
4. Locking: Locking ensures that only one transaction can edit a specific piece of data at a time. This helps avoid data conflicts across database transactions while also helping maintain data integrity.

Consistency: Transactional databases maintain data consistency by enforcing predefined rules and data constraints which not only apply to ingested data but also data modifications, as well. If an ingested or modified transaction violates these rules, the transaction will not be ingested or the modification will not be saved. In addition, in the case of two users (or more) writing transactions to the database, each user cannot see the changes the other user is making until they are committed and locked.

Isolation: Each transaction is isolated from the other transactions to prevent data conflicts and maintain a consistent state of data integrity. This also helps database operations in relation to managing multiple entries and multi-level transactions. For example, if two users are trying to modify the same data (or even transaction), the DBMS lock manager will suspend other users until the changes currently being made are complete.

Durability: Durability ensures that stored transactions are preserved — even in the event of a system failure. There are a variety of ways that transactional DBMS work to ensure data durability, including:

1. Data replication: Data replication software maintains multiple copies of the same data across different nodes or servers. In this way, even if there is a power failure or other type of technical issue, existing data in the database is preserved.
2. Write-ahead logging: When changes are being made to a transactional database, the transaction changes are first loaded into an append-only file called a commit log. Once validated, the changes are then written to the database itself.
3. Checkpointing: This technique periodically writes the database's current state to disk, ensuring that saved transaction changes are permanently stored. This reduces the amount of effort required should the database ever need to be recovered after a power failure or system failure. Checkpointing also aids in minimizing the amount of work required for database recovery after a cybersecurity attack.
4. Redundant Array of Inexpensive Disks (RAID): RAID technology is used to integrate several drives into a single logical unit. This way, in case of a drive failure, there is storage redundancy and the transactional data is retained.

ACID property database example

Consider an online bookstore that tracks current inventory as well as customer orders. This information might be stored in two collections (or tables) — an inventory collection and an orders collection. As such, this example includes a multi-document ACID transaction.

Library example of ACID properties

• Atomicity: When an order is placed, the inventory collection must be updated to accurately reflect the number of copies remaining in stock. If an error occurs while updating the inventory or recording the order, the entire transaction will be aborted. This is because updating the inventory without recording the order, or vice versa, would leave the transactional data in an inconsistent state.

• Consistency: A book's inventory cannot drop below zero as this would indicate the book had been sold when it was out of stock. For this reason, a database constraint is applied to prevent a book's inventory from going below zero. This means that any order causing a book's inventory to drop below zero would be aborted.

• Isolation: Consider a case where two users place the same order at the same time. The final result of running the transactions simultaneously should be the same as if they were run sequentially providing all inventory is available (e.g., if only one book is available, one order would go through and one would be aborted).

• Durability: What if a system failure occurs right after the database has confirmed a transaction has been committed? Because the database still has the updated inventory and order details from the committed transaction in the redo log (despite the unexpected server crash), the transactional data would be preserved. The illustration below depicts the flow of events as two users attempt to purchase copies of a book when an outage occurs.

Why are transactional databases relevant today?

The global market for database management systems (DBMS) reached a value of more than USD 65.3 billion in 2022 and is expected to grow at a Compound Annual Growth Rate (CAGR) of 10.8% between 2023 and 2028. This isn't surprising since organizations are increasingly relying on big data analytics with the ultimate goal of real-time analysis and data-driven decision making to drive increased competitiveness.

However, one of the key trends relating to big data analytics and transactional databases is the shift from dumb apps to smart apps that incorporate machine learning, AI, and sophisticated automation.

This shift, which will impact organizational data strategies across most industries, will not only enhance analytical database transaction insights, but also make those insights easier to access. With that said, it will still be necessary for companies to upgrade their enterprise systems (e.g., database server, production systems, data stores) whether on-premises or in the cloud to support the transition to smart apps and the benefits AI can offer.