Transactional Design Issues

[Previous] [Next]

I'd like to cover just a few more issues in this chapter. I've explained how to program local transaction as well as how to program distributed transactions with COM+ and the DTC. Now let's compare these two approaches so that you can make an informed decision when choosing between them. I also want to talk about a few different strategies for partitioning your business logic and your data access code. As you'll see, each approach offers trade-offs with respect to application performance and code maintainability. I'll finish by discussing how to avoid deadlocks and how to tune a transaction.

COM+ Transactions vs. Local Transactions

The creators of MTS and COM+ put a great deal of work into the infrastructure for running distributed transactions. Consequently, Microsoft's documentation usually pushes COM+ transactions much more than local transactions. However, both approaches are viable. Many DBMSs and mainframe applications have built-in TMs that are capable of enforcing the ACID rules on their own, without the DTC. You should understand all the relevant issues so that you can make the best choice for any given project.

Running a local transaction in a single round trip always offers better performance than a COM+ transaction because a COM+ transaction requires extra calls between the COM+ application, the DTC, and the RM to enlist connections and run the two-phase commit protocol. However, it's hard to say how much better the performance will be because it depends on your hardware and your network configuration. The difference could be significant or it could be marginal. The only way to really know is to run benchmarks.

If your data is spread out across multiple computers or differing database formats or both, you have no choice but to use COM+ transactions and the DTC rather than local transactions. But even if you currently don't have to deal with multiple DBMSs, you might decide to use COM+ transactions to minimize the impact of integrating other DBMSs into your application in the future.

Another reason to choose COM+ transactions over local transactions is that COM+ transactions simplify your application designs and reduce coding requirements. In the case of a component that will be used as a secondary object, you don't even have to add any code. You can simply configure the component to support transactions, and all of its read/write activity will be charged against the distributed transaction started by the root object. If you're creating a component to play the role of the root object, you usually need to add some code—you'll probably add calls to SetComplete or SetAbort—but this is easier than working in a procedural paradigm in which the programmer is responsible for explicitly starting, committing, and rolling back transactions.

So it comes down to a choice between performance and scalability on one hand and flexibility and ease-of-use on the other. Do you want maximum speed? If so, make sure all your data is in one DBMS and use local transactions. Do you need to run distributed transactions? If so, use COM+ transactions. Do you like the way that declarative transactions simplify your life? If so, use COM+ transactions.

