CS212 Distributed Database Systems Distributed Database Transaction Management

CS-212 Distributed Database Systems Distributed Database Transaction Management Instructor : Ms. Mariam Nosheen Computer Science Department LCWU, Lhr

Distributed Database Transaction Management Outline • Definition of a transaction • Termination conditions of transactions • Characterization of transactions • Formalization of the transaction concept • Properties of transactions ACID • Types of transactions • Flat, nested, and workflow • DDBMS architecture revisited Ms. Mariam Nosheen CS 212 Distributed Database Systems 2

Distributed Database Transaction Management Transaction Concept • A basic unit of consistent and reliable computing in databases • One or multiple queries in one transaction • A sequence of databases reads and writes, and computation • An execution of a program Ms. Mariam Nosheen CS 212 Distributed Database Systems 3

Distributed Database Transaction Management Consistency • Database Consistency • A database is in a consistent state if it obeys all of the consistency constraints defined over it. • State changes when updates occur. • Transaction consistency • A consistent transaction takes a database from one consistent state to another. Ms. Mariam Nosheen CS 212 Distributed Database Systems 4

Distributed Database Transaction Management A Transaction Model DB in a consistent DB may be in an state inconsistent state during execution Begin transaction T Ms. Mariam Nosheen Execution of a transaction T CS 212 Distributed Database Systems DB in a consistent state End transaction T 5

Distributed Database Transaction Management Reliability • Resiliency of a system to various failures • Tolerant of failures and able to function even when failures occur • Capability to recover from failures • One can get to a consistent state after failures Ms. Mariam Nosheen CS 212 Distributed Database Systems 6

Distributed Database Transaction Management Example Transaction Begin_transaction Reservation { input(flight_no, date, customer_name); EXEC SQL UPDATE FLIGHT SET STSOLD = STSOLD +1 WHERE FNO = flight_no AND DATE = date; EXEC SQL INSERT INTO FC (FNO, DATE, CNAME, SPECIAL) VALUES (flight_no, date, customer_name, null); output(“reservation completed”); } // Assume there always seats available! Ms. Mariam Nosheen CS 212 Distributed Database Systems 7

Distributed Database Transaction Management Termination Conditions of Transactions • Termination of a transaction • Commit – the act completes successfully • Abort – the act stops w/o completing its task • Execution is stopped • Previous actions in this act are undone (rollback) • Effect of commit • Done – DBMS can make it visible to others • Point of no return – results can’t be undone Ms. Mariam Nosheen CS 212 Distributed Database Systems 8

