at Manoa 2 UPDATE Flights SET seatStatus = 'occupied' WHERE fltNo = 123 AND fltDate = DATE '2008-12-25 ' AND seatNo = '22A'; SELECT seatNo FROM Flights WHERE fltNo = 123 AND fltDate = DATE '2008-12-25' AND seatStatus = ' available ' ; Flights ( fltNo , fltDate , seatNo , seatStatus ) To view available seats: To reserve a particular seat:
of a user program: a sequence of reads and writes. – Eg. User 1 views available seats and reserves seat 22A. • A DBMS supports mul8ple users, ie, mul8ple transac8ons may be running concurrently. – Eg. User 2 views available seats and reserves seat 22A. – Eg. User 3 views available seats and reserves seat 23D. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 3
concurrently – why ? Lipyeow Lim -‐-‐ University of Hawaii at Manoa 4 U1 U2 Finds 22A empty Finds 22A empty Reserves 22A Reserves 22A U1 U2 Finds 22A empty Reserves 22A Finds 22A taken Does not reserve 22A U1 U2 Finds 22A empty Reserves 22A Finds 22A taken Does not reserve 22A Schedule 1 Schedule 2 Schedule 3
Atomicity: all or nothing – Users regard execu8on of a transac8on as atomic – No worries about incomplete transac8ons • Consistency: a transac8on must leave the database in a good state – Seman8cs of consistency is applica8on dependent – The user assumes responsibility • Isola/on: a transac8on is isolated from the effects of other concurrent transac8on • Durability: Effects of completed transac8ons persists even if system crashes before all changes are wriZen out to disk Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5
a\er comple8ng all its ac8ons, or it could – abort (or be aborted by the DBMS) a\er execu8ng some ac8ons. • A very important property guaranteed by the DBMS for all transac8ons is that they are atomic. – A user can think of a Xact as always execu8ng all its ac8ons in one step, or not execu8ng any ac8ons at all. • DBMS logs all ac8ons so that it can undo the ac8ons of aborted transac8ons. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 6
from B’s account to A’s account. • The second is credi8ng both accounts with a 6% interest payment • There is no guarantee that T1 will execute before T2 or vice-‐versa, if both are submiZed together. However, the net effect must be equivalent to these two transac8ons running serially in some order. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 7 T1: BEGIN A=A+100 B=B-‐100 END T2: BEGIN A=1.06*A B=1.06*B END
Hawaii at Manoa 8 T1: BEGIN A=A+100 B=B-‐100 END T1: BEGIN Read A from disk A=A+100 Write A to disk Read B from disk B=B-‐100 Write B to disk END T1: BEGIN R(A) W(A) R(B) W(B) END
Manoa 9 T1 T2 A=A+100 B=B-‐100 A=1.06*A B=1.06*B T1 T2 A=1.06*A B=1.06*B A=A+100 B=B-‐100 A = 100, B = 200 A = 200, B = 200 A = 200, B = 100 A = 212, B = 100 A = 212, B = 106 A = 100, B = 200 A = 106, B = 200 A = 106, B = 212 A = 206, B = 212 A = 206, B = 112
Manoa 10 T1 T2 A=A+100 A=1.06*A B=B-‐100 B=1.06*B T1 T2 A=A+100 A=1.06*A B=1.06*B B=B-‐100 T1 T2 A=A+100 B=B-‐100 A=1.06*A B=1.06*B equivalent A = 100, B = 200 A = 200, B = 200 A = 212, B = 200 A = 212, B = 100 A = 212, B = 106 A = 100, B = 200 A = 200, B = 200 A = 212, B = 200 A = 212, B = 212 A = 212, B = 112
interleave the ac8ons of different transac8ons. • Equivalent schedules: For any database state, the effect (on the set of objects in the database) of execu8ng the first schedule is iden8cal to the effect of execu8ng the second schedule. • Serializable schedule: A schedule that is equivalent to some serial execu8on of the transac8ons. (Note: If each transac8on preserves consistency, every serializable schedule preserves consistency.) Lipyeow Lim -‐-‐ University of Hawaii at Manoa 11
a transac8on is automa8cally started – Different connec8ons -‐> different transac8ons • Within a connec8on, a transac8on is ended by – COMMIT or COMMIT WORK – ROLLBACK (= “abort”) • DBMS can also ini8ate rollback and return an error. • SAVEPOINT <savepoint name> • ROLLBACK TO SAVEPOINT <savepoint name> – Locks obtained a\er savepoint can be released a\er rollback to that savepoint • Using savepoints vs sequence of transac8ons – Transac8on rollback is to last transac8on only Lipyeow Lim -‐-‐ University of Hawaii at Manoa 12
levels Lipyeow Lim -‐-‐ University of Hawaii at Manoa 13 SQL Isola/on Levels DB2 Isola/on Levels Dirty read Unrepeat able Read Phantom READ UNCOMMITTED UNCOMMITTED READ (UR) Maybe Maybe Maybe READ COMMITTED CURSOR STABILITY * (CS) No Maybe Maybe REPEATABLE READ READ STABILITY (RS) No No Maybe SERIALIZABLE REPEATABLE READ (RR) No No No SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT * FROM Reserves WHERE SID=100 WITH UR
T2 which may abort Lipyeow Lim -‐-‐ University of Hawaii at Manoa 14 T1 T2 A=A+100 A=1.06*A Commit B=B-‐100 Abort A = 20 A = 120 A = 127.2 With T2 aborted correct value of A = 21.2
of A, because updates are commiZed from another transac8on (T2) Lipyeow Lim -‐-‐ University of Hawaii at Manoa 15 T1 T2 Print A A=1.06*A Commit Print A A = 100 Commit A = 20 A = 20 A = 21.2 A = 21.2 T1 sees two different values of A even though T1 did not change A!
transac8on sees different set of tuples Lipyeow Lim -‐-‐ University of Hawaii at Manoa 16 T1 T2 Find all ics321 students Enroll student D into ics321 Commit Find all ics321 students Commit {A,B,C} Insert D {A,B,C,D} T1 sees two different results of the query even though T1 did not change the table!
lipyeow
robintux
jonoalderson
matleenalaakso
datarian
cbtlibrary
sciencetokyo
masakat0
dsalo
moriyuya
masakiokuda
molmolken
aleyda
paigeccino
letsgokoyo
reverentgeek
szymonslowik
kastner
marktimemedia
inesmontani
geshan
smashingmag
jnunemaker
techseoconnect
