SQL-part 2CSCI-4380

Transactions• A transaction is a series of database operations

executed as a logical unit.

• Example: Withdraw 100 dollars from account X: • Read account value of X into memory• If greater than 100 then

• subtract 100 from X• write X back to disk• commit

• else abort.

Transactions• Example: Transfer 100 dollars from account X to

account Y:

• Read account value of X into memory• If greater than 100 then

• subtract 100 from X• write X back to disk• read account value of Y into memory• add 100 to Y• write Y back to disk • commit

• else abort.

Concurrency• Multiple transactions executing at the same

time may have bad effects on each other.

T1 T2

r(x)x++

r(x)x--

w(x)r(y)

w(x)y--

w(Y)

Time

x=10, y=10 to start

x=9, y=9 at the end

Concurrency• Different execution orders changes the result

of the operation. Which one is correct?

T1 T2

r(x)x++w(x)

r(x)x--

r(y)w(x)

y--w(Y)

Time

x=10, y=10 to start

x=10, y=9 to start

Concurrency• Isolation says that if one transaction executes

completely before the other, than its result is acceptable.

T1 T2

r(x)x++w(x)r(y)y--

w(Y)r(x)x--

w(x)

T1 T2

r(x)x--

w(x)r(x)x++w(x)r(y)y--

w(Y)

x=10, y=10 to start

x=10, y=9 to start

in both cases

Serializability

• Make sure that even though operations of different transactions may be interleaved, the resulting state is equivalent to the result of some serial execution.

Atomicity• A transaction, i.e. all operations in a transaction

are either executed completely or it has no effect on the database.

• Define a transaction start point with START TRANSACTION

• A transaction either ends successfully with a COMMIT or aborted completely with a ROLLBACK.

Dirty read• Dirty read is a value written by

an uncommitted transaction

• T2 must not be allowed to commit before T1 commits

• If T1 aborts, then T2 must also be aborted (cascading rollbacks)

T1 T2

r(x)x++w(x)

r(x)x--

r(y)w(x)

y--w(Y)abort

Dirty read

Levels of isolation

• A transaction may define a level of isolation, how critical it is that the data it reads is correct.

• SET TRANSACTION READ ONLY• allows transactions to read dirty values.

This is the least restrictive level.

Levels of isolation• SET TRANSACTION READ WRITE• Does not allows transactions to read dirty values. This is the

least restrictive level.

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

• Does not allow dirty reads. All read values must be committed.

• It is possible for a value to be modified by committed transactions more than once, resulting in different reads.

Levels of isolation• SET TRANSACTION ISOLATION LEVEL REPEATABLE

READ;

