Transcript Distributed Databases - University of Texas at El Paso

MIT5314: Database Applications
Slide # 1
Database Administration
Chapter 12:
Database Administration
(With Modifications)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 2
Database Administration
Once upon a time, the typical IS Organization appeared as:
CEO
VP Marketing
VP Finance
VP Production
•••••
Accounting
EDP Depart.
Why ???
 It made perfect sense:
 Information Systems were applied where they were most needed
•
•
Dr. Peeter Kirs
Accounting Systems
Other standardized, routine applications
Fall, 2003
MIT5314: Database Applications
Slide # 3
Database Administration
As information became used for more purposes and
across more functions, the IS Organization changed:
CEO
VP Marketing
VP Finance
VP Production
CIO
Systems Development
Database Administration
End User Services
 Information Systems were applied everywhere
 Information Systems were recognized as an Organizational
Resource
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 4
Database Administration
Basic Definitions:
 Data Administrator (DA)
 A high-level function that is responsible for the
overall management of data resources in an
organization:
•
May be the CIO
 Database Administrator (DBA)
I am the boss!!
 A technical function that is responsible
for the physical database design and
such issues as security enforcement and
database performance
 Database Steward
Without me,
You’re Nothing!!
 A administrative function that is
responsible for assuring that organizational
applications meet the enterprise goals
Dr. Peeter Kirs
Die, you
Egomaniacs!!
Fall, 2003
MIT5314: Database Applications
Slide # 5
Database Administration
Data Administration Functions:
 Data Policies
 Explicit statement of goals, objectives, and targets
• Goal: To Support Cost-Effective Use of the computer
environment
• Objective: To improve sharing of information across organizational units
• Target: Linking of all departmental databases within 2 years
 Data Procedures
 Written Statement of actions to be taken for a certain activity
• “In the event of a database failure, the DBA will:
1. • • •
 Data Standards
 Explicit statement of conventions to be followed in data usage
• “All table names will be prefaced by their physical location”
• “All fields containing age, weight, …. Will contain the data type short”
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 6
Database Administration
Data Administration Functions:
 Planning
 Development of the Organization’s IT Strategy
• Must correspond to the Organization’s Business Strategy
• E.g., Consider the Difference between UTEP and Harvard
 Development of the enterprise model
• Top-Down versus Bottom-Up Viewpoint
 Development of cost/benefit model
• Targets must be measurable
 Design of the database environment
• Centralized, distributed, Decentralized?? How??
 Develop the data administration plan
• A lower-level plan for database implementation, maintenance and
growth
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 7
Database Administration
Data Administration Functions:
 Data Analysis
 Define and model data requirements
 Define and model business rules
 Define operational requirements
 Maintain corporate data dictionary
 Data Conflict Resolution
 Who owns the data?
• The department, the business subunit, the
corporation?
• NOT a trivial question.
• Procedures MUST be established in advance
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 8
Database Administration
Data Administration Functions:
 Internal Marketing
 Information Systems are political entities
• The DA must sell their arguments
 Recall the Systems Trinity:
• The Manager: The person in charge of the functional department
• The System Developer: The person developing the system
• The User: The person who will use the system
 Recall why systems fail:
• Lack of Top management support
• Lack of user Acceptance
• Bad system Design
The relationship is
like a 3-legged stool:
If any leg breaks, the
stool collapses
 It is the DA’s job to make sure that ALL stakeholders are happy
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 9
Database Administration
Data Administration Functions:
 Managing the Data Repository
 Used by the DA to manage the information-
processing environment
• Contain metadata that describes the organization’s
data and data processing resources
• Replacing Data Dictionaries (simple data-element documentation tools)
• Provides information about:
• What users must know what
• What automated CASE tools that are used to specify and
develop information systems
• All Applications that access and manipulate data
•
Dr. Peeter Kirs
DBMS that maintain the repository and update system
privileges, passwords, and other information
Fall, 2003
MIT5314: Database Applications
Slide # 10
Database Administration
Database Administration Functions:
 Selection of Hardware and Software
 Difficult to keep abreast of current technology
 Difficult to predict future changes
 Emphasis on established off-the-shelf products
 Managing Data Security and Privacy
 Firewalls
 Establishment of user privileges
 Complicated by use of distributed systems
 Managing Data Integrity
 Data consistency
 Maintaining data relationships
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 11
Database Administration
Database Administration Functions:
 Database Backup
 We must assume that a database will
