OLTP (Online Transactional Processing) is a category of data processing that is focused on transaction-oriented tasks. OLTP typically involves inserting, updating, and/or deleting small amounts of data in a database.

OLTP mainly deals with large numbers of transactions by a large number of users.

Examples of OLTP Transactions

Examples of OLTP transactions include:

• Online banking
• Purchasing a book online
• Booking an airline ticket
• Sending a text message
• Order entry
• Telemarketers entering telephone survey results
• Call center staff viewing and updating customers’ details

Characteristics of OLTP

OLTP transactions are usually very specific in the task that they perform, and they usually involve a single record or a small selection of records.

For example, an online banking customer might send money from his account to his wife’s account. In this case, the transaction only involves two accounts – his account and his wife’s. It does not involve the other bank customers.

This is in contrast to Online Analytical Processing (OLAP), which usually involves querying many records (even all records) in a database for analytical purposes. An OLAP banking example could be a bank manager performing a query across all customer accounts, so that he can see which suburbs had the most active online banking customers during a certain period.

OLAP is often used to provide analytics on data that was captured via an OLTP application.

So, while OLTP and OLAP often work with the same data sets, they have different characteristics.

OLTP applications typically possess the following characteristics:

• Transactions that involve small amounts of data
• Indexed access to data
• A large number of users
• Frequent queries and updates
• Fast response times

Availability

OLTP systems typically need to have extremely high availability.

This is because OLTP systems often deal with mission-critical data, and have a large number of users.

For example, a shopping cart application is mission-critical to an online retailer. If an online retailer only makes money when customers buy something via their shopping cart, that shopping cart needs to have an extremely high availability.  If the shopping cart goes down, customers can’t order any products.

This means the retailer loses money that they would’ve otherwise collected, had the OLTP been available. Some users might instead jump over to a competitor’s website to purchase the product. In this case original retailer doesn’t make any money, plus they possibly lose the opportunity to welcome a lifelong customer.

ACID Compliance

In order to maintain data integrity, OLTP databases need to be ACID-compliant. ACID refers to a standard set of properties that guarantee database transactions are processed reliably. It ensures that the transactions are accurate, consistent, isolated, and durable.

In the world of databases, a transaction is a sequence of operations performed as a single logical unit of work.  A transaction can only be successful if the whole sequence is successful. In other words, if any part of the transaction fails, the whole transaction fails. This ensures that your money doesn’t just disappear into thin air when you transfer it to another account. If the money leaves your account but it fails to enter the recipient’s account, the whole transaction will fail and the money will remain in your account.