Chapter 12:
Download
1 / 76

Chapter 12: - PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on

Chapter 12:. Database Administration. (With Modifications). VP Finance. •••••. VP Marketing. VP Production. Accounting. EDP Depart. Once upon a time, the typical IS Organization appeared as:. CEO. Why ???. It made perfect sense:.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Chapter 12:' - alexandra-buck


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Chapter 12:

Database Administration

(With Modifications)


VP Finance

•••••

VP Marketing

VP Production

Accounting

EDP Depart.

Once upon a time, the typical IS Organization appeared as:

CEO

Why ???

  • It made perfect sense:

  • Information Systems were applied where they were most needed

  • Accounting Systems

  • Other standardized, routine applications


VP Finance

CIO

VP Marketing

Systems Development

VP Production

Database Administration

End User Services

As information became used for more purposes and across more functions, the IS Organization changed:

CEO

  • Information Systems were applied everywhere

  • Information Systems were recognized as an Organizational Resource


I am the boss!!

Without me, You’re Nothing!!

Die, you Egomaniacs!!

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)

  • A technical function that is responsible for the physical database design and such issues as security enforcement and database performance

  • Database Steward

  • A administrative function that is responsible for assuring that organizational applications meet the enterprise goals


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:

  • • • •

  • 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”


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


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


The relationship is like a 3-legged stool: If any leg breaks, the stool collapses

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

  • It is the DA’s job to make sure that ALL stakeholders are happy


Data Administration Functions: breaks, the stool collapses

  • 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

  • DBMS that maintain the repository and update system privileges, passwords, and other information


Database Administration Functions: breaks, the stool collapses

  • 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


Database Administration Functions: breaks, the stool collapses

  • 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


Shared Administration Activities: breaks, the stool collapses

  • 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:

  • Specify Access Policies

  • USER TRAINING


Shared Administration Activities: breaks, the stool collapses

  • 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


Data Warehouse Administration: breaks, the stool collapses

  • 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


Data Dictionaries and Repositories: breaks, the stool collapses

  • 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


Data Dictionaries and Repositories: breaks, the stool collapses

  • Components of the repository system architecture

A schema of the repository information

Software that manages the repository objects

Where repository objects are stored


Database performance tuning: breaks, the stool collapses

  • 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


Possible locations of data security threats breaks, the stool collapses

Database Security:

  • Protection of data against accidental or intentional loss, destruction, or misuse

  • Increased difficulty due to internet access and client-server technologies


Threats to Data Security: breaks, the stool collapses

  • 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)


Threats to Data Security: breaks, the stool collapses

  • 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


Managing Data Security: breaks, the stool collapses

  • 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


Managing Data Security: breaks, the stool collapses

  • 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)


Authorization Matrix breaks, the stool collapses

SQL Privileges

Subject Tables

Object Tables

Managing Data Security:

  • Authorization Rules:

  • Rules to Restrict Access


Managing Data Security: breaks, the stool collapses

  • Statistical Databases:

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


Managing Data Security: breaks, the stool collapses

  • 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)


Managing Data Security: breaks, the stool collapses

  • 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


Managing Data Security: breaks, the stool collapses

  • Authentication Schemes:

  • Potential Solutions:

  • Randomly Assigned Passwords

  • Forced Password Changes

  • Secondary Passwords

  • Biometric Devices

  • Thumbprint

  • Hand Geometry

  • Retinal Scan

  • Voice Recognition

  • Facial Recognition

  • Future:

  • Body Odor

  • Multi-attribute


Managing Data Security: breaks, the stool collapses

  • 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.


“Now is the time for all good people to come to the aid…”

“KLT FP QEB QFJB CLO XII DLLA MBLMIB QI ZLJB QI QEB XFA …”

Managing Data Security:

  • Encryption (“The Second Oldest Profession”):

  • Substitution Ciphers

  • The Original symbols are substituted for other symbols

  • Plain Text: ABCDEFGHIJKLMNOPQRSTUVWXYZ

