I33Database management

1
I33/Database management

• Transaction Processing
• (update 28.4.99/jcn)
• OBJECTIVE present the methods used for
  maintaining
• database integrity
• in a multi-user, multi-transaction,
  environment.
• TOPICS Transactions Definition, characteristics
• Integrity Constraints
• Assertions, Triggers, Rules
• EX. TR TR.1 Give a 10 pay raise to IFI
  teachers.
• TR.2 Employ John Doe as the DB-course teacher.

2
I33/Database management TRM

• TRANSACTION, TR A program/ work unit which
  changes the content
• of a DB from one consistent state to
  another.
• TR Management, TRM TR execution while maintaining
  DB integrity.
• TR characteristics
• Short (flat) TR lt 1 sec. high concurrency,
• ex. OLTP, account processing few DB changes
• Long TR hours, days, moderate changes
• ex. CSCW, CASE, CAD/CAM team work
• Nested TR long transaction with high
  concurrency
• independent sub-transactions interdependent
  changes
• ex. order processing, travel arrangements

3
I33/Database management TR example-1

• INSERT one or more tuples into one RELATION
• UPDATE one or more data values in one RELATION
• DELETE one or more tuples from one or more
  RELATIONs
• Integrity constraints entity, referential,
  participation, data values

?
PERSON
TEACHER
Pid Salary Hire_date
Pid Name Position Address
Position T
Ex.1 SQL (SQL3) TR DBMS / TRM INSERT INTO
PERSON R P_index PK 150370-23456 VALUES (
150370-23456, IF found THAN error John,
T, Bergen, ELSE 250000, 1.1.99 ) W
PERSON, TEACHER
4
I33/Database management DB integrity

• DB integrity correct data as defined by the
  application
• Integrity constraints ? business rules
• DBMS Tool DDL for constraint specification
• Inherent Constraints built into the data model
• ex. RM unique tuples (relation instances)
• Implicit Constraints supported by the DBMS
• standard DDL specification
• ex. Primary and Foreign keys, data types, NOT
  NULL
• Explicit Constraints activated by the DBMS
• user defined, extended DDL specification
• ex. data set assertions, triggers
• --------------------------------------------------
  ------------------------------------------------
• Program control application implementation

5
I33/Database management Constraint specification
- examples

• entity and referential integrity, data value
• CREATE TABLE TEACHER (Pid DEC(11) NOT NULL,
  )
• PRIMARY KEY (Pid)
• FOREIGN KEY (Pid) REFERENCES PERSON
• ON DELETE CASCADE
• data value set assertions
• ASSERTION Salary_constraint CHECK (NOT EXISTS
• ( SELECT FROM TEACHER T, TEACHER S
• WHERE S.Salary gt T.Salary AND S.Position
  S )
• Triggers (rules, procedures) ON ltconditiongt DO
  ltactiongt
• CREATE RULE Salary_integrity AS
• ON UPDATE TEACHER.Salary
• DO UPDATE TEACHER SET Salary_date today

6
I33/Database management TR Properties

• Transaction, TR A program/ work unit which
  changes the content
• of a DB from one consistent state to another.
• ACID Property Definition TRM technique
• DML control statements
• Atomicity all or nothing Commit, Rollback
• Consistency Correct modifications Constraint
  management
• Isolation Unavailability of Lock, unlock
• partial results 2PL
• Durability Permanent storage Lock Commit
• persistence DB recovery

7
I33/Database management TR problems - Ex.2

• Ex. Give a 10 raise to IFI teachers.
• UPDATE TEACHER
• SET Salary Salary 1.1
• WHERE Pid IN
• ( SELECT Pid FROM COURSE C, TEACHES T
• WHERE C.Cid T.Cid AND Dept
  IFI )
• S R,W ca. 400,000 tuples
• The DBMS /TRM must assure that the following
  result is obtained
• Atomicity all IFI teachers get a raise
• Constraint only IFI teachers get a raise, DB
  constraints are maintained
• Isolation no partial information is made
  available
• Durability IFI teachers keep their raise!

8
I33/Database management TR Control - ex.3

• Ex. Give a 10 raise to IFI teachers.
• RA W TEACHER ( m (Salary Salary1.1) TEACHER
  
• (p Pid( TEACHES ( s (Dept IFI (COURSE)
  )))))
• Pseudo TR
• begin_tr
• lock (COURSE, TEACHES, TEACHER)
• R1 ? TEACHER (p Pid( TEACHES ( s (Dept
  IFI (COURSE) ))))
• R2 ? m (Salary Salary1.1) R1
• IF OK THAN commit / R2 ? DB /
• ELSE rollback / buffer emptied /
• unlock (COURSE, TEACHES, TEACHER)
• end_tr.