Distributed Database Transaction Management Example Commits and Aborts Begin_transaction Reservation { input(flight_no, date, customer_name); EXEC SQL SELECT STSOLD, CAP INTO temp 1, temp 2 FROM FLIGHT WHERE FNO = flight_no AND DATE = date; if (temp 1 = temp 2) { output(“no free seats”); ABORT; }//end of abort Ms. Mariam Nosheen else { EXEC SQL UPDATE FLIGHT SET STSOLD = STSOLD +1 WHERE FNO = flight_no AND DATE = date; EXEC SQL INSERT INTO FC (FNO, DATE, CNAME, SPECIAL) VALUES (flight_no, date, customer_name, null); COMMIT; output(“reservation completed”); } }//end of transaction CS 212 Distributed Database Systems 9

Distributed Database Transaction Management Characterization of Transactions • Basis for characterization – reads and writes • Read set (RS) • The set of data items that a transaction reads • RS [Reservation] = {FLIGHT. STSOLD, FLIGHT. CAP, FLIGHT. FNO, FLIGHT. DATE} • Write set (WS) • The set of data items that a transaction writes • WS [Reservation] = {FLIGHT. STSOLD, FC. FNO, FC. DATE, FC. CNAME, FC. SPECIAL} • Base set (BS) • BS = RS WS • BS [Reservation] = RS [Reservation] WS [Reservation] • Focus on logical reads/writes on static DB Ms. Mariam Nosheen CS 212 Distributed Database Systems 10

Distributed Database Transaction Management Formalization of Transaction Concept Given • Oij(x) : operation Oj of transaction Ti operating on entity x, where Oij {read, write} and Oij is atomic • OSi = j Oij : the set of all operations in Ti • Ni {abort, commit}: the termination condition for Ti reflexive, transitive, anti-symmetric Transaction Ti is a partial order Ti = { i , <i } where • i = OSi {Ni } • For any two operations Oij, Oik OSi , if Oij = {R(x) or W(x)} and Oik = W(x) for any data item x, then either • Oij OSi , Oij <i Ni Ms. Mariam Nosheen Oij <i Oik or Oik <i Oij CS 212 Distributed Database Systems 11

Distributed Database Transaction Management Formalization Example T: Read(x) Read(y) x x+y Write(x) Commit R(x) W(x) C R(y) The above transaction can be specified as = { R(x), R(y), W(x), C } < = { (R(x), W(x)), (R(y), W(x)), (W(x), C), (R(y), C)} Ms. Mariam Nosheen CS 212 Distributed Database Systems 12

Distributed Database Transaction Management Properties of Transactions ATOMICITY all or nothing CONSISTENCY obeys integrity constraints ISOLATION uncommitted changes invisible DURABILITY committed updates persist Ms. Mariam Nosheen CS 212 Distributed Database Systems 13

Distributed Database Transaction Management Atomicity • Either all or none of the transaction's operations are completed. • If a transaction is interrupted by a failure, the DBMS will be responsible for recovery. • Transaction recovery: from transaction aborts due to input errors, system overloads, or deadlocks. • Crash recovery: from system crashes such as media failure, power outage. Ms. Mariam Nosheen CS 212 Distributed Database Systems 14

Distributed Database Transaction Management Consistency (Correctness) • Dirty data: updated data prior to commit • Degrees of consistency (seen by Xact T) Low • • Degree 0: no write to other xacts’ dirty data Degree 1: no commit any writes before EOT Degree 2: no read from other xact’s dirty data Degree 3: other xacts do not write any data that are read by T before T completes High Each higher degree encompasses all its lower degrees. Mariam Nosheen CS 212 Distributed Database Systems 15

Distributed Database Transaction Management Isolation • An executing transaction cannot reveal its (incomplete) results before it commits. • Problems prevented by isolation • Lost updates (also called cursor stability) • Cascading aborts • Closely related to consistency levels as well as concurrency control Ms. Mariam Nosheen CS 212 Distributed Database Systems 16

Distributed Database Transaction Management Isolation Example T 1: Read(x) x x+1 Write(x) Commit T 2: Read(x) x x+1 Write(x) Commit Ms. Mariam Nosheen Case (A) Case (B): T 1 : Read(x) T 1 : x x+1 T 1 : Write(x) T 1 : Commit T 2 : Read(x) T 2 : x x+1 T 2 : Write(x) T 2 : Commit T 1 : Read(x) T 1 : x x+1 T 2 : Read(x) T 1 : Write(x) T 2 : x x+1 T 2 : Write(x) T 1 : Commit T 2 : Commit Happy Ending Lost updates! CS 212 Distributed Database Systems 17

Distributed Database Transaction Management SQL 92 Isolation Phenomena • Dirty read • T 1 modifies x which is then read by T 2 before T 1 terminates. If T 1 aborts, T 2 has read a value which never exists in the database. • Non repeatable (fuzzy) read • T 1 reads x; T 2 then modifies or deletes x and commits. T 1 tries to read x again but reads a different value or can't find it. • Phantom • T 1 searches the database according to a predicate while T 2 inserts new tuples that satisfy the predicate. Ms. Mariam Nosheen CS 212 Distributed Database Systems 18

Distributed Database Transaction Management SQL 92 Isolation Levels • Read Uncommitted • All three phenomena are possible. • Read Committed • Fuzzy reads and phantoms are possible, but dirty reads are not. • Repeatable Read • Only phantoms possible. • Anomaly Serializable • None of the phenomena are possible. Ms. Mariam Nosheen CS 212 Distributed Database Systems 19

Distributed Database Transaction Management Durability • Once a transaction commits, its results are permanent. • Database recovery will ensure transaction durability in the presence of failures. Ms. Mariam Nosheen CS 212 Distributed Database Systems 20

Distributed Database Transaction Management Types of Transactions • By duration of transactions • On line (short life) or batch (long life) • By organization of reads and writes • General – no specific ordering of reads/writes • Two step – all reads performed before writes • Restricted (or read before write) – a data item must be read before it can be written (updated). • Restricted two step – both restricted and two step • Action – restricted + each <read, write> pair is atomic Ms. Mariam Nosheen CS 212 Distributed Database Systems 21

Distributed Database Transaction Management Structure of Transactions • Flat transactions (Main focus of the book) • A single start point and a single end point • Nested transactions • Have sub transactions embedded • Workflows (usu. for some specific apps) • Longer and more elaborate activities • Hardly follow any ACID properties Ms. Mariam Nosheen CS 212 Distributed Database Systems 22

Distributed Database Transaction Management Example of Nested Transactions Begin_transaction Reservation. . . Begin transaction Airline. . . end. {Airline} Begin transaction Hotel. . . end. {Hotel}. . . Begin transaction Car. . . end. {Car} end. {Reservation} Ms. Mariam Nosheen CS 212 Distributed Database Systems 23

Distributed Database Transaction Management Types of Nested Transactions • Closed nesting • Subtransactions begin after their parents and finish before them. • The commitment of a sub transaction is conditional upon the commitment of the parent (commitment bottom up through the root). • Open nesting • Subtransactions can execute and commit independently. • Compensation (inverse) transactions may be necessary. Ms. Mariam Nosheen CS 212 Distributed Database Systems 24

Distributed Database Transaction Management Advantages of Nested Transactions • Higher level of concurrency • Possible to recover independently from failures of each subtransaction • Possible to create new transactions from existing ones by nesting them Ms. Mariam Nosheen CS 212 Distributed Database Systems 25

Distributed Database Transaction Management Workflows • An activity consisting of a set of tasks with well defined relationship among them • Types of workflows • Human oriented workflows • System oriented workflows • Transactional workflows – a mix of both Ms. Mariam Nosheen CS 212 Distributed Database Systems 26

Distributed Database Transaction Management Architecture Revisited Begin_transaction, Read, Write, Commit, Abort Results Distributed Execution Monitor Transaction Manager (TM) Scheduling/ Descheduling Requests Scheduler (SC) Other SCs Other data processors Other TMs To data processors Ms. Mariam Nosheen CS 212 Distributed Database Systems 27

Distributed Database Transaction Management Summary • Basic concepts in transaction management • • • Definition of transactions ACID Properties of transactions Levels of consistency and isolation Type of transactions Architecture of dist. transaction management Ms. Mariam Nosheen CS 212 Distributed Database Systems 28