eventually fail
 Establishment of procedures
• How often should the data be back-up?
• What data should be backed-up more frequently?
• Who is responsible for the back-ups?
 Database Recovery
 Application of proven strategies for
reinstallation of database after crash
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 12
Database Administration
Shared Administration Activities:
 Database Design
 DA: Logical Design
 DBA:
• External Model Design (Subschemas)
• Physical Design/Construction
• Design Integrity Controls
 Database Implementation
 DBA:
• Establish Security Controls
• Supervise Database Loading
• Specify Test Procedures
• Develop Programming Standards
• Establish Back-up/Recovery Procedures
 Both:
Dr. Peeter Kirs
• Specify Access Policies
• USER TRAINING
Fall, 2003
MIT5314: Database Applications
Slide # 13
Database Administration
Shared Administration Activities:
 Operations and maintenance
 DBA:
• Monitor database performance
• Tune and reorganize databases as needed
• Enforce standards and procedures
 Both:
Support Users
 Growth and Change
 Both:
• Implement Change-Control Procedures
• Plan for growth and change
• Evaluate new technologies
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 14
Database Administration
Data Warehouse Administration:
 New function due to increased use of data warehousing
 (Massively) Integrated decision support databases from various
sources
 Emphasis on integration and coordination of data and
metadata from multiple databases
 Specific Functions
1. Support decision-oriented applications
2. Manage data warehouse (exponential) growth
2. Establish service level agreements
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 15
Database Administration
Data Dictionaries and Repositories:
 Data Dictionary
 Documents data and metadata elements of
a database
 Systems Catalog
 System-generated database that describes all
database objects
 Information Repository
 Stores metadata describing data and data
processing resources
 Information Repository Dictionary System
(IRDS)
 A software tool managing and controlling
access to the Information Repository
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 16
Database Administration
Data Dictionaries and Repositories:
 Components of the repository system architecture
A schema of the
repository
information
Software that
manages the
repository objects
Where repository
objects are stored
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 17
Database Administration
Database performance tuning:
 DBMS Installation
 Setting installation parameters
 Memory Usage
 Setting cache-levels
 Choosing background processes
 Input/Output Contention
 Deciding who gets what and when
 How to distribute heavily accessed files
 CPU usage
 Monitoring of CPU loads
 Application Tuning
 Modification of SQL code in applications
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 18
Database Administration
Database Security:
 Protection of data against accidental or
intentional loss, destruction, or misuse
 Increased difficulty due to internet access
and client-server technologies
Possible locations of data security threats
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 19
Database Administration
Threats to Data Security:
 Accidental Losses
 Human Error
 Software Failure
 Hardware Failure
 Theft and Fraud
 Establishment of firewalls
 Monitoring of activities
 Be careful of ‘disgruntled’ employees
 Improper data access
 Loss of Privacy (Personal data)
 Loss of Confidentiality (Corporate data)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 20
Database Administration
Threats to Data Security:
 Loss of data integrity
 Data may be compromised due to
database crashes
 Improper recovery can be costly
 Loss of Availability
 Through Sabotage/Data
Misplacement
 Viruses/Worms
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 21
Database Administration
Managing Data Security:
 Data Integrity Controls:
 Default Values Entered
• Minimization of user data entry
 Domain Restrictions
• Only certain values can be entered
 Probability Checks
• Echoing of input to user for confirmation
 Self-checking routines
• E.g., Check-digits
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 22
Database Administration
Managing Data Security:
 Views and Subschemas:
 Views are not only useful, but can also restrict user access to data
 Recall our Physician/Patient Database View:
CREATE VIEW drugs_given AS
SELECT physname, patient.name, illness.name,
prescription.drugcode
FROM physician, patient, treatment, illness, prescription
WHERE physician.physid = patient.physid
AND patient.patid = treatment.patid
AND treatment.illcode = illness.illcode
AND treatment.drugcode = prescription.drugcode
ORDER BY physname;
 The user might be restricted from using the view
 The user might be restricted from seeing the view’s code
(And hence seeing the physical relationships)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 23
Database Administration
Managing Data Security:
 Authorization Rules:
 Rules to Restrict Access
Authorization Matrix
Subject Tables
Dr. Peeter Kirs
SQL Privileges
Object Tables
Fall, 2003
MIT5314: Database Applications
Slide # 24
Database Administration
Managing Data Security:
 Statistical Databases:
1.
The Conceptual Model
• Only the datasets with common attributes
and their statistics are made available
• No data manipulation language is allowed
to merge and intersect populations
2.
Query Restriction
• Query-set Size controls (large only)
• Number of over-lapping entities among
successive queries
• Auditing User Queries
• Clustering individual entities in mutually
exclusive subsets
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 25
Database Administration
Managing Data Security:
 Statistical Databases:
3. Output Perturbation
• Queries made on actual data
• Output ‘perturbed’ so that statistical
characteristics remain but individual data
is ‘non-sensical’
4.
Data Perturbation
• The entire database is first ‘perturbed’
• All statistical relations are maintained in the
perturbed dataset
• User allowed to make all queries on the
perturbed data set (individual data entities
show no relationship to the real data)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 26
Database Administration
Managing Data Security:
 Authentication Schemes:
 Problem: Passwords are flawed
• Users Share them
• Sometimes easy to determine
• User write them down and they get copied
• Automatic logon scripts make it
unnecessary to enter them manually
• Unencrypted passwords travel the internet
 Goal: Verify User Identity
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 27
Database Administration
Managing Data Security:
 Authentication Schemes:
 Potential Solutions:
• Randomly Assigned Passwords
• Forced Password Changes
• Secondary Passwords
• Biometric Devices
• Thumbprint
• Hand Geometry
• Retinal Scan
• Voice Recognition
• Facial Recognition
• Future:
•
•
Dr. Peeter Kirs
Body Odor
Multi-attribute
Fall, 2003
MIT5314: Database Applications
Slide # 28
Database Administration
Managing Data Security:
 Encryption (“The Second Oldest Profession”):
 The earliest recorded use of cryptography is 1900 BC in Egypt.
 The scribes who sketched the hieroglyphs telling the story of the
life of Khnumhotep II in the town of Menet Khufu used a
substitution cipher to encrypt the names and titles of individuals in
the story.
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 29
Database Administration
Managing Data Security:
 Encryption (“The Second Oldest Profession”):
 Substitution Ciphers
• The Original symbols are substituted for other symbols
• Plain Text: ABCDEFGHIJKLMNOPQRSTUVWXYZ
Cipher Text: XYZABCDEFGHIJKLMNOPQRSTUVW
“Now is the
time for all
good people
to come to
the aid…”
Dr. Peeter Kirs
“KLT FP QEB
QFJB CLO
XII DLLA
MBLMIB QI
ZLJB QI QEB
XFA …”
Fall, 2003
MIT5314: Database Applications
Slide # 30
Database Administration
Managing Data Security:
 Encryption:
 Public/Private Keys
 Pretty Good Privacy (PGP)
Phil Zimmerman
• Should the Government have
the right to a “Master Key”?
• Target of 3-year investigation
that he violated export laws
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 31
Database Administration
Database Recovery:
 Mechanisms for restoring a database quickly
and accurately after loss of damage
 Recovery Facilities/Components:
1. Back-up Facilities
• Periodic back-up copies of the entire database
2. Journalizing Facilities
• To maintain audit trails of transactions and logs of database changes
3. Checkpoint Facilities
• When the DBMS temporarily halts all activities and synchronizes all
files and journals
4. Recovery Manager
• A DBMS component that restores the database to a correct condition
and restarts processing activities
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 32
Database Administration
Database Recovery:
 Ongoing Facilities:
Backup Facility:
Automatic periodic
duplication of entire
Database
Current Database
•
DBMS
Transaction Log
Journalizing Facility:
Database Backup
Dr. Peeter Kirs
Logging of Transactions
and Database Changes
Before and after
images of records
that have been
changed
DB Change Log
•
Logging of every
transaction along
with timestamps
Fall, 2003
MIT5314: Database Applications
Slide # 33
Database Administration
Database Recovery:
 Periodic/On Demand Facilities:
Checkpoint Facility:
The processing is
stopped and database
synchronized
Current Database
DBMS
Transaction Log
DB Change Log
Recovery Manager:
Database Backup
Dr. Peeter Kirs
Upon crash, the database is rebuilt using the
Database backup, DB Change log, and
Transaction Log
Fall, 2003
MIT5314: Database Applications
Slide # 34
Database Administration
Database Recovery:
 Back-up Facilities:
 How long between backup (hourly, daily,
weekly) is a policy determined by the DA
 Frequent back-ups increase reliability BUT each takes some time
 Back-ups should be stored off-site
 Approaches:
 Cold Backup
