Complex Transactions

[Previous] [Next]

This section presents an example of a complex transaction. The example isn't overly complex, but it's complicated enough to be used as a pattern for other transactions, much more complex than the example. The sample transaction is about saving data about a race.

As early as Chapter 1, "Planning for a New Architecture," we showed you a hierarchical recordset containing data not only for a race but also for all the participants of that race. The recordset featured in that section had two levels:

• The parent level, holding data about the race itself. RaceId, TrackId, Date, RaceNumber, Distance, TrackType, and RaceClass are examples of fields on the parent level.
• The child level, holding data about each of the horses running in that race. Examples of fields on the child level are FinishPlace, Horse, Weight, and Jockey.

In Chapter 1, we also showed you an example of such a hierarchical recordset. Here's a copy of part of that example, representing one race and all the horses and jockeys entered in that race:

Two Scenarios

There are mainly two scenarios in which you'd want to send data from such a hierarchical recordset to the database:

• When you first enter the starting list for the race. In this scenario, you'd insert data for the race itself in the Races table; then you'd insert data about each of the entrants in the RaceEntrants table.
• When you enter the results of the race. In this scenario, you wouldn't insert any new rows, but you'd update one row in the Races table and also probably all the corresponding rows in the RaceEntrants table.

Incidentally, the use case diagram pictured in Figure 23-1 represents these two scenarios as two different use cases. In the first of these use cases, an Administrator actor imports start lists; in the second, he or she imports race results into the database.

Figure 23-1. The Administrator's use cases. The two scenarios we're talking about are the topmost ones near the Administrator icon.

In both these use cases, you'll use a transaction to make sure that all your inserts or updates are sent to the database, or that none of them are. Under no circumstances can you accept a condition in which only the first three entrants of the preceding table are inserted or only the winner and the second horse are updated from the result list. It's all or nothing at all. You want it all to go in the same transaction.

Letting the RaceTrSrvcs component do it all

One way of making sure that everything runs in the same transaction is to use the same object to update both tables. Figure 23-2 illustrates that alternative.

Figure 23-2. The RaceTrSrvcs component updates the Races table as well as the RaceEntrants table in the database.

We don't, however, consider this an ideal design. In our view, it breaks the conceptual integrity of the component structure by allowing one transactional services component to update more than one table in the database. This is entirely different from allowing one data fetcher component to set up a join by accessing several tables in the database. Joins are extremely useful because they allow you to combine the best features of two worlds. The in-memory objects give the user a complete and understandable denormalized view of data, whereas the database stores a stable and normalized view of the same data. Using joins to convert normalized views, difficult for humans to digest, to denormalized views, a better fit for the human mind, is something we strongly recommend.

A better design is to have the RaceTrSrvcs component act as a transactional manager for the entire transaction. Such a design would give this component two roles:

• Like any modifier component, the RaceTrSrvcs component would manage inserts, updates, and deletions in the Races table.
• Unlike most modifier components, it would also act as a manager for a complex transaction, calling on other modifier components to manage parts of the transaction.

In Figure 23-3, the RaceTrSrvcs component updates the Races tables from the parent part of the recordset; then it sends the child part of the recordset to the RaceEntrantsTrSrvcs object. This is a clear case of separation of concerns: each component involved in a transaction is responsible for one well-defined part of the transaction only. In the case presented in Figure 23-3, the RaceTrSrvcs component is responsible for updating the Races table; the RaceEntrantTrSrvcs component is equally responsible for updating the RaceEntrants table.

Figure 23-3. The RaceTrSrvcs component handles updates of the parent records only; the component extracts the child recordset and sends it to the RaceEntrantsTrSrvcs object for updating the child records of the database.

With this kind of design, you can put those business rules that concern races in the RaceTrSrvcs component and those that concern race entrants in the RaceEntrantTrSrvcs component. In this book, we don't discuss rules concerning races or race entrants, only rules that concern horses, but we can promise you this: rules concerning races or race entrants are many, and some of them aren't simple. It's a very good thing to divide them into two parts: one for races and one for race entrants. This is what's generally called separation of concerns, and it simplifies not only development of business services but also maintaining and enhancing them.

Just for the fun of it, let's study this process with the help of a new type of diagram (made available with Service Pack 1 of Rational Rose 98i and also available in Rational Rose 2000), the activity diagram, to show the flow of events between different objects involved in the same operation.

