Give two examples of the kind of transactional data for which an oltp system may be used.

Overview of OLTP

OLTP or Online Transaction Processing is a type of data processing approach where the transactions play a major role in data manipulation in the database. This provides facts and statistics for the Business Intelligence required for the business decision-making process. OLTP is usually applied on applications related to e-commerce, online banking, online shopping, sales and service platforms, etc. This type of data processing is known for its high performance, faster accessibility, and reliable & consistent data.

Understanding OLTP

OLTP supports only those operations that are already defined. For example, In the case of online airline booking, we need to book an airline that is related to insertion in the database. OLTP ensures the availability in the cart and concurrency in case a large number of users are accessing the same website at the same time. This is done using the following characteristics of OLTP:-

1. 3NF databases – It uses fully normalized databases to ensure the consistency and optimization of all the transactional tasks.
2. Predefined operations – It strictly performs only the predefined operations only on a small number of records.
3. Updation of databases is directly accessible to end-users.
4. A small number of records – It processes only a handful of records only. For example – ‘Retrieving the money from ATM.’
5. Maintaining historical data- It stores the records of the last few days or a week to successfully perform the transactions.

How does OLTP make working so easy?

• Online transaction process concerns concurrency and atomicity.

When two users trying to access the same data in a particular database system, none of them will be able to change the data until and unless one user has finished processing. In general, one user will process, and one will wait while making any amendments. This is coined as Concurrency controls.

Atomicity controls guarantee that in a transaction, all steps are completed successfully as a group. That is, if any steps between the transaction fail, all other steps must fail also.

• OLTP works only on normalized databases with a small number of records at a time in a decentralized system that optimizes all the transactional tasks being predefined to the system. OLTP stores less historical data, which makes it efficient.
• As it only performs the task related to insertion/deletion, it maintains the consistency and concurrency of the data in the databases, which always ensures the availability of the databases.

What can you do with OLTP?

• Its goal is availability, speed, concurrency, and recoverability.
• A large number of users can conduct short transactions using OLTP systems.
• We can design such systems that help in performing operations whose database queries are usually simple, require less than second response times, and return comparatively fewer records.

Working with OLTP

• Online transaction processing (OLTP) involves gathering information as input, processing the data according to needs, and updating data to reflect the processing information. It is used in a client-server system.
• For various decentralized database systems, OLTP brokering programs distribute transactions processes among multiple computers on a network.
• OLTP is also carried into the service-oriented architecture (SOA) and Web services.

Some crucial segments that affect the performance of OLTP are

• Rollback segments: Rollback segments are the parts of the database that record the transactions that are rolled back. This ensures consistency in the database among several users. It also records the transactions rolled back and helps in the recovery of databases to avoid loss.
• Clusters: It is a schema that has records of the columns that are common among different tables, which will further help in clustering. These clustering operations help in optimizing join operations.
• Discrete transactions: The transactions that revert all the changes made into the database until the last commit refers to discrete transactions. These are crucial in the case of short and non-distributed transactions.
• Blocksize: The size of the block should be multiple of the Operating system size to avoid non-required I/O operations.
• Buffer cache size: It is used to avoid unnecessary resource utilization by SQL statements.
• Dynamic allocation: It refers to the space allocated to tables and rollback segments
• Transaction processing: It is used to coordinate different transactions over multiple computing devices. It is similar to an operating system to maintain multithreaded operations.
• Partition (database): It is used to increase performance for parts that have transactions on a regular basis and still maintains availability and security.
• Database tuning: An OLTP system can maximize its performance as efficiently and rapidly as possible using database tuning.

Advantages

1. Concurrency: OLTP ensures transactions made into the database should not deplete the concurrency between different users. Otherwise, the users will not be able to change, or he has to wait for other users to complete so that there is not a deadlock situation.

2. Acid Compliance: ACID means a database that holds properties such as atomicity, consistency, isolated and durable. This property is an essential part of the database that records the transactions made on money. One failure could lead to much big loss. Thus OLTP ensures there is no loss of transactions and maintains the ACID property to its databases.

3. Availability: The latest data is available to all the users as concurrency is properly maintained.

4. Integrity: As the normalized database is maintained, integrity holds at every step in the case of OLTP transactions.

Disadvantages

For such concurrency, availability, and faster transactions, OLTP often requires support for transactions that include many company’s networks. Thus in today’s era, we require a more decentralized system.

Examples

Some examples of OLTP systems include

1. Order entry
2. Retail sales
3. Financial transaction systems
4. CICS – Sometimes, OLTP depends on transaction management software and database optimization tactics to process a greater number of simultaneous OLTP-oriented databases.

Why should we use OLTP?

1. To use less paper and make a faster, more accurate prediction of revenues and expenses. 2. The system that requires offline maintenance makes a good requirement for online transaction processing.

3. Availability, concurrency, and atomicity of data are much more important.

Why do we need OLTP?

We need OLTP to perform the tasks which are often performed by the system and require only a smaller number of records. The tasks are related to the insertion, update or deletion of data in databases. Consistency and concurrency are required to perform such tasks, which ensures its greater availability. OLTP maintains normalized databases and decentralized systems, which provides greater availability and consistency. This also ensures maintaining concurrency among the database. OLTP often works differently in the case of batch processing and grid computing. On the contrary, OLAP(Online Analytic Processing) works on a greater number of tasks related to complex on databases used in main business intelligence tasks.

Conclusion

OLTP is a type of data processing where a large number of users make transactions, i.e. updation, insertion or deletion, that deals with a smaller number of records. It always ensures concurrency, atomicity, availability of data using normalized databases, decentralized systems, and less historical data.

Recommended Articles

This has been a guide to What is OLTP. Here we discussed the Basic concepts, Definitions, and Advantages of OLTP. You can also go through our other suggested articles to learn more –

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.