Data Center.com

OLTP (online transaction processing)

By Ben Lutkevich

What is OLTP?

OLTP (online transaction processing) is a class of software programs capable of supporting transaction-oriented applications. In computing, a transaction is a sequence of discrete information exchanges that are treated as a unit. Many everyday acts involve OLTP, including online banking, online shopping and even in-store shopping when the point of sale (POS) terminal is tied to inventory management software.

Two important characteristics of an OLTP system are concurrency and atomicity. Atomicity guarantees that if one step is incomplete or fails during the transaction, the process will not continue. Concurrency prevents multiple users from altering the same data at the same time. In order for a transaction to be completed successfully, all database changes must be permanent, a condition known in computing as atomic statefulness.

To avoid single points of failure, OLTP systems are often decentralized. For example, Google Cloud Spanner is a distributed relational database service that runs on Google Cloud. It is designed to support global online transaction processing.

How OLTP works

OLTP involves taking transactional data, processing it and updating a back-end database to reflect the new input. While the applications may be complex, these updates are usually simple and involve only a few database records.

A relational database management system is often used to manage OLTP. Relational databases are a good option for OLTP because it requires a database that can handle a large number of queries and updates while supporting fast response times.

OLTP is used for executing online database transactions that frontline workers such as cashiers and bank tellers generate. Customer self-service applications like online banking, travel and e-commerce also generate database transactions and are tied into OLTP systems. Online transactional processing systems typically use a 3-tiered architecture, which consists of presentation, application and data tiers.

Characteristics of OLTP

OLTP-based applications have a range of characteristics and features. They include the following:

• high volume of concurrent users accessing data;
• frequent data modification;
• data integrity because data transactions happen in a specific order and users cannot change data simultaneously;
• transactions that usually involve only a few database records and small amounts of data;
• simple transactions, such as data updates, insertions, deletions and simple queries;
• indexed data sets for fast search, query and retrieval;
• fast response times, usually measured in milliseconds; and
• high availability because downtime in an OLTP application can have negative consequences, such as lost sales.

OLTP vs. OLAP

It is important to distinguish between OLTP databases and online analytical processing (OLAP) ones. To bridge the two systems, extract, transform and load processes can periodically move data from an OLTP database to an OLAP database.

OLTP

OLTP systems were originally designed to handle only operational data. They process various different kinds of queries and are geared toward surface-level transactions.

Today, some in-memory databases are able to process memory-optimized tables of transaction data stored in system memory instead of having to pull them from disk storage. This approach is called in-memory OLTP.

OLTP transactions provide data that OLAP systems analyze. They then use the results of the analysis to change how the OLTP system operates.

OLAP

OLAP databases handle any analytical processes. They are generally optimized for read-only queries and are geared toward "what if" thinking, more complex queries and deeper business analytics.

The business intelligence insights derived from OLAP systems can be used to inform an organization's OLTP strategy.

Benefits and challenges of OLTP

OLTP systems provide several benefits to users, but they also come with challenges.

Benefits

As mentioned earlier, concurrency and atomicity are the two main benefits of OLTP. Together, they provide order to real-time online transactions with the following capabilities:

• Concurrency is the ability to handle multiple users at one time. When one user makes a transaction at the same time as another, the OLTP application ensures that they don't overlap and that each transaction is accounted for.
• Atomicity, consistency, isolation and durability (ACID) compliance enables OLTP applications to manage transactions properly. Atomicity specifically ensures that all parts of the transaction work for the transaction to go through.

These traits lead to several benefits for businesses, including the following:

• Business insight. Data gathered from OLTP transactions can be used to improve business analytics and intelligence. That analysis along with any from an OLAP application can enhance both an organization's OLTP and business strategies.
• Usability. OLTP systems are simple, reliable and convenient to use, which attracts new customers.
• Speed. These systems execute business transactions quickly and reliably.

Challenges

Despite its benefits, there are challenges and shortcomings in OLTP systems if they aren't properly designed and managed. They include the following:

• Data overload. Too much data can make it difficult for businesses to process meaningful business insights in a back-end OLAP database or data warehouse.
• Data silos. Since each application has its own designated database, data gets separated in silos and it becomes difficult to share across applications.
• Limited analysis. OLTP applications are built for quick, simple database transactions and are not as effective for more rigorous analysis.
• Small to medium-sized businesses issues. OLTP applications are usually constructed for large enterprises and may not work well for SMBs.
• Hardware-related shortcomings. If something happens to the hardware -- the server hosting the relational database for example -- the customer-facing processes will be severely affected.

Examples of OLTP systems and transactions

OLTP systems are used across a variety of industries and are in many consumer-facing systems. Some common examples of systems that use OLTP include:

• ATMs, financial transaction systems and online banking applications;
• online bookings, ticketing and reservation systems; and
• recordkeeping applications, such as health records, inventory control and production scheduling.

An example of an OLTP transaction would be at an ATM. In this scenario, two people share a joint bank account. They go to ATMs in separate locations at about the same time and try to withdraw all the money in the shared account. The OLTP system handles this transaction in real time. It allows the withdrawal from the ATM that finishes the authentication process first and then deals with, and ultimately denies, the second request.

Future of OLTP

Today's big data stores are too complex and voluminous for legacy OLTP systems to process and draw insights from on the fly. To successfully use the massive amounts of data organizations deal with, the OLAP's deeper processing capabilities must be merged with OLTP's ability to analyze data fast.

As such, businesses may turn to more advanced data processing technology like predictive analytics and data structures like probabilistic databases, which deal with data that is recorded only on certain conditions.

Still, OLTP systems will likely be used in many transactional applications for the foreseeable future and in cases in which merging the real-time capabilities of OLTP and OLAP isn't feasible.

Find out more about the strategy behind Oracle's move to combine OLTP and OLAP capabilities on its MySQL platform.