Cipher Text: XYZABCDEFGHIJKLMNOPQRSTUVW


Phil Zimmerman aid…”

Managing Data Security:

  • Encryption:

  • Public/Private Keys

  • Pretty Good Privacy (PGP)

  • Should the Government have the right to a “Master Key”?

  • Target of 3-year investigation that he violated export laws


Database Recovery: aid…”

  • 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


Current Database aid…”

Transaction Log

DB Change Log

Database Backup

Database Recovery:

  • Ongoing Facilities:

Backup Facility:

Automatic periodic duplication of entire Database

  • Before and after images of records that have been changed

DBMS

Journalizing Facility:

Logging of Transactions and Database Changes

  • Logging of every transaction along with timestamps


DBMS aid…”

Current Database

Transaction Log

DB Change Log

Database Backup

Database Recovery:

  • Periodic/On Demand Facilities:

Checkpoint Facility:

The processing is stopped and database synchronized

Recovery Manager:

Upon crash, the database is rebuilt using the Database backup, DB Change log, and Transaction Log


Database Recovery: aid…”

  • 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

  • Not as disruptive BUT more complicated


Database Recovery: aid…”

  • 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:

  • Transaction Audits

  • Database Recovery


Current Database aid…”

Transaction Log

DB Change Log

Database Recovery:

(Recap)

  • Journalizing Facilities:

DBMS

Transaction

Effect of transaction added to current database

Copy of record affected by transaction stored

Copy of transaction stored

(In case of database failure)

  • Before transaction

  • After transaction


Current Database aid…”

Transaction Log

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

DBMS

Transaction


Database Recovery: aid…”

  • 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


Database Recovery: aid…”

  • 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


New Database aid…”

Transaction Log

Database Backup

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

  • Simple/Cheap

  • May take considerable time to reprocess

  • Resequencing errors may occur


DB Change Log aid…”

(Using only Before Images)

New Database

Database Backup

Current Database

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 aid…”

(Using only After Images)

New Database

Database Backup

Database Backup

Database Recovery:

  • Recovery and Restart Procedures

  • Forward Recovery (Rollforward)

  • After images (in the Database Change Log) are applied to the Database Backup


Database Recovery: aid…”

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


Transaction Management: aid…”

  • 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.


Transaction Management: aid…”

  • 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


Transaction Management: aid…”

  • 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

  • Data/Information provided/updated by a transaction can not be used by another (later transaction) until the first transaction is complete (i.e., accepted)


03 aid…”

01

part

03

02

01

02

part

Paper

Paper

Erasers

Erasers

Pens

Pens

descrip

descrip

276

1000

onhand

500

1000

475

onhand

276

Table Inventory

Table Inventory

Transaction Management:

  • Suppose that we wish to withdraw items from inventory

Bye!

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)


part aid…”

03

02

03

part

02

01

01

Pens

Paper

Erasers

descrip

descrip

Erasers

Paper

Pens

1000

276

onhand

276

500

475

onhand

1000

Table Inventory

Table Inventory

Transaction Management:

  • The SQL Commands needed are (sort-of) straight-forward:

SELECTonhand

FROMinventory

WHEREpart = 02;

UPDATEinventory

SETonhand = 475 ;

OR Maybe

SELECTonhand

FROMinventory

WHEREdescrip = ‘Erasers’;

UPDATEinventory

SETonhand = onhand - 25 ;

COMMIT;


Transaction Management: aid…”

Why did you saysort-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 QueryLanguage/Programming Language)

  • Stay Tuned


Transaction Management: aid…”

  • 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:

UPDATEpatient

SETpatient.physid = ‘374659201’

WHEREpatient.physid = ‘123456789’;

  • Will NOT be accepted unless we first enter the command:

INSERT INTOpatient

VALUES(‘374659201’, ‘Von Bulow, Klaus’, ……);


Customer aid…”

Merchant

Transaction

Bank

Transaction Management:

  • Consider the following Statement:

“A credit card transaction is a ternary relationship between a customer, a merchant, and a bank”

