TRANSACTION CONCURRENCY CONTROL 1 CONTENT Transactions Nested transactions

TRANSACTION & CONCURRENCY CONTROL 1

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

TRANSACTION & NESTED TRANSACTION 3 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.

TRANSACTION & NESTED TRANSACTION Transaction applies to recoverable objects and intended to be atomic (atomic transaction): 4 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.

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 Application Program DBMS at Site 1 DBMS at Site 2 5

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

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: 7 Service actions related to process crashes. Client actions related to server process crashes.

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

Lost Update Problem Transaction. T : Transaction. U: balance = b. get. Balance(); b. set. Balance(balance*1. 1); a. withdraw(balance/10) balance = b. get. Balance(); b. set. Balance(balance*1. 1); c. withdraw(balance/10) balance = b. get. Balance(); $200 b. set. Balance(balance*1. 1); $220 a. withdraw(balance/10) $80 c. withdraw(balance/10) $280 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. 9

The inconsistent retrievals problem Transaction V: Transaction. W: a. withdraw(100) b. deposit(100) a. Branch. branch. Total() a. withdraw(100); $100 total = a. get. Balance() $100 total = total+b. get. Balance() $300 total = total+c. get. Balance() 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.

A serially equivalent interleaving of T and U Transaction T: Transaction U: balance = b. get. Balance() b. set. Balance(balance*1. 1) a. withdraw(balance/10) balance = b. get. Balance() b. set. Balance(balance*1. 1) c. withdraw(balance/10) balance = b. get. Balance()$200 b. set. Balance(balance*1. 1)$220 balance = b. get. Balance()$220 b. set. Balance(balance*1. 1)$242 a. withdraw(balance/10) $80 c. withdraw(balance/10) $278

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

A serially equivalent interleaving of V and W Transaction. V: Transaction. W: a. withdraw(100); b. deposit(100) a. Branch. branch. Total() a. withdraw(100); $100 b. deposit(100) $300 total = a. get. Balance() $100 total = total+b. get. Balance() $400 total = total+c. get. Balance(). . . 13

Read and write operation conflict rules Operations of different Conflict transactions read write Reason Because the effect of a pair of read operations does not depend on the order in which they are executed Yes Because the effect of a read and a write operation depends on the order of their execution Yes Because the effect of a pair of write operations depends on the order of their execution No 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. 14

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

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

A dirty read when transaction T aborts TRANSACTION T TRANSACTION U a. get. Balance( ); a. set. Balance(balance+10); a. get. Balance( ); a. set. Balance(balance+20); balance = a. get. Balance( ); a. set. Balance(balance+10); $100 $110 balance = a. get. Balance( ); a. set. Balance(balance+20); commit transaction; $110 $130 abort transaction; -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. 17

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

Premature Writes === > leading dirty read Transaction T Transaction U $100 a. set. Balance(105) $105 a. set. Balance(110) $110 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 19 updated the same objects have either committed or aborted

Nested transaction T: top-level transaction T 1 = open. Sub. Transaction T 2 = open. Sub. Transaction commit T 1 T 2 open. Sub. Transaction prov. commit T 11 T 12 abort T 21 open. Sub. Transaction prov. commit T 211 20

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

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

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 subtransactions run in different servers, they can work parallel. Subtransactions can commit or abort independently 23

NESTED TRANSACTION Rules for commitment of nested transactions: 24 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

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

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

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

Transaction T and U with exclusive locks. TRANSACTION T TRANSACTION U balance = b. get. Balance( ); b. set. Balance(balance*1. 1); a. withdraw(balance/10); balance = b. get. Balance( ); b. set. Balance(balance*1. 1); c. withdraw(balance/10); open. Transaction balance = b. get. Balance( ); lock B b. set. Balance(balance*1. 1); a. withdraw(balance/10); lock A open. Transaction balance = b. get. Balance( ); close. Transaction unlock A, B wait for T’lock on B … b. set. Balance(balance*1. 1); c. withdraw(balance/10) ; lock B lock C close. Transaction unlock B, C • 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 28

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 29

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

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 31

Lock compatibility For one object Lock already set Read none read write OK OK Wait Lock requested Write OK wait 32

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

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

METHOD FOR CONCURRENT CONTROL Deadlock: Detection: Finding cycle in a wait-for graph select a transaction for aborting to break the cycle. Timeouts: each lock is given a limited period in which it is invulnerable. Choice of transaction to be aborted is not simple. 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: 35 Deadlock detection within a single system Distributed deadlock

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” …. 36 COMP 28112 Lecture 12 24 -May-21

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

