[SOLVED] Information Management

Information Management
Transaction Management (slides provided by Prof. Samarati)

Universita degli Studi di (1)

Copyright By Assignmentchef assignmentchef

Transaction
Elementary unit of work executed by an application, characterized by
specific properties of correctness, robustness, and isolation Transactional system
Systems that provide a mechanism for the definition and execution of transactions by different concurrent transactions

Transaction (2)
Syntactically
A transaction is a procedure included between two commands begin transaction (bot)
end transaction (eot)
Within a transaction, one of the following commands is executed exactly once
commit work (commit): to terminate with success
rollback work (abort): to undo the procedure execution

Transaction (3)
A transaction is said to be well formed if
it starts with begin transaction
or equivalent, depending on the language
it ends with end transaction
it includes only one between commands commit and rollback
there is access operation (read/write) to the database after the execution of command commit or rollback

Transaction: example (1)
begin transaction x := x 10 y := y + 10
z := z y if z < 50 then commit work else rollbackend transaction Transaction: example (2)begin transactionupdate Accountset Total = Total – 100 where AccountNum= 123456update Accountset Total = Total + 100 where AccountNum = 123457commit workend transaction Transactions: propertiesTransactions should satisfy ACID properties Atomicity Consistency Isolation DurabilityA transaction is an atomic unit of work It cannot leave the database in an intermediate state a failure or an error before commit causes the UNDO of the work done up to that point a failure or an error after commit may require to REDO the work previously done, if its permanence on the database is not guaranteedPossible transaction: commit: usual behavior (99.9%) rollback requested by the application: suicide rollback requested by the system: homicide ConsistencyThe execution of a transaction should not violate integrity constraints defined for the database Integrity checks can be: immediate: during the transaction (the operation causing the violation is deferred: at the end of the transaction (if constraints are violated, the whole transaction is refused)The execution of a transaction should be independent from the execution of any other concurrent transaction Concurrent execution of a set of transactions should lead to the same result as an arbitrary sequential execution of the same transactions DurabilityThe effects of a transaction that executed commit should not be lost The system should guarantee persistency of data even in case of failures and malfunctioning Transactions and system modules (1)ACID properties are checked and guaranteed by DBMS modules Atomicity: reliability manager Consistency: DDL compiler generates the verification activities enforced at transaction execution time Isolation: concurrency manager Durability: reliability manager Transactions and system modules (2) Transaction manager Coordinates activities related with transactions through the execution ofbegin transaction, commit, and rollback operations Reliability manager Guarantees atomicity and persistency by interacting with Access methods manager (to keep track of requested operations) Buffer manager (to request, whenever necessary, write operations on disks) Concurrency manager Manages isolation by filtering and possibly reorganizing accesses (requested by the access manager) Transactions and system modules (3) Query and update managerAccess methods manager Concurrency managerTransaction managerBuffer managerReliability managerSecondary storage manager Secondary storage Reliability managerResponsible for: Execution of transactional commands begin transaction (B) commit (C) rollback (A, for Abort) Restart after malfunctions warm restart cold restartGuarantees atomicity and persistency Persistent storageFailure resistant storage It is an abstraction No storage device can guarantee 0% failure probability Replication and robust write protocols can provide failure probability close to 0% Organized in different ways depending on the critical aspects of the application, for instance: atapeandadisk two mirrored disksA failure of persistent storage is considered catastrophic and impossibleSequential file that registers, in chronological order, the actions executed by different transactions Written on persistent storage (permanent archive) Managed by the reliability manager Enables undo and redo operations metaphors: Ariannas wire, Hansel and Gretels pieces of bread, … Log organization (1)Sequential file Records in chronological order Log organization (2)Two kinds of records: transaction begin, B(T) insert, I(T,O,AS) delete, D(T,O,BS) update, U(T,O,BS,AS) commit, C(T) abort, A(T) dump, (rare) checkpoint, (more frequent) Log organization (3)Checkpoint Checkpoint BUUCRecords of transaction TTop of the log Log organization (4) Transaction records include, for operations (insert, delete, update) before state (BS) State of the object before the operation after state (AS) State of the object afer the operation it is possible to perform undo and redo of operationsTo undo an action: update: undo(U(T,O,BS,AS)) copies value BS into object O delete: undo(D(T,O,BS)) recovers object O with value BS insert: undo(I(T,O,AS)) deletes object OTo redo an action: update: redo(U(T,O,BS,AS)) copies value AS into object O delete: redo(D(T,O,BS)) deletes object O insert: redo(I(T,O,AS)) recovers object O with value AS Undo and Redo: idempotencyUndo and redo have idempotency property: an arbitrary number of undo/redo of the same action is equivalent to executing once the undo/redo of the action undo(A) … undo(A) = undo(A) redo(A) … redo(A) = redo(A)Idempotency guarantees correctness even in case of repeated execution of undo and redo operations CheckpointSystem operation executed by the reliability manager with the coordination of the buffer manager Logs active transactions Updates secondary storage according to all the completedtransactions It is executed periodically Checkpoint execution Suspends accepting write, commit and abort operations from transactions Transfers (force) to secondary storage all the dirty pages of the buffer relative to transactions that have already committed Writes on the log in a synchronous manner (force) a checkpoint record CK(T1,…,Tn) containing the identifiers of active transactions Restarts accepting operations from transactionsCompete copy of the database stored on persistent storage (backup) Created when the system is not operating inmutualexclusionwithalltheothertransactions Typically on tapeAt the end of the dump, a dump record (DUMP) is written in the log to signal that a backup has been executed at a given point in time and that identifies the copy Log record writeMust obey two rules: Write Ahead Log Commit-Precedence Write Ahead LogThe before state part of the log record should be written in the log before executing the corresponding operation on the database permits to undo write operations executed by transactions that did not commit permits to recover in case of failure after the execution of the operation on the database if the log was not written before the operation, the preceding value would be lost Commit-precedenceThe after state part of the log record should be written in the log before the commit permits to redo write operations that have already been decided by transactions that have committed, but whose updated pages have not been yet written on secondary storage by the buffer manager Write log recordsEven if the rules refer to before state and after state, practically the components of log records are written at the same timeA simplified version of the rules requires that logs are written: before the corresponding records in the database before executing commit Commit recordWritten, in a synchronous manner (force), in the log record by the transactions that chose to terminate successfully Failure before commit undo of the executed actions and restore of the initial status of the database Failure after commit redo of the actions to reconstruct the final status of the transaction Abort recordDefines the choice of abort (produced by system) Without modifying the decisions of the reliability manager, it can be: written asynchronously in the buffer containing the current block of the log later asynchronously rewritten on the log (flush) Combined writing: log and database (1)We distinguish three schemas, depending on whether the updates on the database performed by a transaction are executed (force of the buffer manager) Before commit After commit Some before and some after commit Combined writing: log and database (2)Database modified before commit no need of redo operationsB(T) U(T,X,BS,AS) U(T,Y,BS,AS) C(T)Writes on the logWrites on the databaseCombined writing: log and database (3)Database modified after commit no need of undo operationsB(T) U(T,X,BS,AS) U(T,Y,BS,AS) C(T) Combined writing: log and database (4)Database modified at any time (before and after) commit requires both undo and redo operations It is most commonly used because it allows the buffer manager tooptimize flush operations independently from the reliability managerB(T) U(T,X,BS,AS) U(T,Y,BS,AS) C(T)Two classes System failures: software bugs (e.g., of the operative system) or interruption of the working of devices (e.g., electric power failure) Device failures: failures of mass storage devices (e.g., scratches of the disk) System failuresSoftware bugs (e.g., of the operative system) or interruption of the working of devices (e.g., electric power failure) Central memory (and all buffers) content is lost The content of secondary storage is not lost warm restart Device failuresFailures of mass storage devices (e.g., scratches of the disk) Central memory (and all buffers) content is lost Secondary memory content is lost Persistent storage content is not lost cold restart Failure managerFail-stop model: if the system identifies a failure forces the complete stop of transactions forces the correct working of the operative system (boot) performs a restart warm (for system failures) cold (for device failures) becomes usable again empty buffer Fail-stop modelNormal WorkingEnd recovery Restart: classification of transactionsConsidering a failure, there are two classes of transactions committed their actions should be repeated (redo) uncommitted their actions should be undone (undo) Warm restart (1)Phase 1: determine the set of transactions that need to be undone (UNDO) and repeated (REDO) read the log backward from the end till the most recent checkpoint initialize UNDO e REDO UNDO := transactions in the checkpoint REDO := read the log forward: for each B(T) UNDO := UNDO E {T} for each C(T) UNDO := UNDO – {T} REDO := REDO E {T} Warm restart (2)Phase 2: recovery read the log backward for each action A of transactions in UNDO undo(A)go back till the first action of the oldest transaction in UNDO E REDO read the log forward for each action A of transactions in REDO redo(A) Warm restart (3) atomicity: all the active transactions at the time of failure leave the database in the initial or final status persistency: all the pages of active transactions are written on secondary storage Warm restart: example (1)B(T1), B(T2), I(T2,O1,A1), B(T3), I(T3,O2,A2), D(T1,O3,B3), B(T4), U(T3,O2,B4,A4), I(T4,O4,A5), U(T4,O2,B6,A6), C(T2), CK(T1,T3,T4), C(T4), B(T5), D(T5,O4,B7), U(T1,O2,B8,A8), A(T3), C(T1), failure determine UNDO and REDO sets Warm restart: example (2)B(T1), B(T2), I(T2,O1,A1), B(T3), I(T3,O2,A2), D(T1,O3,B3), B(T4), U(T3,O2,B4,A4), I(T4,O4,A5), U(T4,O2,B6,A6), C(T2), CK(T1,T3,T4), C(T4), B(T5), D(T5,O4,B7), U(T1,O2,B8,A8), A(T3), C(T1), failure UNDO {T3,T5}D(T5,O4,B7) U(T3,O2,B4,A4) I(T3,O2,A2)insert O4, O4:=B7 O2:=B4Warm restart: example (3)B(T1), B(T2), I(T2,O1,A1), B(T3), I(T3,O2,A2), D(T1,O3,B3), B(T4), U(T3,O2,B4,A4), I(T4,O4,A5), U(T4,O2,B6,A6), C(T2), CK(T1,T3,T4), C(T4), B(T5), D(T5,O4,B7), U(T1,O2,B8,A8), A(T3), C(T1), failure REDO {T1,T4}D(T1,O3,B3) I(T4,O4,A5)U(T4,O2,B6,A6) U(T1,O2,B8,A8)insert O4, O4:=A5O2:=A6 O2:=A8Cold restartPhase 1: recover the database in the same status as at the time of failure access the dump and selectively copy all the damaged parts of the database access the most recent dump record registered in the log read the log forward applying, for the damaged part, both the actionsof the database and commit/abort actions Execute warm restart Concurrency control (1)Permits the concurrent execution different transactions Crucial in information systems with high load banks,financialinstitution,airlinebooking Permits to efficiently use the DBMS Maximizing the number of served transactions Minimizing response times Application load measured in transactions per second (tps) Typical values: from tens to thousands Concurrency control (2)Mediates access requests to the data Decides to grant or deny requests Establishes the order of accesses (scheduler) Concurrency control (3)For the sake of lectures, we consider two simplifying assumptions: Databases in terms of abstract objects x, y, z with numerical values Read/write operations on object x, as read/write of the whole page where x is stored Concurrency control: architectureread, write begin, commit, abort Lock tableread, write(not all and possibly in different order)Access methods manager Concurrency managerTransaction managerSecondary storage manager Concurrency control (4)The concurrent execution of different transactions can cause correctness issues and anomalies Update loss Dirty read Inconsistent reads Ghost update Ghost insert Update lossUpdates by a transaction lost because overwritten by a concurrent transaction Transaction t1x := x + 1w1(x) commitTransaction t2x := x + 1w2(x) Initial value: 2commit Final value: 3 instead of 4(the update by t1 is lost)Dirty readA transaction reads the intermediate result of another transaction that then aborts (whose modification is then cancelled) Transaction t1x := x + 1 w1(x)Transaction t2x := x + 1w2(x) commit t2 reads an intemediate state which is then cancelled Inconsistent readsA transaction reads objects that another transaction is modifying: some read operations are before, others are after the updates Transaction t1bot r1(x) w1(y)r1(x) w1(z) commitTransaction t2x := x + 1 w2(x) commit t1 reads different values for x Ghost updateA transaction observes only a subset of the effects of another transaction (observing a status of the data that does not satisfyintegrity constraints)Transaction t1 botTransaction t2 boty := y – 100 r2(z)z := z + 100 w2(y)w2(z) commitConstraint: x+y+z=1000;s=1100 but the constraint is satisfiedr1(x) r1(y)s := x + y + z commit Ghost insertA transaction evaluates twice an aggregated value about a set of elements in a selection conditionselect avg(Mark) from Examwhere Subject=IMIf a new tuple is inserted between an evaluation and the subsequent one, the results may be differentThe anomaly cannot be prevented referring only to already known dataConcurrency control theoryFormally a transaction is a sequence of read and write operations has a transaction identifier assigned by the system starts with begin transaction and ends with end transaction (omitted)t1: r1(x) r1(y) w1(x) w1(y) Sequence of input/output operations presented by concurrent transactions all the operations of each transaction that committed must appear in the schedule for each transaction, operations must appear in the schedule in the same order as in the transactionFor the study: we consider the commit-projection and ignore transactions that(simplifying assumption, not acceptable in practice, will be removed afterwards) Schedule: exampleTransactions t1 : r1(x) w1(x) r1(y) w1(y) t2 : r2(y) w2(y)Some possible schedules S1 : r1(x) w1(x) r1(y) w1(y) r2(y) w2(y) S2 : r2(y) w2(y) r1(x) w1(x) r1(y) w1(y) S3 : r1(x) r2(y) w1(x) r1(y) w2(y) w1(y) S4 : r2(y) r1(x) w1(x) r1(y) w1(y) w2(y) Concurrency control Avoids schedules causing anomalies Managed by a module that accepts or refuses operations requestedby transactions (scheduler) Based on the identification of classes of acceptable schedules based on definitions of equivalence serial schedule serializable schedule Serial and serializable schedulesSerial schedule For each transaction ti in the schedule, all the operations in ti areexecuted consecutively n transactions n! possible serial schedulesSerializable schedule non-serial schedule that produces the same result as a serial schedule of the same transactions need definition of equivalence among schedules progressive concepts: view-equivalence, conflict-equivalence, two-phase locking, timestamp-based Serial schedule: exampleTransactions t1 : r1(x) w1(x) r1(y) w1(y) t2 : r2(y) w2(y)Serial schedules: S1 : r1(x) w1(x) r1(y) w1(y) r2(y) w2(y) S2 : r2(y) w2(y) r1(x) w1(x) r1(y) w1(y)Possible schedules: S3 : r1(x) r2(y) w1(x) r1(y) w2(y) w1(y) S4 : r2(y) r1(x) w1(x) r1(y) w1(y) w2(y) View-serializability (1)Preliminary definitions ri(x) reads-from wj(x) in a schedule S if: wj(x) precedes ri(x) in S there is no wk(x) between wj(x) and ri(x) in S wi(x) is a final write in a schedule S if: itisthelastwriteonxinSS: r1(x) w1(x) w1(y) r2(x) w2(y) r2(x) read-from w1(x) w1(x) final write on x w2(y) final write on yView-serializability (2)View-equivalence two schedules Si and Sj (with i = j) are view-equivalent (Si ov Sj) if they have the same read-from relations the same final writesView-serializability a schedule is view-serializable if it is view-equivalent to a serial scheduleWe denote with VSR the set of view-serializable schedules View-serializability: example (1)S: w0(x) r2(x) r1(x) w2(x) w2(z) read-from: r2(x) w0(x) r1(x) w0(x) final write: x: w2(x) z: w2(z) view-equivalent to the serial schedule w0(x) r1(x) r2(x) w2(x) w2(z) view-serializable View-serializability: example (2)S: w0(x) r1(x) w1(x) r2(x) w1(z) read-from: r1(x) w0(x) r2(x) w1(x) final write: x: w1(x) z: w1(z) view-equivalent to the serial schedule w0(x) r1(x) w1(x) w1(z) r2(x) view-serializable View-serializability: example (3)S: r1(x) r2(x) w2(x) w1(x) read-from: r1(x) final write: x: w1(x) not view-serializable (loss of update) View-serializability: example (4)S: r1(x) r2(x) w2(x) r1(x) read-from: r1(x) r1(x) w2(x) final write: x: w2(x) not view-serializable (inconsistent read) View-serializability: example (5)S: r1(x) r1(y) r2(z) r2(y) w2(y) w2(z) r1(z) read-from: r1(x) r1(y) r2(z) r1(z) w2(z) final write: y: w2(y) z: w2(z) not view-serializable (ghost update) View-seria CS: assignmentchef QQ: 1823890830 Email: [email protected]