Database Transaction Processing

As database systems are the earlier form of TP systems, we will start with database TP.

Databases Transactions. A database system refers to a database and the access facilities (DBMS) to the database. One important job of DBMSs is to control and coordinate the execution of concurrent database transactions.

A database is a collection of related data items that satisfy a set of integrity constraints. The database should reflect the relevant state as a snapshot of the part of the real world it models. It is natural to assume that the states of the database are constrained to represent the legal (permissible) states of the world. The set of intintegrity constraints such as functional dependencies, referential integrity, inclusion, exclusion constraints, and some other user-defined constraints are identified in the process of information analysis of the application domain.

These constraints represent real-world conditions or restrictions (7). For example, functional dependencies specify some constraints between two sets of attributes in a relation schema, whereas referential integrity constraints specify constraints between two sets of attributes from different relations. For detailed definitions and discussions on various constraints, we refer readers to Refs. 7 and 8. Here, we illustrate only a few constraints with a simple example.

Suppose that a relational database schema has the following two table structures for Employee and Department with attributes like Name and SSN:

Employee (Name, SSN, Bdate, Address, Dnumber)
Department (Dname, Dnumber, Dlocation).

Name = employee name
SSN = social security number
Bdate = birth date
Address = living address
Dnumber = department number

Dname = department name
Dlocation = department location

Each employee has a unique social security number (SSN) that can be used to identify the employee. For each SSN value in the Employee table, there will be only one associated value for Bdate, Address, and Dnumber in the table, respectively. In this case, there are functional dependencies from SSN to Bdate, Address, Dnumber. If any Dnumber value in the Employee relation has the same Dnumber value in the Department relation, there will be a referential integrity constraint from Employee's Dnumber to Department’s Dnumber.

A database is said to be ‘‘consistent’’ if it satisfies a set of integrity constraints. It is assumed that the initial state of the database is consistent. As an empty database always satisfies all constraints, often it is assumed that the initial state is an empty database. It is obvious that a database system is not responsible for possible discrepancies between a state of the real world and the corresponding state of the database if the existing constraints were inadequately identified in the process of information analysis.

The values of data items can be queried or modified by a set of application programs or transactions. As the states of the database corresponding to the states of the real world are consistent, a transaction can be regarded as a transformation of a database from one consistent state to another consistent state. Users’ access to a database is facilitated by the software system called a DBMS, which provides services for maintaining consistency, integrity, and security of the database.

Figure 2 illustrates a simplified database system. The transaction scheduler provides functions for transaction concurrency control, and the recovery manager is for transaction recovery in the presence of failures, which will be discussed in the next section.

Figure 2. Database system and DBMS

The fundamental purpose of the DBMS is to carry out queries and transactions. A query is an expression, in a suitable language, that determines a portion of the data contained in the database (9). A query is considered as a read-only transaction. The goal of query processing is extracting information from a large amount of data to assist a decision-making process. A transaction is a piece of programming that manipulates the database by a sequence of read and write operations.

Read (X) orR (X), which transfers the data item X from the database to a local buffer of the transaction
write (X) or W(X), which transfers the data item X from the local buffer of the transaction back to the database

In addition to read and write operations, a transaction starts with a start (or begin) operation and ends with a commit operation when the transaction succeeds or an abort when the transaction fails to finish. The following example shows a transaction transferring funds between two bank accounts (start and end operations are omitted).

Here, X and Y stand for the balances of savings and credit accounts of a customer, respectively. This transaction transfers some money ($100) from the savings account to the credit account. It is an atomic unit of database work. That is, all these operations must be treated as a single unit.

Many database systems support multiple user accesses or transactions to the database. When multiple transactions execute concurrently, their operations are interleaved. Operations from one transaction may be executed between operations of other transactions. This interleaving may cause inconsistencies in a database, even though the individual transactions satisfy the specified integrity constraints. One such example is the lost update phenomenon.

Example 2. For the lost update phenomenon, assume that two transactions, crediting and debiting the same bank account, are executed at the same time without any control. The data item being modified is the account balance. The transactions read the balance, calculate a new balance based on the relevant customer operation, and write the new balance to the file. If the execution of the two transactions interleaves in the following pattern (supposing the initial balance of the account is $1500), the customer will suffer a loss:

The final account balance is $500 instead of $1000. Obviously, these two transactions have produced an inconsistent state of the database because they were allowed to operate on the same data item and neither of them was completed before another. In other words, neither of these transactions was treated as an atomic unit in the execution. Traditionally, transactions are expected to satisfy the following four conditions, known as ACID properties (9-11):

- Atomicity is also referred to as the all-or-nothing property. It requires that either all or none of the transaction’s operations are performed. Atomicity requires that if a transaction fails to commit, its partial results cannot remain in the database.

- Consistency requires a transaction to be correct. In other words, if a transaction is executed alone, it takes the database from one consistent state to another. When all the members of a set of transactions are executed concurrently, the DBMS must ensure the consistency of the database.

- Isolation is the property that an incomplete transaction cannot reveal its results to other transactions before its commitment, which is the requirement for avoiding the problem of cascading abort (i.e., the necessity to abort all the transactions that have observed the partial results of a transaction that was later aborted).

- Durability means that once a transaction has been committed, all the changes made by this transaction must not be lost even in the presence of system failures.

The ACID properties are also defined in RM-ODP (Reference Model of Open Distributed Processing) (12). ODP is a standardization in a joint effort of the International Standardization Organization (ISO) and International Telecommunication Union (ITU), which describes systems that support heterogeneous distributed processing both within and between organizations through the use of a common interaction model.

Consistency and isolation properties are taken care of by the concurrency control mechanisms, whereas the maintenance of atomicity and durability are covered by the recovery services provided in transaction management. Therefore, concurrency control and recovery are the most important tasks for transaction management in a database system.