Activity diagrams

Total quality management (TQM) and business process reengineering (BPR) consultants have long used a specific kind of diagram called a swim lane diagram. In such a diagram, each actor involved in a business process is given a swim lane. The diagram shows how each actor performs activities and then sends the results of each activity across swim lanes to another actor. An activity diagram in Rose uses a swim lane for each actor involved in a certain flow of events. In our case, all the actors involved are objects. For example, Figure 23-4 shows the RaceManager object sending a Save request to the RaceTrSrvcs object, which does parts of the update job itself and delegates the rest of it to the RaceEntrantTrSrvcs object.

Figure 23-4. An activity diagram, using one swim lane for each object involved in the process diagrammed.

Figure 23-4 also shows how the two modifier objects, the RaceTrSrvcs object and the RaceEntrantTrSrvcs object, check the business rules that concern the respective type of object. In addition, each object sends an update request to the database. The diagram shows under which circumstances the different objects involved will call the SetComplete and SetAbort methods, respectively. If the work of each object is successful, the object calls SetComplete; otherwise, it calls SetAbort. As we explained in Chapter 14, "Using Microsoft Transaction Server," if every object involved in a transaction calls the SetComplete method, the transaction is committed to the database or databases. If just one of the objects involved calls SetAbort, the transaction is lost and will be rolled back instead—all the databases involved will roll back to the state they were in before the transaction began.

This is one way of using a swim lane type of activity diagram. Another way is to describe with the diagram high-level business processes. The principle of the diagram type is still the same: actors send messages to other actors, and actors receiving messages react to them. For example, a Customer actor might send an order to an order clerk; the order clerk checks the customer's credit, then (you hope) enters the order and sends a confirmation to the customer, perhaps also a message to a manufacturing clerk, and so on. All this can be captured in an activity diagram.

Code samples

Going back to our complex transaction, let's take a look at some of the code needed to implement the transaction. You'll find that thanks to the separation of concerns, the necessary code isn't too complicated. Let's begin by taking a look at the Save method in the RaceTrSrvcs object:

Public Sub Save(rsHierarch As Recordset) On Error GoTo SaveErr  If rsHierarch.EditMode = adEditAdd Then Insert rsHierarch Else Update rsHierarch End If GetObjectContext.SetComplete Exit Sub SaveErr: GetObjectContext.SetAbort Err.Raise Number:=Err.Number, Source:=Err.Source, Description:=Err.Description End Sub

As you can see, the Save method takes a recordset as its only argument. Furthermore, the variable name used for the argument—rsHierarch—indicates that the recordset is hierarchical. The method first takes a look at the only record of that recordset to see whether its EditMode property is set to adEditAdd. If this is the case, the Insert method is called to insert the new record in the database.

WARNING

---

To simplify, we assume that this recordset contains one record only. In a production system, you shouldn't make such assumptions as lightly as we've done here. Either you should check that it contains one record only or you should treat it as a multirecord recordset.

Anyway, let's look at the following Insert method just called from the Save method.

Private Sub Insert(rsHierarch As Recordset) Dim lngId As Long, rsStartlist As Recordset Dim objTakeANumber As AdvRaceDataAccess.RaceADOSrvcs Dim objRaceEntrantTrSrvcs As AdvRaceDataAccess.RaceEntrantTrSrvcs On Error GoTo InsertErr Set objTakeANumber = CreateObject _ ("AdvRaceDataAccess.RaceADOSrvcs")  lngId = objTakeANumber.GetNewNumber("Races") Set rsStartlist = rsHierarch("Startlist").Value rsHierarch!RaceNmbrRunning = lngId rsHierarch.ActiveConnection = mstrConn rsHierarch.UpdateBatch Set objRaceEntrantTrSrvcs = CreateObject _ ("AdvRaceDataAccess.RaceEntrantTrSrvcs")  objRaceEntrantTrSrvcs.Save rsStartlist, lngId GetObjectContext.SetComplete Exit Sub InsertErr: GetObjectContext.SetAbort Err.Raise Number:=Err.Number, Source:=Err.Source, _ Description:=Err.Description End Sub

