Transactions

A transaction is a series of operations on data with the all-or-nothing property. That is, all of the operations must complete successfully, or none of the operations must complete. (Partial completion is not permitted.) For example, in a transfer of funds from one bank account to another, there are two operations, subtracting money from one account and adding it to the other. Suppose there was a power failure or hardware error when subtraction had just finished, and addition had not yet started. Then, after the failure was corrected, there is the chance that the data would be left in an inconsistent state (that is, the customer’s money would have "disappeared").

To prevent such a catastrophe from happening, databases can do one of two things.

• They can run the transaction to completion. (This would be possible if the destination account number was saved somewhere prior to the crash, and can be recovered.)
• If running to completion is not possible, the database must invalidate the entire transaction. (The process of invalidation and returning to square one is called rollback. The opposite of rollback, when a transaction finishes successfully, is called committing the transaction. )

Rollback is also necessary in situations where several customers are accessing a central inventory, and ordering varying quantities of a part that happens to be in short supply. It may happen that when one customer just finishes a purchase, the quantity of the part left in inventory is too little to satisfy the next customer’s order. The database must prevent the inventory from becoming less than zero (i.e., it must prevent a nonsensical condition) and must rollback the next customer’s transaction, if it has just begun. (A similar situation could apply to purchase of tickets for a heavily booked airline flight.) Rolling back is, of course, a pain, but it is better than inconsistent data.

Propagation of Changes: Immediate and Deferred

Under some circumstances, changes made by some users must be immediately propagated to all other users (so that, for example, the same airline seat cannot be given to two people).

At other times, changes made by some users must be made temporarily invisible to others, because they don’t really impact them so critically. For example, if a bank administrator is summing up dollar amounts across all accounts, the moment-to-moment fluctuations in the amount due to the thousands of customers depositing or withdrawing their money or having their checks processed, should be ignored until the summing up is complete. If it were not ignored, each summing up would be flagged as erroneous the summing up would have to be redone over and over again.

To summarize, a transaction’s properties are summarized in the acronym ACID:

• Atomic: (All the operations within a transaction happen, or none happen)
• Consistent: The operations as a whole do not violate the integrity constraints in the data (e.g., there can be no negative inventory in the example above).
• Isolated: Different users’ view of the data is isolated from each other, so that propagation of changes made by a user is either deferred or done immediately, depending on the circumstances (as mentioned above).
• Durable: Once committed, a transaction should be permanent, to the point that it is immune from hardware failure. How "durable" a transaction really is depends on the circumstances of failure and the system architecture. All systems should be immune from the simple software glitch or power outage, and systems that use "mirroring" of data should be immune to hardware crashes. (Mirroring means that data written simultaneously to multiple devices, so that if one device fails, the others are still available. The most sophisticated mirrored devices will be activated within seconds of failure of the primary device.) Sometimes, data is even transferred simultaneously to remote sites (so that the transaction would be preserved even if the primary computer site was hit directly by an A-bomb).

Locking

Locking is a means of controlling access to shared resources. For a database, it implies making a single record, a set of records, an entire table, or even the entire database, partly or wholly inaccessible to others. The severity of the lock depends on the circumstances.

• For example, if a database administrator is trying to recover from a hardware failure, other users should not be permitted to either look at data or change it – so the entire database is locked.
• At the other extreme, when several customers want to peruse the same airline flight for available seats, it is OK to let other users inspect the same flight even when one user is already looking at this data. After all, just because they are looking at the flight data does not mean that they will choose the same seat (or even choose a seat at all). It is only necessary to prevent two people from booking the same seat accidentally.

Locking can be used at various levels. One can permit others to look at data but not write it (i.e., read-only access) or even disallow read access. Mainstream database engines by and large choose the appropriate level of locking automatically, but there are times when a database programmer can override the default locking mode and set locks manually.

There are two approaches used in locking.

• The pessimistic approach (exemplified in the first case) assumes that when a user accesses a potentially sharable resource, s/he is almost always going to perform a critical operation, such as changing the data, so others should be prevented from accessing it for possible writing.
• The optimistic approach assumes that most users will only look at the data and not change it. Only at the instant where the user decides to actually make a change is a lock acquired, the data written, and the lock released. Here, there is a possibility that, since the time the user began looking at the data of interest, another user has already changed it. In this case, the present user might unknowingly overwrite the other person’s changes with older data.
  Therefore, a good system should warn this user that the data was changed by somebody else, and offer to "refresh" the data on this user’s screen, so that the recent changes may be inspected. After refreshing, the user must make the changes once again and try to commit them. (This scenario very often occurs in business, because different people may make changes to different fields in a record. For example, when editing information on a product, some may be authorized to change information on the product’s supplier, or packaging, while others may be authorized to change price information.)

The optimistic locking approach is commonly used in the airline reservation scenario.

Transaction Monitors / Transaction Servers

When multiple users are connected to a database, there is typically a single open connection per user. Connections take up computer resources (typically memory, but also disk), and therefore the maximum number of users that can simultaneously access a machine is determined by the available hardware. Most of these connections are relatively idle, because, compared to the raw speed at which a computer works, even operations such as data entry are very slow. That is, the computer spends most of its time idling and waiting for something to happen.

It is possible for a clever program to allow a large number of users (say a thousand) to simultaneously access a machine that could not handle more than 50 simultaneous physical connections. Such programs exist, and are called transaction monitors or transaction servers. These programs essentially multitask among the available pool of connections, switching a connection to a particular user session only for those few milliseconds when that session needs to be serviced (e.g., when it makes a data request or when it sends data to be written). That is, these programs perform connection pooling.

Resource Pooling

Sometimes, there may be tens of thousands of users who are simultaneously connected to a particular system (e.g., for stock market applications), and a single computer may not be enough, even with a transaction monitor. Therefore, the system will consist of several computers that service incoming requests. (The availability of multiple machines allows individual machines to be taken off-line, e.g., for servicing and maintenance, and also provides an insurance against a single machine failing. Multiple machines allow round-the-clock availability of a service.)

In such a scenario, the users do not communicate with individual machines directly, but do so through transaction servers, which maintain a list of the "next available" machines and switch a new user to one of these.

Application Servers

In many cases, different machines in a cluster offer different services to a user. (For example, in scientific supercomputing, some machines in a cluster may be specialized for 3-D graphics computations, while others may be general-purpose. A user wants to take advantage of any available facilities without worrying exactly where those facilities are located. (This is because the number of resources for a particular facility may change.) An application server is like a transaction server, and is a program that keeps track of what resources are available on what machines. What actually happens is that each machine in the network "tells" the application server about the types of resources it has available. (In computerese, each resource is termed an "object", and because objects are distributed across several machines, we have a distributed object scenario. When a particular user (actually the user’s program) makes a request for a particular object, the application program directs it to an available resource. In computerese, the application server is acting as an "object request broker". (A broker is an intermediary.)

From the database programmer’s view, the nice thing about application servers and transaction servers is that s/he does not have to worry about how many resources actually exist. The program does not have to be rewritten as the configuration changes, because the transaction server is geared to take advantage of all new machines that are added to the pool.

Resource pooling is particularly important for Web sites that manage electronic commerce, and receive hundreds of hits a second. Many companies that offer Web servers in fact offer bundled transaction/application servers. In fact, the application and transaction servers are often the same program.