Download
transaction management n.
Skip this Video
Loading SlideShow in 5 Seconds..
Transaction Management PowerPoint Presentation
Download Presentation
Transaction Management

Transaction Management

206 Vues Download Presentation
Télécharger la présentation

Transaction Management

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Transaction Management Prepared by: Remedios de Dios Bulos

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

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

  4. 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

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

  6. 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

  7. 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

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

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

  10. 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

  11. 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

  12. 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

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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

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

  33. 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

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

  35. 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

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

  37. 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

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

  39. 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

  40. Failed state Prepared by: Remedios de Dios Bulos

  41. 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

  42. Prepared by: Remedios de Dios Bulos

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

  44. 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

  45. 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

  46. 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

  47. 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

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

  49. 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

  50. 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