Over the past year or two, the concept of data warehousing has been in the limelight throughout the IT industry. Dozens of books and hundreds of articles have been written on data warehousing, and entire conferences are now dedicated to the topic. However, amid all this flurry, it seems that many people have forgotten to focus on what is at the core of every data warehouse: the online transaction processing (OLTP) systems that are often responsible for the data that feeds the data warehouse. In addition to performing their traditional role of supporting a corporation's day-to-day operations, OLTP systems are now increasingly responsible for supplying accurate data to be stored long-term in the data warehouse. Furthermore, the emergence of business-to-business and business-to-consumer electronic commerce is drawing attention to well-designed OLTP systems capable of gracefully managing large volumes of transactions. Although OLTP systems may seem like yesterday's news from a marketing perspective, their newly expanded role means that they are more important than ever.
Academic texts often attempt to create very precise definitions of transaction-processing systems. However, no single explanation can be sufficient, because a transaction-processing system can be viewed from multiple perspectives: the user, DBA, database vendor, and application developer. In this article I will cover the key issues required to understand OLTP systems from each of these perspectives. It is the first in a series of articles that will cover the important aspects of understanding and building successful OLTP systems. Future articles will include topics such as high-performance OLTP hardware, three-tier OLTP architectures, benchmarking, security, and the impact of the Web on OLTP.
From a user's point of view, OLTP systems are extremely dynamic. New records are continually being added, and existing records are being updated or deleted. This creates an environment where change is nonstop. Historical (static) data is often a part of these systems, but it is typically limited to a few months of history.
In addition, transaction-processing systems are characterized by at least three common user requirements. First, OLTP systems need to support many users (often hundreds or thousands) simultaneously. Second, each of these users has stringent response time requirements that dictate that their transactions must complete within the contractual "service-level commitment" time, which is usually a few seconds. And third, since these systems are critical to day-to-day operations, organizations require that strict systems availability requirements be met. In addition, users depend on absolute integrity in the processing of their work. Losing a transaction or executing the same transaction more than once are considered severe problems (this scenario is referred to as transaction integrity). Also, in the modern transaction-processing environment it is not uncommon for a transaction to spawn a subtransaction within the commit protocol of the first transaction. This is referred to as a nested transaction, and it enables users to explore data without interrupting a transaction in process.
As I already stated, transaction-processing applications must often support many users, each of whom is executing transactions. The transaction set may be similar, or they may address different business objectives. By design, each transaction does not require a great deal of computing resources to execute, because each only adds or modifies a minimum number of rows in the database. But, in aggregate, the strain on system resources is often extreme. It is the DBA and system administrator's job to make sure the application and system are running efficiently in order to handle the manifestations of this strain, the most common of which are discussed later in this article.
First, administrators must address the fact that OLTP systems are prone to I/O bottlenecks. The intuitive notion that these bottlenecks are caused by a large number of transactions changing an overwhelming amount of data on disk is incorrect. It's not the amount of data that's being changed that's important here ý rather, it's the location or position of the data that is being changed. More precisely, it's where the data being changed by one transaction is located relative to the data being changed by other transactions. One transaction may be modifying rows at the beginning of a table, another may be modifying rows at the end of the table, a third may be focusing on rows that happen to fall in the middle of the table, and a fourth may be trying to modify rows that are being modified by the first. This means that the disk head is madly scrambling all over the disk to get to the particular row that's being modified. Once the head is in position, it modifies the small amount of data associated with the transaction, and then the head moves again to find the rows for the next transaction.
This is, unfortunately, just about the least efficient way to use a disk drive because moving a disk head all over the platter is a time-consuming operation. For example, assuming the disk head moves to a randomly selected track for each I/O request, a fast disk drive can execute about 80 I/Os per second. Even if each I/O reads or writes an 8K data block (which is far larger than the amount of data affected by most transactions), the effective I/O throughput is only 640K per second. By comparison, if the disk head starts at one end of the disk and scans to the other end in an orderly fashion, I/O throughput can easily exceed 4MB per second. Such excessive head seeking limits throughput and causes bottlenecks. And unfortunately, it is inherent in the nature of OLTP transactions. To avoid this situation, administrators of large OLTP systems are sometimes forced to split up the database across several machines. (However, if the database is small enough, the administrator can make it memory resident, which will also solve the problem.)
Second, OLTP systems inevitably stress the process manager or the thread manager that is part of the operating system. Depending on the database, there is essentially one operating system process or one thread within a process for each database request that is being carried out on behalf of a user. With a large number users, there will generally be a large number of concurrent tasks being executed, and this often results in a lot of operating system overhead required to manage all the processes or threads. The administrator must tune the system carefully to handle this. Note that much of this issue can also be handled in the design stage, rather than the tuning stage, by using transaction processing monitors (discussed more later) such as CICS and Encina.
Third, transaction processing systems require lots of memory because of the large numbers of users they must support. As each user executes a transaction, the DBMS needs to maintain certain context information about the transaction and the user, and it therefore carves out a piece of memory for each user. Again, depending on the implementation, this memory is used to store such things as the user's name, a unique transaction ID (used to track which rows were changed by a particular transaction), the transaction status, the text of the SQL statements that make up the transaction, and any results that come back from the transaction. In some cases, these memory areas may be a few hundred kilobytes for each user. This is often swappable or pageable memory, so the system's disks can be used if memory runs out. But there is a performance penalty to pay, since it is far faster to access the context data if it remains in memory rather than having to page it back into memory from disk.
OLTP systems pose many technical challenges that must be addressed by the DBMS itself. This, in turn, means that the database vendors are responsible for putting the required functionality into the DBMS. These challenges, which include concurrency issues, efficiently managing large numbers of users, and reliability issues, have traditionally been unique to the realm of transaction processing.
To meet users' response-time requirements, a DBMS must provide maximum performance to a large number of simultaneous users. The DBMS must manage concurrency issues that can arise because you have multiple users modifying the data concurrently. If we were only concerned about people reading but not modifying the data, then there would be no concurrency problems ý we could let every user read the same piece of data at the same time. We could even give each user his or her own copy of the data, because it would never need to be refreshed or updated.
The fact that the data is changing forces database vendors to devise ways to handle concurrency problems. One of the goals of concurrency management is to ensure that two users are not allowed to update the same piece of data (or the same database structure) at the same time. If this weren't prevented, we'd risk running into what is known as the "lost write" problem. To understand this problem, consider a banking application that has a table with bank account numbers and their associated balances. Assume that a married couple has a joint account with the bank, and their current balance is $2,000. Whenever a check is deposited, the transaction that executes first reads the current balance, adds to it the amount of the deposit, and then writes the result to the database. What if the husband and wife are at different ATMs and they both try to deposit checks at the same time? Both transactions would first read the current balance, and both would see that it is $2,000. Then the husband's transaction would add to it the amount of his deposit (say it's $100) and would write out the new balance of $2,100. Unfortunately, the wife's transaction would add the amount of her check (say it's $150) to what she believes is still the current balance (the original $2,000) and would write out the new balance of $2,150. In this case, the husband's write gets lost, and the final balance incorrectly reads $2,150 instead of $2,250.
The solution to this involves requiring that a DBMS lock a piece of data before modifying it. To be successful, a database's locking scheme must ensure that a lock can only be held by one person at a time. In the lost write example, at the time the current balance is read, either the husband or the wife would get the lock on the account, but not both. Whoever gets the lock can execute the transaction. The other person must wait until the first transaction completes and the lock becomes available again.
Locking also has its own set of issues, usually referred to as locking granularity. Many years ago, database locking schemes had very low (or coarse) granularity. The lowest granularity, which is still being used for many batch jobs, is to lock the whole database. However such a lock is clearly is inappropriate for OLTP, because only one user can modify the database at a time. To improve the situation a bit, many DBMSs initially used table-level locks, which meant that if you wanted to lock a piece of data that you intended to update, you locked the entire table. But even this wasn't sufficient, because other users were prevented from updating any other rows in the table until your transaction completed, resulting in insufficient system throughput. The situation improved markedly when databases began to support page-level locking, which meant that you could lock an individual disk page/block instead of the whole table. But, because each disk block holds multiple rows, you were still locking more rows than was truly necessary.
The real solution came with the advent of an efficient scheme to support true row-level locking. Rather than having a lock for each row that had to be separately managed, the locks are placed within each row itself. A single bit appended to each row is used to indicate whether or not that row is locked. Rather than having to look at a long list of locks to see if the one you wanted is already taken, you simply look at the row itself to see it if is locked. This high level of granularity maximizes throughput, and nearly all major databases either currently support or have announced support for row-level locking. (See Figure 1).
Another goal of concurrency is to ensure transaction atomicity. This refers to the notion that a transaction is logically a single, indivisible unit of work (even though in reality transactions are composed of multiple steps). For example, taking a customer's order might involve inserting records for the customer's billing and shipping addresses into the address table (if it's a new customer), updating inventory table records to reflect the new inventory levels, inserting records into the outgoing shipments table that state the quantity and product number of each item purchased, and inserting a record into the accounts payable table so a bill will be sent out. But, all these steps are part of a single logical unit of work: taking a customer's order. Atomicity implies that either all or none of the changes made by this transaction are visible. In other words, if atomicity is supported and a user is in the middle of executing this transaction, the changes are not visible to any other concurrent users until the transaction is committed.
There are two solutions to achieve transaction atomicity. First, the rows could be locked in such a way as to prevent other users from not only modifying but also even reading the rows until a transaction is committed. Other users would have to wait until the entire transaction either committed or rolled back before they could see any of the changes. While this scheme works, it can have a negative effect on throughput by forcing users to wait.
Another solution relies on the fact that database engines must keep the original copy of any changed row until the transaction is committed, in case the user decides to cancel the entire transaction by issuing a rollback command. Since an original copy of the row already exists, any other user who wants to read rows that have been changed but not yet committed can be redirected transparently to look at the copy of the original row. These other users would still be prohibited from changing the row (otherwise we're back at the lost write problem), but at least rows in the middle of being changed by one transaction could be concurrently read by other transactions while still maintaining atomicity. (See Figure 2.)
As mentioned previously, databases must have a separate thread for each database request that is being executed on behalf of a user. (I'm using the generic term "thread" to encompass both operating system processes as well as operating system threads.) But, this doesn't mean that there must be a thread for each user. Rather, the real requirement is that a separate thread exists for each database action being carried out concurrently. In OLTP systems, you may have 1,000 users connected, but at any given moment you might only have 100 of them actively executing transactions ý the others are temporarily idle.
This fact can be used to reduce greatly the number of threads that must be created and managed on the database server platform, thereby dramatically reducing both memory requirements and the operating system overhead required to manage large numbers of threads. Since most users are idle at any given point in time, let's examine an optimization that associates threads only with actively executing transactions rather than having threads be permanently associated with individual users. (Actually, it's possible to have threads that are associated only with actively executing SQL statements rather than with an entire transaction, but it's a little more complicated to do.) In the scenario I just described, you would only need roughly 100 threads instead of 1,000. When a user begins a new transaction, the next available thread in the thread "pool" is associated with the transaction. Each SQL statement that is part of the transaction is executed by the same thread, and when the transaction completes (with a commit or rollback), the thread is freed up and returned to the pool to await the next incoming transaction, regardless of which user initiates the transaction.
Often this optimization is carried out within the database server, but it is in fact more commonly carried out by a separate class of middleware products known as transaction processing monitors, abbreviated TP monitors. Mainframe TP monitors have been around since the earliest OLTP applications were built decades ago, and they have grown to be very sophisticated tools encompassing far more functionality than just optimizing thread management. They are now becoming increasingly popular for large Unix and Windows NT OLTP applications as well, driven by the need to support ever-growing user populations.
Be forewarned, though, that by itself, the previous description of the database sharing threads among multiple users is only a small part of the picture. For an OLTP system to support large numbers of users, not only must the database be multithreaded, but so must the message queuing and retrieval routines, the way input is received from clients/terminals, the way output is delivered, transaction logging and journalling, and so on.
OLTP systems are critical to the daily operations of many companies. Administrators need database vendors to provide functionality to back up the database so that none of the data will be permanently lost in case a system error causes some of the data to be corrupted or unreadable. If the data in OLTP systems never changed, then you would only need to make one copy of the database, put it in a safe place, and never have to worry about backing up your data again. But, because the data in an OLTP system is highly dynamic, backups must be made at regular intervals to ensure that none of the changes made to the database will be lost if a system error occurs.
Backups at regular intervals aren't enough. Two technical problems that vendors must address still remain. First, all changes made before the last backup are protected from failure, but what about the changes made after the last backup? How are those preserved? Database transaction logs are the answer. Glossing over the details (which can get very complicated), every time a transaction is committed, not only are the actual changes written to the database files, but a record of what transaction was executed is also written to a separate log file, located on a separate disk. If a database file is corrupted, you restore the most recent backup and then use the log to ýreplay" all the transactions that were made since the backup, thereby restoring the database files to what they looked like just before the system error occurred.
The second problem is caused by the fact that many organizations need their OLTP systems running 24 x 7, and it is therefore often unacceptable to require shutting down a database in order to back up the files. But, how can you back up a database while users are still making changes to it? It would seem that the resultant backup would contain numerous inconsistencies. For example, assume that a backup is in progress and that half of the database has already been backed up. What happens if a transaction then modifies multiple rows, some of which are in the half that has been backed up and others that are in the half that hasn't yet been backed up? The changes made to the latter half will be part of the backup, but the changes made to the first half will not. However, you can use a clever recovery algorithm to fix these inconsistencies. By liberally using timestamps in the database files to identify when rows were last changed, and by also including the same timestamps in the log files, it is possible to start with an "inconsistent" backup and then examine the logs to determine which changes are missing from the backup. With this solution, database vendors can now provide true online backup functionality.
Fault tolerance is also an important aspect of OLTP systems. Although some fault tolerance can be implemented in DBMS software (for example, you can consider backups a form of fault tolerance), systems typically rely on hardware enhancements such as RAID disks and dual power supplies for increased reliability. A full discussion of fault-tolerant hardware is outside the scope of this article, but the general idea is to replicate components that can fail (thereby providing redundancy) or to provide a means to cordon off and work around failed components so the system still runs (though in a slightly degraded fashion).
Application developers often have the most difficult job. To be successful, they must have a strong understanding of everyone's perspective. Clearly, they must understand the users' perspective to deliver an application that meets users' needs. But, they must also understand the vendors' technical capabilities and limitations and they must understand the technical issues that the administrator must handle (which often includes hardware platform capabilities and limitations). Then, they must synthesize these various perspectives to build a business-oriented solution.
It is important to recognize that larger, more complex systems do require deep technical understanding from a number of perspectives. You cannot successfully rely only on high-level abstractions and assumptions when your system is pushing the limits. For example, it is not sufficient to assume that building a relational database application on a uniprocessor requires the same approach as building one on a multiprocessor machine. Sure, the SQL in both cases might be the same, but the underlying physical designs will be markedly different in order to take advantage of the unique capabilities of multiprocessor hardware.
Now that you have some background on the different perspectives of an OLTP system, it is important to understand how OLTP applications differ from other applications, notably decision-support systems (DSS) applications, including data warehouses and data marts. Traditionally, in many ways, the classic OLTP system is the polar opposite of the classic DSS system.
These differences mean that the skills required to build and maintain an OLTP application successfully are very different from the skills required to build a successful DSS system. As one example, the use of indexes in OLTP systems must be thought out carefully, but in DSS applications, DBAs can essentially get away with adding just about any index they can think of. Assuming the query optimizer is reasonably intelligent enough to determine correctly when to use an index (granted, query optimizers aren't perfect here), the only real downsides to adding too many indexes in a DSS application are the increased disk space usage and, consequently, the additional time required to update several indexes after a batch load or large incremental update. However, in an OLTP system, every time you change the value of an indexed column in a particular row, you also have to update the index to reflect the change. This additional overhead slows the application down. Therefore, in OLTP systems, the tradeoffs of adding an index must be considered.
One important trend occurring in the OLTP world is the move toward combining traditional OLTP and DSS systems. Because of the differences I already described, OLTP and DSS systems have been kept separate. However, two separate systems means that DSS users are usually working off a static copy of the data and therefore do not have access to the most recent changes that have occurred in the OLTP system's database. Organizations are trying to find ways to combine OLTP and DSS functionality into one encompassing architecture for two reasons: first, to have the most up-to-the-minute data available for DSS analysis, and second, once the analysis is done, to be able to make changes to the data in real time. For example, in a retail environment, an analyst could use up-to-the-minute sales statistics in a DSS fashion to determine the optimal set of products to put on sale and then immediately change the prices of those products as well as enter orders to increase the inventory levels of those products.
But, this is a very difficult proposition. The different goals of OLTP and DSS systems mean that each has a different optimal architecture and that the operations of each interfere with each other's performance. For example, complex queries against large tables can bring a system to its knees, making it nearly impossible to meet the response-time requirements of the OLTP users. In my opinion, these combined OLTP and DSS systems will have some limited successes, simply because if the application is small enough, hardware performance advances (such as parallel hardware architectures) have made it possible to provide enough raw horsepower to support both sets of needs. But, even using current technologies, most large combined systems have failed miserably. That's why a third option called the operational data store was invented. By having near-realtime feeds from the OLTP systems, the DSS systems carry fresher data that, although not up-to-the-second, can be up-to-the-minute. This provides many of the benefits desired while keeping the OLTP and DSS systems separate.
The parallel hardware architectures I referred to earlier are themselves another powerful trend that is changing the face of OLTP. These architectures include symmetric multiprocessors (SMPs), clusters, massively parallel processors (MPPs), and nonuniform memory access (NUMA) machines. Leveraging these architectures, systems with enormous CPU and I/O power can be built economically, which means OLTP systems can be made more realtime, support more users, and capture and process more transactions than was ever before possible.
This series of articles focuses on helping you understand and build successful OLTP systems. But, how do you define success? Criteria such as high-performance, predictable response times, high availability, reliability, and scalability are all important. So are data integrity, transaction integrity, and the ability to share resources efficiently among many users. No single factor defines success. But, such is the nature of OLTP applications ý they are complex systems with multiple layers of interactions, all of which must meet strict requirements in order for the overall system to satisfy an organization's needs.
What did you think of this article? Send a letter to the editor.
Figure 1.
The effects of different database locking granularities.
Figure 2.
Ensuring transaction atomicity without blocking reads.
Ken Rudin is the CEO and co-founder of Emergent Corporation, a San Mateo, California-based consulting firm dedicated to helping businesses design and implement scalable IT solutions. Emergent also produces the report "Scalable Program Management: The Key to Building Scalable Applications." You can email Ken at krudin@emergent.com.
January 1998 Table of Contents | Article Index | Search | Site Index | Home
DBMS and Internet Systems (http://www.dbmsmag.com)
Copyright © 1997 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.