• Database shut down during back-up
• More secure BUT transactions delayed
 Hot Backup
• Selected portion of database is shut
down during back-up
•
Dr. Peeter Kirs
Not as disruptive BUT more complicated
Fall, 2003
MIT5314: Database Applications
Slide # 35
Database Administration
Database Recovery:
 Journalizing Facilities:
 Every transaction is stored to the transaction
log as well as the database
 Transaction Log
• Record of essential data for each
transaction processed against the database
 Database Change Log
•
Before-Images of records
(before transaction)
•
After-Images of records
(After modification)
 Needed for:
•
•
Dr. Peeter Kirs
Transaction Audits
Database Recovery
Fall, 2003
MIT5314: Database Applications
Slide # 36
Database Administration
Database Recovery:
 Journalizing Facilities: (Recap)
Transaction
Current Database
Effect of transaction
added to current
database
Dr. Peeter Kirs
DBMS
Transaction Log
Copy of transaction stored
(In case of database failure)
DB Change Log
Copy of record
affected by
transaction stored
• Before transaction
• After transaction
Fall, 2003
MIT5314: Database Applications
Slide # 37
Database Administration
Database Recovery:
 Checkpoint Facilities:
 At some specified point in time (by the DA) the
DBMS refuses all transactions
(The system is in a Quiet state)
 The database and the transaction logs are synchronized
Transaction
Current Database
Dr. Peeter Kirs
DBMS
Transaction Log
Fall, 2003
MIT5314: Database Applications
Slide # 38
Database Administration
Database Recovery:
 Recovery Manager:
 Module of DBMS that restores the database
to a ‘correct’ position when a failure occurs
Why do databases Fail?
 Aborted Transactions
• The transaction terminates abnormally due to human error, input of invalid
data, loss of transmission, hardware failure, deadlock, etc.
 Incorrect Data
• Incorrect, but valid, data entered
• E.g., incorrect account number, customer payment
 System Failure
• E.g., Power loss, operator error, systems software failure
• The database is NOT damaged
 Database Destruction
• The database is lost, destroyed, or can not be read
• Often due to disk failure
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 39
Database Administration
Database Recovery:
 Recovery and Restart Procedures
 Switch
• 2 mirror-image databases maintained
• All transactions stored/updated in both
databases
• Upon failure, the database is ‘switched’ for
the mirror image
• Generally stored across distributed databases
• Fastest/most secure
• Expensive
• Does not protect against power failures or catastrophes
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 40
Database Administration
Database Recovery:
 Recovery and Restart Procedures
 Restore/Run
• The previous transactions are reprocessed (up to the point of
the failure) against the backup copy of the database
• The most recent copy of the database is mounted and the
latest transactions rerun
Transaction Log
Database Backup
New Database
• Simple/Cheap
• May take considerable time to reprocess
• Resequencing errors may occur
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 41
Database Administration
Database Recovery:
 Recovery and Restart Procedures
 Backward Recovery (Rollback)
• Unwanted changes are undone through the use of Before
images (in the Database Change Log)
DB Change Log
(Using only Before
Images)
Current Database
New Database
Database Backup
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 42
Database Administration
Database Recovery:
 Recovery and Restart Procedures
 Forward Recovery (Rollforward)
• After images (in the Database Change Log) are applied to the
Database Backup
DB Change Log
(Using only After
Images)
Database Backup
New Database
Database Backup
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 43
Database Administration
Database Recovery: What Strategy should be applied?
 That depends on the type of failure
 Aborted Transactions
• Preferred: Rollback
• Alternative: Rollforward (To a state just prior to the abort)
 Incorrect Data
• Preferred: First correct data (if possible) then rollback and rollforward with
corrected data
• Alternative: Compensating transactions (debit then re-credit)
 System Failure (Database intact)
• Preferred: Switch
• Alternatives: (1) Rollback (2) Restart from Checkpoint
 Database Destruction
• Preferred: Suicide (unless you can Switch)
• Alternatives: (1) Rollforward (2) Reprocess transactions
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 44
Database Administration
Transaction Management:
 Transaction:
 A logical unit of work that must be
