OLTP

What is OLTP?

OLTP or online transactional processing is a software program or operating system that supports transaction-oriented applications in a three-tier architecture. It facilitates and supports the execution of a large number of real-time transactions in a database.

OLTP monitors daily transactions and is typically done over an internet-based multi-access environment. It handles query processing and, at the same time, ensures and protects data integrity. The efficacy of OLTP is determined by the number of transactions per second that it can process.  OLTP systems are optimized for transactional superiority hence, suitable for most monetary transactions.

The defining characteristic of OLTP transactions is atomicity and concurrency. Concurrency prevents multiple users from changing the same data simultaneously. Atomicity (or indivisibility) ensures that all transactional steps are completed for the transaction to be successful. If one step fails or is incomplete, the entire transaction fails.

Atomic statefulness is a computing condition in which database changes are permanent, requiring transactions to be completed successfully. OLTP systems enable inserting, deleting, changing, and querying data in a database.

OLTP systems activities consist of gathering input data, processing the data, and updating it using the data collected. OLTP is usually supported by a database management system (DBMS) and operates in a client-server system. It also relies on advanced transaction management systems to facilitate multiple concurrent updates.

OLTP Transaction Examples

OLTP systems facilitate many types of financial and non-financial transactions such as:

• Automated teller machines (ATMs)
• Online banking applications
• Online bookings for airline ticketing, hotel reservations, etc.
• Online and in-store credit card payment processing
• Order entry
• E-commerce and in-store purchases
• Password changes and sending text messages

OLTP systems are found in a broad spectrum of industries with a concentration in client-facing environments.

OLTP Characteristics

1. Short response time

OLTP systems maintain very short response times to be effective for users. For example, responses from an ATM operation need to be quick to make the process effective, worthwhile, and convenient.

2. Process small transactions

OLTP systems support numerous small transactions with a small amount of data executed simultaneously over the network. It can be a mixture of queries and Data Manipulation Language (DML) overload. The queries normally include insertions, deletions, updates, and related actions. Response time measures the effectiveness of OLTP transactions, and millisecond responses are becoming common.

3. Data maintenance operations

Data maintenance operations are data-intensive computational reporting and data update programs that run alongside OLTP systems without interfering with user queries.

4. High-level transaction volume and multi-user access

OLTP systems are synonymous with a large number of users accessing the same data at the same time. Online purchases of a popular or trending gadget such as an iPhone may involve an enormous number of users all vying for the same product. The system is built to handle such situations expertly.

5. Very high concurrency

An OLTP environment experiences very high concurrency due to the large user population, small transactions, and very short response times. However, data integrity is maintained by a concurrency algorithm, which prevents two or more users from altering the same data at the same time. It prevents double bookings or allocations in online ticketing and sales, respectively.

A mobile money transfer application is a good example where concurrency is very high as thousands of users can be making transfers simultaneously on the platform at every time of the day.

6. Round-the-clock availability

OLTP systems often need to be available round the clock, 24/7, without interruption. A small period of unavailability or offline operations can significantly impact a large number of people and an equally huge transaction quantity.

Downtimes can also pose potential losses to organizations, e.g., an online banking system downtime has adverse consequences to the bank’s bottom line.  Therefore, an OLTP system requires frequent, regular, and incremental backup.

7. Data usage patterns

OLTP systems experience periods of both high data usage and low data usage. Finance-related OLTP systems typically see high data usage during month ends when financial obligations are settled.

8. Indexed data sets

Index data sets are used to facilitate rapid query, search, and retrieval.

9. Normalized schema

OLTP systems utilize a fully normalized schema for database consistency.

10. Storage

OLTP stores data records for the past few days or about a week. It supports sophisticated data models and tables.

OLTP Architecture and System Design

1. Business Strategy

The business strategy influences the OLTP systems design. The strategy is formulated at the senior management and the level of the board of directors.

2. Business Process

They are processes by the OLTP system that will accomplish the goals set by the business strategy. The processes comprise a set of activities, tasks, and actions.

3. Product, Customer/Supplier, Transactions, Employees

The OLTP database contains information on products, transactions, employees, and customers, and suppliers.

4. Extract, Transform, Load (ETL) Process

The ETL process extracts data from the OLTP database and transforms it into the staging area, which includes data cleansing and optimizing the data for analysis. The transformed data is then loaded into the online analytical processing (OLAP) database, which is synonymous with the data warehouse environment.

5. Data Warehouse and Data Mart

Data warehouses are central repositories of integrated data from one or more incongruent sources. A data mart is an access layer of the data warehouse that is used to access specific/summarized information of a unit or department.

6. Data Mining, Analytics, and Decision Making

The data stored in the data warehouse and data mart is used for analysis, data mining, and decision making.

OLTP System Design

Designing an OLTP system requires knowing its base characteristics such as atomicity, concurrency, and integrity and avoiding excessive use of clusters and indexes. The following factors should be considered in OLTP design.

OLTP and OLAP

OLTP feeds transactional data and provides support to the Online Analytical Processing (OLAP) system. The key differences between the two systems are indicated below:

• OLTP systems supported transaction-oriented applications in a three-tier architecture while OLAP systems analyze data stored in a data warehouse.
• OLTP controls the daily transactions of an organization, while OLAP provides a platform for business analysis encompassing forecasting, analysis, planning, and budgeting.
• OLTP involves short online transactions, while OLAP is characterized by large volumes of data.
• OLTP uses a relational database that handles multiple concurrent transactions, while OLAP employs a data warehouse, which consolidates multiple data sources for building an integrated multidimensional database.
• OLTP systems are designed for use by frontline workers like cashiers, tellers, etc., while OLAP is used by business analysts and data scientists.
• OLTP systems modify data, balance read and write, require relatively little storage, and need frequent and concurrent backup. OLAP systems do not modify data, are read-intensive, need significant storage, and can be backed up less frequently.

Benefits of OLTP Systems

OLTP provides accurate forecasts of revenues and expenses.

• OLTP makes transacting more convenient and user-friendly for customers. The short response time and timely transaction modifications provide a lot of convenience.
• The simplicity and convenience of the system attract new customers and broadens the customer base.
• OLTP offers support to other larger databases by acting as a feeder or source, e.g., to OLAP.
• It provides a data foundation to organizations that supports transacting at the base level to decision-making at the upper level.
• Data manipulation is made easy through data partitioning.
• OLTP allows the insertion, deletion, updating, and other related queries.
• OLTP characteristics of atomicity and concurrency, which exist while guaranteeing data integrity, are among the greatest benefits for users.

Drawbacks of OLTP Systems

OLTP system crash and hardware failures that can lead to system downtime can severely affect online transactions. If the server hangs on for a few seconds, a large number of transactions can also get affected.

• OLTP lacks proper methods of transferring products to buyers by themselves.
• OLTP systems are prone to hackers and cybercriminals due to worldwide availability.
• Server failure can lead to the loss of a large amount of data from the system.
• The number of queries and updates to the system is limited.
• In business-to-business (B2B) transactions, some transactions must go offline to complete some stages, leading to buyers and suppliers losing some OLTP efficiency benefits.

Additional Resources

Thank you for reading CFI’s guide to OLTP. To keep advancing your career, the additional CFI resources below will be useful:

• Data Mart
• Enterprise Risk Management (ERM)
• Data Warehouse
• Organization Design
• See all data science resources