Databases Reference
In-Depth Information
10.1.3 Understanding Transaction Properties
An abbreviation that you will commonly see when discussing transactions is
ACID.
This stands for atomicity, consistency, isolation, and durability. Let's look
at each of these separately.
Atomicity:
either all of the statements in the transaction are executed, or
none of the statements are executed. An atomic transaction represents a
complete unit of work.
▲
Consistency:
the transaction preserves database consistency. If a transac-
tion executes successfully and performs its database updates properly, the
database is transformed from one consistent state to another consistent
state.
▲
Isolation:
a transaction must appear as though it executed by itself with-
out interference from any other concurrent transaction. In practice, how-
ever, many concurrent database transactions execute simultaneously.
▲
Durability:
changes made to the database must be permanent. Even if
and when there are subsequent failures, the effects must persist in the
database. One way this is ensured is through the transaction log and the
recovery process after a failure, as discussed earlier.
▲
Factors relating to isolation deserve a closer look. To ensure complete isola-
tion, the DBMS would not be able to process any other transactions while the
current transaction is running. This could have disastrous effects on performance
while transactions build up in a queue, waiting for other transactions to com-
plete. For this reason, operations of one transaction are interleaved with opera-
tions of other transactions. However, the database system must guarantee the
effects of database updates for every pair of concurrent transactions to be as
though one transaction executed from beginning to end and then the second
started, or vice versa. Transactions must execute independently, and intermedi-
ary or partial results of incomplete transactions must be transparent to other
transactions.
10.1.4 Understanding Transaction Scope
Transaction scope support and scope options are somewhat DBMS-specific. Two
scope options, implicit and explicit transactions, are supported by most DBMSs
to varying degrees. Another scope supported by some SQL management systems,
including SQL Server, is autocommit transactions.
Explicit Transactions
We'll start with explicit transactions because they're the easiest to understand.
With an
explicit transaction,
the entire transaction life cycle is controlled