Given 1 merchant and 1 bank, how many customers?

Given 1 customer and 1 bank, how many Merchants?

Mandatory?

Mandatory?

Given 1 customer and 1 Merchant, how many banks?

Which Makes the relationship?

  • An Associative Entity

Mandatory?


Customer aid…”

Merchant

Transaction

MerchID

CustID

CreditLim

Balance

Other

Other

BankID

MerchantID

TransAMT

TransDate

BankID

CustID

Bank

Transaction Management:

  • Assume that the following attributes apply:

Too Simple?

Probably!!


CustID aid…”

MerchID

BankID

TranAmt

TranDate

MerchID

Other

A112235

A112233

A112234

A112235

A112233

A112233

LMR678

ALD609

LMR678

RTU665

GXT678

RTU665

CB789

CB789

FN034

CB789

WF890

WF890

46.75.45

45,00

56.12

87.45

107.34

425.76

05/04/03

05/03/03

05/04/03

05/03/03

05/03/03

05/03/03

RTU665

ALD609

GXT678

LMR678

∙∙∙

∙∙∙

∙∙∙

∙∙∙

A112234

A112235

CustID

A112233

5000

CredLim

400

1000

Balance

325.87

4030.20

125.87

BankID

Other

CB789

WF890

FN034

∙∙∙

∙∙∙

∙∙∙

Transaction Management:

  • Our actual tables might appear as:

Table Transaction

Table Customer

Table Merchant

Table Bank


Transaction Management: aid…”

  • 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:

  • CustomerBalanceMust be updated

  • MerchantBalMust be updated

  • IFF the purchase is denied:

  • The entire TRANSACTIONis deleted


Transaction Management: aid…”

(A Quick and Dirty Review)

  • Transaction Logs:

  • 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


CustID aid…”

MerchID

BankID

TranAmt

TranDate

• • •

A112233

• • •

• • •

• • •

GXT678

CB789

• • •

• • •

56.12

• • •

• • •

• • •

• • •

05/03/03

Table Customer

Table Transaction

Trans_Num

Table

Row_ID

Attribute

Before

After

CustID

A112235

A112233

A112234

1000

400

CredLim

5000

4030.20

125.87

Balance

325.87

10441

10441

10441

10441

10441

10441

TRANSACTION

CUSTOMER

TRANSACTION

TRANSACTION

TRANSACTION

TRANSACTION

A112233

MerchID

BankID

TransAmt

Balance

TransDate

CustID

05/03/03

A112233

56.12

269.75

GXT678

CB798

CB798

GXT678

56.12

A112233

05/03/03

325.87

Transaction Management:

  • Transaction Logs:

  • Consider a Sample Transaction Log for our Previous Problem:

Assigned by DBMS

NOTE: Only Information about affected Tables Included


Table Customer aid…”

Table Customer

Trans_Num

Table

Row_ID

Attribute

Before

After

CustID

A112234

CustID

A112233

A112233

A112235

A112235

A112234

1000

CredLim

1000

CredLim

5000

400

5000

400

Balance

125.87

325.87

269.75

4030.20

4030.20

Balance

125.87

10441

10441

10441

10441

10441

10441

TRANSACTION

TRANSACTION

TRANSACTION

TRANSACTION

CUSTOMER

TRANSACTION

A112233

CustID

Balance

TransAmt

TransDate

MerchID

BankID

05/03/03

GXT678

56.12

269.75

A112233

CB798

CB798

A112233

05/03/03

325.87

56.12

GXT678

Transaction Management:

  • Transaction Logs:

  • If the transaction is aborted, we can rollback with the transaction log:

(Before)

(After)


Concurrency Control: aid…”

  • Problem:

  • In a multi-user environment, simultaneous access to data can result in interference and data loss

Concurrency Control

  • Solution:

  • 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 multi-user environment.


Concurrency Control: aid…”

  • Issues in Concurrency Control:

  • Lost Updates

  • 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

Time

Event

Process

Balance

11:04:00

Deposit

