TRANSACTION & CONCURRENCY CONTROL

1. TRANSACTION & CONCURRENCY CONTROL

2. CONTENT • Transactions & Nested transactions • Methods for concurrency control • Locks • Optimistic concurrency control • Timestamp ordering • Distributed Transactions • Distributed Deadlock

3. TRANSACTION & NESTED TRANSACTION • Transaction: specified by a client as a set of operations on objects to be performed as an indivisible unit by the servers managed those objects. • Goal of transaction: ensure all the objects managed by a server remain in a consistent state when accessed by multiple transactions and in the presence of server crashes.

4. TRANSACTION & NESTED TRANSACTION • Transaction applies to recoverable objects and intended to be atomic (atomic transaction): • Recoverable objects: objects can be recovered after their server crashes. • Atomic operations: operations that are free from interference from concurrent operations being performed in the other threads.

5. Distributed Transaction • A distributed transaction accesses resource managers distributed across a network • When resource managers are DBMSs we refer to the system as a distributed database system DBMS at Site 1 Application Program DBMS at Site 2

6. Transactions (ACID) • Atomic: All or nothing. No intermediate states are visible. • Consistent: system invariants preserved. • Isolated: Two transactions do not interfere with each other. They appear as serial executions. • Durable: The commit causes a permanent change. 6 6

7. TRANSACTION & NESTED TRANSACTION • Maximize concurrency: transactions are allowed to execute concurrently if they would have the same effect as a serial execution  serially equivalent. • Cases of transaction failing: • Service actions related to process crashes. • Client actions related to server process crashes.

8. Concurrency Control Issues • Lost Update Problems • Inconsistent Retrievals Problems • Serial Equivalence • Conflicting Operations

9. Transaction T : Transaction U : balance = b.getBalance(); balance = b.getBalance(); b.setBalance(balance*1.1); b.setBalance(balance*1.1); a.withdraw(balance/10) c.withdraw(balance/10) balance = b.getBalance(); $200 balance = b.getBalance(); $200 b.setBalance(balance*1.1); $220 b.setBalance(balance*1.1); $220 a.withdraw(balance/10) $80 c.withdraw(balance/10) $280 Lost Update Problem • Accounts A, B, C has initial balance of $100, 200 & 300. • Transaction T transfers amounts from A to B. Transaction U transfers amounts from C to B. in both cases, amount transfer is calculated to increase balance by 10%. • The net effect should be $242. but U’s update is lost, T overwrite it without seeing it. Both reads the old values.

10. The inconsistent retrievals problem : Transaction V : Transaction W a.withdraw(100) aBranch.branchTotal() b.deposit(100) a.withdraw(100); $100 total = a.getBalance() $100 total = total+b.getBalance() $300 total = total+c.getBalance() b.deposit(100) $300 • -Transaction V transfer a sum from account A to B & W invokes the branch total method to obtain sum of balances to all accounts. • Balance of both A & B has initially $200. the result of branch total includes sum of A & B as $300 is wrong. • W’s retrieval is inconsistent because V has performed only withdrawal part of a transfer at the time the sum is calculated.

11. Transaction T : Transaction U : balance = b.getBalance() balance = b.getBalance() b.setBalance(balance*1.1) b.setBalance(balance*1.1) a.withdraw(balance/10) c.withdraw(balance/10) balance = b.getBalance() $200 b.setBalance(balance*1.1) $220 balance = b.getBalance() $220 b.setBalance(balance*1.1) $242 a.withdraw(balance/10) $80 c.withdraw(balance/10) $278 A serially equivalent interleaving of T and U

12. Serial Equivalence Interleaving • An interleaving of the operations of transaction in which the combined effect is the same as if the transactions had been performed one at a time in some order is a serially equivalent interleaving. • Use of serial equivalence as a criterion for a correct concurrent execution prevents the occurrence of lost update problem and inconsistent retrieval problem.

13. Transaction V : Transaction W : a.withdraw(100); aBranch.branchTotal() b.deposit(100) $100 a.withdraw(100); $300 b.deposit(100) $100 total = a.getBalance() $400 total = total+b.getBalance() total = total+c.getBalance() ... A serially equivalent interleaving of V and W

