Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 421

of two-phase locking is to acquire locks throughout the
transaction, but not to free any lock until the transaction
is finished.
Deadlock, or the deadly embrace, occurs when
two transactions are each waiting on a resource that
the other transaction holds. Deadlock can be prevented
by requiring transactions to acquire all locks at the same
time. Once deadlock occurs, the only way to cure it is to
abort one of the transactions (and back out of partially
completed work). Optimistic locking assumes that no
transaction conflict will occur and deals with the conse-
quences if it does. Pessimistic locking assumes that con-
flict will occur and so prevents it ahead of time with locks.
In general, optimistic locking is preferred for the Internet
and for many intranet applications.
Most application programs do not explicitly declare
locks. Instead, they use SQL Transaction Control Language
(TCL) to mark transaction boundaries with BEGIN,
COMMIT, and ROLLBACK transaction statements and
declare the concurrent behavior they want. The DBMS
then places locks for the application that will result in the
desired behavior.
An ACID transaction is one that is atomic, consis-
tent, isolated, and durable. Durable means that database
changes are permanent. Consistency can mean either
statement-level or transaction-level consistency. With
transaction-level consistency, a transaction may not see its
own changes. The SQL standard defines four SQL transac-
tion isolation levels: read uncommitted, read committed,
repeatable read, and serializable. The characteristics of
each are summarized in Figure 9-12.

An SQL cursor is a pointer into a set of records. Four
cursor types are prevalent: forward only, static, keyset, and
dynamic. Developers should select isolation levels and cur-
sor types that are appropriate for their application work-
load and for the DBMS product in use.
The goal of database security is to ensure that only
authorized users can perform authorized activities at au-
thorized times. To develop effective database security, the
processing rights and responsibilities of all users must be
determined.
DBMS products provide security facilities. Most in-
volve the declaration of users, groups, objects to be pro-
tected, and permissions or privileges on those objects.
Almost all DBMS products use some form of user name
and password security. Security guidelines are listed in
Figure 9-16. DBMS security can be augmented by applica-
tion security.
In the event of system failure, the database must be
restored to a usable state as soon as possible. Transactions
in process at the time of the failure must be reapplied or
restarted. Although in some cases recovery can be done by
reprocessing, the use of logs and rollback and rollforward is
almost always preferred. Checkpoints can be taken to reduce
the amount of work that needs to be done after a failure.
In addition to these tasks, the DBA manages the
DBMS product itself, measuring database application per-
formance and assessing the need for changes in database
structure or DBMS performance tuning. The DBA also
ensures that new DBMS features are evaluated and used as
appropriate. Finally, the DBA is responsible for maintain-
ing the data repository.

Key Terms

ACID transaction
active repository
after image
atomic
before image
checkpoint
concurrent transaction
concurrent update problem
consistent
cursor
data administration
data repository
database administration
database administrator
database save
DBA

deadlock
deadly embrace
dirty read
durable
dynamic cursor
exclusive lock
explicit lock
forward only cursor
growing phase
implicit lock
inconsistent read problem
isolated
isolation level
keyset cursor
lock
lock granularity