Transcript + Database Consistency

+
Constraints and triggers:
maintaining the ACID properties of
transactions
1
CSCI 2141 W2013
Slides from:
www.cs.sjsu.edu/~lee/cs157b/Transactions.ppt
www.cs.sunysb.edu/~cse515/Fall07/slides/ch21.ppt
http://www.psut.edu.jo/sites/khamis/DB/ppt/ENCh08.ppt
cs.gmu.edu/~aobaidi/fall_05/index_files/.../ENCh09.ppt
+
+
Constraints as Assertions

General constraints: constraints that do not fit in the basic
SQL categories (presented in chapter 8)

Mechanism: CREAT ASSERTION

components include: a constraint name, followed by CHECK,
followed by a condition
Chapter 9-3
+
Assertions: An Example

“The salary of an employee must not be greater than the salary
of the manager of the department that the employee works
for’’
CREAT ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY AND
E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))
Chapter 9-4
+
Using General Assertions

Specify a query that violates the condition; include inside a
NOT EXISTS clause

Query result must be empty

if the query result is not empty, the assertion has been violated
Chapter 9-5
+
SQL Triggers

Objective: to monitor a database and take action when a
condition occurs

Triggers are expressed in a syntax similar to assertions and
include the following:



event (e.g., an update operation)
condition
action (to be taken when the condition is satisfied)
Chapter 9-6
+
SQL Triggers: An Example