either entirely completed or aborted
 No intermediate states are acceptable.
 Most real-world database transactions are formed by two or more
database requests.
• A database request is the equivalent of a single SQL statement in an
application program or transaction
 A transaction that changes the contents of the database must alter
the database from one consistent database state to another.
 To ensure consistency of the database, every transaction must
begin with the database in a known consistent state.
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 45
Database Administration
Transaction Management:
 Transaction Properties:
 Atomicity
•
•
A transaction is a SINGLE (indivisible),
invisible, logical unit of work
A database request and ALL related operations MUST be completed
•
If ALL requirements are not, the transaction is aborted
 Durability
•
A transaction must be PERMANENT
•
When a transaction is completed, it has reached
(and must remain) in a permanent state
•
Once in a permanent state, it can not be lost
•
Even if the database fails, the transaction remains
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 46
Database Administration
Transaction Management:
 Transaction Properties:
 Serializability
•
•
•
Each concurrent transaction is treated as
thought they were received and executed
in a serial (one after the other) fashion
This is true even in a multi-user or distributed database
If transactions do occur simultaneously, one is assigned precedence
over the other
 Isolation
•
Dr. Peeter Kirs
Data/Information provided/updated by a
transaction can not be used by another
(later transaction) until the first transaction
is complete (i.e., accepted)
Fall, 2003
MIT5314: Database Applications
Slide # 47
Database Administration
Transaction Management:
 Suppose that we wish to withdraw items from inventory
Table Inventory
part descrip onhand
01
Pens
276
02
Erasers
500
03
Paper
1000
Table Inventory
part descrip onhand
01
Pens
276
02
Erasers
475
03
Paper
Dr. Peeter Kirs
1000
IF we sell 25 Erasers:
1. Find the part Number
2. Read the number onhand
3. If the number onhand is < 25, ABORT
the transaction
4. If the number onhand is >= 25,
calculate the new number onhand
quantity
500 - 25 = 475
5. Enter (update) the new number
onhand quantity
(The DBMS will update the Transaction
log and Database Change Log)
Fall, 2003
MIT5314: Database Applications
Slide # 48
Database Administration
Transaction Management:
 The SQL Commands needed are (sort-of) straight-forward:
SELECT onhand
FROM inventory
WHERE part = 02;
UPDATE inventory
SET onhand = 475 ;
OR Maybe
SELECT onhand
FROM inventory
WHERE descrip = ‘Erasers’;
UPDATE inventory
SET onhand = onhand - 25 ;
COMMIT;
Dr. Peeter Kirs
Table Inventory
part descrip onhand
01
Pens
276
02
Erasers
500
03
Paper
1000
Table Inventory
part descrip onhand
01
Pens
276
02
Erasers
475
03
Paper
1000
Fall, 2003
MIT5314: Database Applications
Slide # 49
Database Administration
Transaction Management:
Why did you say sort-of ??
 Notice we didn’t check to see if there were 25 Erasers available
 If there were not, we could not complete the transaction
How do we do that ??
 That is why we are going to learn SQL/PL
(Structured Query Language/Programming Language)
 Stay Tuned
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 50
Database Administration
Transaction Management:
 Of course, even simple transactions are sometimes problematic:
• Suppose that Dr. Mary Smith (physid: ‘123456789’) Transfers all her
patients to Dr. Von Bulow (physid: ‘374659201’)
 The command:
UPDATE patient
SET patient.physid = ‘374659201’
WHERE patient.physid = ‘123456789’;
 Will NOT be accepted unless we first enter the command:
INSERT INTO patient
VALUES (‘374659201’, ‘Von Bulow, Klaus’, ……);
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 51
Database Administration
Transaction Management:
 Consider the following Statement:
“A credit card transaction is a ternary relationship
between a customer, a merchant, and a bank”
Customer
Transaction
Merchant
Given 1 merchant
and 1 bank, how
many customers?
Given 1 customer
and 1 bank, how
many Merchants?
Mandatory?
Mandatory?
Bank
Which Makes the relationship?
• An Associative Entity
Dr. Peeter Kirs
Given 1 customer
and 1 Merchant,
how many banks?
Mandatory?
Fall, 2003
MIT5314: Database Applications
Slide # 52
Database Administration
Transaction Management:
 Assume that the following attributes apply:
