Transaction Management

Transaction Management Prepared by: Remedios de Dios Bulos

Outline • Introduction • Basic Transaction Concepts • Concurrency • Recovery Prepared by: Remedios de Dios Bulos

Introduction DBMS should ensure that a database: • Events: • In the presence of hardware and software failures • When multiple users are accessing the database • Properties: • Reliable • Remains in a consistent state • Functions: • Transaction support • Concurrency • Recovery Prepared by: Remedios de Dios Bulos

Outline • Introduction • Basic Transaction Concepts • Concurrency Control Prepared by: Remedios de Dios Bulos

CB2000 What is a transaction? • a logical unit of work • Entire program, part of a program or a single command • Carried out by: • A single user or • An application program • To: • Access the database or • Change the contents of the database Prepared by: Remedios de Dios Bulos

EN94 What is a transaction? • A Transaction: logical unit of database processing that includes one or more access operations (read-retrieval, write-insert or update, delete) • A transaction (set of operations) may be stand-alone specified in a high level language like SQL submitted interactively, or may be embedded within a program. Prepared by: Remedios de Dios Bulos

What is a transaction? • Transaction boundaries: • Begin transaction • End transaction Prepared by: Remedios de Dios Bulos

Example T1 :Withdrawal transaction Prepared by: Remedios de Dios Bulos

Simple Model of a Database • A database : • is a collection of named data items • Permanently resides on a disk, but some portion of it is temporarily residing in memory • Granularity of data : • a field, a record, or a whole disk block Prepared by: Remedios de Dios Bulos

2 Basic Transaction Operations • read (X): Reads a database item named X into a program variable (local buffer). • write (X): Writes the value of program variable (local buffer) into the database item named X Prepared by: Remedios de Dios Bulos

Data • Basic unit of data transfer from the disk to the computer main memory is one block. • In general, a data item (what is read or written) will be the field of some record in the database, although it may be a larger unit such as a record or even a whole block. Prepared by: Remedios de Dios Bulos

1 buffer block 1 block Block Storage Operations Prepared by: Remedios de Dios Bulos

Read Operation • read(X) command includes the following steps: • Find the address of the disk block that contains item X. • Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer). • Copy item X from the buffer to the program variable. Prepared by: Remedios de Dios Bulos

Buffer block A X A Buffer B X1 Work Area of T1 Work Area of T2 MAIN MEMORY input(A) X T1:read (X) Prepared by: Remedios de Dios Bulos

Write Operation • write (X) command includes the following steps: • Find the address of the disk block that contains item X. • Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer). • Copy item X from the program variable into its correct location in the buffer. • Store the updated block from the buffer back to disk (either immediately or at some later point in time). Prepared by: Remedios de Dios Bulos

X X A Buffer B Work Area of T1 Work Area of T2 MAIN MEMORY Buffer block A output(A) X X T1:write (X) X1 Prepared by: Remedios de Dios Bulos

A B X X2 X1 Y Work Area of T2 Y1 Buffer Work Area of T1 Buffer block A input(A) Buffer block B output(B) read-item(X) write-item(Y) Example of Data Access MAIN MEMORY Prepared by: Remedios de Dios Bulos

Transaction Examples Database Relations: EMPLOYEE (Eno, Name, Address, Sex, CivilStatus, Bdate, Position, Salary, SuperEno, Dno) PROJECT (Pno, Pname, Plocation, Dno) WORKS_ON (Eno, Pno, Hours) Prepared by: Remedios de Dios Bulos

Transaction example (1) • Problem: Update the salary (add 10% to present salary) of a particular employee with an Eno = x. EMPLOYEE (Eno, Name, Address, Sex, CivilStatus, Bdate, Position, Salary, SuperEno, Dno) Prepared by: Remedios de Dios Bulos

Transaction example (1) Read(Eno = x, salary) dbop salary = salary * 1.1 Write(Eno = x, salary) dbop Transaction: consists of 2 dbop and a non-dbop Note: dbop means database operation Prepared by: Remedios de Dios Bulos

Transaction example (2) • Problem: • Delete the employee with Eno=x. • Reassign the projects of the employee with Eno=x toanother employee, say Eno=new_eno. EMPLOYEE (Eno, Name, Address, Sex, CivilStatus, Bdate, Position, Salary, SuperEno, Dno) WORKS_ON (Eno, Pno, Hours) Prepared by: Remedios de Dios Bulos

Transaction example (2) Delete (Eno = x) dbop for all Works_On records, pno begin read (Pno = pno, Eno) dbop if (Eno = x ) then begin Eno = new_eno, write (Pno = pno, Eno) dbop end end Prepared by: Remedios de Dios Bulos

Transactions: Properties (ACID) • Atomicity • Consistency • Isolation • Durability Prepared by: Remedios de Dios Bulos

Transactions: Properties (ACID) • Atomicity • The “all or nothing” property. • A transaction is an indivisible unit that is either performed in its entirety or it is not performed at all. • Consistency • A transaction must transform the database from one consistent state to another consistent state. • The database must reflect the real-word at all times. Prepared by: Remedios de Dios Bulos

Transactions: Properties (ACID) • Isolation • Transactions execute independently of one another. • The partial effects of incomplete transactions should not be visible to other transactions. • Durability • The effects of a successfully completed (committed) transactions are permanently recorded in the database. • They must not be lost because of a subsequent failure. Prepared by: Remedios de Dios Bulos

