Database Transactions: Spring, 2003
Exam #3 is Tuesday.
Questions on Headless Project?
Introduction to Transactions
A transaction, or atomic transaction,
is a logical unit of work that must be completed as a whole or not at all.
-
Note the way the modifier atomic is used in this
context.
-
A transaction is first opened. All steps
in the transaction are then executed. This is typically followed
by a commit operation. If there is a problem, we execute a
rollback operation.
-
We consider the ACID properties. Atomicity, Consistency,
Isolation, Durability.
-
Actions of a transaction are requests for object
access -- as, read, write, commit, rollback.
Single-Transaction Systems
-- only one transaction executes at a time.
-
The main concern is atomicity, and the main concern there
is rollback.
-
SQL's default mode is autocommit -- not adequate
for transactions.
-
Instead, application makes a special call to enter explicit-commit
mode, then issues action commands, followed by a commit-transaction
statement. Either a commit or a rollback occurs.
-
How is rollback done? Two copies of appropriate
tuples must be maintained. Can be done in two ways:
-
Oracle uses a rollback segment (RBS), with images
of original values. To rollback, replace the database copies with
the RBS copies. To commit, reject RBS copies.
-
Another approach is the cache strategy. Keep
modified rows in cache. To rollback, flush the cache; to commit,
copy cache to the database.
-
Consistency for single-transaction systems includes enforcing
primary and foreign-key constraints.
-
Modifying foreign key values is a concern; so is modifying
primary key values. Most DBMSs do not handle this, so it is usually
necessary to insert-and-delete within a transaction.
-
A sidebar refers to the "on delete cascade" and "on
update cascade" that we can see in Access2000.
Concurrent Transaction
Processing
Concurrency is unavoidable, and important. But
it makes database integrity much harder to ensure.
-
Figure 14.5, page 363 illustrates the kind of problem
that may occur when multiple transactions access the same values "simultaneously".
T1 and T2 interfere with one another, denying Isolation.
-
Figure 14.6, page 364 demonstrates the "lost update" and
"dirty read" problems.
-
Figure 14.7 shows the "incorrect summary" problem.
-
Figure 14.8 shows the "unrepeatable read" problem.
-
Figure 14.9 shows the "phantom" problem.
The answer to most problems is to use locks.
But they are not trivial to use, and must be employed carefully.
-
We consider "granularity" of the locks -- individual
tuples, tables, database?
-
We consider "read locks" and "write locks". Why?
shared vs exclusive locks.
-
The readers-and-writers problem.
-
Explicit vs implicit locks.
- We mention and discuss the problem of
deadlock.
- There is a clear need to prevent or avoid
deadlocks, to detect them when they occur, and to recover
from them.