CustID
Customer
BankID
MerchantID
TransAMT
Transaction
TransDate
Merchant
CustID
MerchID
CreditLim
Other
Bank
Balance
BankID
Dr. Peeter Kirs
Other
Too Simple?
Probably!!
Fall, 2003
MIT5314: Database Applications
Slide # 53
Database Administration
Transaction Management:
 Our actual tables might appear as:
Table Transaction
CustID
CredLim
Balance
A112233
1000
A112234
A112235
MerchID
BankID
TranAmt
TranDate
MerchID
Other
325.87
A112233 LMR678
CB789
425.76
05/03/03
LMR678
∙∙∙
5000
4030.20
A112235
ALD609
WF890
107.34
05/03/03
GXT678
∙∙∙
400
125.87
A112234 LMR678
FN034
45,00
05/03/03
ALD609
∙∙∙
A112233 GXT678
CB789
56.12
05/03/03
RTU665
∙∙∙
A112235 RTU665
WF890
87.45
05/04/03
A112233 RTU665
CB789
46.75.45
05/04/03
Table Customer
CustID
BankID
Other
WF890
∙∙∙
FN034
∙∙∙
CB789
∙∙∙
Table Merchant
Table Bank
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 54
Database Administration
Transaction Management:
 A few Activities need to be carried out:
 When a transaction takes place, all of the attributes in the associative
entity TRANSACTION must be recorded:
 At the same time, the customer’s CreditLim and balance must be
checked:
• If CredLim – Balance – TransAmt < 0, the purchase is denied (Aborted)
• If CredLim – Balance – TransAmt >= 0, the purchase is Accepted
 IFF the purchase is accepted:
• Customer Balance Must be updated
• MerchantBal Must be updated
 IFF the purchase is denied:
• The entire TRANSACTION is deleted
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 55
Database Administration
Transaction Management:
 Transaction Logs: (A Quick and Dirty Review)
 The DBMS uses transaction logs (A Table) to keep track of all
transactions on a database
 Intended as an organizational record of transactions
 Necessary if a ROLLBACK is issued
 Necessary in case of a database failure/crash
•
In case of failure, the transaction log is used to ROLLFORWARD
•
Transactions added since the previous COMMIT are added and
COMMITted to the database
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 56
Database Administration
Transaction Management:
 Transaction Logs:
 Consider a Sample Transaction Log for our Previous Problem:
Table Customer
Table Transaction
CustID
CredLim
Balance
CustID
MerchID
BankID
TranAmt
TranDate
A112233
1000
325.87
•••
•••
•••
•••
•••
A112234
5000
4030.20
A112233
GXT678
CB789
56.12
05/03/03
A112235
400
125.87
•••
•••
•••
•••
•••
Trans_Num
Table
Row_ID
Attribute
Before
After
10441
CUSTOMER
A112233
Balance
269.75
325.87
10441
TRANSACTION
CustID
A112233
A112233
10441
TRANSACTION
MerchID
GXT678
GXT678
10441
TRANSACTION
BankID
CB798
CB798
10441
TRANSACTION
TransDate
05/03/03
05/03/03
10441
TRANSACTION
TransAmt
56.12
56.12
Assigned by DBMS
Dr. Peeter Kirs
NOTE: Only Information about
affected Tables Included
Fall, 2003
MIT5314: Database Applications
Slide # 57
Database Administration
Transaction Management:
 Transaction Logs:
 If the transaction is aborted, we can rollback with the transaction log:
Table Customer (Before)
Table Customer (After)
CustID
CredLim
Balance
CustID
CredLim
Balance
A112233
1000
325.87
A112233
1000
269.75
A112234
5000
4030.20
A112234
5000
4030.20
A112235
400
125.87
A112235
400
125.87
Trans_Num
Table
Row_ID
Attribute
Before
After
10441
CUSTOMER
A112233
Balance
269.75
325.87
10441
TRANSACTION
CustID
A112233
A112233
10441
TRANSACTION
MerchID
GXT678
GXT678
10441
TRANSACTION
BankID
CB798
CB798
10441
TRANSACTION
TransDate
05/03/03
05/03/03
10441
TRANSACTION
TransAmt
56.12
56.12
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 58
Database Administration
Concurrency Control:
 Problem:
 In a multi-user environment,
simultaneous access to data can result
in interference and data loss
 Solution: Concurrency Control
 The process of managing simultaneous