14. Operations of different Conflict Reason transactions read read No Because the effect of a pair of read operations does not depend on the order in which they are executed read read write Yes Because the effect of a and a write operation depends on the order of their execution write write Yes Because the effect of a pair of write operations depends on the order of their execution Read and write operation conflict rules For two transaction to be serially equivalent, it is necessary and sufficient that all pairs of conflicting operations of the two transactions be executed in the same order at all of the objects they both access.

15. TRANSACTION & NESTED TRANSACTION • Recoverability from aborts • Problems with aborting: • Dirty read • Premature writes • Solutions: • Recoverability of transactions • Avoid cascading aborts • Strict execution of transactions

16. A dirty read when transaction T aborts • Recoverability from Aborts: Server must records the effects of all committed transactions and none of the effects of aborted transactions. Transactions may abort by preventing it affecting other concurrent transaction if it does so. • Dirty read: the isolation property of transactions require that transactions do not see the uncommitted state of other transactions. • The dirty read problem is caused by the interaction between a read operation in one transaction and an earlier write operation in another transaction on the same object.

17. A dirty read when transaction T aborts • Now suppose that the transaction T aborts after U has committed. Then the transaction U will have seen a value that never existed, since A will be restored to its original value. Hence transaction U has performed a dirty read. As it has committed and cant be undone.

18. A dirty read when transaction T aborts • Recoverability of transactions: any transaction like U, that is in danger of having a dirty read delays its commit operation until after the commitment of any other transaction whose uncommitted state has been observed. Eg: U delays its commit until after T commits. • Cascading aborts: the aborting of any transactions may cause further transactions to be aborted  transactions are only allowed to read objects that were written by committed transactions. • Any read operations must be delayed until other transaction that applied a written operation to the same object have committed or aborted.

19. Premature Writes === > leading dirty read Some DBs implement the action of abort by restoring “before image” of all the writes of a transaction. The two executions are serially equivalent if the transaction U aborts and T commit. The balance should be $105 A=$100 is the before image of T’s write. $105 is the before image of U’s write. If U commits and T-Aborts, the before image is $100. we get wrong balance of $100 Solution: Write operations must be delayed until earlier transaction that updated the same objects have either committed or aborted

20. T: top-level transaction T1 = openSubTransaction T2 = openSubTransaction commit T1 T2 openSubTransaction openSubTransaction openSubTransaction prov.commit abort T11 T12 T21 openSubTransaction prov.commit prov.commit prov.commit T211 Nested transaction

21. NESTED TRANSACTION • The outermost transaction in a set of nested transactions is called the top-level transaction. • Transaction other than the top-level transaction are called sub-transaction. • Any sub-transaction appears atomic to its parent with respect to failures. • Sub-transaction at the same level can run concurrently but their access to common objects is serialized.

22. NESTED TRANSACTION • Each sub-transaction can fail independently of its parent and of the other sub-transaction. • When a sub-transaction aborts, the parent transaction can sometimes choose an alternative sub-transaction to complete its task. • If all the tasks is done on same level, then it is called flat transaction.

23. TRANSACTION & NESTED TRANSACTION • Advantages of nested transaction: • Sub-transaction at one level (and descendent) may run concurrently with other sub-transaction: Additional concurrency in a transaction. If sub-transactions run in different servers, they can work parallel. • Subtransactions can commit or abort independently

24. NESTED TRANSACTION • Rules for commitment of nested transactions: • Transactions commit/abort only after its child have completed. • After completing, subtransaction makes independent decision either to commit provisionally or to abort. • When a parent aborts, all of its subtransactions are aborted. • When a subtransaction aborts, parent can decide whether to abort or not. • Top-level transaction commits  all of the provisionally committed subtransactions can commit too (provided none of their ancestor has aborted).

25. Schemes for Concurrency control • Locking • Server attempts to gain an exclusive ‘lock’ that is about to be used by one of its operations in a transaction. • Can use different lock types (read/write for example) • Two-phase locking • Optimistic concurrency control • Time-stamp based concurrency control Transactions