The rsHierarch recordset received with the call isn't the only recordset this method handles. It also declares an rsStartlist recordset, the purpose of which is to hold data for the full set of race entrant records. This set is initially the child part of the rsHierarch recordset.

Before beginning the transaction, the Save method calls the GetNewNumber method to get a new ID for the race to insert. It saves the ID value received in the lngId variable. It also updates the parent's RaceNmbrRunning field with this ID value; this is necessary to have the record successfully inserted in the database.

After setting up a connection to the database, the Save method issues an UpdateBatch command to the recordset. ADO looks at the EditMode property, described in Chapter 4, "Implementing the Rules," of the record and decides to send an INSERT command to SQL Server. Notice that this act affects only the parent record—not any of its children, nor any grandchildren or other descendants if there were any. Most people, including us, initially expect an UpdateBatch command, sent to a hierarchical recordset, to update the entire recordset, including each and any level. But it doesn't!

The reason for this peculiarity isn't peculiar at all. A hierarchical recordset consists of several recordsets. ADO uses magic to give the illusion of it being one recordset with different kinds of records in it. When you send messages to such a recordset, you send them, in fact, to the parent level of the recordset only. Therefore, as the last of the boldface lines in the preceding code example shows, we must do something about each of its children too; that line sends the rsStartlist recordset, extracted from the hierarchy, together with the new RaceId to a RaceEntrantTrSrvcs object for insertion into the database. This is in accordance with our preference for handling each record type in its own component. This gives our design a higher degree of conceptual integrity.

Not so easy

Unfortunately, things in the RaceEntrantTrSrvcs object aren't as easy to handle as we'd like them to be. The following code, implementing the Save method of the RaceEntrantTrSrvcs object, is an example of the complexity you must handle when saving data in a hierarchical child recordset.

Public Sub Save(rsStartlist As Recordset, lngRaceNmbrRunning As Long) On Error GoTo SaveErr rsStartlist.MoveFirst While Not rsStartlist.EOF  rsStartlist!RaceNmbrRunning = lngRaceNmbrRunning If rsStartlist.EditMode = adEditAdd Then  InsertRaceEntrant rsStartlist End If rsStartlist.MoveNext Wend GetObjectContext.SetComplete Exit Sub SaveErr: GetObjectContext.SetAbort Err.Raise Number:=Err.Number, Source:=Err.Source, Description:=Err.Description End Sub

This Save method takes an rsStartlist recordset, which contains all the horses that ran in the race just inserted, as the first call argument. It goes without saying that the COM+ or MTS transactional attribute settings of these components should guarantee that the work of both components shares the same transaction.¹ The second argument of the method is the ID value of the race that was just inserted.

After calling the recordset's MoveNext method to make sure that the current record is the first one, the Save method uses a loop to have each record individually added to the database. It first uses the ID value received as the second argument to update each record, thus making sure that the foreign key of the race entrant points to the right race. While we're still inside the loop, the InsertRaceEntrant method is called for each record to have the record inserted in the database.

Why doesn't the Save method just issue an UpdateBatch command and have ADO automatically do all the insertions? Because it can't! This is a mystery, but it really can't. Furthermore, as you run through the recordset, updating the RaceNmbrRunning field of each record, the records tend to disappear from the recordset. To avoid that, we call the InsertRaceEntrant method to perform the insertion. We do that for each record as soon as we've updated it with the race number.

The real problem for us was to understand and accept the peculiarities in general and the fact that we couldn't use the UpdateBatch command in particular. By the way, our first test shot didn't include COM+ or MTS. Instead, we called the RaceTrSrvcs component in-process. When we did, we were fully able to use UpdateBatch also for the child recordset, something that turned out to be impossible when we later ran the project as it should be run, out-of-process. This is an enormous anomaly, and it can do you a lot of harm. It's absolutely imperative that you test out-of-process issues out-of-process. There's no guarantee that a component behaves the same when called out-of-process as it does when called in-process!

The purpose of this "Not so easy" section is twofold. First we want to attract your attention to some problems with hierarchical recordsets; second we want to save you the hours and days you might otherwise spend solving, or trying to solve, these problems. We hope this section saves you quite a lot of time, and perhaps it can also reduce the number of sleepless nights (in Seattle or elsewhere) you have to suffer.

The rest is a breeze, but let us show you the following code for the InsertRaceEntrant method.

