Transaction Management

1. Transaction Management Dale-Marie Wilson, Ph.D.

2. Transaction Support Transaction Action, or series of actions, carried out by user or application, which reads or updates contents of database. • Logical unit of work • Application program - series of transactions with non-database processing in between • Transforms database from one consistent state to another • Consistency may be violated during transaction

3. Transaction Example

4. Transaction Support • Two possible outcomes: • Success - transaction commits and database reaches new consistent state • Failure - transaction aborts, and database restored to consistent state before transaction started • Referred to as rolled back or undone transaction • Committed transaction cannot be aborted • Aborted transaction that is rolled back can be restarted later

5. State Transition Diagram for Transaction

6. Properties of Transaction • Basic (ACID) properties: Atomicity ‘All or nothing’ property – transaction is an indivisible unit performed in entirety or not at all Consistency Must transform database from one consistent state to another Isolation Partial effects of incomplete transactions should not be visible to other transactions Durability Effects of a committed transaction are permanent and must not be lost because of later failure

7. DBMS Transaction Subsystem

8. Concurrency Control Process of managing simultaneous operations on the database without having them interfere with one another • Prevents interference when two or more users access database simultaneously and at least one updates data • Interleaving of operations may produce incorrect results

9. Need for Concurrency Control • Potential concurrency problems: • Lost update problem • Uncommitted dependency problem • Inconsistent analysis problem

10. Lost Update Problem • Successfully completed update overridden by another user • Example: • T1 withdrawing £10 from an account with balx, initially £100 • T2 depositing £100 into same account • Serially, final balance would be £190

11. Lost Update Problem • Loss of T2’s update avoided by preventing T1 from reading balx until after update

12. Uncommitted Dependency Problem • Occurs when one transaction can see intermediate results of another transaction before it has committed • Example: • T4 updates balx to £200 but it aborts, so balx should be back at original value of £100 • T3 has read new value of balx (£200) and uses value as basis of £10 reduction, giving a new balance of £190, instead of £90

13. Uncommitted Dependency Problem • Problem avoided by preventing T3 from reading balx until after T4 commits or aborts

14. Inconsistent Analysis Problem • Occurs when transaction reads several values but 2nd transaction updates some of them during execution of first • Aka dirty read or unrepeatable read • Example: • T6 is totaling balances of account x (£100), account y (£50), and account z (£25). • Meantime, T5 has transferred £10 from balx to balz, so T6 now has wrong result (£10 too high).

15. Inconsistent Analysis Problem • Problem avoided by preventing T6 from reading balx and balz until after T5 completed updates

16. Serializability • Objective of concurrency control protocol • To schedule transactions to avoid interference • Transactions could be executed serially • Limits degree of concurrency or parallelism in system • Serializability identifies those executions of transactions guaranteed to ensure consistency

17. Serializability Schedule Sequence of reads/writes by set of concurrent transactions Serial Schedule Schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions • No guarantee that results of all serial executions of a given set of transactions will be identical

18. Nonserial schedule • Schedule where operations from set of concurrent transactions are interleaved • Objective of serializability • To find nonserial schedules that allow transactions to execute concurrently without interfering with one another • Objective - to find nonserial schedules that are equivalent to some serial schedule • Called serializable

19. Serializability • In serializability, ordering of read/writes is important: • If two transactions only read data item • No conflict and order not important • If two transactions either read or write completely separate data items • No conflict and order not important (c) If one transaction writes a data item and another reads or writes same data item • Order of execution important

20. Conflict Serializability Example

21. Serializability • Conflict serializable schedule orders conflicting operations to achieve serial execution • Under constrained write rule (transaction updates data item based on its old value, which is first read) • Precedence graph used to test for serializability

22. Precedence Graph • Create: • node for each transaction • directed edge Ti Tj, if Tj reads value of item written by TI • directed edge Ti Tj, if Tj writes value into item after read by Ti • If precedence graph contains cycle • Not conflict serializable

23. Non-conflict Serializable Schedule Example • T9 is transferring £100 from one account with balance balx to another account with balance baly • T10 is increasing balance of these two accounts by 10% • Precedence graph has a cycle and so is not serializable

24. Non-conflict Serializable Schedule Example

25. Recoverability • Serializability identifies schedules that maintain database consistency • Assumes no failed transactions • If transaction fails • Atomicity requires effects of transaction to be undone • Durability states that once transaction commits, its changes cannot be undone (without running another, compensating, transaction)

26. Recoverable Schedule A schedule where, for each pair of transactions Ti and Tj, if Tj reads a data item previously written by Ti, then the commit operation of Ti precedes the commit operation of Tj

27. Concurrency Control Techniques • Conservative approaches • Delay transactions in case they conflict with other transactions • Optimistic methods • Assume conflict rare and only check for conflicts at commit • Two conservative concurrency control techniques: • Locking • Timestamping

28. Locking Transaction uses locks to deny access to other transactions and so prevent incorrect updates • Most widely used approach to ensure serializability • Transaction claims shared (read) or exclusive (write) lock on data item before read or write • Lock prevents another transaction from modifying item or even reading it, in the case of a write lock