operations against a database so that data
integrity is maintained and the operations
do not interfere with each other in a multiuser environment.
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 59
Database Administration
Concurrency Control:
 Issues in Concurrency Control:
 Lost Updates
•
Time
Assume that there are two individuals sharing a checking account with a
present balance of $400
• One of the individuals deposits $200
• Shortly afterward, one withdraws $150
Balance if
Deposit is Lost
Event
Process
Balance
11:04:00 Deposit
11:04:01
11:04:12
11:10:26 Withdrawl
11:11:12
11:11:52
READ BAL
BAL = BAL + 200
WRITE BAL
READBAL
BAL = BAL - 150
WRITE BAL
400.00
600.00
600.00
600.00
450.00
450.00
400.00
250.00
250.00
Inaccurate Balance
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 60
Database Administration
Concurrency Control:
 Issues in Concurrency Control:
 Uncommitted Data
•
If A ROLLBACK is to take place, it must occur BEFORE any New
Transactions
Consider our previous example With the proper ROLLBACK
•
Time
11:04:00
11:04:01
11:04:12
11:04:49
11:10:26
11:11:12
11:11:52
Dr. Peeter Kirs
Event
Deposit
Withdrawl
Process
READ BAL
BAL = BAL + 200
WRITE BAL
** ROLLBACK
READBAL
BAL = BAL - 150
WRITE BAL
Balance
400.00
600.00
600.00
400.00
250.00
250.00
Fall, 2003
MIT5314: Database Applications
Slide # 61
Database Administration
Concurrency Control:
 Issues in Concurrency Control:
 Uncommitted Data
•
Now consider what would occur if the rollback takes place AFTER the
second withdrawl
Time
11:04:00
11:04:01
11:04:12
11:10:26
11:11:12
11:11:39
11:11:52
Dr. Peeter Kirs
Event
Deposit
Withdrawl
Process
READ BAL
BAL = BAL + 200
WRITE BAL
READBAL
BAL = BAL - 150
** ROLLBACK
WRITE BAL
Balance
400.00
600.00
600.00
600.00
450.00
400.00
Fall, 2003
MIT5314: Database Applications
Slide # 62
Database Administration
Concurrency Control:
 Issues in Concurrency Control:
 Inconsistent retrievals:
•
Occur when a transaction calculates results while another operation is
taking place
Time
11:04:00
11:04:01
11:04:15
11:04:32
Event
Deposit
Withdrawl
Process
READ BAL
BAL = BAL + 200
BAL = BAL - 150
WRITE BAL
Balance
400.00
600.00
250.00
600.00
Withdrawl Occurs while Deposit Update taking place
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 63
Database Administration
Concurrency Control:
 Transaction Scheduling
•
•
Establishes the order in which concurrent
transactions are processed
Interleaves (meshes) the execution of database operations to
ensure serializability
•
Bases actions on time stamping and locking techniques (to be
explained)
•
Attempts to Optimize CPU usage by not having the CPU wait for a
WRITE to occur after a READ
•
In our previous examples, transactions would be written to the log,
and a read/write would not be processed until the previous
transactions write was processed
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 64
Database Administration
Concurrency Control:
 Locking
•
•
•
Most common technique to achieve serialization
Guarantees exclusive use of data items to a
current transaction
The Lock denies access (update) to another transaction until the
previous transaction is committed
•
Locks prevent another transaction from reading inconsistent data
•
DBMSs automatically enforce locking procedures through the
use of a Lock Manager
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 65
Database Administration
Concurrency Control:
 Locking
 Lock Granularity:
•
•
The level at which the data is locked
Database Level:
• Entire database is locked
• No transaction can access the data until the previous
transaction has been committed
• Preferable for batch operations
• Inadequate for multi-user databases
User A requests
data from Table 1
(Database Locked)
User A Commits or aborts
(Database Unlocked)
Dr. Peeter Kirs
Current Database
Table 1
User B requests data
from Table 2
Table 2
(Wait ---- Database Locked)
User B Transaction initiated
Fall, 2003
MIT5314: Database Applications
Slide # 66
Database Administration
Concurrency Control:
 Locking
 Lock Granularity:
