OLTP defined
OLTP or Online Transaction Processing is a type of data processing that consists ofexecuting a number of transactions occurring concurrently—online banking, shopping,order entry, or sending text messages, for example. These transactions traditionally arereferred to as economic or financial transactions, recorded and secured so that anenterprise can access the information anytime for accounting or reporting purposes.
In the past, OLTP was limited to real-world interactions in which something wasexchanged–money, products, information, request for services, and so on. But thedefinition of transaction in this context has expanded over the years, especially sincethe advent of the internet, to encompass any kind of digital interaction or engagementwith a business that can be triggered from anywhere in the world and via anyweb-connected sensor. It also includes any kind of interaction or action such asdownloading pdfs on a web page, viewing a specific video, or automatic maintenancetriggers or comments on social channels that maybe critical for a business to record toserve their customers better.
The primary definition for transactions—economic or financial—remains the foundation formost OLTP systems, so online transaction processing typically involves inserting,updating, and/or deleting small amounts of data in a data store to collect, manage, andsecure those transactions. Typically a web, mobile, or enterprise application tracks allthose interactions or transactions with customers, suppliers, or partners and updatesthem in the OLTP database. This transaction data stored in the database is critical forbusinesses and used for reporting or analyzed to use for data-driven decision making.
Read how other companies like Retraced, ArchaeologicalPark of Pompeii, Jasci or Siemenshave been successful in building their transaction processing workloads in the cloud.
Businesses usually have two types of data processing capabilities: OLTP and OLAP.
OLTP versus OLAP
Though they sound similar and are both online data processing systems, there is astark difference between the two.
OLTP enables the real-time execution of large numbers of transactions by largenumbers of people, whereas online analytical processing (OLAP) usually involvesquerying these transactions (also referred to as records) in a database foranalytical purposes. OLAP helps companies extract insights from their transactiondata so they can use it for making more informed decisions.
The table below shows comparison between OLTP and OLAP systems.
OLTP systems | OLAP systems |
Enable the real-time execution of large numbers of databasetransactions by large numbers of people | Usually involve querying many records (even all records) in adatabase for analytical purposes |
Require lightning-fast response times | Require response times that are orders of magnitude slower thanthose required by OLTP |
Modify small amounts of data frequently and usually involve abalance of reads and writes | Do not modify data at all; workloads are usually read-intensive |
Use indexed data to improve response times | Store data in columnar format to allow easy access to largenumbers of records |
Require frequent or concurrent database backups | Require far less frequent database backup |
Require relatively little storage space | Typically have significant storage space requirements, becausethey store large amounts of historical data |
Usually run simple queries involving just one or a few records | Run complex queries involving large numbers of records |
So, OLTP is an online data modification system, whereas OLAP is an online historicalmultidimensional data store system that’s used to retrieve large amounts data foranalytical purpose. OLAP usually provides analytics on data that was captured by one ormore OLTP systems.
Requirements for an OLTP system
The most common architecture of an OLTP system that uses transactional data is athree-tier architecture that typically consists of a presentation tier, a business logictier, and a data store tier. The presentation tier is the front end, where thetransaction originates via a human interaction or is system-generated. The logic tierconsists of rules that verify the transaction and ensure all the data required tocomplete the transaction is available. The data store tier stores the transaction andall the data related to it.
The main characteristics of an online transaction processing system are the following:
- ACID compliance: OLTP systems must ensure that the entiretransaction is recorded correctly. A transaction is usually an execution of aprogram that may require the execution of multiple steps or operations. It may becomplete when all parties involved acknowledge the transaction, or when theproduct/service is delivered, or when a certain number of updates are made to thespecific tables in the database. A transaction is recorded correctly only if all thesteps involved are executed and recorded. If there is any error in any one of thesteps, the entire transaction must be aborted and all the steps must be deleted fromthe system. Thus OLTP systems must comply with atomic, consistent, isolated, anddurable (ACID) properties to ensure the accuracy of the data in the system.
- Atomic: Atomicity controls guarantee that all the steps in a transactionare completed successfully as a group. That is, if any steps between thetransactions fail, all other steps must also fail or be reverted. Thesuccessful completion of a transaction is called commit. The failure of atransaction is called abort.
- Consistent: The transaction preserves the internal consistency of thedatabase. If you execute the transaction all by itself on a database that’sinitially consistent, then when the transaction finishes executing thedatabase is again consistent.
- Isolated: The transaction executes as if it were running alone, with noother transactions. That is, the effect of running a set of transactions isthe same as running them one at a time. This behavior is calledserializability and is usually implemented by locking the specific rows inthe table.
- Durable: The transaction’s results will not be lost in a failure.
- Concurrency: OLTP systems can have enormously large userpopulations, with many users trying to access the same data at the same time. Thesystem must ensure that all these users trying to read or write into the system cando so concurrently. Concurrency controls guarantee that two users accessing the samedata in the database system at the same time will not be able to change that data,or that one user has to wait until the other user has finished processing beforechanging that piece of data.
- Scale: OLTP systems must be able to scale up and down instantly tomanage the transaction volume in real time and execute transactions concurrently,irrespective of the number of users trying to access the system.
- Availability: An OLTP system must be always available and alwaysready to accept transactions. Loss of a transaction can lead to loss of revenue ormay have legal implications. Because transactions can be executed from anywhere inthe world and at any time, the system must be available 24/7.
- High throughput and short response time: OLTP systems requirenanosecond or even shorter response times to keep enterprise users productive and tomeet the growing expectations of customers.
- Reliability: OLTP systems typically read and manipulate highlyselective, small amounts of data. It is paramount that at any given point of timethe data in the database is reliable and trustworthy for the users and applicationsaccessing that data.
- Security: Because these systems store highly sensitive customertransaction data, data security is critical. Any breach can be very costly for thecompany.
- Recoverability: OLTP systems must have the ability to recover incase of any hardware or software failure.
Databases for OLTP workloads
Relational databases were built specifically for transaction applications. They embodyall the essential elements required for storing and processing large volumes oftransactions, while also continuously being updated with new features and functionalityfor extracting more value from this rich transaction data. Relational databases aredesigned from the ground up to provide the highest possible availability and fastestperformance. They provide concurrency and ACID compliance so the data is accurate,always available, and easily accessible. They store data in tables after extractingrelationships between the data so the data can be used by any application, ensuring asingle source of truth.
The evolution of transaction processing databases
As transactions became more complex, originating from any source or device, from anywherein the world, traditional relational databases were not advanced enough to meet theneeds of modern-day transactional workflows. They had to evolve to handle the modern-daytransactions, heterogeneous data, and global scale, and most importantly to run mixedworkloads. Relational databases transformed into multimodal databases that store andprocess not only relational data but also all other types of data, including xml, html,JSON, Apache Avro and Parquet, and documents in their native form, without muchtransformation.. Relational databases also needed to add more functionality such asclustering and sharding so they could be distributed globally and scale infinitely tostore and process increasingly large volumes of data and to make use of cheaper storageavailable on cloud. With other capabilities such as in-memory, advanced analytics,visualization, and transaction event queues included, these databases now can runmultiple workloads — such as running analytics on transaction data or processingstreaming (Internet of Things (IoT)) data, or running spatial, and graph analytics.
Modern relational databases built in the cloud automate a lot of the management andoperational aspects of the database, making them easier for users to provision and use.They provide automated provisioning, security, recovery, backup, and scaling so DBAs andIT teams have to spend much less time maintaining them. They also embed intelligence toautomatically tune and index the data so database query performance is consistentirrespective of the amount of data, the number of concurrent users, or the complexity ofthe queries. These cloud databases also include self-service capabilities and REST APIsso developers and analysts can easily access and use the data. This simplifiesapplication development, giving flexibility and making it easier for developers to buildnew functionality and customizations into their applications. It also simplifiesanalytics, making it easier for analysts and data scientists to use the data forextracting insights.
How to select the right database for your OLTP workload
As IT struggles to keep pace with the speed of business, it is important that when youchoose an operational database you consider your immediate data needs and long-term datarequirements. For storing transactions, maintaining systems of record, or contentmanagement, you will need a database with high concurrency, high throughput, lowlatency, and mission-critical characteristics such as high availability, dataprotection, and disaster recovery. Most likely, your workload will fluctuate throughoutthe day or week or year, so ensuring that the database can autoscale will help you savea lot of expense. You’ll also may need to decide whether to use a purpose-built databaseor general-purpose database. If your requirements are for a specific type of data, apurpose-built database may work for you, but make sure you aren’t compromising on any ofthe other characteristics you need. It would be costly and resource-intensive to buildfor those characteristics later in the application layer. Also, if your data needs growand you want to expand the functionality of your application, adding more single-purposeor fit-for-purpose databases will only create data silos and amplify the data managementproblems. You must also consider other functionalities that may be necessary for yourspecific workload—for example, ingestion requirements, push-down compute requirements,and size at limit.
Select a future-proof cloud database service with self-service capabilities that willautomate all the data management so that your data consumers—developers, analysts, dataengineers, data scientists and DBAs—can do more with the data and accelerate applicationdevelopment.
Learn more about Oracle’s AutonomousTransaction Processing Database, the cloud OLTP database service. Try it for free.