29. Basic Locking Rules • If transaction has shared lock on item • Can read; not update item • If transaction has exclusive lock on item • Can read and update item • More than one transaction can hold shared locks simultaneously on same item • Reads cannot conflict • Exclusive lock • Gives transaction exclusive access to that item • Some systems allow transaction to: • Upgrade read lock to exclusive lock • Downgrade exclusive lock to shared lock

30. Incorrect Locking Schedule • For two transactions above, a valid schedule using these rules is: S = {write_lock(T9, balx), read(T9, balx), write(T9, balx), unlock(T9, balx), write_lock(T10, balx), read(T10, balx), write(T10, balx), unlock(T10, balx), write_lock(T10, baly), read(T10, baly), write(T10, baly), unlock(T10, baly), commit(T10), write_lock(T9, baly), read(T9, baly), write(T9, baly), unlock(T9, baly), commit(T9) }

31. Incorrect Locking Schedule • If at start, balx = 100, baly = 400, result should be: • balx = 220, baly = 330, if T9 executes before T10, or • balx = 210, baly = 340, if T10 executes before T9. • However, result gives balx = 220 and baly = 340 • S is not a serializable schedule • Problem: • Transactions release locks too soon => results in loss of total isolation and atomicity • To guarantee serializability • Additional protocol concerning positioning of lock and unlock operations in every transaction needed

32. Two-Phase Locking (2PL) Transaction follows 2PL protocol if all locking operations precede first unlock operation in transaction • Two phases for transaction: • Growing phase - acquires all locks but cannot release any locks • Shrinking phase - releases locks but cannot acquire any new locks

33. Preventing Lost Update Problem using 2PL

34. Preventing Uncommitted Dependency Problem using 2PL

35. Preventing Inconsistent Analysis Problem using 2PL

36. Cascading Rollback • If every transaction in schedule follows 2PL, • Schedule is serializable • Problems occur with interpretation of when locks can be released

37. Cascading Rollback

38. Cascading Rollback • Transactions conform to 2PL • T14 aborts • Since T15 dependent on T14, T15 must also be rolled back • Since T16 dependent on T15, T16 must be rolled back • To prevent this with 2PL • Leave release of all locks until end of transaction

39. Concurrency Control with Index Structures • Each page of index treated as data item and 2PL applied • Problem: • Indexes frequently accessed => leads to high lock contention • Index traversal: • Search path starts from root and moves down to leaf nodes • Search never moves back up tree • Once lower-level node accessed => higher-level nodes in path will not be used again • Insertion of new index value (key and pointer): • If node not full, insertion will not cause changes to higher-level nodes

40. Concurrency Control with Index Structures • Exclusively lock leaf node • If insertion to non full node • Exclusively lock higher-level nodes • If node full and must split • Locking strategy: • For searches, obtain shared locks on nodes starting at root and proceeding downwards along required path. Release lock on node once lock has been obtained on the child node • For insertions, obtain exclusive locks on all nodes as descend tree to leaf node to be modified (conservative approach) • Obtain shared locks on all nodes as descend to leaf node to be modified, where obtain exclusive lock. If leaf node has to split, upgrade shared lock on parent to exclusive lock. If this node also has to split, continue to upgrade locks at next higher level (optimistic approach)

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

42. Deadlock • To break deadlock: abort one or more of transactions • Deadlock transparent to user • DBMS restarts transaction(s) • Three techniques for handling deadlock: • Timeouts • Deadlock prevention • Deadlock detection and recovery

43. Timeouts • Transaction that requests lock will only wait for system-defined period of time • If lock not been granted within period • Lock request times out • DBMS assumes transaction deadlocked => aborts and automatically restarts the transaction

44. Deadlock Prevention • DBMS looks ahead to see if transaction would cause deadlock and never allows deadlock to occur • Can order transactions using transaction timestamps: • Wait-Die - only older transaction can wait for younger one, otherwise transaction aborted (dies) and restarted with same timestamp • Wound-Wait - only younger transaction can wait for older one. If older transaction requests lock held by younger one, younger one aborted (wounded)

45. Deadlock Detection and Recovery • DBMS allows deadlock to occur; recognizes it and breaks it • Handled by construction of wait-for graph (WFG) showing transaction dependencies: • Create a node for each transaction • Create edge Ti -> Tj, if Ti waiting to lock item locked by Tj • Deadlock exists iff WFG contains cycle • WFG created at regular intervals

46. Recovery from Deadlock Detection • Several issues: • Choice of deadlock victim • How far to roll transaction back • Avoiding starvation – same transaction always deadlock victim

47. Timestamping • Transactions ordered globally: • Older transactions, transactions with smaller timestamps, get priority in event of conflict • Conflict resolved by rolling back and restarting transaction • No locks => no deadlock

48. Timestamping Timestamp A unique identifier created by DBMS that indicates relative starting time of a transaction • Generated: • Using system clock at start of transaction • Incrementing logical counter every time new transaction starts

49. Timestamping • Read/write proceeds only if last update on that data item carried out by older transaction • Else, transaction requesting read/write restarted and given new timestamp • Timestamps for data items: • read-timestamp - timestamp of last transaction to read item • write-timestamp - timestamp of last transaction to write item

50. Timestamping - Read(x) • Consider transaction T with timestamp ts(T): ts(T) < write_timestamp(x) • x already updated by younger (later) transaction • Any other values of T may be inconsistent • Transaction must be aborted and restarted with new timestamp