Transcript Chapter 9 - personal.kent.edu

Database Processing
Managing MultiUser Databases
Chapter 9
David M. Kroenke
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-1
Multi-User Databases
• Serving the needs of multiple users and
multiple applications adds complexity in…
– design,
– development, and
– migration (future updates)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-2
Multi-User Database Issues
• Interdependency
– Changes required by one user may impact
others
• Concurrency
– People or applications may try to update
the same information at the same time
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-3
Multi-User Database Issues
• Record Retention
– When information should be discarded
• Backup/Recovery
– How to protect yourself from losing critical
information
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-4
Role of the Database Administrator
• Organizations typically hire a database
administrator (DBA) to handle the issues
and complexities associated with multiuser databases.
• A DBA facilitates the development and use
of one or more databases.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-5
Data Administrator versus
Database Administrator
Data Administrator
– Handle the database functions and responsibilities
for the entire organization.
Database Administrator (DBA)
– Handle the functions associated with a specific
database, including those applications served by the
database.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-6
The Characteristics of a DBA
• Technical
– The DBA is responsible for the performance and
maintenance of one or more databases.
• Diplomatic
– The DBA must coordinate the efforts, requirements,
and sometimes conflicting goals of various user
groups to develop community-wide solutions.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-7
Technical Skills of the DBA
•
•
•
•
•
•
Managing the database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Providing database recovery
Managing the database management system
(DBMS)
• Maintaining the data repository
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-8
Managing the Database
Structure
• Managing the database structure includes
configuration control and
documentation regarding:
– The allocation of space
– Table creation
– Indices creation
– Storage procedures
– Trigger creation
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-9
Configuration Control
• Post-implementation changes
• Procedures & Policies
• Address unanticipated problems
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-10
The Need for Documentation
• Crucial when trying to understand
problems
• Backups and Restores
• Documentation provides the “paper” trail
for changes
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-11
Documentation
• All structural changes must be carefully
documented with the following:
–
–
–
–
–
Reason for change
Who made the changes
Specifically what was changed
How and when the changes were implemented
How were the changes tested and what were the
results
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-12
Controlling Concurrency
Processing
• Concurrency control ensures that one user’s
actions do not adversely impact another user’s
actions
• At the core of concurrency is accessibility.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-13
Aspects of Concurrency Control
• Rollback/Commit: Ensuring all actions
are successful before posting to the
database
• Multitasking: Simultaneously serving
multiple users
• Lost Updates: When one user’s action
overwrites another user’s request
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-14
Rollback/Commit
• Involve logical units of work (LUW).
• Commit when entire LUW successful.
• Rollback if any part of LUW is
unsuccessful
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-15
Lost Update Problem
• When two or more users are attempting to
update the same piece of data at the same
time.
• Resource locking scenarios are designed
to address this problem
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-16
Resource Locking
• A resource lock prevents a user from
reading and/or writing to a piece of data
• The size of the piece of data (e.g.,
database, table, row) is termed the lock
granularity
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-17
Types of Resource Locks
• Implicit versus Explicit
• Exclusive versus Shared
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-18
Two-Phased Resource Locking
• Two-phased locking, whereby locks are
obtained as they are needed
– A growing phase, whereby the transaction
continues to request additional locks
– A shrinking phase, whereby the transaction
begins to release the locks
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-19
Deadlocks
• When two transactions are waiting on one
another to release resources.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-20
Avoiding Deadlocks
• Strategy 1:
– Wait until all resources are available, then lock them
all before beginning
• Strategy 2:
– Establish and use clear locking orders/sequences
• Strategy 3:
– Once detected, the DBMS will rollback one
transaction
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-21
Resource Locking Strategies
Optimistic Locking
–
–
–
–
–
Read data
Process transaction
Issue update
Look for conflict
If conflict occurred,
rollback and repeat or
else commit
Pessimistic Locking
– Lock required
resources
– Read data
– Process transaction
– Issue update
– Release locks
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-22
Providing Database Recovery
• Common causes of database failures…
– Hardware failures
– Programming bugs
– Human errors/mistakes
– Malicious actions
• Since these issues are impossible to
completely avoid, recovery procedures are
essential
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-23
Database Recovery
Characteristics
• Continuing business operations (Fall-back
procedures/Continuity planning)
• Restore from backup
• Replay database activities since backup
was originally made
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-24
Fall-back Procedures/
Continuity Planning
• The business will continue to operate even
when the database is inaccessible
• The fall-back procedure defines how the
organization will continue operations
• Careful attention must be paid to…
– saving essential data
– continuing to provide quality service
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-25
Restoring from Backup
• In the event that the system must be
rebuilt or reloaded, the database is
restored from the last full backup.
• Since it is inevitable that activities
occurred since the last full backup was
made, subsequent activities must be
replayed/restored.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-26
Recovery via Reprocessing
• This is a brute-force technique.
• Simply re-type all activities since the backup was
performed.
– Costly (extra time)
– Risky (human error)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-27
Recovery via
Rollback/Rollforward
• Most database management systems
provide a mechanism to record activities
into a log file.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-28
Rollforward
• Activities recorded in the log files may be
replayed. In doing so, all activities are reapplied to the database.
• Use to resynchronize restored database
data.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-29
Rollback
• Since log files save activities in sequence
order, can undo activities in reverse order
than when they were originally executed.
• Used to correct/undo erroneous or
malicious transaction(s).
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-30
Database Security
• Database security strives to ensure…
– Only authorized users perform authorized
activities at authorized times
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-31
Managing Processing Rights
and Responsibilities
• Processing rights define who is
permitted to do what, when
• The individuals performing these activities
have full responsibility for the implications
of their actions
• Individuals are identified by a username
and a password
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-32
Granting of Processing Rights
• Database users are known as an individual and
as a member of one or more role
• Access and processing rights/privileges may be
granted to an individual and/or a role
• Users possess the compilation of rights granted
to the individual and all the roles for which they
are members
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-33
Granting Privileges
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-34
Managing the Database
Management System (DBMS)
• In addition to controlling and maintaining the
users and the data, the DBA must also maintain
and monitor the DBMS itself.
– Performance statistics (performance tuning /
optimizing)
– System and data integrity
– Establishing, configuring, and maintaining database
features and utilities
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-35
Maintaining the Data Repository
• The data repository contains metadata.
Metadata is data about data.
• The data repository specifies the name, type,
size, format, structure, definitions, and
relationships among the data. They also contain
the details about applications, users, add-on
products, etc.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-36
Types of Data Repositories
• Active data
repository
– The development and
management tools
automatically maintain
and upkeep the
metadata.
• Passive data
repository
– People manually
maintain and upkeep
the metadata
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-37
Consistent Transactions
• Consistent transactions are often referred
to by the acronym ACID
–
–
–
–
Atomic
Consistent
Isolated
Durable
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-38
ACID: Atomic
• A transaction consists of a series of steps.
Each step must be successful for the
transaction to be saved.
• This ensures that the transaction
completes everything it intended to do
before saving the changes.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-39
ACID: Consistent
• No other transactions are permitted on the
records until the current statement finishes
• This ensures that the transaction integrity
has statement level consistence among
all records
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-40
ACID: Isolation
• Within multi-user environments, different
transactions may be operating on the same
data. As such, the sequencing of uncommitted
updates, rollbacks, and commits continuously
change the data content.
• The 1992 ANSI SQL standards define four
isolation levels and specify respective issues.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-41
Summary of Isolation Levels
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-42
ACID: Durable
• Once committed, durable transactions are
saved to the data permanently
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-43
Set-at-a-Time Versus
Row-at-a-Time
• SQL statements act as filters for the entire
data set.
• A cursor may be defined within a SQL
statement to point to a particular record.
• Several types of cursors have been
defined. The cursor type defines how the
cursor behaves.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-44
Types of Cursors
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
4-45