What Is OLTP? An Expert Guide

From ATM withdrawals to ecommerce purchases, the transactions we make each day are tracked, recorded and processed by online transaction processing, or OLTP, systems. Businesses rely on OLTP to execute thousands, and in some cases millions, of database transactions per second, making it a foundational system that supports the digital services used by the company's customers. This guide breaks down how OLTP works, examines its industry use cases and explains how companies can take full advantage of OLTP to serve more customers more efficiently.

What Is OLTP (Online Transaction Processing)?

OLTP is a form of data processing specifically suited to real-time database transactions that must be always correct and consistent. Database transactions refer to any change made in a database. OLTP databases allow businesses to manage large volumes of transactions concurrently from many users while maintaining data integrity at all times.

Relational databases are built on the principals of ACID to support OLTP systems. The tenants of ACID are:

• Atomicity — This principle ensures that transactions succeed or fail as a whole — there is no partial completion.
• Consistency — Databases use latching and locking mechanisms to ensure that transactions are applied in the correct order.
• Isolation — This guarantees no transaction will be affected by another and occur in isolation.
• Durability — All transactions are written to a transaction log to ensure they can be restored in the event of system failure.

OLTP systems built on relational databases plays a crucial role in the delivery of transactional systems at scale. For instance, travel-booking sites rely on OLTP to manage flight and hotel bookings. Financial institutions use OLTP databases to manage a range of services, from ATM transactions to online banking to record-keeping. By design OLTP transactions are simple and fast, and are foundational to ecommerce — meaning transactions in which money is exchanged as well as nonmonetary digital interactions where data integrity is critical. This means relational databases have virtually limitless applications, especially as companies deliver more services online and via digital platforms.

Key Takeaways

• OLTP systems allow businesses to support thousands or even millions of transactions for many users concurrently in near real time.
• OLTP plays a central role in delivering everyday services, from ATM withdrawals to online purchases.
• Three defining characteristics of OLTP are concurrency, assured data integrity and high availability.

OLTP Explained

When data is inserted into a relational database — a few things happen. Typically, the first thing that happens is that the record is written into memory on the database server; however, before a transaction is marked as committed, it is written to disk and recorded in the transaction log. This transaction log file provides the durability part of ACID, by having a hardened record of the transaction before it can be marked committed. Relational databases make heavy use of memory to improve their overall throughput, which allows businesses to process transactions quickly, no matter how many customers are buying or using their services at once.

Information in databases isn't always exactly correct. For instance, you might be shopping online for an item that is available in your favorite color – red, but when you go to check out, the red version is no longer available. Databases like the one showing inventory as you browse give up some accuracy to achieve very high performance without requiring very specialized and expensive database systems. These databases are intended to be read mostly like shoppers looking at inventory levels. But they are written to by very few systems. In this case, only the inventory management system.

OLTP systems don't operate this way. They are designed to always be correct no matter what. This is what you'd expect when money in transacting. To ensure that records are always correct while still achieving high performance, transactions are made to be as simple as possible. That way, records are modified very quickly while still ensuring that they are correct. So, in this case, there can be no doubt — if the customer buys a red shirt, it is taken out of inventory and then the customer's card is charged.

Integrity, speed and concurrency are the primary design goals for OLTP systems. OLTP databases are constantly read from, written to, and updated very quickly. For instance, when you buy your red shirt, your credit card issuer's database will be checked to make sure your card is valid and has sufficient credit. Then the card is debited. Once the transaction happens, the shirt is removed from inventory. A sale record is created and your credit card transaction is recorded. These small fast transactions ensure proper handling of your order and money. The OLTP system is designed to do this very quickly, which is why many users can make numerous successful transactions at the same time.

OLTP systems are often mission-critical and use algorithms designed to protect the integrity of any transaction data they contain, even for failed transactions. OLTP systems are also typically decentralized — that is, spread over multiple servers — so that availability is maintained even if one server stops working. Best practice for OLTP systems is to back them up continuously; businesses with high transaction volumes back up in a matter of seconds.

How OLTP Works

All OLTP database systems are designed to insure correctness. In database design the acronym, ‘ACID’ is used to describe these systems. Here’s what ACID stands for:

• Atomic. Each transaction must complete in full. If it doesn't, the database reverts to its last correct state. The Atomic nature ensures that the database is always correct. There are never 'half complete' transactions.
• Consistent. No transaction can change the structure of the database. So the database will always be found to be in the state expected.
• Isolated. No transaction can interfere with another transaction. So, if I'm buying that red shirt, no one else buy a red shirt until my transaction is done. My buying a red shirt will never impede someone else buying a green shirt.
• Durable. Once the transaction is completed, a power outage or system failure will not lose any data about the transaction. Particularly in OLTP databases, this requires expensive dedicated hardware. That and every other credit card transaction can never be lost.

OLTP vs OLAP

You may have heard of OLTP's similar sounding but very different cousin, OLAP or online analytical processing. Both systems work with the same large data sets, but they operate independently and serve different functions in the processing and analysis of that information. Typically, OLTP and OLAP systems are paired together, such that the OLTP system serves the needs of the business's commercial customer interactions while the OLAP system responds to business managers' analytical queries about those interactions.

Key differences between OLTP and OLAP: The infographic below lays out the differences between OLTP and OLAP systems, from their basic characteristics to how they feed into broader business strategy and better-informed decisions. While both are frequently built on relational databases, the main difference between OLTP and OLAP is that OLTP systems handle a large number of individual transactions, usually involving small data volumes, while OLAP systems query many records at once to support business-analysis queries. In the case of a flight booking, for instance, OLTP would process the transaction between a customer's bank account and the airline's bank. The airline might then use OLAP to query the entirety of its customer records to learn more about the characteristics of customers who are making bookings at any given time.

A Side-By-Side Comparison of OLTP and OLAP

Why Use OLTP

OLTP systems are fast and they don't lose data — that's exactly what you'd want when money is changing hands. Banks are the classic use case for OLTP, as they offer different types of transactions for a large and dispersed set of customers. OLTP also has applications for retailers, warehouses and supply chain managers, among others. In short, OLTP can help any business that needs to accurately track and store large volumes of transactions or that needs fast, accurate forecasting and data management.

Advantages of OLTP:

OLTP helps organizations deliver accurate concurrent services at scale and in near real time. Even prior to widespread use of ecommerce, OLTP systems let consumers use credit cards and businesses track inventory with precision, speed and assured reliability. OLTP databases are often a central repository for all activity of the business. When volumes are high, that same data can be accessed through OLAP systems to learn customer trends and better inform forecasts.

Challenges of OLTP:

As we've seen, the design goal of OLTP systems is to be very fast, very accurate, never wrong and to never lose data. Getting those things right makes them perfect for financial transactions. Those qualities also make these systems expensive. The expense is well justified for financial transactions, but is prohibitive when high volumes of queries don't necessarily produce a financial transaction. Take our shirt example. As customers browse a website, they scroll past dozens of shirts before they find the one they want. Checking the actual inventory for each shirt that scrolled by isn't justified by the causal interaction of the customer. So, showing a 'close enough' inventory is generally good enough until it's time to buy. Other database technology that doesn't conform to the ACID model can do the job far more economically.

Characteristics of OLTP Systems

OLTP systems vary based on a company's needs, but they all share a number of core ACID characteristics. As a result, they allow multiple users to make transactions with the same business concurrency. They are also tuned for speed, with transaction response times typically under one second. Finally, as a mission-critical system, OLTP offers high availability.

Below is a more detailed overview of OLTP system characteristics:

• Large transaction volumes for multiple users.

  OLTP systems are built to handle many transactions at once for large volumes of users. Online retailers depend on OLTP to manage spikes in demand for popular products on Black Friday, for example, so that every shopper can purchase without their transaction failing.

• Concurrency algorithms.

  OLTP systems handle thousands of small transactions, each of which generally occurs in a matter of seconds. Concurrency algorithms ensure that each user's transactions run independently, so that no two people modify the same data at once. In addition to preserving the integrity of a company's transaction data, this also prevents double bookings or purchases, which lead to customer dissatisfaction.

• Rapid response.

  Rapid response times are a defining characteristic of OLTP environments. Sub-second response times are the key to smooth online purchases, and customers expect nothing less.

• Available round the clock.

  Consistent, 24/7 availability is essential to OLTP systems, especially for international businesses that operate in multiple time zones. For the OLTP system as a whole, there can be no downtime. Many mission critical OLTP systems are architected with redundant components so that maintenance can occur with minimal or no loss of availability, though not inherently.

