Classic Client/Server Computing
Let's get back to the ordering scenario we considered earlier, where we had to find the client, find the thing to order, and then make an order we were having to carry out. Here, our three pieces of server-side work-two reads and an update- would be three separate database transactions/interactions. We pull the data down for the picklist with the first read. We pick a customer ID and use that as a parameter on the next read. We make all changes in the user interface, and when the user is happy, he or she clicks the OK button (or whatever user interface standard we are following). The data goes back-perhaps as parameters on a stored procedure call or an action query-and all the work is done within the database server to make the changes as one database transaction. This is how most early client/server systems were made-it was frequently the model with terminal front-ended applications.
What happens in this approach if something goes wrong? For instance, suppose a business rule, implemented in the stored procedure we called, makes sure that the order we have just input doesn't take the customer's total outstanding orders over their credit limit. Perhaps we even checked for this in the user interface by retrieving the current total order value and credit limit for the customer. In the meantime, however, some other order has also been processed, and that has tipped the balance.
Clearly, the database doesn't get updated. The stored procedure returns something to tell the user interface this, and makes sure anything it did to the database is now rolled back (perhaps including assigning order numbers). This model has advantages:
- There are no locks on the database until we actually do the insert/update work, and then only for the briefest time we can get away with-for example, while the stored procedure is running in the database.
- Our two read routines can be reused in other pieces of functionality within the system. (It is quite likely that the customer picklist read-and the customer details read if it is not too order-processing specific-can be used in a number of places in the system.)
- If we needed to tweak the work process or the user interface, we wouldn't have to rewrite our functionality, just reorder it. Thus if we wanted to take the order details before we checked on the customer, we could do it.
This model aims at making sure that all requests on the server are read-only ones, until the user hits OK. Then all the actions (updates, inserts, deletes) are done in one database transaction. We at TMS like this approach-it isn't a classic for nothing. We have to admit, however, that it won't always work-just because 8 million flies choose it.