Transcript Multi-user Databases

Database Processing
Eighth Edition
Managing
Multi-User
Databases
1
Chapter 11
David M. Kroenke
© 2002 by Prentice Hall
Multi-User Databases
• Serving the needs of multiple users
and multiple applications adds
complexity in…
– design,
– development, and
– migration (future updates)
2
© 2002 by Prentice Hall
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
3
© 2002 by Prentice Hall
Multi-User Database Issues
• Record Retention
– When information should be
discarded
• Backup/Recovery
– How to protect yourself from losing
critical information
4
© 2002 by Prentice Hall
Common Multi-User DBMS
• Windows 2000
– Access 2000
– SQL Server
– ORACLE
5
• UNIX
– ORACLE
– Sybase
– Informix
© 2002 by Prentice Hall
Role of the Database
Administrator
• Organizations typically hire a
database administrator (DBA) to
handle the issues and complexities
associated with multi-user databases.
• A DBA facilitates the development
and use of one or more databases.
6
© 2002 by Prentice Hall
Data Administrator versus
Database Administrator
• Data Administrator
– Handle the
database functions
and responsibilities
for the entire
organization.
7
• Database
Administrator (DBA)
– Handle the
functions
associated with a
specific database,
including those
applications served
by the database.
© 2002 by Prentice Hall
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.
8
© 2002 by Prentice Hall
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
9
© 2002 by Prentice Hall
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
10
© 2002 by Prentice Hall
Configuration Control
• Post-implementation changes
• Procedures & Policies
• Address unanticipated problems
11
© 2002 by Prentice Hall
The Need for
Documentation
• Crucial when trying to understand
problems
• Backups and Restores
• Documentation provides the “paper”
trail for changes
12
© 2002 by Prentice Hall
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
13
© 2002 by Prentice Hall
Documentation Aids
• Version Control and Computer Assisted
Software Engineering (CASE) tools
automate and/or manage many tedious
documentation tasks.
• Printing the data dictionaries after
structural changes also helps eliminate
many tedious documentation tasks
14
© 2002 by Prentice Hall
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.
15
© 2002 by Prentice Hall
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
16
© 2002 by Prentice Hall
Rollback/Commit
• Involve logical units of work (LUW).
• Commit when entire LUW successful.
• Rollback if any part of LUW is
unsuccessful
17
© 2002 by Prentice Hall
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
18
© 2002 by Prentice Hall
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, field) is termed the
lock granularity
19
© 2002 by Prentice Hall
Types of Resource Locks
• Implicit versus Explicit
• Exclusive versus Shared
20
© 2002 by Prentice Hall
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
21
© 2002 by Prentice Hall
Deadlocks
• When two transactions are waiting on
one another to release resources.
22
© 2002 by Prentice Hall
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
23
© 2002 by Prentice Hall
Resource Locking Strategies
• Optimistic Locking
– Read data
– Process transaction
– Issue update
– Look for conflict
– If conflict
occurred, rollback
and repeat or else
commit
24
• Pessimistic Locking
– Lock required
resources
– Read data
– Process
transaction
– Issue update
– Release locks
© 2002 by Prentice Hall
Consistent Transactions
• Consistent transactions are often
referred to by the acronym ACID
– Atomic
– Consistent
– Isolated
– Durable
25
© 2002 by Prentice Hall
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.
26
© 2002 by Prentice Hall
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
27
© 2002 by Prentice Hall
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.
28
© 2002 by Prentice Hall
Summary of Isolation Levels
29
© 2002 by Prentice Hall
ACID: Durable
• Once committed, durable transactions
are saved to the data permanently
30
© 2002 by Prentice Hall
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.
31
© 2002 by Prentice Hall
Types of Cursors
32
© 2002 by Prentice Hall
Database Security
• Database security strives to ensure…
– Only authorized users perform
authorized activities at authorized
times
33
© 2002 by Prentice Hall
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
34
© 2002 by Prentice Hall
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
35
© 2002 by Prentice Hall
Granting Privileges
36
© 2002 by Prentice Hall
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
37
© 2002 by Prentice Hall
Database Recovery
Characteristics
• Continuing business operations (Fallback procedures/Continuity planning)
• Restore from backup
• Replay database activities since
backup was originally made
38
© 2002 by Prentice Hall
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
39
© 2002 by Prentice Hall
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.
40
© 2002 by Prentice Hall
Recovery via Reprocessing
• This is a brunt-force technique.
• Simply re-type all activities since the
backup was performed.
• This procedure is costly because of the
effort involved in re-entering the data.
• This procedure is risky in that human error
is likely and in that paper record-keeping
may not be accurate.
41
© 2002 by Prentice Hall
Recovery via
Rollback/Rollforward
• Most database management systems
provide a mechanism to record
activities into a log file.
42
© 2002 by Prentice Hall
Rollforward
• Activities recorded in the log files
may be replayed. In doing so, all
activities are re-applied to the
database.
• This procedure is used to
resynchronize restored database
data.
• This procedure is termed a
Rollforward.
43
© 2002 by Prentice Hall
Rollback
• Since log files save activities in
sequence order, it is possible to undo
activities in reserve order that they
were originally executed.
• This is performed to correct/undo
erroneous or malicious transaction(s).
• This procedure is known as a Rollback.
44
© 2002 by Prentice Hall
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
45
© 2002 by Prentice Hall
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.
46
© 2002 by Prentice Hall
Types of Data Repositories
• Active data
repository
– The development
and management
tools
automatically
maintain and
upkeep the
metadata.
47
• Passive data
repository
– People manually
maintain and
upkeep the
metadata
© 2002 by Prentice Hall
Database Processing
Eighth Edition
Managing
Multi-User
Databases
48
Chapter 11
David M. Kroenke
© 2002 by Prentice Hall