Database management systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 43

数据库管理系统 Database Management Systems PowerPoint PPT Presentation


  • 87 Views
  • Uploaded on
  • Presentation posted in: General

数据库管理系统 Database Management Systems. Chapter 7 Database Integrity and Transactions 第七章 数据库完整性和事务. Create code (1) Within the query system (2) In forms and reports (3) Hosted in external programs. Programming Environment. DBMS. Tables. Queries. (1). If ( . . ) Then SELECT . . .

Download Presentation

数据库管理系统 Database Management Systems

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


Database management systems

数据库管理系统Database Management Systems

Chapter 7

Database Integrity and Transactions

第七章 数据库完整性和事务


Programming environment

Create code

(1) Within the query system

(2) In forms and reports

(3) Hosted in external programs

Programming Environment

DBMS

Tables

Queries

(1)

If ( . . ) Then

SELECT . . .

Else . . .

UPDATE . . .

End If

External

Program

Forms &

Reports

(3)

C++

if (. . .) {

// embed SQL

SELECT …

}

(2)

If (Click) Then

MsgBox . . .

End If


User defined function

User-Defined Function

CREATE FUNCTION EstimateCosts

(ListPrice Currency, ItemCategory VarChar)

RETURNS Currency

BEGIN

IF (ItemCategory = ‘Clothing’) THEN

RETURN ListPrice * 0.5

ELSE

RETURN ListPrice * 0.75

END IF

END


Function to perform conditional update

Function to Perform Conditional Update

CREATE FUNCTION IncreaseSalary

(EmpID INTEGER, Amt CURRENCY)

RETURNS CURRENCY

BEGIN

IF (Amt > 50000) THEN

RETURN -1-- error flag

END

UPDATE Employee SET Salary = Salary + Amt

WHERE EmployeeID = EmpID;

RETURN Amt

END


Looking up data

Looking Up Data

CREATE FUNCTION IncreaseSalary

(EmpID INTEGER, Amt CURRENCY)

RETURNS CURRENCY

DECLARE

CURRENCY MaxAmount;

BEGIN

SELECT MaxRaise INTO MaxAmount

FROM CompanyLimits

WHERE LimitName = ‘Raise’;

IF (Amt > 50000) THEN

RETURN -1-- error flag

END

UPDATE Employee SET Salary = Salary + Amt

WHERE EmployeeID = EmpID;

RETURN Amt;

END


Data trigger events

Data Trigger Events

INSERT

DELETE

UPDATE

  • Oracle additions:

  • TablesALTER, CREATE, DROP

  • UserLOGOFF, LOGON

  • DatabaseSERVERERROR, SHUTDOWN, STARTUP

BEFORE

AFTER


Statement v row triggers

Statement v. Row Triggers

UPDATE Employee

SET Salary = Salary + 10000

WHERE EmployeeID=442

OR EmployeeID=558

SQL

After Update

On table

Before Update

On table

Triggers for overall table

Update

Row 442

After Update

Row 442

… other rows

time

Before Update

Row 442

Triggers for each row


Data trigger example

Data Trigger Example

CREATE TRIGGER LogSalaryChanges

AFTER UPDATE OF Salary ON Employee

REFERENCINGOLD ROW as oldrow

NEW ROW AS newrow

FOR EACH ROW

INSERT INTO SalaryChanges

(EmpID, ChangeDate, User, OldValue, NewValue)

VALUES

(newrow.EmployeeID, CURRENT_TIMESTAMP,

CURRENT_USER, oldrow.Salary, newrow.Salary);


Canceling data changes in triggers

Canceling Data Changes in Triggers

CREATE TRIGGER TestDeletePresident

BEFORE DELETE ON Employee

REFERENCING OLD ROW AS oldrow

FOR EACH ROW

WHEN (oldrow.Title = ‘President’)

SIGNAL _CANNOT_DELETE_PRES;


Cascading triggers

Cascading Triggers

Sale(SaleID, SaleDate, …)

SaleItem(SaleID, ItemID, Quantity, …)

AFTER INSERT

UPDATE Inventory

SET QOH = QOH – newrow.Quantity

Inventory(ItemID, QOH, …)

AFTER UPDATE

WHEN newrow.QOH < newrow.Reorder

INSERT {new order}

INSERT {new OrderItem}

Order(OrderID, OrderDate, …)

OrderItem(OrderID, ItemID, Quantity, …)


Trigger loop

Trigger Loop

Employee(EID, Salary)

AFTER UPDATE

IF newrow.Salary > 100000 THEN

Add Bonus

END

BonusPaid(EID, BonusDate, Amount)

AFTER UPDATE Or INSERT

IF newrow.Bonus > 50000 THEN

Reduce Bonus

Add Options

END

