Transactional Database FAQs
What is an Online Transaction Processing Database?
An online transaction processing (OLTP) database is focused on rapidly reading and writing data to support the business’ ongoing operations. OLTP database workloads are characterized by a lot of quick transactions with relatively small amounts of data.
Online Analytical Processing (OLAP) stands for the exact opposite: fewer lengthy operations that retrieve/produce large amounts of data. Each workload is thus oriented towards a different measurement of performance: OLTP is latency oriented while OLAP is throughput oriented.
Consider this analogy. OLAP databases are like freight trucks: large eighteen wheelers hauling a lot of data. The raw throughput — data volume — is what is important. Online Transaction Processing, on the other hand, is more like a sports car. Built for data velocity, it is latency-sensitive. Read more about OLTP vs OLAP.
Non-Transactional Databases vs Transactional Databases
A non-transactional database does not typically support transactions in the same way as a transactional database. Many NoSQL databases are generally designed for high scalability and flexibility, and may sacrifice some transactional guarantees to achieve these goals.
In a non-transactional database, some operations may be partially completed or data may be inconsistent for short periods of time. This is because NoSQL databases are often optimized for handling large volumes of data and high concurrency, which can make it difficult to maintain strict consistency guarantees.
However, NoSQL databases can still provide other forms of consistency and durability, such as eventual consistency, where data is guaranteed to eventually converge to a consistent state, or write durability, where data is stored on disk even in the event of a system failure.
Specific application demands determine which type of database is best. Applications that require strict consistency guarantees and transactions, such as financial systems, may be better suited to a transactional database. Applications that demand high scalability and flexibility, such as social media platforms or online gaming, may benefit from NoSQL database performance and flexibility.
What are Database Transactions?
What is a database transaction? It is a series of database operations treated as a single, logical unit of work performed within a database management system (DBMS). These operations can include inserting, updating, or deleting data from the database.
Transactions are important in database management because they ensure that the database remains in a consistent state, even in the event of a system failure or other errors. When a transaction is executed, either all of its operations are completed successfully, or none of them are. For example, even during a power outage, either your bank transfer will move money out of your account or it won’t, but it won’t partially complete any operations.
What are the Database Requirements for Transactions?
Transactional databases are designed to meet the ACID (Atomicity Consistency Isolation and Durability) properties:
Atomicity. A transaction must be treated as a single unit that is indivisible. Either all or none of its operations are completed successfully. If a failure occurs during the execution of a transaction, all changes made by the transaction must be rolled back, and the database must be restored to its previous state.
Consistency. Transactional systems ensure data leaves the database consistent. This is because the database must be in a consistent state before and after the transaction is executed. In other words, the transaction must obey all the rules and constraints specified by the database schema.
Isolation. Transactions must be executed in isolation from each other, and concurrent transactions must not interfere with each other. At the start of each transaction, a consistent view of the database must exist.
Durability. After a transaction is committed, the database stores its effects permanently, even in the event of a database system failure. The changes made by a committed transaction must be persistent and recoverable.
Meeting these requirements is important for ensuring that data is consistent, accurate, and reliable, and this type of transaction support is a fundamental feature of modern database management systems (DBMS) for secure applications.
What are Lightweight Transactions for NoSQL?
There are cases when it is necessary to modify data based on its current state: that is, to perform an update that is executed only if a row does not exist or contains a certain value. Lightweight Transactions (LWTs) in NoSQL databases such as ScyllaDB and Cassandra can provide this functionality by only allowing changes to data to occur if the condition provided evaluates as true. The conditional statements provide linearizable semantics, thus allowing data to remain consistent. A basic rule of thumb is that any statement with an IF clause is a conditional statement. A batch that has at least one conditional statement is a conditional batch. Conditional statements and conditional batches are executed atomically as a LWT. Read more about Lightweight Transactions.
Transactional vs Analytical Databases
There are several important differences between transactional and analytical databases. An analytical database, also known as an OLAP (Online Analytical Processing) database, is a type of database that is designed for complex queries and data analysis. These databases are optimized for read-heavy workloads and are used to support business intelligence, data mining, and reporting applications, which require analyzing large amounts of data to derive insights and make informed business decisions.
The structure is also different between analytical database vs transactional database. The data in analytical databases is often denormalized, or duplicated across multiple tables to simplify and speed up queries. In contrast, transactional databases are usually normalized to minimize data redundancy and ensure data consistency.
Analytical databases often use a star or snowflake schema to organize data, with a fact table at the center and multiple dimension tables surrounding it. This schema makes it easy to perform queries that involve aggregating data across multiple dimensions, such as time, location, or product category.
Analytical structures also typically include tools for data visualization and reporting, allowing analysts and business database users to easily explore and understand complex data sets.
Are There NoSQL Transactional Databases?
Yes, there are some transactional NoSQL databases. While the overall goal of NoSQL databases is to provide a flexible, scalable alternative to traditional relational databases, many NoSQL databases also support ACID transactions.
Some document-oriented databases, such as MongoDB, support transactions. These databases use multi-document transactions to ensure that a group of related operations can be treated as a single unit. Other NoSQL databases, such as ScyllaDB and Apache Cassandra, have also added transaction support, using lightweight transactions to ensure data consistency and accuracy.
However, not all NoSQL databases support transactions, and the level of transaction support can vary between different databases. Additionally, because NoSQL databases are designed to be flexible and scalable, some may sacrifice transaction support in favor of other features, such as high availability or partition tolerance.
Transactional Database vs Data Warehouse
A transactional database and a data warehouse serve different purposes and are designed to handle different types of data and workloads. However, either a data warehouse or a transactional database may serve as a destination system for loading data as part of a data management system. As such, either might receive the extracted and transformed data at the end of a data pipeline.
A data warehouse is designed to support business intelligence and analytics applications. Data warehouses are optimized for querying and analyzing large volumes of historical data. They are designed to support complex queries that involve aggregations, joins, and other operations that can be time-consuming in a transactional database. Data warehouses typically contain data from multiple sources, which is consolidated, transformed, and loaded into the warehouse in a process known as Extract, Transform, Load (ETL).
While transactional databases and data warehouses both store data, they have different design principles, goals, and characteristics. Transactional databases are designed to handle fast, transactional workloads, while data warehouses are designed to handle complex analytical workloads. The data in a transactional database is typically highly normalized and optimized for data consistency and accuracy, while the data in a data warehouse is often denormalized and optimized for query performance.
Does ScyllaDB Offer Solutions for Transactional Database?
Yes, ScyllaDB is primarily used as an Online Transaction Processing database. It is used for use cases that require predictable low latencies at high throughput. This commonly involves use cases across social media, AdTech, fraud detection, location tracking, IoT, and more.
ScyllaDB supports lightweight transactions and is using Raft to implement its move from eventual consistency to strong consistency.
