Transaction Management

1. Transaction Management by Benjamin Nguyen

2. Overview • Transaction • Properties of transaction • Pessimistic & optimistic techniques • Locking • No lock techniques • Oracle recovery tools

3. Transaction • An action, or series of actions, carried out by a single user or application program, which reads or updates the contents of the database

4. Properties of Transaction • ACID • Atomicity: ‘all or nothing’ property. (Responsible by DBMS recovery subsystem) • Consistency: database must be transform from one consistent state to another consistent state.

5. Properties of Transaction (cont.) • ACID • Isolation: transaction execute independently of one another. (Responsible by concurrency control) • Durability: The effects of a successfully completed (committed) transaction are permanently recorded in the database and must not be lost because of a subsequent failure (Responsible by recovery subsystem)

6. Over view of Database

7. Pessimistic & Optimistic techniques • Pessimistic • Check for conflict when read/write • Optimistic • Only check for conflict when the transaction wishes to commit

8. Pessimistic & Optimistic techniques • Pessimistic • Using locking techniques • Optimistic • The concurrency control that does not use lock and it involve the following phases: • Read phase • Validation phase • Write phase

9. Optimistic Techniques Read phase Write phase write Read Validation commit

10. Read phase • Extends from start of transaction to just before commit • Read from database • Store in local variable

11. Validation phase • Follow the read phase • Purpose is to ensure that data is still current.

12. Write phase • Follow after validation • Updated variable from local variable can now be applied to database

13. Pessimistic techniques • Using locking technique to reduce conflict and ensure data consistency. • The lost update problem

14. The lost update problem T1 T2 balx = $0 balx = $100

15. The lost update problem T1 T2 balx = $100 balx = $200

16. The lost update problem T1 T2 balx = $90 balx = $200

17. The lost update problem T1 T2 balx = $90 balx = $200 bal should be = (100+100 -10) = $190 Since T1 committed after T2, it written over the value of T2

18. The uncommitted dependency (or dirty read) problem T3 Balx should be $90

19. The inconsistent analysis problem

20. How to deal with these problem? • Serial schedule • A schedule where the operations of each transaction are executed consecutively without any interleaved operations from the other transaction • Locking technique • A procedure used to control concurrent access to data. When one transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results.

21. Serializability Not serial Serial

22. Serializability (cont.) • The objective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another • Ex: • two transaction that read data only • Two transaction that read or update separate data item

23. Serialiability (cont.) • 2 types of serializability: • Conflict serializability • Non-conflict serializable

24. Rules for constructing precedence (or serialization) graph • Create a node for each transaction Create a directed edge Ti->Tj if • Tj reads the value of an item written by Ti • Tj writes a value into an item after it has been read by Ti • Tj writes a value into an item after it has been written by Tj

25. Precedence graph Step 1 T9 T10

26. Precedence graph Rule 2 Create a directed edge Ti->Tj, if Tj reads the value of an item written by Ti T9 T10

27. Precedence graph Rule 2 Create a directed edge Ti->Tj, if Tj reads the value of an item written by Ti T9 T10

28. Precedence graph (cont.) Rule2 T7 T8

29. Precedence graph (cont.) Rule2 T7 T8

30. Lock • 2 types of Lock: • Shared lock (read only lock) • If a transaction has a shared lock on a data item, it can read the item but not update it. • Exclusive lock (read/update lock) • If a transaction has an exclusive lock on a data item, it can both read and update the item

31. Example of Lock

32. Deadlock • An impasse that may result when two (or more) transactions are each waiting for locks to be released that are held by the other

33. How to handle deadlock • 3 methods • Timeouts • Deadlock prevention (not very popular because it is too complicated) • Wait-die • Wound-wait • Deadlock detection (using wait-for graph)

34. Rules to construct wait-for graph • Create a node for each transaction • Create a directed edge Ti->Tj, if transaction Ti is waiting to lock an item that is currently locked by Tj

35. Wait-for graph (WFG) X Y lock T17 T18

36. Wait-for graph (WFG) X Y lock lock T17 T18

37. Wait-for graph (WFG) X Y lock lock T17 T18

38. Wait-for graph (WFG) X Y lock lock T17 T18

39. Recovery • Generally there are four different types of media that can be use for storage • Main memory • Magnetic disk • Magnetic tape • Optical disk

40. Types of media Optical disk Magnetic tape Magnetic disk Main memory Degree of reliability

41. Recovery (cont.) • Oracle provides various tools for system recovery • Recovery manager • Provide you with way to create back up of your data • Help you restore you data incase of failure • Instance recover • After a crash, Oracle use the information in the control file to recover the database to the consistent state before the crash

42. Recovery (cont.) • Standby database • Allow a standby database to be maintained in the event of the primary database failing

43. End!