1 / 172

Transaction Management

Transaction Management. Outline. Introduction Basic Transaction Concepts Concurrency Recovery. Outline. Introduction Basic Transaction Concepts Concurrency Recovery. Introduction. DBMS should ensure that a database: Events: In the presence of hardware and software failures

zudora
Télécharger la présentation

Transaction Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related