READ BAL

400.00

11:04:01

BAL = BAL + 200

600.00

11:04:12

WRITE BAL

600.00

11:10:26

Withdrawl

READBAL

600.00

400.00

11:11:12

BAL = BAL - 150

450.00

250.00

11:11:52

WRITE BAL

450.00

250.00

Inaccurate Balance


Concurrency Control: aid…”

  • 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

Event

Process

Balance

11:04:00

Deposit

READ BAL

400.00

11:04:01

BAL = BAL + 200

600.00

11:04:12

WRITE BAL

600.00

11:04:49

** ROLLBACK

11:10:26

Withdrawl

READBAL

400.00

11:11:12

BAL = BAL - 150

250.00

11:11:52

WRITE BAL

250.00


Concurrency Control: aid…”

  • Issues in Concurrency Control:

  • Uncommitted Data

  • Now consider what would occur if the rollback takes place AFTER the second withdrawl

Time

Event

Process

Balance

11:04:00

Deposit

READ BAL

400.00

11:04:01

BAL = BAL + 200

600.00

11:04:12

WRITE BAL

600.00

Withdrawl

11:10:26

READBAL

600.00

11:11:12

BAL = BAL - 150

450.00

11:11:39

** ROLLBACK

11:11:52

WRITE BAL

400.00


Concurrency Control: aid…”

  • Issues in Concurrency Control:

  • Inconsistent retrievals:

  • Occur when a transaction calculates results while another operation is taking place

Time

Event

Process

Balance

11:04:00

Deposit

READ BAL

400.00

11:04:01

BAL = BAL + 200

600.00

11:04:15

Withdrawl

BAL = BAL - 150

250.00

11:04:32

600.00

WRITE BAL

Withdrawl Occurs while Deposit Update taking place


Concurrency Control: aid…”

  • 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


Concurrency Control: aid…”

  • 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


Current Database aid…”

Table 1

Table 2

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

User B requests data from Table 2

(Database Locked)

(Wait ---- Database Locked)

User A Commits or aborts

(Database Unlocked)

User B Transaction initiated


Table 1 aid…”

Table 2

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 B requests data from Table 1

User A requests data from Table 2

(OK ---- Table Available)

(Table 2 Locked)

User C requests data from Table 2

(Wait ---- Table Locked)

User A Commits or aborts

(Table 2 Unlocked)

User C Transaction initiated


Concurrency Control: aid…”

  • 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)


Concurrency Control: aid…”

  • 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 ----

  • Requires involved programmatic checking


Concurrency Control: aid…”

  • 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


George aid…”

Balance Read:

$700.00

Withdrawl Request

--- DENIED ---

ATM Accessed:

S-Lock Placed

Wait

(DEADLOCK)

Time:

1:31:45

1:32:00

1:32:15

1:32:30

1:32:45

1:33:15

ATM Accessed:

S-Lock Placed

Balance Read:

$700.00

Withdrawl Request

--- DENIED ---

Laura

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:


Concurrency Control: aid…”

  • 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)


Concurrency Control: aid…”

  • Deadlock Management

  • Time Stamping

  • UNIQUE, MONOTONIC (i.e., increasing) time applied to each transaction

  • One time stamp for last read

  • One time stamp for last update

(additional record fields required)

  • Read time stamp can not precede update time stamp

  • Transaction is aborted and rescheduled

  • Transaction submitted for processing in order of time stamp


Concurrency Control: aid…”

  • 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


George aid…”

Commit

Balance Read:

$700.00

Withdraw $200

New Balance $500

ATM Accessed

1:33:24

1:31:45

1:32:00

1:32:15

1:32:30

1:32:45

1:33:15

ATM Accessed

Balance Read

$700.00

Laura

Rollback and Restart

Concurrency Control:

  • Versioning (Optimistic Management)

  • Consider our previous example

Check Against Original

Withdraw $300

New Balance $400

Check Against (new) Original


You Moron!! aid…”

I know Evrythin!!!

??? Any Questions ???


ad