METHOD FOR CONCURRENCY CONTROL Drawbacks of locking: 38 Lock maintenance represents an overhead that is not present in systems that do not support concurrent access to shared data. 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.

METHOD FOR CONCURRENT CONTROL Optimistic concurrency control: 39 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.

METHOD FOR CONCURRENT CONTROL Optimistic concurrency control: Each transaction has the following 3 phases: 40 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

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). Forward validate: check the transaction undergoing validation with other later transactions 41 Read set of the transaction being validated is compared with the write sets of other transactions that have already committed. Write set of the transaction being validated is compared with the read sets of other overlapping active transactions (still in working phase).

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

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

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*/ 44 abort transaction Tc

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 rule wait until the transaction that made version Dselected commits or aborts then reapply the read } Else 45 abort transaction Tc

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 46 commit Transactions

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

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

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

A distributed banking transaction join open. Transaction close. Transaction. participant A a. withdraw(4); join Branch. X T Client T = open. Transaction a. withdraw(4); c. deposit(4); b. withdraw(3); d. deposit(3); close. Transaction participant b. withdraw(T, 3); B Branch. Y join participant Note: the coordinator is in one of the servers, e. g. Branch. X 50 b. withdraw(3); Transactions C c. deposit(4); D d. deposit(3); Branch. Z

Concurrency Control for Distributed Transactions Locking Distributed deadlocks possible Timestamp 51 ordering Lamport time stamps for efficiency it is required that timestamps issued by coordinators be roughly synchronized Transactions

Distributed Deadlock Within a single server, allocating and releasing locks can be done so as to maintain a wait-for graph which can be periodically checked. With distributed transactions locks are held in different servers – and the loop in the entire waitfor graph will not be apparent to any one server One solution is to have a coordinator to which each server forwards its wait-for graph But centralised coordination is not ideal in a distributed system 52 COMP 28112 Lecture 12 24 -May-21

Distributed deadlock (a) (b) W Held by D C A X Z Waits for W Waits for V Held by V U Waits for B Held by Y 53 Transactions U

Local and global wait-for graphs local wait-for graph T U X 54 local wait-for graph V global deadlock detector T T U Y Transactions V

Atomic Commit Protocols The atomicity of a transaction requires that when a distributed transaction comes to an end, either all of its operations are carried out or none of them Two phase commit (2 PC) Covered In CH-9 Three Phase Commit (3 PC) Recovery…. 55…………………. Repeated……………. Transactions

The two-phase commit protocol - 1 Phase 1 (voting phase): 1. The coordinator sends a can. Commit? (VOTE_REQUEST) request to each of the participants in the transaction. 2. When a participant receives a can. Commit? request it replies with its vote Yes (VOTE_COMMIT) or No (VOTE_ABORT) to the coordinator. Before voting Yes, it prepares to commit by saving objects in permanent storage. If the vote is No the participant aborts immediately. 56 Transactions

The two-phase commit protocol - 2 Phase 2 (completion according to outcome of vote): 3. The coordinator collects the votes (including its own). (a) If there are no failures and all the votes are Yes the coordinator decides to commit the transaction and sends a do. Commit (GLOBAL_COMMIT) request to each of the participants. (b)Otherwise the coordinator decides to abort the transaction and sends do. Abort (GLOBAL_ABORT) requests to all participants that voted Yes. 4. Participants that voted Yes are waiting for a do. Commit or do. Abort request from the coordinator. When a participant receives one of these messages it acts accordingly and in the case of commit, makes a have. Committed call as confirmation to the coordinator. 57 Transactions

Communication in two-phase commit protocol Coordinator Participant step status 1 3 prepared to commit (waiting for votes) committed can. Commit? Yes 2 prepared to commit (uncertain) 4 committed do. Commit have. Committed done 58 Transactions

Operations for two-phase commit protocol can. Commit? (trans)-> Yes / No Call from coordinator to participant to ask whether it can commit a transaction. Participant replies with its vote. do. Commit(trans) Call from coordinator to participant to tell participant to commit its part of a transaction. do. Abort(trans) Call from coordinator to participant to tell participant to abort its part of a transaction. have. Committed(trans, participant) Call from participant to coordinator to confirm that it has committed the transaction. get. Decision(trans) -> Yes / No Call from participant to coordinator to ask for the decision on a transaction after it has voted Yes but has still had no reply after some delay. Used to recover from server crash or delayed messages. 59 Transactions

