Database Reference
In-Depth Information
Consistency:
The transaction should leave the database in a consistent state—
whether or not it completed successfully. The data modified by the transaction
must comply with all the constraints placed on the columns in order to
maintain data integrity. In the case of Northwind, you can't have rows in the
Order Details table without a corresponding row in the Orders table, because
this would leave the data in an inconsistent state.
Isolation:
Every transaction has a well-defined boundary—that is, it is isolated
from another transaction. One transaction shouldn't affect other transactions
running at the same time. Data modifications made by one transaction must be
isolated from the data modifications made by all other transactions. A
transaction sees data in the state it was in before another concurrent
transaction modified it, or it sees the data after the second transaction has
completed, but it doesn't see an intermediate state.
Durability:
Data modifications that occur within a successful transaction are
kept permanently within the system regardless of what else occurs. Transaction
logs are maintained so that should a failure occur, the database can be restored
to its original state before the failure. As each transaction is completed, a row is
entered in the database transaction log. If you have a major system failure that
requires the database to be restored from a backup, you could then use this
transaction log to insert (roll forward) any successful transactions that have
taken place.
Every database server that offers support for transactions enforces these four ACID properties
automatically.
Transaction Design
Transactions represent real-world events such as bank transactions, airline reservations, remittance of
funds, and so forth.
The purpose of transaction design is to define and document the high-level characteristics of
transactions required on the database system, including the following:
•
Data to be used by the transaction
•
Functional characteristics of the transaction
•
Output of the transaction
•
Importance to users
•
Expected rate of usage
There are three main types of transactions:
•
Retrieval transactions
: Retrieves data from display on the screen
•
Update transactions
: Inserts new records, deletes old records, or modifies existing
records in the database
•
Mixed transactions
: Involves both retrieving and updating data