StockOptions(EID, OptionDate, Amount, SalaryAdj)

AFTER UPDATE Or INSERT

IF newrow.Amount > 100000 THEN

Reduce Salary

END


Transactions

Some transactions result in multiple changes.

These changes must all be completed successfully, or the group must fail.

Protection for hardware and communication failures.

example: bank customer transfers money from savings account to checking account.

Decrease savings balance

Increase checking balance

Problem if one transaction and machine crashes.

Possibly: give users a chance to reverse/undo a transaction.

Performance gain by executing transactions as a block.

Transactions

Savings Accounts

Inez:5340.92

4340.92

$1000

Checking Accounts

Inez:1424.27

Transaction

1. Subtract $1000 from savings.

(machine crashes)

2. Add $1000 to Checking.

(money disappears)


Defining transactions

The computer needs to be told which changes must be grouped into a transaction.

Turn on transaction processing.

Signify a transaction start.

Signify the end.

Success: save all changes

Failure: cancel all changes

Must be set in module code

Commit

Rollback

Defining Transactions


Sql transaction code

SQL Transaction Code

CREATE FUNCTION TransferMoney(Amount Currency, AccountFrom Number,

AccountTo Number) RETURNS NUMBER

curBalance Currency;

BEGIN

DECLARE HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

Return -2;-- flag for completion error

END;

START TRANSACTION;-- optional

SELECT CurrentBalance INTO curBalance

FROM Accounts WHERE (AccountID = AccountFrom);

IF (curBalance < Amount) THEN

RETURN -1;-- flag for insufficient funds

END IF

UPDATE Accounts

SET CurrentBalance = CurrentBalance – Amount

WHERE AccountID = AccountFrom;

UPDATE Accounts

SET CurrentBalance = CurrentBalance + Amount

WHERE AccountID = AccountTo;

COMMIT;

RETURN 0;-- flag for success

END;


Savepoint

SAVEPOINT

SAVEPOINT

StartOptional

start

commit

Required elements

Risky steps

time

Partial

rollback

START TRANSACTION;

SELECT …

UPDATE …

SAVEPOINT StartOptional;

UPDATE …

UPDATE …

If error THEN

ROLLBACK TO SAVEPOINT StartOptional;

END IF

COMMIT;


Concurrent access

Concurrent Access

Multiple users or processes changing the same data at the same time.

Final data will be wrong!

Force sequential

Locking

Delayed, batch updates

Two processes

Receive payment ($200)

Place new order ($150)

Initial balance $800

Result should be $800 -200 + 150 = $750

Interference result is either $600 or $950

Concurrent Access

Customers

Receive Payment

Place New Order

IDBalance

Jones$800

$600

$950

1) Read balance800

2) Subtract pmt-200

4) Save new bal.600

3) Read balance800

5) Add order150

6) Write balance950


Pessimistic locks serialization

Pessimistic Locks: Serialization

  • One answer to concurrent access is to prevent it.

  • When a transaction needs to alter data, it places a SERIALIZABLE lock on the data used, so no other transactions can even read the data until the first transaction is completed.

SET TRANSACTION SERIALIZABLE, READ WRITE

Customers

Receive Payment

Place New Order

IDBalance

Jones$800

$600

1) Read balance800

2) Subtract pmt-200

4) Save new bal.600

3) Read balance

Receive error message that it is locked.


Sql pessimistic lock

SQL Pessimistic Lock

CREATE FUNCTION ReceivePayment (

AccountID NUMBER, Amount Currency) RETURNS NUMBER

BEGIN

DECLARE HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

RETURN -2;

END

SET TRANSACTION SERIALIZABLE, READ WRITE;

UPDATE Accounts

SET AccountBalance = AccountBalance - Amount

WHERE AccountNumber = AccountID;

COMMIT;

RETURN 0;

END


Deadlock

Deadlock

Two (or more) processes have placed locks on data and are waiting for the other’s data.

Many solutions

Random wait time

Global lock manager

Two-phase commit - messages

Deadlock

1) Lock Data A

3) Wait for Data B

Data A

Data B

2) Lock Data B

4) Wait for Data A


Lock manager

Lock Manager


Optimistic locks

Optimistic Locks

  • Assume that collisions are rare

  • Improved performance, fewer resources

  • Allow all code to read any data (no locks)

  • When code tries to write a new value

    • Check to see if the existing value is different from the one you were given earlier

    • If it is different, someone changed the database before you finished, so it is a collision--raise an error

    • Reread the value and try again


Optimistic locks for simple update

Optimistic Locks for Simple Update

(1) Read the balance

(2) Add the new order value

(3) Write the new balance

(4) Check for errors

(5) If there are errors, go back to step (1).


Optimistic locks with sql

Optimistic Locks with SQL