26. METHOD FOR CONCURRENT CONTROL • Lock: • Server attempts to lock any object that is about to use by client’s transaction. • Requests to lock objects are suspended and wait until the objects are unlocked. • Serial equivalence: transaction is not allowed any new locks after it has release a lock. • Two-phase lock: growing phase (new locks are acquired), shrinking phase (locks are released). • Strict execution: locks are held until transaction commits/aborts (Strict two-phase locking). • Recoverability: locks must be held until all the objects it updated have been written to permanent storage.

27. METHOD FOR CONCURRENT CONTROL • Lock: • Simple exclusive lock reduces concurrency locking scheme for multiple transaction reading an object, single transaction writing an object. • Two types of locks used: read locks (shared lock) & write locks. • Operation conflict rules: • Request for a write lock is delayed by the presence of a read lock belonging to another transaction. • Request for either a read/write lock is delayed by the presence of a write lock belonging to another transaction.

28. Transaction T and U with exclusive locks. • Assumption: balance of ABC are not yet locked when transaction T and U starts. • When T starts using B, then it is locked for B. subsequently when U starts using B, it is still locked for T and so U waits. When T committed, B is unlocked and C resumes : effective serialization

29. Two-Phase Locking (1) In two-phase locking, a transaction is not allowed to acquire any new locks after it has released a lock Transactions

30. Strict Two-Phase Locking (2) • Strict two-phase locking. Transactions

31. Use of locks in strict two-phase locking • 1. When an operation accesses an object within a transaction: • (a) If the object is not already locked, it is locked and the operation proceeds. • (b) If the object has a conflicting lock set by another transaction, the transaction must wait until it is unlocked. • (c) If the object has a non-conflicting lock set by another transaction, the lock is shared and the operation proceeds. • (d) If the object has already been locked in the same transaction, the lock will be promoted if necessary and the operation proceeds. (Where promotion is prevented by a conflicting lock, rule (b) is used.) • 2. When a transaction is committed or aborted, the server unlocks all objects it locked for the transaction. Transactions

32. Lock compatibility

33. METHOD FOR CONCURRENT CONTROL • Locking rule for nested transactions: • Locks that are acquired by a successful subtransaction is inherited by its parent & ancestors when it completes. Locks held until top-level transaction commits/aborts. • Parent transactions are not allowed to run concurrently with their child transactions. • Subtransactions at the same level are allowed to run concurrently.

34. Method for concurrent control • Deadlock: • Definition: A state in which each member of a group of transactions is waiting for some other member to release a lock. • Prevention: • Lock all the objects used by a transaction when it starts  not a good way. • Request locks on objects in a predefined order  premature locking & reduction in concurrency.

35. METHOD FOR CONCURRENT CONTROL • Deadlock: • Detection: Finding cycle in a wait-for graph select a transaction for aborting to break the cycle. • Choice of transaction to be aborted is not simple. • Timeouts: each lock is given a limited period in which it is invulnerable. • Transaction is sometimes aborted but actually there is no deadlock. • If we use locking to implement concurrency control in transactions, we can get deadlocks (even within a single server) • So we need to discuss: • Deadlock detection within a single system • Distributed deadlock

36. Deadlock detection • A deadlock occurs when there is a cycle in the wait-for graph of transactions for locks • There may be more than one • Resolve the deadlock by aborting one of the transactions …. • E.g. the youngest, or the one involved in more than one cycle, or can even use “priority” …. COMP28112 Lecture 12

37. Held by Waits for A T U U T B Waits for Held by A cycle in a wait-for graph

38. METHOD FOR CONCURRENCY CONTROL • Drawbacks of locking: • Lock maintenance represents an overhead that is not present in systems that do not support concurrent access to shared data. • Deadlock. Deadlock prevention reduces concurrency. Deadlock detection or timeout not wholly satisfactory for use in interactive programs. • To avoid cascading aborts, locks cant be released until the end of the transaction. This may reduce significantly the potential for concurrency.