Transactions: Properties (ACID) • Problem: transfer $50 from account A to account B read(A); A:= A-50; write(A); read(B); B:= B+50; write(B). Prepared by: Remedios de Dios Bulos

Transaction Properties: Consistency A= $1000 B=$2000 read(A); A:= A-50; write(A); read(B); B:= B+50; write(B). • the sum of A and B should be unchanged by the execution of the transaction. • Ensuring consistency for an individual transaction is the responsibility of the application programmer. Prepared by: Remedios de Dios Bulos

Transaction Properties: Atomicity A= $1000 B=$2000 Ti : read(A); A:= A-50; write(A); read(B); B:= B+50; Fail! write(B). • the database system keeps track (on disk) of the old values of any data on which a transaction performs a write, and • if a transaction does not complete its execution, the database system restores the old values to make it appear as though the transaction never executed. • Ensuring atomicity is the responsibility of the transaction-management component of the database system. Prepared by: Remedios de Dios Bulos

Transaction Properties: Durability • Durability is guaranteed: • Updates carried out by the transaction have been written to disk before the transaction completes; • Information about updates carried out by the transaction and written to disk is sufficient to enable the database to reconstruct the updates when the database system is restarted after failure. • Ensuring durability is the responsibility of the recovery-management component of the database system. Prepared by: Remedios de Dios Bulos

Transaction Properties: Isolation A= $1000 B=$2000 Ti : read(A); A:= A-50; write(A); read(B); B:= B+50; T2: read(A) read(B) sum=A+B write(B). • Isolation is guaranteed: • Execute transactions serially; however performance is affected; • Other solutions have been developed to allow multiple transactions to execute concurrently • Ensuring isolation is the responsibility of the concurrency-control component of the database system. Prepared by: Remedios de Dios Bulos

Transaction States • Active • Partially committed • Committed • Failed • Aborted Prepared by: Remedios de Dios Bulos

Transaction States • A transaction starts in the active state. • The transaction stays in the active state while it is executing. active Prepared by: Remedios de Dios Bulos

Active State Example T1 :Withdrawal transaction Prepared by: Remedios de Dios Bulos

Transaction State partially committed active • When the transaction finishes its final statement, it enters the partially committed state. • The actual output may still be residing in memory; thus, a hardware failure may preclude its successful completion Prepared by: Remedios de Dios Bulos

Example T1 :Withdrawal transaction Partially committed Prepared by: Remedios de Dios Bulos

Transaction State committed partially committed active • When the last of the information is written out in the disk, the transaction enters the committed state. Prepared by: Remedios de Dios Bulos

Example T1 :Withdrawal transaction committed state Prepared by: Remedios de Dios Bulos

Transaction State • If a transaction can no longer proceed with its normal execution (because of some failure), it enters the failed state. active failed Prepared by: Remedios de Dios Bulos

Failed state Prepared by: Remedios de Dios Bulos

Transaction State • A failed transaction must be rolled back and the database should be restored to its state prior to the start of the transaction. Such a transaction is said to be aborted. active failed aborted Prepared by: Remedios de Dios Bulos

Prepared by: Remedios de Dios Bulos

Transaction State committed partially committed active 2 Outcomes of a transaction failed aborted Prepared by: Remedios de Dios Bulos

2 Outcomes of a transaction • committed • The transaction is successfully completed; • The database reaches a new consistent state • aborted • The transaction does not execute successfully; • The database must be restored to the consistent state it was in before the transaction started. (rolled back or undone). Prepared by: Remedios de Dios Bulos

Transactions: other processes • A committed transaction cannot be aborted. • To reverse the effects of a committed transaction, a compensating transaction must be executed. • An aborted transaction that is rolled back: • can be restarted later • depending on the cause of failure, it may successfully execute and then commit at that time. Prepared by: Remedios de Dios Bulos

Transactions in DBMS • The DBMS does not know which updates are grouped together to form a single transaction. • The user should indicate the boundaries of the transaction through DML commands (delimeters) provided for in SQL2: • BEGIN TRANSACTION • COMMIT • If delimeters are not used the entire program is considered as a single transaction. Prepared by: Remedios de Dios Bulos

Transaction manager Scheduler Buffer manager Recovery manager File manager Access manager Systems manager DBMS Transaction Subsystem • Transaction manager coordinates transactions on behalf of application programs • It communicates with the scheduler • The scheduler handles concurrency control. • Its objective is to maximize concurrency without allowing simultaneous transactions to interfere with one another • The recovery manager ensures that the database is restored to the right state before a failure occurred. • The buffer manager is responsible for the transfer of data between disk storage and main memory. Prepared by: Remedios de Dios Bulos

Outline • Introduction • Basic Transaction Concepts • Concurrency Control • Recovery Prepared by: Remedios de Dios Bulos

Single-user vs Multiuser Systems • Single-user DBMS – at most one user at a time can use the system • Multiuser DBMS – many users can use the system concurrently Prepared by: Remedios de Dios Bulos

Multiprogramming in Multiuser Systems • allows multiple users to use computer systems simultaneously. • allows the computer to process multiple programs (transactions). • multiprogramming operating systems execute some commands from one program, then suspend that program, and execute some commands from the next program, and so on. Prepared by: Remedios de Dios Bulos

Transaction Management