Two-Phase Commit protocol - 3 actions by coordinator: while START _2 PC to local log; multicast VOTE_REQUEST to all participants; while not all votes have been collected { wait for any incoming vote; if timeout { write GLOBAL_ABORT to local log; multicast GLOBAL_ABORT to all participants; exit; } record vote; } if all participants sent VOTE_COMMIT and coordinator votes COMMIT{ write GLOBAL_COMMIT to local log; multicast GLOBAL_COMMIT to all participants; } else { write GLOBAL_ABORT to local log; multicast GLOBAL_ABORT to all participants; } Outline of the steps taken by the coordinator in a two phase commit protocol 60 Transactions

Two-Phase Commit protocol - 4 actions by participant: Steps taken by participant process in 2 PC. 61 write INIT to local log; wait for VOTE_REQUEST from coordinator; if timeout { write VOTE_ABORT to local log; exit; } if participant votes COMMIT { write VOTE_COMMIT to local log; send VOTE_COMMIT to coordinator; wait for DECISION from coordinator; if timeout { multicast DECISION_REQUEST to other participants; wait until DECISION is received; /* remain blocked */ write DECISION to local log; } if DECISION == GLOBAL_COMMIT write GLOBAL_COMMIT to local log; else if DECISION == GLOBAL_ABORT write GLOBAL_ABORT to local log; } else { write VOTE_ABORT to local log; send VOTE ABORT to coordinator; } Transactions

Two-Phase Commit protocol - 5 a) b) The finite state machine for the coordinator in 2 PC. The finite state machine for a participant. If a failure occurs during a ‘blocking’ state (red boxes), there needs to be a recovery mechanism. 62 Transactions

Two Phase Commit Protocol - 6 Recovery ‘Wait’ in Coordinator – use a time-out mechanism to detect participant crashes. Send GLOBAL_ABORT ‘Init’ in Participant – Can also use a time-out and send VOTE_ABORT ‘Ready’ in Participant P – abort is not an option (since already voted to COMMIT and so coordinator might eventually send GLOBAL_COMMIT). Can contact another participant Q and choose an action based on its state. State of Q Action by P COMMIT Transition to COMMIT ABORT Transition to ABORT INIT Both P and Q transition to ABORT (Q sends VOTE_ABORT) READY Contact more participants. If all participants are ‘READY’, must wait for coordinator to recover Transactions 63

Two-Phase Commit protocol - 7 actions for handling decision requests: /* executed by separate thread */ while true { wait until any incoming DECISION_REQUEST is received; /* remain blocked */ read most recently recorded STATE from the local log; if STATE == GLOBAL_COMMIT send GLOBAL_COMMIT to requesting participant; else if STATE == INIT or STATE == GLOBAL_ABORT send GLOBAL_ABORT to requesting participant; else skip; /* participant remains blocked */ Steps taken for handling incoming decision requests. 64 Transactions

Three Phase Commit protocol - 1 Problem with 2 PC If coordinator crashes, participants cannot reach a decision, stay blocked until coordinator recovers Three Phase Commit 3 PC There is no single state from which it is possible to make a transition directly to either COMMIT or ABORT states There is no state in which it is not possible to make a final decision, and from which a transition 65 to COMMIT can be made Transactions

Three-Phase Commit protocol - 2 a) b) 66 Finite state machine for the coordinator in 3 PC Finite state machine for a participant Transactions

Three Phase Commit Protocol - 3 ‘Wait’ in Coordinator – same ‘Init’ in Participant – same ‘Pre. Commit’ in Coordinator – Some participant has crashed but we know it wanted to commit. GLOBAL_COMMIT the application knowing that once the participant recovers, it will commit. ‘Ready’ or ‘Pre. Commit’ in Participant P – (i. e. P has voted to COMMIT) State of Q Action by P PRECOMMIT Transition to PRECOMMIT. If all participants in PRECOMMIT, can COMMIT the transaction ABORT Transition to ABORT INIT Both P (in READY) and Q transition to ABORT (Q sends VOTE_ABORT) READY Contact more participants. If can contact a majority and they are in ‘Ready’, then ABORT the transaction. If the participants contacted in ‘Pre. Commit’ it is safe to COMMIT the transaction Note: if any participant is in state PRECOMMIT, it is impossible for any other participant to be in any state other than READY or PRECOMMIT. Transactions 67