Online Transaction Processing

Online transaction processing is database software designed to support transaction-related applications on the Internet.

OLTP database systems are commonly used for order entry, financial transactions, customer relationship management and retail sales via the Internet. Almost any business that has a large number of users who conduct short online transactions needs an OLTP system. Database queries with online transaction processing systems are simple, typically with sub-second response times and in most cases return relatively few records. OLTP databases need to operate in as close to real time as possible.

In essence, OLTP involves data input, processing the data and then updating existing data to reflect the gathered and processed data. The complexity of the process means most organizations today use database management systems to support OLTP operations.

Online transaction processing systems  were developed to optimize  concurrency (multiple users making multiple transactions in real time). OLTP systems are typically decentralized so the failure of a single server will not cause a failure in concurrency. These types of mission critical database are also typically backed up very frequently, as often as every few seconds.

Unlike the denormalized star schema used for online analytical processing systems, the data model for online transaction processing systems is a highly normalized entity model involving a large number of tables.

According to Microsoft SQL Server TechNet, “for OLTP systems, the middle tier plays an important role in defining the overall success of the system.”  The article points out that when a new OLTP system is developed, problems with  scalability and performance typically originate in the middle tier, and in most cases, you can only really come to grips with database tier scalability and performance issues after nailing down the middle tier.