•
Table Level:
•
•
Only the table accessed by a transaction
is locked
Less restrictive, but still inadequate for multi-user databases
User A requests
data from Table 2
(Table 2 Locked)
User A Commits or aborts
(Table 2 Unlocked)
Dr. Peeter Kirs
Table 1
Table 2
User B requests data
from Table 1
(OK ---- Table Available)
User C requests data
from Table 2
(Wait ---- Table Locked)
User C Transaction initiated
Fall, 2003
MIT5314: Database Applications
Slide # 67
Database Administration
Concurrency Control:
 Locking
 Lock Granularity:
•
Page Level:
•
•
A Page is a pre-specified amount of data
(4K, 8K, etc.) which is read into memory
from the database (stored on the disk)
Allows for some multi-user transactions, but requires
detailed checking
(i.e., are the records requested by a transaction being used by a previous
transaction)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 68
Database Administration
Concurrency Control:
 Locking
 Lock Granularity:
•
Record Level:
• ONLY the record requested is locked
• All other records are available for subsequent transactions
• Generally suitable for most multi-user systems
•
Field Level:
•
•
Only the individual field accessed is locked
Excellent for multi-user systems
--- BUT ---•
Dr. Peeter Kirs
Requires involved programmatic checking
Fall, 2003
MIT5314: Database Applications
Slide # 69
Database Administration
Concurrency Control:
 Locking
 Lock Types:
• All locks are Binary: They are either locked or unlocked
• Regardless of level of granularity, if locked the data is
unavailable to other transactions
 Shared Locks (S-Locks):
• Multiple users can read, but NOT update, data
• If data is S-Locked, an X-Lock (Below) can not be placed on it
 Exclusive Locks (X-Locks):
• Data can NOT be accessed, even for reading, by other users
• If X-Locked, no other lock type can be placed on it
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 70
Database Administration
Concurrency Control:
 Deadlock
•
Impasse resulting from two or more transactions
locking the same data at the same time
•
Each must wait for the other to unlock the data
•
Assume 2 people share a checking account and both try to withdraw
money from an ATM at the same time:
George
Balance Read:
$700.00
ATM Accessed:
S-Lock Placed
Time: 1:31:45
1:32:00
1:32:15
ATM Accessed:
S-Lock Placed
Withdrawl Request
--- DENIED ---
1:32:30
Balance Read:
$700.00
1:32:45
1:33:15
Wait
(DEADLOCK)
Withdrawl Request
--- DENIED ---
Laura
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 71
Database Administration
Concurrency Control:
 Deadlock Management
 Deadlock Prevention
•
•
When accessed, all records necessary are
X-Locked
Other users must wait for the records to be
released
 Deadlock Detection/Resolution
•
The DBMS periodically scans for deadlocks
•
If detected, one of the transactions is
‘backed-out’
Any transactions made during the deadlock
are aborted
When he resources become unlocked, the process is restarted
•
•
(Note that this requires additional Computer Resources)
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 72
Database Administration
Concurrency Control:
 Deadlock Management
 Time Stamping
•
UNIQUE, MONOTONIC (i.e., increasing)
time applied to each transaction
• One time stamp for last read
•
•
(additional record fields required)
Read time stamp can not precede update time stamp
•
•
Dr. Peeter Kirs
One time stamp for last update
Transaction is aborted and rescheduled
Transaction submitted for processing in order of time stamp
Fall, 2003
MIT5314: Database Applications
Slide # 73
Database Administration
Concurrency Control:
 Versioning (Optimistic Management)
•
•
Assumes that in most cases the same
record will NOT be accessed concurrently
OR will simply be read
Each time a record is requested, the DBMS
creates a new record ‘version’
Any changes made are made to the DB version
•
The changed version is compared to the original
•
•
If no conflicts exist, the version is accepted
•
Otherwise, the changes are aborted, and the system is rolled-back
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 74
Database Administration
Concurrency Control:
 Versioning (Optimistic Management)
•
Consider our previous example
Check Against Original
George
Commit
ATM Accessed
1:31:45
Balance Read:
$700.00
1:32:00
ATM Accessed
1:32:15
Withdraw $200
New Balance $500
1:32:30
1:32:45
1:33:15
1:33:24
Balance Read
$700.00
Withdraw $300
New Balance $400
Laura
Check Against
(new) Original
Rollback and
Restart
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 75
Database Administration
??? Any Questions ???
You Moron!!
I know Evrythin!!!
Dr. Peeter Kirs
Fall, 2003
MIT5314: Database Applications
Slide # 76
Database Administration
Dr. Peeter Kirs
Fall, 2003