Online Transaction Processing Database Design

Online transaction processing database applications are optimal for managing changing data. These applications typically have many users who are performing transactions at the same time that change real-time data. Although individual requests by users for data usually reference few records, many of these requests are being made at the same time. Common examples of these types of databases are airline ticketing systems and banking transaction systems. The primary concerns in this kind of application are concurrency and atomicity.

Concurrency controls in a database system guarantee that two users cannot change the same data, or that one user cannot change a piece of data before another user is finished with it. For example, if you are talking to an airline ticket agent to reserve the last available seat on a flight and the agent starts the process of reserving the seat in your name, another agent should not be able to tell another passenger that the seat is available.

Atomicity guarantees that all the steps in a transaction are completed successfully as a group. If any step fails, no other steps should be completed. For example, a banking transaction may involve two steps: taking funds out of your checking account and putting them into your savings account. If the step that removes the funds from your checking account succeeds, you want to make sure that the funds are put into your savings account or put back into your checking account.

Online Transaction Processing Design Considerations

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Transaction processing system databases should be designed to promote the following:

• Good data placement
  
  I/O bottlenecks are a big concern for OLTP systems, because of the number of users modifying data all over the database. When you design a database, determine the likely access patterns of the data and combine frequently accessed data together. Use filegroups and RAID (redundant array of independent disks) systems to help in this.
  
  
• Short transactions to minimize long-term locks and improve concurrency
  
  Avoid user interaction during transactions. Whenever possible, run a single stored procedure to process the whole transaction. The order in which you reference tables within your transactions can affect concurrency. Put references to frequently accessed tables at the end of the transaction to minimize the duration that locks are held.
  
  
• Online backup
  
  OLTP systems are frequently characterized by continuous operations in which down time is kept to an absolute minimum. That is, they operate 24 hours a day, 7 days a week. Although the SQL Server Database Engine can back up a database while it is being used, schedule the backup process to occur during times of low activity to minimize the effects on users.
  
  
• High normalization of the database
  
  Reduce redundant information to increase the speed of updates and improve concurrency. Reducing data also improves the speed of backups, because less data has to be backed up.
  
  
• Minimal or no historical or aggregated data
  
  Data that is rarely referenced can be archived into separate databases, or moved out of the heavily updated tables into tables that contain only historical data. This keeps tables as small as possible, and improves backup times and query performance.
  
  
• Careful use of indexes
  
  Indexes must be updated every time a row is added or modified. To avoid over-indexing heavily updated tables, keep indexes narrow. Use the Database Engine Tuning Advisor to design your indexes.
  
  
• Optimum hardware configuration to handle the large numbers of concurrent users and quick response times required by an OLTP system

</ 2003 authoring ddue.schemas.microsoft.com 5:content>