MIS/CS 43 Lecture Notes: Thursday, 5/1/2003
I. Complete discussion of Chapter 14: Transaction Processing
We know the importance of concurrency, and problems that can arise.
The resolution of the problem involves locks. We consider:
- Granularity of locks.
- Exclusive vs Shared locks. Upgrading/downgrading locks.
- Deadlocks. Two or more transactions awaiting events that
will not occur. Transactions "hang". Deadlock problems can be
addressed via prevention, avoidance, detection, recovery
algorithms. Most of that is not discussed in this course.
- We look at the text's example -- Figure 14.11 on page 370.
-
- The need for recovery from the effects of rollback -- we recall
the dirty read problem. So a schedule can be compelled to
be recoverable.
- A recoverable schedule is one in which no transaction T
commits until every other transaction that writes a value that
is read by T has committed. -- avoids cascading rollbacks.
- A strict schedule is one in which no transaction may read
from or write to an object until any other transaction that wrote
to the object has committed or rolled back.
- Managing these schedules brings up the idea of serializable
schedules. We do not go into the details of analyzing and resolving
the associated problems. So, except for the concept of serial
and serializable schedules, student are invited to omit Section
14.4.
II. Complete discussion of Chapter 13: Query Processing/Optimization
We recall that the query optimization process consists of these steps:
- Enumerate the query plans.
- Estimate the costs of the plans.
- Choose the best plan for the query.
In order to create query plans, the approach is to translate the SQL into
relational algebra, then use some identities from linear algebra to
reorganize the expression. The query optimizer uses some heuristics
to generate the most promising. We look at an example query, and three
equivalent expressions, in the form of expression trees.