Chapter 8 -- Programming Transactions

[Previous] [Next]

Chapter 8

At the end of the last chapter, I made a bold statement: The key to building the fastest possible application is in understanding when locking must occur and in avoiding locking when it's not necessary. However, some form of locking is inevitable in an application when many clients are contending for a finite pool of resources. One client must wait for another client to relinquish a thread, and one thread must wait for another thread to relinquish a processor. Each client needs exclusive access to various resources, and this need is detrimental to an application's concurrency and overall throughput.

When you're dealing with resources such as processors, threads, and database connections, you can respond to a growing user base by adding more hardware. Keeping the client-to-resource ratio at a reasonable level is an important part of reducing contention and maintaining acceptable response times. However, when you're dealing with the data that runs your business, the problem of contention doesn't have such a straightforward solution.

Let's say you're building an online commerce application that sells animals such as dogs, cats, and birds. You have a Products table that contains a row for each type of animal, and each row tracks the inventory level in the Quantity field. Now, here's the problem: There's only one place in the entire application where the inventory level for dogs is kept. The record that tracks the quantity of dogs represents a single point of contention across all clients. When multiple clients attempt to read and write to this value at the same time, consistency and concurrency issues arise. As the number of clients accessing the inventory level of dogs increases, it becomes harder and harder to prevent this contention from seriously compromising the performance of your application.

Just about every business application uses data in one form or another. Some applications are concerned only with querying and analyzing data, while others are primarily concerned with modifying data. Applications that modify data and have a large number of concurrent users are commonly referred to as online transaction processing (OLTP) systems. This chapter discusses the most significant issues involved with building this type of application.

You should understand several key OLTP concepts before you start writing transactions for a COM+ application. This chapter reviews the basics of transaction processing, including why a transaction must meet the "ACID rules" and what's meant by a transaction's isolation level. I'll also present two approaches to programming transactions. The first involves writing local transactions against a single database management system (DBMS) such as Microsoft SQL Server. The second involves writing distributed transactions using a more complex infrastructure supplied by COM+ and the Distributed Transaction Coordinator (DTC). Each technique is best suited for certain situations, so you must know the right questions to ask when you're deciding which approach to use.

It's really hard to create a highly optimized OLTP application using SQL statements that are portable across DBMSs from different vendors. If you're primarily concerned with application performance and scalability, you must often compromise on portability. As painful as it sounds, you must often write SQL statements and stored procedures that are particular to one specific DBMS. For this reason, I've chosen to use SQL Server and its native language, Transact-SQL, for the examples in this chapter.

I've chosen SQL Server for two reasons. First, it comes with my MSDN subscription, and second, it's the DBMS with which I have the most experience. I know that many of you use other DBMS products, such as Oracle and DB2. At a high level, the topics examined in this chapter are equally important no matter what DBMS you're using. However, when I give you a hint using Transact-SQL or talk about locking behavior and granularity, I'll leave it up to you to research your DBMS to see how those features are supported.