Optimistic vs. Pessimistic Locking and the DataSet

for RuBoard

Transactions help preserve database consistency. When you move money from your savings to your checking account to pay your phone bill, transaction processing ensures that the credit and withdrawal will both happen, or neither will happen. You will not wind up with a situation where the money goes into your checking account but is not withdrawn from the savings (good for you, and bad for the bank) or the reverse (bad for you, but good for the bank). Nothing about that transaction prevents your spouse from using that same money to eat out at a fancy restaurant. [11]

[11] The failure to distinguish between these two leads to the apparently common problem (as related to me by a bank vice president) of people wondering why their checks bounce when their ATM balance said they had enough money to withdraw some cash.

Under an optimistic locking strategy, you assume this will not happen, but you have to be prepared to deal with it when does. [12] A pessimistic locking strategy requires coordination among all the users of a database table so that this never happens. Of course, the fewer locks you hold on database rows to prevent use by more than one user , the more scalable your application will be.

[12] This is the database equivalent of overdraft protection.

An understanding of how this affects your application applies to both reads and actual updates. For example, suppose your spouse sees that money is available in the checking account and makes plans based on that fact. This could be as much of a problem as the actual withdrawal of money from the joint checking account.

While a discussion of how to solve these problems is beyond the scope of this chapter, it is important to realize that the issue arises because no locks are held on the database records held within a DataSet . Just using the DataSet with SqlDataAdapter.Update assumes an optimistic locking strategy.

Why does this matter? It matters because the performance and scalability of your application depend on it. Why is it so complicated? Because there is no answer that applies to all applications in all situations. If users do not share the same set of data, optimistic concurrency is an excellent assumption. If you have to lock records for a long period of time, this increases the wait to use these resources, thus decreasing performance and scalability.

You have to understand transaction isolation levels, the database's Lock Manager, the probability of contention for particular rows, and the probability that this contention results in deadlock in your application. You have to understand how much time and resources you can spend reconciling divergent operations, and how much tolerance for inconsistent or incorrect results your application can stand, in order to decide under what circumstances you want to avoid deadlock at all costs, or can deal with the consequences of conflicting operations. [13]

[13] Tim Ewald's book is worth reading to understand this topic. Philip Bernstein and Eric Newcomer's Principles of Transaction Processing is another good reference.

You might have to use the DataSet with additional logic to test whether the records in the DataSet have been changed since the last time they were fetched or modified. Or you might just decide to use the SqlDataReader and refetch the data. It all depends.

For example, when making a reservation in our HotelBroker case study you cannot make an optimistic assumption about the availability of rooms. It is not acceptable to assume an infinite supply of rooms at a hotel and let the reservations clerk deal with what happens when more people show up then there are rooms for. [14] We use the MakeReservation stored procedure to check on the availability of a room before we make the reservation. [15]

[14] Of course, airlines and hotels overbook. This is a conscious strategy to deal with passengers or guests not making explicit cancellations , not a database concurrency strategy.

[15] In fact, the transaction in MakeReservation includes the checking of the availability of the room as well as the actual making of the reservation in order to maintain consistency. It also breaks up what could be one multiple table join into several queries in order to return better error information.

Sometimes, even without concurrency issues, the DataSet cannot be used to add new rows in isolation from the database. Sometimes, as in our HotelBroker application, an arbitrary primary key cannot be used. [16] Many users will be making reservations at the same time. Reservation ids cannot be assigned locally; some central logic on the database has to be employed to issue them. [17] The MakeReservation stored procedure does this as well.

[16] For instance, a GUID. Well, theoretically GUIDs could be used in our case, but when was the last time you got a reservation number from a hotel or airline that was composed of 32 identifiers? Many times a primary key has meaning to an organization ”for example, a part number whose subsections indicate various categories.

[17] Of course, if performance were critical, instances of the HotelBroker could be preassigned ranges of reservation ids to give out. But this would have to be done by some central authority as well (the database, some singleton object?). But then this raises the issue of state management in the middle tier . This just reinforces my previous point about the dependency of any solution on the specific requirements of your program. It also reinforces the maxim that any programming problem can be solved either by trading memory against time, or adding another level of indirection.

The degree of disconnected operation that your application can tolerate has to be understood before you can decide how to use SqlDataReader or the DataSet in your applications.

Why bother to use the DataSet at all in our HotelBroker application? In fact, the code for the Customer object does not use the DataSet at all. The HotelBroker object does ”for two reasons. The first is pedagogical . We wanted to show you how a complete application might use the features of the DataSet , rather than just isolated sample programs. Second, in the Web version of the application which is developed in subsequent chapters, it is convenient to cache certain pieces of information. For example, it is probably reasonable to assume that a user can work with their own local copy of reservations. On the other hand, the information about a customer such as their email address can be obtained just once when they log in. There is no need for an elaborate mechanism to cache customer information, so the Customer object uses methods on the SqlCommand object.

for RuBoard


Application Development Using C# and .NET
Application Development Using C# and .NET
ISBN: 013093383X
EAN: 2147483647
Year: 2001
Pages: 158

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