A trigger to compare an employee’s salary to his/her supervisor during insert or update operations:
CREATE TRIGGER INFORM_SUPERVISOR
BEFORE INSERT OR UPDATE OF
SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN
(NEW.SALARY> (SELECT SALARY FROM EMPLOYEE
WHERE SSN=NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN;
Chapter 9-7
+
Views in SQL

A view is a “virtual” table that is derived from other tables

Allows for limited update operations (since the table may not
physically be stored)

Allows full query operations

A convenience for expressing certain operations
Chapter 9-8
+
Specification of Views

SQL command: CREATE VIEW

a table (view) name

a possible list of attribute names (for example, when arithmetic
operations are specified or when we want the names to be
different from the attributes in the base relations)

a query to specify the table contents
Chapter 9-9
+
SQL Views: An Example

Specify a different WORKS_ON table
CREATE TABLE WORKS_ON_NEW AS
SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER
GROUP BY PNAME;
Chapter 9-10
+
Using a Virtual Table

We can specify SQL queries on a newly create table (view):
SELECT FNAME, LNAME FROM WORKS_ON_NEW
WHERE PNAME=‘Seena’;

When no longer needed, a view can be dropped:
DROP WORKS_ON_NEW;
Chapter 9-11
+
Efficient View Implementation

Query modification: present the view query in terms of a
query on the underlying base tables

disadvantage: inefficient for views defined via complex queries
(especially if additional queries are to be applied to the view
within a short time period)
Chapter 9-12
+
Efficient View Implementation

View materialization: involves physically creating and
keeping a temporary table

assumption: other queries on the view will follow

concerns: maintaining correspondence between the base table
and the view when the base table is updated

strategy: incremental update
Chapter 9-13
+
View Update

Update on a single view without aggregate operations:
update may map to an update on the underlying base table

Views involving joins: an update may map to an update on
the underlying base relations

not always possible
Chapter 9-14
+
Un-updatable Views

Views defined using groups and aggregate functions are not
updateable

Views defined on multiple tables using joins are generally
not updateable
 WITH CHECK OPTION: must be added to the
definition of a view if the view is to be updated

to allow check for updatability and to plan for an execution
strategy
Chapter 9-15
+
REFERENTIAL INTEGRITY
OPTIONS

We can specify RESTRICT, CASCADE, SET NULL or SET
DEFAULT on referential integrity constraints (foreign
keys)
CREATE TABLE DEPT
(
DNAME
VARCHAR(10)
NOT NULL,
DNUMBER INTEGER
NOT NULL,
MGRSSN
CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE
);
Slide 8-16
+
REFERENTIAL INTEGRITY
OPTIONS (continued)
CREATE TABLE EMP
(
ENAME
VARCHAR(30) NOT NULL,
ESSN CHAR(9),
BDATE DATE,
DNO INTEGER DEFAULT 1,
SUPERSSN
CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP
ON DELETE SET NULL ON UPDATE CASCADE );
Slide 8-17
+
18
Transactions
 Many
enterprises use databases to store
information about their state
 e.g.,
Balances of all depositors at a bank
 When
an event occurs in the real world that
changes the state of the enterprise, a program
is executed to change the database state in a
corresponding way
 e.g.,
Bank balance must be updated when deposit is
made
 Such
a program is called a transaction
+ What Does a Transaction Do?
 Return
information from the database
 RequestBalance
transaction: Read customer’s
balance in database and output it
 Update
the database to reflect the
occurrence of a real world event
 Deposit
transaction: Update customer’s
balance in database
 Cause
the occurrence of a real world event
 Withdraw
transaction: Dispense cash (and
update customer’s balance in database)
19
+ Transactions
20
 The
execution of each transaction must maintain
the relationship between the database state and
the enterprise state
 Therefore
additional requirements are placed on
the execution of transactions beyond those placed
on ordinary programs:
 Atomicity
 Consistency
 Isolation
 Durability
ACID properties
+
21
Database Consistency
 Enterprise
(Business) Rules limit the
occurrence of certain real-world events
 Student
cannot register for a course if the current
number of registrants equals the maximum allowed
 Correspondingly,
allowable database states are
restricted
cur_reg <= max_reg
 These
limitations are called (static) integrity
constraints: assertions that must be satisfied
by all database states (state invariants).
+
22
Database Consistency
(state invariants)
 Other static consistency requirements are
related to the fact that the database might
store the same information in different ways
 cur_reg
= |list_of_registered_students|
 Such
limitations are also expressed as integrity
constraints
 Database
is consistent if all static integrity
constraints are satisfied
+ Transaction Consistency
23
A
consistent database state does not necessarily
model the actual state of the enterprise

A deposit transaction that increments the balance by the
wrong amount maintains the integrity constraint balance 
0, but does not maintain the relation between the
enterprise and database states
A
consistent transaction maintains database
consistency and the correspondence between the
database state and the enterprise state (implements its
specification)

Specification of deposit transaction includes
balance = balance + amt_deposit ,
(balance is the next value of balance)
+
24
Dynamic Integrity Constraints
(transition invariants)
 Some
constraints restrict allowable state
transitions
A
transaction might transform the database from
one consistent state to another, but the
transition might not be permissible
 Example:
A letter grade in a course (A, B, C, D, F)
cannot be changed to an incomplete (I)
 Dynamic
constraints cannot be checked by
examining the database state
+
25
Transaction Consistency
 Consistent
transaction: if DB is in consistent
state initially, when the transaction completes:
 All
static integrity constraints are satisfied (but
constraints might be violated in intermediate states)

Can be checked by examining snapshot of database
 New

Cannot be checked from database snapshot
 No

state satisfies specifications of transaction
dynamic constraints have been violated
Cannot be checked from database snapshot
+ Checking Integrity Constraints
 Automatic:
Embed constraint in schema.
CHECK, ASSERTION for static constraints
 TRIGGER for dynamic constraints
 Increases confidence in correctness and decreases
maintenance costs
 Not always desirable since unnecessary checking
(overhead) might result


Deposit transaction modifies balance but cannot violate
constraint balance  0
 Manual:
Perform check in application code.
Only necessary checks are performed
 Scatters references to constraint throughout application
 Difficult to maintain as transactions are modified/added

26
+
27
Atomicity
A
real-world event either happens or does not
happen
 Student
either registers or does not register
 Similarly,
the system must ensure that either
the corresponding transaction runs to
completion or, if not, it has no effect at all
 Not
true of ordinary programs. A crash could leave
files partially updated on recovery
+
28
Commit and Abort
 If
the transaction successfully completes it
is said to commit
 The
system is responsible for ensuring that all
changes to the database have been saved
 If
the transaction does not successfully
complete, it is said to abort
 The
system is responsible for undoing, or rolling
back, all changes the transaction has made
+
29
Reasons for Abort
 System
crash
 Transaction
 Execution
aborted by system
cannot be made atomic (a site is down)
 Execution
did not maintain database consistency
(integrity constraint is violated)
 Execution
was not isolated
 Resources
not available (deadlock)
 Transaction
requests to roll back
+
30
API for Transactions
 DBMS
and TP monitor provide commands for
setting transaction boundaries. Example:
 begin
transaction
 commit
 rollback
 The
commit command is a request
 The
system might commit the transaction, or it might
abort it for one of the reasons on the previous slide
 The
rollback command is always satisfied
+
31
Durability
 The
system must ensure that once a transaction
commits, its effect on the database state is not
lost in spite of subsequent failures
 Not
true of ordinary programs. A media failure after a
program successfully terminates could cause the file
system to be restored to a state that preceded the
program’s execution
+ Implementing Durability
 Database
stored redundantly on mass storage
devices to protect against media failure
 Architecture
of mass storage devices affects
type of media failures that can be tolerated
 Related
to Availability: extent to which a
(possibly distributed) system can provide
service despite failure


Non-stop DBMS (mirrored disks)
Recovery based DBMS (log)
32
+ Isolation
 Serial

Each one starts after the previous one completes.


Execution: transactions execute in sequence
Execution of one transaction is not affected by the
operations of another since they do not overlap in time
The execution of each transaction is isolated from all
others.
 If
the initial database state and all transactions are
consistent, then the final database state will be
consistent and will accurately reflect the real-world
state, but
 Serial
execution is inadequate from a performance
perspective
33
+
34
Isolation
 Concurrent
execution offers performance benefits:

A computer system has multiple resources capable of
executing independently (e.g., cpu’s, I/O devices), but

A transaction typically uses only one resource at a time

Hence, only concurrently executing transactions can
make effective use of the system

Concurrently executing transactions yield interleaved
schedules
+
begin trans
..
op1,1
..
op1,2
..
commit
Concurrent Execution
T1
op1,1 op1.2
sequence of db
operations output by T1
local computation
op1,1 op2,1 op2.2 op1.2
T2
op2,1 op2.2
DBMS
interleaved sequence of db
operations input to DBMS
local variables
35
+ Isolation
36
 Interleaved
execution of a set of consistent transactions
offers performance benefits, but might not be correct
 Example:
course registration; cur_reg is number of
current registrants
local computation
not seen by DBMS
T1: r(cur_reg : 29)…………w(cur_reg : 30) commit
T2:
r(cur_reg : 29)……………..…w(cur_reg : 30) commit
time 
Result: Database state no longer corresponds to
real-world state, integrity constraint violated
cur_reg <> |list_of_registered_students|
+
Interaction of Atomicity and Isolation
T1: r(bal:10) w(bal:1000010)
abort
T2:
r(bal:1000010) w(yes!!!) commit
time 

T1 deposits $1000000

T2 grants credit and commits before T1 completes

T1 aborts and rolls balance back to $10

T1 has had an effect even though it aborted!
37
+
38
Isolation

An interleaved schedule of transactions is isolated if its
effect is the same as if the transactions had executed
serially in some order (serializable)
T1: r(x)
w(x)
T2:
r(y)
w(y)

It follows that serializable schedules are always correct
(for any application)

Serializable is better than serial from a performance point
of view

DBMS uses locking to ensure that concurrent schedules
are serializable
+
39
Isolation in the Real World
 SQL
supports SERIALIZABLE isolation level,
which guarantees serializability and hence
correctness for all applications
 Performance
of applications running at
SERIALIZABLE is often not adequate
 SQL
also supports weaker levels of isolation
with better performance characteristics
 But
beware! -- a particular application might not run
correctly at a weaker level
+
40
Summary
 Application
programmer is responsible for
creating consistent transactions and
choosing appropriate isolation level
 The
system is responsible for
creating
the abstractions of atomicity, durability, and
isolation
 Greatly
simplifies programmer’s task since she
does not have to be concerned with failures or
concurrency