11.1 ACID Properties of Transactions

Regardless of the care with which a database is designed and created, it can be easily damaged or destroyed unless proper concurrency controls and recovery techniques are in place. Database recovery is the process of restoring the database to a correct state in the event of a failure. Concurrency control is the ability to manage simultaneous processes involving the database without having them interfere with one another. Both are needed to protect the database from data contamination or data loss.

The notion of a transaction is central to an understanding of both recovery and concurrency control. A transaction can be thought of as a logical unit of work that contains one or more SQL statements. A logical unit of work might be an entire program with multiple SQL statements, a portion of a program, or a single SQL command. In general, a transaction can involve any number of operations on the database.

To illustrate the concept of a transaction, consider the following relational scheme for the University database

A listing of 4 schemas for the University database. The schemas are as follows. Student open parentheses s t u I d, comma, last Name, comma, first Name, comma, major, comma, credits, close parentheses. The variable s t u I d is underlined. Faculty open parentheses f a c I d, comma, f a c Name, comma, department, comma, rank, close parentheses. The variable f a c I d is underlined. Class open parentheses class Number, comma, f a c I d, comma, schedule, comma, room, close parentheses. The variable class Number is underlined. Enroll open parentheses class Number, comma, s t u I d, comma, grade. The variables class Number and s t u I d are underlined.

A simple transaction against this database might be to update the number of credits in a Student record, given the stuId. This task involves locating the page containing the appropriate Student record on disk, bringing the page into the database buffer, rewriting the value of the credits field in the record in the buffer, and finally writing the updated page out to disk. FIGURE 11.1 summarizes the steps in this transaction.

An illustration of the steps involved in a simple transaction. Step 1. Locate the record. The value of the record within the database is marked 30. Step 2. Bring page into buffer. The record within the database is copied into the buffer. Step 3. Rewrite credits field in buffer. The value of the record in the buffer is changed to 36. Step 4. Write modified page to disk record. The new value of 36 is written back to the database.

FIGURE 11.1 Steps in a Simple Transaction

A more complicated transaction would be to change the stuId assigned to a student. We need to locate the appropriate Student record, bring its page into the buffer, update the stuId in the buffer, and write the update to disk as before, but we also need to find all of the Enroll records having the old stuId and update them. If these updates are not made, we will have an inconsistent state of the database, a state in which the data is contradictory.

A transaction should always bring the database from one consistent state to another. While the transaction is in progress, it is permissible to have a temporary inconsistent state. For example, during the stuId update, there will be some moment when one occurrence of the stuId in an Enroll record contains the new value and another still contains the old one. However, at the end of the transaction, all occurrences should be consistent with the new value. A transaction is the entire series of steps necessary to accomplish a logical unit of work that brings the database to a new consistent state. The transaction is an atomic process, a single all or none unit. We cannot allow only part of a transaction to execute—either the entire set of steps must be done or none can be done because a partial transaction would leave the database in an inconsistent state.

There are two ways a transaction can end or terminate. If it executes to completion successfully, the transaction is said to be committed, which means that any changes it made are stored in the database, and the database is brought to a new consistent state. The other possibility is that the transaction cannot execute successfully. In this case, the transaction is aborted, which means that its changes are not stored in the database. If a transaction is aborted, it is essential that the database be restored to the consistent state it was in before the transaction started. Such a transaction is rolled back, which means any updates it made to the database are undone. A committed transaction cannot be aborted. If we decide that the committed transaction was a mistake, we must perform a compensating transaction to reverse its effects. However, an aborted transaction that has been rolled back can be restarted at some future time and depending on the cause of the failure, may successfully execute and commit at that time. FIGURE 11.2 shows the possible states of a transaction.

A transaction state diagram. The state diagram has 6 states which are as follows. Active transaction. Partially committed. Failed. Committed. Aborted. End transaction. On the assertion of the Begin transaction signal, the state reaches the Active transaction state. In the Active transaction state, when the input signal is Commit transaction, the state transitions to the Partially committed state and if the input signal is Abort transaction, the state transitions to the Failed state. In the partially committed state, if the input signal is Abort, the state transitions to the Failed state else the state transitions to the Committed state. The state following the committed state is the End transaction state. The state following the Failed state is the Aborted state. The state following the Aborted state is the End transaction state.

FIGURE 11.2 Transaction State Diagram

It is usually the responsibility of the programmer to identify the beginning and end of each transaction. The words BEGIN TRANSACTION, COMMIT, ABORT, ROLLBACK, and END TRANSACTION or similar commands are available in data manipulation languages (DMLs) to delimit transactions. If these delimiters are not used, the entire program may be regarded as a single transaction, with the system automatically performing a COMMIT when the program terminates correctly. The active state of the transaction starts with the BEGIN TRANSACTION statement (or its equivalent) and continues until the application program either aborts or terminates successfully, which is the end of the transaction. If a fatal error arises while the transaction is active, it is marked as failed and aborted, and any updates done to the database should be rolled back. If the program terminates successfully, an END TRANSACTION is reached, and the database management system (DBMS) prepares to check that it can commit. During this partially committed stage, the DBMS checks that the transaction will not violate the concurrency control protocols discussed beginning in Section 11.4, or any constraints, and that the system is able to make the required changes to the database. If no problems arise, the transaction is committed, an entry is made in a log of transactions, and the process ends. If problems arise, the transaction is marked as failed and aborted.

All transactions should demonstrate four important properties, referred to as the ACID properties, to ensure that the database maintains a correct state, despite transaction concurrency or system failure. ACID is an acronym for the following properties:

  • Atomicity. The transaction is a single, all-or-none unit. Either the entire set of database update operations is carried out or none are. To ensure this property, the DBMS must be able to roll back transactions that will not be able to complete successfully, undoing the effects of all operations on the database. The DBMS’s recovery subsystem maintains a log, a record of all transaction writes to the database, which is used in the rollback process.

  • Consistency. The user is responsible for ensuring that the execution of an individual transaction leaves the database in a consistent state. It is the job of the concurrency control subsystem of the DBMS to ensure consistency when multiple transactions execute at the same time.

  • Isolation. Several transactions may execute at the same time, with interleaving of their operations. The isolation property requires that changes made by one transaction cannot be seen by another transaction until the first one commits, so that the final effect is as if the transactions were executed one after another rather than concurrently. Because each transaction leaves the database in a consistent state, the overall result would be a consistent state. It is the responsibility of the concurrency control system to guarantee isolation.

  • Durability. If a transaction has been committed, the DBMS must ensure that its effects are recorded in the database, even if the system crashes before all its writes are made to the database. The recovery subsystem is responsible for guaranteeing durability using the transaction log file or a similar mechanism to ensure this property.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.205.214