CREATE FUNCTION ReceivePayment (

AccountID NUMBER, Amount Currency) RETURNS NUMBER

oldAmount Currency;

testEnd Boolean = FALSE;

BEGIN

DO UNTIL testEnd = TRUE

BEGIN

SELECT Amount INTO oldAmount

WHERE AccountNumber = AccountID;

UPDATE Accounts

SET AccountBalance = AccountBalance - Amount

WHERE AccountNumber = AccountID

AND Amount = oldAmount;

COMMIT;

IF SQLCODE = 0 and nrows > 0 THEN

testEnd = TRUE;

RETURN 0;

END IF

-- keep a counter to avoid infinite loops

END

END


Acid transactions

ACID Transactions

  • Atomicity: all changes succeed or fail together.

  • Consistency: all data remain internally consistent (when committed) and can be validated by application checks.

  • Isolation: The system gives each transaction the perception that it is running in isolation. There are no concurrent access issues.

  • Durability: When a transaction is committed, all changes are permanently saved even if there is a hardware or system failure.


Sql 99 200x isolation levels

SQL 99/200x Isolation Levels

  • READ UNCOMMITTED

    • Problem: might read dirty data that is rolled back

    • Restriction: not allowed to save any data

  • READ COMMITTED

    • Problem: Second transaction might change or delete data

    • Restriction: Need optimistic concurrency handling

  • REPEATABLE READ

    • Problem: Phantom rows

  • SERIALIZABLE

    • Provides same level of control as if all transactions were run sequentially.

    • But, still might encounter locks and deadlocks


Phantom rows

Phantom Rows

SELECT SUM(QOH)

FROM Inventory

WHERE Price BETWEEN 10 and 20

Included in first query

UPDATE Inventory

SET Price = Price/2

WHERE …

Additional rows will be included in the second query

SELECT SUM(QOH)

FROM Inventory

WHERE Price BETWEEN 10 and 20


Generated keys

Generated Keys

Customer Table

CustomerID, Name, …

Create an order for a new customer:

(1) Create new key for CustomerID

(2) INSERT row into Customer

(3) Create key for new OrderID

(4) INSERT row into Order

Order Table

OrderID, CustomerID, …


Methods to generate keys

Methods to Generate Keys

  • The DBMS generates key values automatically whenever a row is inserted into a table.

    • Drawback: it is tricky to get the generated value to use it in a second table.

  • A separate key generator is called by a programmer to create a new key for a specified table.

    • Drawback: programmers have to write code to generate a key for every table and each row insertion.

    • Overall drawbacks: neither method is likely to be transportable. If you change the DBMS, you will have to rewrite the procedures to generate keys.


Auto generated keys

Auto-Generated Keys

  • Create an order for a new customer:

  • INSERT row into Customer

  • Get the key value that was generated

  • Verify the key value is correct. How?

  • INSERT row into Order

Major problem:

Step 2 requires that the DBMS return the key value that was most recently generated. How do you know it is the right value? What happens if two transactions generate keys at almost the same time on the same table?


Key generation routine

Key-Generation Routine

  • Create an order for a new customer:

  • Generate a key for CustomerID

  • INSERT row into Customer

  • Generate a key for OrderID

  • INSERT row into Order

This method ensures that unique keys are generated, and that you can use the keys in multiple tables because you know the value. But, none of it is automatic. It always requires procedures and sometimes data triggers.


Database cursors

Purpose

Track through table or query one row at a time.

Data cursor is a pointer to active row.

Why?

Performance.

SQL cannot do everything.

Complex calculations.

Compare multiple rows.

Database Cursors

YearSales

1998104,321

1999145,998

2000276,004

2001362,736

1998104,321

1999145,998

2000276,004

2001362,736


Database cursor program structure

Database Cursor Program Structure

DECLARE cursor1 CURSOR FOR

SELECT AccountBalance

FROM Customer;

sumAccount, balance Currency;

SQLSTATE Char(5);

BEGIN

sumAccount = 0;

OPEN cursor1;

WHILE (SQLSTATE = ‘00000’)

BEGIN

FETCH cursor1 INTO balance;

IF (SQLSTATE = ‘00000’) THEN

sumAccount = sumAccount + balance;

END IF

END

CLOSE cursor1;

-- display the sumAccount or do a calculation

END


Cursor positioning with fetch

Cursor Positioning with FETCH

DECLARE cursor2 SCROLL CURSOR FOR

SELECT …

OPEN cursor2;

FETCH LAST FROM cursor2 INTO …

Loop…

FETCH PRIOR FROM cursor2 INTO …

End loop

CLOSE cursor2;

FETCH positioning options:

FETCH NEXTnext row

FETCH PRIORprior row

FETCH FIRSTfirst row

FETCH LASTlast row