39. METHOD FOR CONCURRENT CONTROL • Optimistic concurrency control: • Is an alternative optimistic approach to the serialization of transactions that avoids the drawbacks of locking. • Idea: in most applications, the likelihood of two clients transactions accessing the same object is low. • Transactions are allowed to proceed as though there were no possibility of conflict with other transactions until the client completes its task and issues a close-Transaction request.

40. METHOD FOR CONCURRENT CONTROL • Optimistic concurrency control: • Each transaction has the following 3 phases: • Working phase: each transaction has a tentative version of each of the objects that it updates. • Validation phase: Once transaction is done, the transaction is validated to establish whether or not its operations on objects conflict with operations of other transactions on the same object. If not conflict, can commit; else some form of conflict resolution is needed and the transaction may abort. • Update phase: changes in tentative versions are made permanent if transaction is validated

41. METHOD FOR CONCURRENT CONTROL • Optimistic concurrency control: • Validation of transactions: use the read-write conflict rules to ensure that the scheduling of a transaction is serially equivalent with respect to all other overlapping transactions. • Backward validation: check the transaction undergoing validation with other preceding overlapping transactions (enter the validation phase before). • Read set of the transaction being validated is compared with the write sets of other transactions that have already committed. • Forward validate: check the transaction undergoing validation with other later transactions • Write set of the transaction being validated is compared with the read sets of other overlapping active transactions (still in working phase).

42. Working Validation Update T 1 Earlier committed transactions T 2 T 3 Transaction T being validated v active 1 Later active active 2 transactions Validation of transactions Transactions

43. Schemes for Concurrency control • Time-stamp based concurrency control • Each transaction is assigned a unique timestamp at the moment it starts • In distributed transactions, Lamport’s timestamps can be used • Every data item has a timestamp • Read timestamp = timestamp of transaction that last read the item • Write timestamp = timestamp of transaction that most recently changed an item Transactions

44. METHOD FOR CONCURRENT CONTROL • Timestamp ordering: • Basic timestamp ordering rule: • A transaction’s request to write an object is valid only if that object was last read and written by earlier transactions. A transaction’s request to read an object is valid only if that object was last written by an earlier transactions • Timestamp ordering write rule: if (Tc ≥ maximum read timestamp on D && Tc > write timestamp on committed version of D) perform write operation on tentative version of D with write timestamp Tc else /*write is too late*/ abort transaction Tc

45. METHOD FOR CONCURRENT CONTROL • Timestamp ordering read rule: If (Tc> write timestamp on committed version of D) { let Dselected be the version of D with the maximum write timestamp ≤ Tc if (Dselected is committed) perform read operation on the version Dselected else wait until the transaction that made version Dselected commits or aborts then reapply the read rule } Else abort transaction Tc

46. Distributed Transactions • Motivation • Provide distributed atomic operations at multiple servers that maintain shared data for clients • Provide recoverability from server crashes • Properties • Atomicity, Consistency, Isolation, Durability (ACID) • Concepts: commit, abort, distributed commit Transactions

47. Distributed Transactions (a) Flat transaction (b) Nested transactions M X T 11 X T Client N 1 T T Y 12 T T T 21 T 2 Client Y P Z T 22 Transactions

48. X Client a.withdraw(10) A T 1 T Y T = openTransaction openSubTransaction T B b.withdraw(20) 2 a.withdraw(10); openSubTransaction b.withdraw(20); Z openSubTransaction . T c deposit(10) C c.deposit(10); 3 openSubTransaction d.deposit(20) D d.deposit(20); T 4 closeTransaction Nested banking transaction Transactions

49. Concurrency Control for Distributed Transactions General organization of managers for handling distributed transactions. Transactions

50. join openTransaction participant closeTransaction A a.withdraw(4); . . join BranchX T participant b.withdraw(T, 3); Client B b.withdraw(3); T = openTransaction BranchY join a.withdraw(4); c.deposit(4); participant b.withdraw(3); c.deposit(4); d.deposit(3); C closeTransaction D d.deposit(3); Note: the coordinator is in one of the servers, e.g. BranchX BranchZ A distributed banking transaction Transactions