If you decide to go with COM+ transactions, you can always compensate for the extra round trips by obtaining faster hardware. You can also do a number of things to optimize the way COM+ and the DTC run distributed transactions. The Windows DNA Performance Kit, which is part of the Microsoft Platform SDK, is an excellent resource for tuning COM+ transactions. (It's not in the default installation, so you have to specify it during a custom installation.) The Windows DNA Performance Kit offers guidelines for application design and hardware tuning that significantly improve performance. It also backs up these recommendations with plenty of benchmarks.

Here are just a few of the guidelines you'll find in the Windows DNA Performance Kit:

  • Place the DTC transaction log in a storage location that offers fast access. The default location for the log isn't as fast as it could be.
  • Don't create transactions across process boundaries.
  • If all of your data is in a single DBMS, such as SQL Server, you can improve performance by reconfiguring the Windows 2000 server that runs your COM+ application to use the DTC on the computer running SQL Server.

Don't mix COM+ transactions with local transactions

You should probably avoid running a COM+ transaction with a local transaction in a single request. For instance, what if a root object creates a nontransactional object and then both objects make a connection to the same DBMS? If the nontransactional object starts a local transaction, you can easily get into trouble. The problem is that the DBMS sees two different transactions and assumes that the distributed transaction belongs to one client and the local transaction belongs to another.

Why is it a problem if a single request from a client results in two different transactions? The biggest problem is that the locks of each transaction will block the other. If the root object runs a SELECT statement resulting in a table lock, the local transaction could block an attempt to access the locked table. Unfortunately, the table lock is held until all the objects are deactivated. As you can see, this is a recipe for deadlock. You should never respond to a client request by running a distributed transaction and a local transaction on the same database at the same time.

Partitioning Business Logic and Data Access Code

Early in the design phase, you must decide how important it is to separate business logic from data access code. In larger projects, it's best to maintain all your business logic in one set of classes and all your data access code in another. In smaller applications, a formalized separation might be overkill.

The transactional programming model of COM+ doesn't force you to take one approach or the other. For example, you can design a transaction with business logic in one component and data access code in another. A business logic component can serve as the root object by modeling a high-level workflow composer. This business component can run COM+ transactions using customized data access components that are configured to run in their creator's transaction. This formalized separation of business logic and data access code results in higher levels of maintainability because your business logic isn't intermingled with your ADO calls. It is also easier to port your applications across different DBMSs.

In a smaller application, you might decide that a formalized separation of business logic and data access code isn't important. Since you've already split the presentation tier code from your nonvisual logic, you might not feel the need to further partition your nonvisual logic. You can thus keep your design simple and get your code into production more quickly. If you can make the assumption that you'll only use one specific DBMS for the lifetime of the project, you don't have to deal with the issue of porting across different DBMSs.

Whatever you decide with regard to separating business logic from data access code, you should never lose sight of performance and the need to minimize round trips to the DBMS. Suppose you're writing a method to remove a requested quantity of a certain product from inventory. It's intuitive to write code to accomplish this task in two discrete database operations. First, you run a SELECT statement to open an ADO recordset on the Products table. This allows a Visual Basic object in the middle tier to determine whether the quantity currently in inventory is enough to satisfy an order request. If the requested quantity is greater than the inventory quantity, the object should roll back the transaction and raise an error back to the caller. If a requested quantity is less than or equal to the inventory quantity, the component can complete its mission by running an UPDATE statement against the Products table.

The upside to this approach is that it allows you to maintain your validation logic inside a Visual Basic component. The downside is that it takes two round trips to the DBMS to complete an operation that could be completed in one. There's obviously a trade-off between performance and maintainability. If you want the best performance, you should submit a SQL batch or execute a stored procedure.

If you decide to write logic in SQL, you must decide between using SQL batches and using stored procedures. Many Visual Basic programmers prefer stored procedures because they're a little faster and because the Visual Basic IDE makes it pretty awkward to maintain and edit SQL batches in Visual Basic source code. If you use stored procedures, you only need to write the ADO code to call stored procedures.

If writing validation logic in Visual Basic is more important, you can compromise by making one round trip to the DBMS to fetch all the data you need for validation and running your validation checks in the middle tier using Visual Basic. If you find any validation errors, you can roll back the transaction and raise an error back to the caller. If no validation errors occur, you can make a second round trip to the DBMS to complete your writing and commit the transaction. The trade-off with this approach is that you'll probably hold locks in between these two round trips.

Avoid mapping Visual Basic classes to tables

Programmers in search of object-oriented purity often forget the requirements of an OLTP system. The most important thing in a scalable design is a "get in and get out" mindset. The class-per-table approach usually doesn't cut it because it sacrifices performance and shorter lock times in favor of a higher level of maintainability.

For example, if you have individual classes for the Products table, the Customers table, and the Orders table, each class probably establishes its own connection and submits its SQL statements independently of the others. This means at least three round trips to the database. If each class makes several round trips on its own, this approach starts to get really expensive.

You can reduce the number of round trips by creating a model that uses a single Visual Basic class for each database connection. Such a class is often referred to as a session component. The purpose of a session component is to conduct all your operations in as few round trips as possible. Generally, the session component exposes a method for each transaction or command that can be run on the DBMS.

The advantage of designing in terms of sessions is that it offers the best performance and scalability. Unfortunately, there are a few disadvantages as well. You have to write more logic and validations in SQL and less in Visual Basic. This makes it harder to port your applications from one DBMS to another. You're also required to raise errors from SQL code and catch them in Visual Basic code. In addition, you might have to abandon your UML code generator unless it has a check box to turn on the Stop Thinking Like An Object-Oriented Guy From The 1980s And Start Thinking Like An Efficient OLTP Design Tool option.

Dealing with Deadlocks

Creating an OLTP application typically requires balancing two competing goals. On one hand, you want the highest possible levels of concurrency. On the other hand, you must have isolation to guarantee the consistency of your data. This means that the system must place locks on data items to block certain transactions while other transactions perform their work.

Locking is critical. Without it, you'd have no way to ensure data consistency. Most of the time, when a transaction is blocked, it simply waits its turn and then gets to do its work. However, sometimes locks acquired by transactions don't have the desired effect. In some situations, the locks held by two or more transactions conflict in such a way that the situation can't be resolved by waiting. This is known as a deadlock.

For example, suppose transaction A has acquired a write lock on data item X and is attempting to acquire a write lock on data item Y to complete its work. If transaction B has acquired a write lock on data item Y and is waiting on the write lock held on data item X, the two transactions have hit a stalemate. Without intervention, both transactions will wait indefinitely for the other to release its locks.

A single DBMS such as SQL Server can quickly detect a deadlock situation. SQL Server resolves a deadlock by terminating one of the transactions and sending an error message to the victim. When the locks of the victim are released, the other transaction can complete its work.

In a distributed transaction, some deadlock situations can't be detected by any single resource manager. This is why COM+ transactions have a default timeout of 60 seconds. If a transaction can't be completed within a minute, the DTC assumes that a deadlock has occurred and aborts the transaction. In either case, your code must be ready to deal with deadlock errors when they occur.

You should think twice before you reconfigure a component's transaction timeout interval to something higher than the default of 60 seconds. In most systems, transactions are short-lived—usually less than a second. If you reconfigure a component to have a 20-minute timeout interval and it experiences a deadlock, you can unintentionally create a set of meaningless locks that will sit there and block other transactions for 19 minutes and 59 seconds. The important point is that you should never casually change the timeout interval for a transactional component to a large number.

Coding to avoid deadlocks

To prevent deadlocks, you can employ a few standard techniques. A cyclic deadlock can occur when two programmers have written separate transactions for the same set of data. For example, one programmer might write a transaction that modifies the Products table and then the Customers table. If another programmer writes a second transaction that accesses those tables in reverse order, the chance of a deadlock is greater. You can reduce the chances of cyclic deadlocks by maintaining a consistent flow of data access across all the transactions that use the same set of data.

Deadlocks resulting from lock conversion are also common and require your attention. Let's say that you're writing a transaction that removes a specific quantity of a product from inventory. Your transaction starts by running a SELECT statement against the Products table to find out whether the requested quantity is in stock. If the inventory quantity is equal to or greater than the requested quantity, you then run an UPDATE statement to remove the quantity from inventory. We'll assume that the SELECT statement and the UPDATE statement are both run against the same record in the Products table.

In a high-volume OTLP application, there's a good chance that two separate transactions will both run the SELECT statement and acquire read locks before either can acquire the write lock required for the UPDATE statement. This results in a deadlock. Each transaction waits for the other to remove its read lock so that it can acquire a write lock. The problem is that the transaction first acquires a read lock on a data item and then tries to convert the lock to a write lock. If two transactions acquire the read lock at the same time, neither party can convert the lock to a write lock.

Many programmers assume that they are safe from deadlocks when a transaction is running at the Serializable level of isolation, but the Serializable level of isolation doesn't provide any protection from the problems of lock conversion. SQL Server supports another type of lock called an update lock, which can be used to solve this problem. You can use an update lock whenever you need to escalate a read lock to a write lock in the course of a transaction.

An update lock conflicts with write locks and with other update locks. A transaction will block while trying to acquire an update lock if another transaction has already acquired a write lock or an update lock. But unlike a write lock, an update lock doesn't conflict with read locks. If a transaction holds a read lock on a data item, another transaction can acquire an update lock. Likewise, if one transaction holds an update lock, other transactions can acquire read locks. An update lock prevents lock conversion problems without impacting concurrency as much as a write lock.

You can explicitly ask for an update lock by using the UPDLOCK hint in a Transact-SQL SELECT statement. Here's an example of using an update hint in a SELECT statement with ADO:

 Dim sSQL As String sSQL = "SELECT Quantity" & _        " FROM Products WITH (UPDLOCK)" & _        " WHERE Product = 'Dog'" Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.CursorType = adOpenForwardOnly rs.LockType = adLockReadOnly ' Assume conn is an open connection. rs.Open sSQL, conn Dim Quantity As Long Quantity = rs.Fields("Quantity") ' The update lock is now in place. ' Execute UPDATE statement if appropriate. 

SQL Server also uses update locks if you use pessimistic locking with server-side cursors. The following code doesn't use the (UPDLOCK) hint, but it has the same effect:

 Dim sSQL As String sSQL = "SELECT Quantity" & _        " FROM Products " & _        " WHERE Product = 'Dog'" Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseServer rs.CursorType = adOpenDynamic rs.LockType = adLockPessimistic ' Assume conn is an open connection. rs.Open sSQL, conn Dim Quantity As Long Quantity = rs.Fields("Quantity") ' The update lock is now in place. ' Execute UPDATE statement if appropriate. 

Both of these examples illustrate techniques that can be used to eliminate deadlocks caused by lock conversion. If your DBMS doesn't support update locks, it supports another lock type to prevent deadlocks from lock conversion. You can always obtain a write lock on a record using an UPDATE statement that sets a field equal to itself. This forces the lock manager to place a write lock on the record in question. This is a brute force approach, but it's commonly used.

While you can write your applications to eliminate some types of deadlocks, other types are unavoidable in a large OLTP system. Some deadlocks will be caught by the DBMS; others will be caught when a COM+ transaction reaches its timeout value. You must plan to trap deadlock errors when they occur and handle them as gracefully as possible. Sometimes you can handle a deadlock by resubmitting the transaction from an error handler. This can hide the deadlock from the user of the application. At other times, you might be forced to return an error to the caller along with instruction to "try again later."

A Final Word on Tuning

We've covered a great deal of territory in this chapter. As a final exercise, I'd like to put all this OLTP theory to work by sketching the workflow of a typical transaction. I'll use the example of submitting a sales order for the Animal Market application. When you start designing the workflow of a transaction, think about which operations cause the greatest amount of contention. Those operations should be run last.

For example, you should always run operations at lower isolation levels first. When you need to lock rows or tables that block other users, you should do that at the very end of the transaction. This practice is essential for minimizing lock times. And as you know, shorter lock times increase throughput and keep response times as low as possible. Here's one possible workflow design for the our sales order example:

  • Run a SELECT statement against the Customers table at Read Uncommitted to verify the customer's existence. Roll back the transaction and raise an error if the customer's name isn't in the database.
  • Run a SELECT statement against the Products table at Read Uncommitted to verify the product's existence and retrieve its price. Assume that product prices don't change during business hours. Roll back the transaction and raise an error if the product isn't in the database.
  • Insert a new record in the Orders table. Of the three tables being updated, this one has the least contention.
  • Update the Customers table. This table has the second-lowest amount of contention. Run a SELECT query with an update lock to read the customer's credit limit and account balance. Roll back the transaction and raise an error if the customer doesn't have enough available credit to make the purchase. If the customer has enough available credit, update the customer record to charge the sales price to the account balance.
  • Update the inventory in the Products table. This table has the greatest amount of contention. Run a SELECT query with an update lock to read the inventory level of the product. Roll back the transaction and raise an error if the product doesn't have enough units in stock. If there is enough inventory, update the product record to decrement inventory.
  • If no errors have occurred, commit the transaction.

Summary

This chapter has covered techniques for programming local transactions as well as COM+ transactions. You should always choose the approach that makes the most sense for the project at hand. Either way, transactional programming is all about keeping application throughput high and response times low as the number of users increases. You do this by avoiding unnecessary locks and minimizing the length of time that locks are held.

Declarative transactions are at the core of the COM+ programming model. They're fairly easy to use and they solve the incredibly difficult problem of running distributed transactions against multiple resource managers. The COM+ runtime handles all the interactions with the DTC and makes sure that connections are properly enlisted. All you have to do is learn how to control the outcome of a transaction by setting the happy bit and the done bit.

By now, you're probably tired of hearing me go on and on about minimizing round trips, but avoiding unnecessary round trips between a COM+ application and your RMs is one of the keys to achieving optimal performance. You have to adopt an OLTP mindset to write a scalable application. Remember that using session components combined with stored procedures usually results in the best performance.

It should also be clear to you that optimizing an OLTP application requires a solid understanding of a database API such as ADO and an in-depth knowledge of the RMs you'll be using. You should also take the time to learn how each RM deals with transactions, locking, and concurrency so that you can write your Visual Basic components and SQL code to make the most of your hardware and software.



Programming Distributed Applications with COM+ and Microsoft Visual Basic 6.0
Programming Distributed Applications with Com and Microsoft Visual Basic 6.0 (Programming/Visual Basic)
ISBN: 1572319615
EAN: 2147483647
Year: 2000
Pages: 70
Authors: Ted Pattison

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net