FETCH ABSOLUTE 5fifth row

FETCH RELATIVE -3back 3 rows


Problems with multiple users

Problems with Multiple Users

Original Data

Modified Data

NameSales

Alice444,321

Carl254,998

Donna652,004

Ed411,736

NameSales

Alice444,321

Bob333,229

Carl254,998

Donna652,004

Ed411,736

New row is

added--while

code is running.

The SQL standard can prevent this problem with the INSENSITIVE option:

DECLARE cursor3 INSENSITIVE CURSOR FOR …

But, this is an expensive approach, because the DBMS usually makes a copy of the data. Instead, avoid moving backwards.


Changing data with cursors

Changing Data with Cursors

DECLARE cursor1 CURSOR FOR

SELECT Year, Sales, Gain

FROM SalesTotal

ORDER BY Year

FOR UPDATE OF Gain;

priorSales, curYear, curSales, curGain

BEGIN

priorSales = 0;

OPEN cursor1;

Loop:

FETCH cursor1 INTO curYear, curSales, curGain

UPDATE SalesTotal

SET Gain = Sales – priorSales

WHERE CURRENT OF cursor1;

priorSales = curSales;

Until end of rows

CLOSE cursor1;

COMMIT;

END


Dynamic parameterized cursor queries

Dynamic Parameterized Cursor Queries

DECLARE cursor2 CURSOR FOR

SELECT ItemID, Description, Price

FROM Inventory

WHERE Price < :maxPrice;

maxPrice Currency;

BEGIN

maxPrice = …-- from user or other query

OPEN cursor2;-- runs query with current value

Loop:

-- Do something with the rows retrieved

Until end of rows

CLOSE cursor2;

END

Parameters enable you to control the rows retrieved dynamically from within the procedure code. The value is applied when the cursor is opened.


Sally s pet store inventory

Sally’s Pet Store Inventory

  • Inventory method 1: calculate the current quantity on hand by totaling all purchases and sales every time the total is needed.

    • Drawback: performance

  • Inventory method 2: keep a running balance in the inventory table and update it when an item is purchased or sold.

    • Drawback: tricky code

  • Also, you need an adjustment process for “inventory shrink”


Inventory quantityonhand

Inventory QuantityOnHand

Merchandise

ItemID

Description

QuantityOnHand

ListPrice

Category

Add items purchased

Subtract items sold

Adjust for shrink

SaleItem

SaleID

ItemID

Quantity

SalePrice


Inventory events

For a new sale, a row is added to the SaleItem table.

A sale or an item could be removed because of a clerical error or the customer changes his or her mind. A SaleItem row will be deleted.

An item could be returned, or the quantity could be adjusted because of a counting error. The Quantity is updated in the SaleItem table.

An item is entered incorrectly. ItemID is updated in the SaleItem table.

Inventory Events

SaleItem

SaleID

ItemID

Quantity

SalePrice

  • Add a row.

  • Delete a row.

  • Update Quantity.

  • Update ItemID.


New sale insert saleitem row

New Sale: Insert SaleItem Row

CREATE TRIGGER NewSaleItem

AFTER INSERT ON SaleItem

REFERENCINGNEW ROW AS newrow

FOR EACH ROW

UPDATE Merchandise

SET QuantityOnHand = QuantityOnHand – newrow.Quantity

WHERE ItemID = newrow.ItemID;


Delete saleitem row

Delete SaleItem Row

CREATE TRIGGER DeleteSaleItem

AFTER DELETE ON SaleItem

REFERENCINGOLD ROW AS oldrow

FOR EACH ROW

UPDATE Merchandise

SET QuantityOnHand = QuantityOnHand + oldrow.Quantity

WHERE ItemID = oldrow.ItemID;


Quantity changed event

Quantity Changed Event

CREATE TRIGGER UpdateSaleItem

AFTER UPDATE ON SaleItem

REFERENCINGOLD ROW AS oldrow

NEW ROW AS newrow

FOR EACH ROW

UPDATE Merchandise

SET QuantityOnHand = QuantityOnHand

+ oldrow.Quantity – newrow.Quantity

WHERE ItemID = oldrow.ItemID;


Itemid or quantity changed event

ItemID or Quantity Changed Event

CREATE TRIGGER UpdateSaleItem

AFTER UPDATE ON SaleItem

REFERENCINGOLD ROW AS oldrow

NEW ROW AS newrow

FOR EACH ROW

BEGIN

UPDATE Merchandise

SET QuantityOnHand = QuantityOnHand + oldRow.Quantity

WHERE ItemID = oldrow.ItemID;

UPDATE Merchandise

SET QuantityOnHand = QuantityOnHand – newRow.Quantity

WHERE ItemID = newrow.ItemID;

COMMIT;

END


  • Login