Private Sub InsertRaceEntrant(rs As Recordset) Dim cmd As ADODB.Command, strSQL As String strSQL = "INSERT INTO RaceEntrants & _ (RaceNmbrRunning, Startnumber, " & _ "Horse, HorseSex, Weight, FormIn, Jockey, " & _ "Trainer, Equipment) " & _ "VALUES (" & rs!RaceNmbrRunning & ", " & _ rs!Startnumber & ", " & _ rs!Horse & ", '" & rs!HorseSex & "', " & _ rs!Weight & ", " & rs!FormIn & ", " & _ rs!Jockey & ", " & rs!Trainer & ", '" & _ rs!Equipment & "')" Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = mstrConn cmd.CommandType = adCmdText cmd.CommandText = strSQL cmd.Execute End Sub

Special Transaction Managers

Leaving the code to go back to the complex transaction issue, we'll show you another way to handle this type of transaction. It won't take away any of the problems we've described, but you might find it more elegant. (We certainly do!)

You can put a special transaction management component between your main business object and the modifier object. Figure 23-5 shows how you can design such a mechanism.

Figure 23-5. In this approach, the RaceManager object uses a special transaction manager object to handle the transaction.

A design such as the one presented in Figure 23-5 works as follows:

• The RaceManager object sends a request to the RaceTrMngr object, asking it to save the undivided hierarchical recordset included with the request. As in every other example, the RaceManager component, being a main business services object, doesn't support transactions at all.
• The RaceTrMngr object at lower left in the diagram becomes the owner of the transaction. This component must either require a transaction or require a new transaction. Which of these settings you select depends mostly on the kinds of operational circumstances you expect objects of this class to encounter.
• The RaceTrMngr object separates the parent part of the recordset and the child part from each other, sending the different parts to different TrSrvcs objects for rule checking and possibly for updates.
• The RaceTrSrvcs object is now responsible only for inserting, modifying, and deleting data in the races table in the database and for checking all the business rules that concern these operations on race records.
• The RaceEntrantTrSrvcs object will do exactly the same job it did in the preceding example, concerning itself with business rules and database updates for race entrant records only.

Conceptually cleaner—more capable

In our view, a design pattern using transaction managers is conceptually cleaner than the first design pattern described in this chapter. Transaction managers allow ordinary modifier components to concentrate on one type of data and one type of business rules only, and this concentration helps you as a developer create a simpler and therefore more stable design. Furthermore, for really complex transactions this pattern works much better. The transaction manager won't do any work of its own; it simply delegates work to the different specialists involved in the transaction. In many ways, transaction managers should remind you of facade objects because they hide complexity from their clients.

Stateful—for a while

In the case we've just described, the transaction management component can be stateless, like the other components involved in the transaction and the RaceManager object. The RaceManager object sends all the data the transaction manager needs to perform its job, so there's no need for the RaceManager object to call the transaction manager more than once. You'll encounter other situations in which you can't do that because the transactions are so complex you can't send all the data needed with one single call: you might have to call the transaction manager several times to give it all the data it needs. Even in the case we just described, you might find yourself needing to call the transaction manager more than once.

Say, for example, that you don't want to use hierarchical recordsets because of the anomalies built into them. You use instead separate recordsets for each level. In such a circumstance, you must call the transaction manager twice, once for each recordset. Then you can't have it use a Set Complete or SetAbort method for every call because that would make the object disappear in the middle of a transaction, which of course would make the transaction abort. Instead, each time the transaction manager receives data with a call, it must call the Enable Commit method (which is the default) or the DisableCommit method. Which one it should call depends on the strategy you choose to apply. In principle, you can choose from two strategies:

• You can have the transaction manager delegate modifier operations as soon as it gets something to delegate. In this case, you should have it call the DisableCommit method. Then, whatever happens, the transaction won't be committed while parts of it are incomplete.
• You can have the transaction manager postpone the delegation of work until it has collected all the data it needs. With this strategy, it doesn't matter much if each method that receives part of that data calls DisableCommit or EnableCommit because no part of the transaction has been carried out.

But without a doubt, the safest way to proceed is to have the transaction manager call the DisableCommit method for every one of these calls. This practice guarantees that MTS or COM+ won't commit the transaction, should it be completed before the object in question has been called again to change the setting.