• Does not allow dirty reads. All read values must be committed. Different reads will produce the same result (i.e. no one will change a read value while the transaction is processing.

• It is possible for newly inserted tuples to be relevant to a transaction in process. To disallow such “phantom updates”, one must use the most restrictive level of isolation:

• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Foreign Keys• A foreign key is a referential integrity

constraint:

• R.A is a foreign key references S.B• means that• non-null values of R.A must be stored in

S.B

• S.B is a unique attribute or a primary key

FOREIGN KEYS• Example:

CREATE TABLE ABC (X int, Y int, PRIMARY KEY(X,Y)) ;

CREATE TABLE DEF (Z int, W int, Q int, PRIMARY KEY Z, FOREIGN KEY (Z,W) REFERENCES ABC(X,Y)) ;

If we try to delete/update a tuple ABC(1,2) and if there is a tuple in DEF that references this tuple, then the deletion will be rejected. If we try to update/insert a tuple DEF(1,2,3) and there is no corresponding tuple ABC(2,3), then the update/insert will be rejected.

MaintenanceCREATE TABLE DEF (

Z int, W int, Q int, PRIMARY KEY Z, FOREIGN KEY (Z,W) REFERENCES ABC(X,Y) ON DELETE CASCADE ON UPDATE SET NULL) ;

• When we delete a tuple from ABC, we can choose to delete all referencing tuples from DEF (called CASCADE) or set the referencing values to NULL (called SET NULL).

• When we update a tuple from ABC, we can cascade the update or set the tuples referencing the old value to NULL as well.

• All these events cascade and set null events become part of the same transaction as the triggering update/delete/insert.

Constraint checking• All constraints are checked immediately, i.e. as soon as

the relation they are attached to is changed (foreign keys are attached to the referenced relation).

• This is not desirable for cyclic constraints:• Example: check for egg a chicken exists and check

for chicken an egg exists

• Defer checking of constraints to the end of the transaction:

FOREIGN KEY (Z,W) REFERENCES ABC(X,Y)

DEFERRABLE INITIALLY DEFERRED

Other constraints

• NOT NULL: checks that the values stored for an attribute should not be null

• CHECK: checks for an attribute or tuple, the values satisfy a condition (anything that can be written in the WHERE clause of a query that refers to the appropriate attributes only)

Other constraints CREATE TABLE class (

id int PRIMARY KEY

, code CHAR(4)

, name VARCHAR(50) NOT NULL

, semester VARCHAR(5)

CHECK (semester in (‘Fall’, ‘Spring’,’Summer’)

, year INT

CHECK (year > 1990)

, CHECK (code in (SELECT name FROM departments)

or name = ‘MISC’)

) ;

Attribute based constraints

• The constraint:

• CHECK (code in (SELECT name FROM departments) or name = ‘MISC’)

• is checked when a new tuple is inserted into class or when it is updated.

• However, this is not checked when the departments relation is updated.

• If it was crucial to check it when departments is updated, then it must be declared as a foreign key.

Assertions• Integrity constraints can be expressed in SQL using

assertions.• CREATE ASSERTION assertionName CHECK ( … )

• Assertions are created for a database, i.e. for all tables in a schema. They are evaluated anytime a table in the schema is changed.

• The check clause of an assertion is similar to the WHERE clause, except there is no FROM clause and relations.

• Anytime a change (INSERT/UPDATE/DELETE) in a table violates an assertion, then the transaction causing the change is aborted.

Example Database• STUDENT(Id, Name, Password, Address)• FACULTY(Id, Name, DeptId, Password, Address)• COURSE(CrsCode, DeptId, CrsName, CreditHours)• REQUIRES(CrsCode, PrereqCrsCode,

EnforcedSince)• CLASS(CrsCode, SectionNo, Semester,

Year,Textbook, ClassTime, Enrollment, MaxEnrollment, ClassroomId, InstructorId)

• CLASSROOM(ClassroomId, Seats)• TRANSCRIPT(StudId, CrsCode, SectionNo,

Semester, Year, Grade)

Assertions• The max_enrollment for a class cannot be larger than

the seating capacity of the classroom assigned to the class.

CREATE ASSERTION maxClassSizeCHECK NOT EXISTS (SELECT * FROM CLASS C, CLASSROOM CR WHERE C.ClassroomId = CR.ClassroomId AND C.maxEnrollment >

CR.seats )

Assertions• Students cannot take a course without completing the prerequisites of

that course.

CREATE ASSERTION mustHavePrereqCHECK NOT EXISTS ( SELECT * FROM Transcript T1, Requires R WHERE T1.crscode=R.crscode AND NOT EXISTS ( SELECT * FROM Transcript T2 WHERE T2.crscode = R.crscode AND T2.studid=T1.studid AND T2.grade IN (ʻAʼ,ʼBʼ,ʼCʼ,ʼDʼ) ) )

Assertions• Students cannot register for more than one section of the same

course in the same semester.

CREATE ASSERTION no2SectionsCHECK NOT EXISTS ( SELECT * FROM Transcript T1, Transcript T2 WHERE T1.studid=T2.studid AND T1.year=T2.year AND T1.semester=T2.semester AND T1.crscode=T2.crscode AND T1.sectionNoT2.sectionNo )

Assertions• Students cannot take courses that meet at the same time.

CREATE ASSERTION noSameTimeRegistrationCHECK NOT EXISTS ( SELECT * FROM Transcript T1, Transcript T2, Class C1, Class C2 WHERE T1.studid=T2.studid AND T1.year=T2.year AND T1.semester=T2.semester AND C1.crscode=T1.crscode AND C1.semester=T1.semester AND C1.year=T1.year AND C1.sectionNo=T1.sectionNo AND C2.crscode=T2.crscode AND C2.semester=T2.semester AND C1.year=T1.year AND C1.sectionNo=T1.sectionNo AND C1.classtime = C2.classtime)

Assertions• A student cannot take the same course more than

three times.

CREATE ASSERTION atMostThreeTimesCHECK NOT EXISTS ( SELECT T.studid FROM Transcript T GROUP BY T.studid, T.crscode HAVING count(*) > 3)

Assertions

• All courses must be 3 or 4 credits.CREATE ASSERTION numCreditsCHECK 0 = (SELECT count(*) FROM Course C WHERE C.CreditHours NOT IN (3,4))

Triggers• A trigger has• a database event that must be true for the

trigger to be activated

• Example: insert of class• a condition that must be true for the

trigger to be executed

• Example: when the new tuple has code CSCI

Triggers

• A trigger has• a condition of how it is to be executed• for each row that is being changed

(inserted/updated/deleted)

• for each statement (for a given transaction)

Triggers• A trigger has

• a triggering time• BEFORE the triggering event makes

the updates

• AFTER the triggering event makes the updates

• INSTEAD OF the triggering event

Triggers

• A before trigger is executed before the triggering change is even executed (and recorded).

• An after trigger is executed after the triggering change is recorded.

• Triggers become part of the transaction that triggered them.

Triggers

• A trigger has• a statement body:• a procedure that contains possibly

multiple statements

Triggers

• For each update that changes a database, the tuple before and after the change can be accessed in a trigger

• OLD: the tuple before an update• NEW: the tuple after the update

• Inserts have no OLD, deletes have no NEW.

Trigger example• CREATE TRIGGER availableSpace• AFTER INSERT OF transcript• REFERENCING

• NEW ROW AS newt• FOR EACH ROW• WHEN (newt.grade is null)

• UPDATE Class SET maxenrollment = maxenrollment - 1 WHERE CrsCode = newt.crsCode and SectionNo = newt.sectionNo and Semester = newt.semester and Year = newt.year ;

Trigger example

• Postgresql syntax is a bit different for triggers. • Define first a function that returns a trigger, then define a

trigger.

Postgresql exampleCREATE OR REPLACE FUNCTION cleanName () RETURNS trigger AS '

BEGIN

IF(NEW.name = ''sibel'') THEN

UPDATE users SET name = ''adali'' where id = NEW.id ;

END IF;

RETURN NEW;

END;

' LANGUAGE plpgsql;

CREATE TRIGGER tafter AFTER INSERT OR UPDATE ON users

FOR EACH ROW EXECUTE PROCEDURE cleanName();

Postgresql exampleCREATE OR REPLACE FUNCTION cleanName () RETURNS trigger AS '

BEGIN

IF(NEW.name = ''sibel'') THEN

New.name = ''adali'' ;

END IF;

RETURN NEW;

END;

' LANGUAGE plpgsql;

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE ON users

FOR EACH ROW EXECUTE PROCEDURE cleanName();