Types of Queries Processed by OLTP

OLTP systems support a range of database queries, which in turn allow businesses to apply a number of queries to well-indexed information. Nearly all relational databases are based up Structured Query Language (SQL) which allows for queries to be written by developers and business analysts alike. Queries are broken down into two categories data definition language (DDL) and data manipulation language (DML). DDL queries define the tables, indexes, and views that make up the database, while DML supports inserting, updating, and deleting the data within those table.

For an example of how these queries manifest for a customer-facing application, consider an online travel booking company that needs to keep seat availability on popular flights up to date for customers in the lead-up to summer holidays. Some queries the company's OLTP system would support include: searching for a returning customer's record, filtering holiday packages based on a customer's search preferences, retrieving in-depth descriptions of individual travel packages or hotels, and verifying that search results fall within the customer's specified dates and price range.

Architecture of OLTP systems

Architecturally, OLTP systems consist of three layers that operate independently to avoid the possibility that processes or changes to one could affect the others. The three layers of an OLTP system are:

• The presentation layer, which defines the user interface.
• The logic layer, sometimes called the business logic or application layer, where data is processed based on customer and staff interactions.
• The database layer, where information is stored and indexed.

But OLTP architectural diagrams — including the one below — are always more complex than that. The reason is that OLTP and OLAP systems typically are paired together, creating a single, broader architecture that serves the business's transactional needs (OLTP) and analytical needs (OLAP). Here's an overview of how OLTP databases collect, manage and transform data for further analysis and then share it with OLAP systems for more complex querying:

• While its main job is to manage a multitude of commercial transactions simultaneously, at high speed and nonstop, OLTP databases contain multiple data sets that also are important to the business analyses driving better-informed decisions. These data sets include product data, transaction data, and supplier data (depending on industry), and employee data, all of which are essential in delivering customer service.
• Source data from multiple OLTP systems can be fed into an OLAP system for more complex analyses.
• OLTP data is prepared for the OLAP system using extract, transform and load (ETL) processes. These processes might involve cleansing and deduplication or changes in format. Once transformed, this data is loaded into the OLAP data warehouse.
• OLAP databases typically include a data warehouse and/or data mart. A data warehouse is a central storage environment for all forms of data, compiled and integrated from various sources. Data marts are an optional subset of a data warehouse optimized for use by a particular business unit or division of the organization, meant for faster and easier access to the most relevant data for analysis. The data aggregated in these environments is used for deeper data analysis and decision-making across the organization.
• The insights a business extracts from its OLAP data analyses are fed back into its strategy and processes, which in turn shape the parameters that govern OLTP systems. By understanding the broader strategy, OLTP designers can adapt OLTP processes to lead to specific outcomes, such as offering loyal customers personalized discounts based on indexed data about their previous transactions.

Examples of OLTP Transactions

There is no shortage of use cases for OLTP. From banks and financial institutions to call centers, the ability to collect, index and manage transaction data for multiple users in near real time is a value-add across sectors. Here are just a few examples of OLTP transactions from various industries:

• Cash withdrawals from ATM machines.
• The purchasing journey and transactions taking place on large ecommerce websites.
• Mobile phone network access and transmission for voice calls and text message conversations.
• Taking restaurant orders via a food delivery app, even with many customers ordering at once.
• Managing call center data to enable associates to take customers' latest interactions into account.

Conclusion

As more businesses increase their digital customer interactions, OLTP systems are coming into greater demand. In some cases, they are replacing paper-based processes and making businesses more efficient. Digital customer interactions require companies to collect, store and effectively manage large volumes of data on their customers and operations. Achieving this manually is often impossible at the speed required, not to mention the drawbacks of the high cost of manual processes and risk of human error. OLTP systems take on all of these tasks and can do them automatically, helping organizations successfully manage their large and growing digital customer bases.

OLTP FAQs

What is an OLTP database?

An OLTP database collects, stores and processes transaction data in near real time, supporting businesses as they seek to make their services available to multiple customers simultaneously without failures or data duplication.

What is an OLTP example?

A bank's ATM network is a good example of OLTP. With an OLTP system managing transactions centrally, the bank's customers can log in and withdraw cash at the same time, no matter their point of access or how many people are accessing the ATMs at the same time.