Online transactional processing (OLTP) enables the real-time execution of large numbers of database transactions by large numbers of people, typically over the internet.
OLTP is what enables the rapid, accurate data processing behind ATMs and online banking, cash registers and ecommerce, and scores of other services we interact with each day.
A database transaction is a change, insertion, deletion, or query of data in a database. OLTP systems (and the database transactions they enable) drive many of the financial transactions we make every day, including online banking and ATM transactions, e-commerce and in-store purchases, and hotel and airline bookings, to name a very few. In each of these cases, the database transaction also remains as a record of the corresponding financial transaction. OLTP can also drive non-financial database exchanges, including password changes and text messages.
In OLTP, the common, defining characteristic of any database transaction is its atomicity (or indivisibility)—a transaction either succeeds as a whole or fails (or is canceled). It cannot remain in a pending or intermediate state.
Explore the free O'Reilly ebook to learn how to get started with Presto, the open source SQL engine for data analytics.
Register for the ebook on generative AI
In general, OLTP systems do the following:
- Process a large number of relatively simple transactions: Usually insertions, updates, and deletions to data, as well as simple data queries (for example, a balance check at an ATM).
- Enable multi-user access to the same data, while ensuring data integrity: OLTP systems rely on concurrency algorithms to ensure that no two users can change the same data at the same time and that all transactions are carried out in the proper order. This prevents people from using online reservation systems from double-booking the same room and protects holders of jointly held bank accounts from accidental overdrafts.
- Emphasize very rapid processing, with response times measured in milliseconds: The effectiveness of an OLTP system is measured by the total number of transactions that can be carried out per second.
- Provide indexed data sets: These are used for rapid searching, retrieval, and querying.
- Are available 24/7/365: Again, OLTP systems process huge numbers of concurrent transactions, so any data loss or downtime can have significant and costly repercussions. A complete data backup must be available for any moment in time. OLTP systems require frequent regular backups and constant incremental backups.
OLTP is often confused with online analytical processing, or OLAP. Both have similar acronyms and are online data processing systems, but that's where the similarity ends.
OLTP is optimized for executing online database transactions. OLTP systems are designed for use by frontline workers (e.g., cashiers, bank tellers, part desk clerks) or for customer self-service applications (e.g., online banking, e-commerce, travel reservations).
OLAP, on the other hand, is optimized for conducting complex data analysis. OLAP systems are designed for use by data scientists, business analysts, and knowledge workers, and they support business intelligence (BI), data mining, and other decision support applications.
Not surprisingly, there are several distinct technical differences OLTP and OLAP systems:
- OLTP systems use a relational database that can accommodate a large number of concurrent users and frequent queries and updates, while supporting very fast response times. OLAP systems use a multidimensional database—a special kind of database created from multiple relational databases that enables complex queries of involving multiple data facts from current and historical data. (An OLAP database may be organized as a data warehouse.)
- OLTP queries are simple and typically involve just one or a few database records. OLAP queries are complex queries involving large numbers of records.
- OLTP transaction and query response times are lightning-fast; OLAP response times are orders of magnitude slower.
- OLTP systems modify data frequently (this is the nature of transactional processing); OLAP systems do not modify data at all.
- OLTP workloads involve a balance of read and write; OLAP workloads are read-intensive.
- OLTP databases require relatively little storage space; OLAP databases work with enormous data sets and typically have significant storage space requirements.
- OLTP systems require frequent or concurrent backups; OLAP systems can be backed up far less frequently.
It's worth noting OLTP systems often serve as a source of information for OLAP systems. And often, the goal of the analytics performed using OLAP is to improve business strategy and optimize business processes, which can provide a basis for making improvements to the OLTP system.
For a deep dive into the differences between these approaches, check out "OLAP vs. OLTP: What's the Difference?"
Since the inception of the internet and the e-commerce era, OLTP systems have grown ubiquitous. They’re found in nearly every industry or vertical market and in many consumer-facing systems. Everyday examples of OLTP systems include the following:
- ATM machines (this is the classic, most often-cited example) and online banking applications
- Credit card payment processing (both online and in-store)
- Order entry (retail and back-office)
- Online bookings (ticketing, reservation systems, etc.)
- Record keeping (including health records, inventory control, production scheduling, claims processing, customer service ticketing, and many other applications)
Connect the right data, at the right time, to the right people anywhere.
Built for the world’s mission critical workloads.
An embeddable database optimized for OLTP and IoT data. Deploy anywhere through IBM Cloud Pak for Data.