Database Systems Transaction Management 1 Necessity Database systems

Database Systems: Transaction Management 1

Necessity • Database systems are normally being accessed by many users or processes at the same time. – Both queries and modifications. • Unlike Operating Systems, which support interaction of processes, a DMBS needs to keep processes away from troublesome interactions. 2

Example: Bad Interaction • You and your father ( Joint A/c) each take Rs. 10, 000 from different ATM’s at about the same time. – The DBMS should make sure one account deduction doesn’t get lost. Comparison: An OS allows two people to edit a document at the same time. If both write, one’s changes get lost. What if the connection to the bank is lost during the transaction? 3

Transaction Management Support Two main issues to deal with: Concurrent execution of multiple transactions Failures of various kinds, such as hardware failures and system crashes 4

Transactions: Basic concepts Transaction A logical unit of database processing. An action or series of actions, carried out by user or application, which accesses or changes contents of database. Transforms database from one consistent state to another, although consistency may be violated during transaction. Transaction Processing Systems with large databases and multiple concurrent users that are executing database transactions. Examples Banking systems, Airline reservations, Supermarket checkouts, . . . 5

Database Access Operations Performed on a data item read-item (X) write-item (X) A transaction can have multiple database access operations. Each transaction has clearly specified beginning and end statements. A single application program may contain many transactions. 6

read-item (X), write-item (X) Read_item(X) includes steps: 1. Find the address of the disk block that contains the item X 2. Copy the disk block into a buffer in main memory 3. Copy the item X from the buffer to a program variable ( for simplicity also called X) Write_item(X) includes steps: 1. Find the address of the disk block that contains the item X 2. Copy the disk block into a buffer in main memory 3. Copy the item X from a program variable into its correct location in the buffer 4. Store the updated block from the buffer back to disk 7

A Simple Transaction T 1: read-item (X); X: = X + M; write-item (X); read and write sets of a Transaction read-set of T 1 is {X} reads database item x into a program variable x write-set of T 1 is also {X} writes x program value of the variable x into the database item 8

Another example T 2 read_item(X); X: =X-N; write_item(X); read_item(Y); read_item(Z); Y: =Y+Z+N; write(Y); read_set of T 2 is {X, Y, Z}, write_set of T 2 is {X, Y} 9

Transaction Operations A transaction is either completed in its entirely or not done at all. Hence for recovery purpose, the recovery manager keeps track of the following transaction operations. begin-transaction read-item write-item end-transaction commit abort (or rollback) 10

Transaction States read-item, write-item begintransaction Active endtransaction abort Transition commit Partially Committed abort Failed Terminated STATE 11

Commit Point of a Transaction T Marks the successful completion of T and having recorded the effect of all the transaction operations on the database in the system log. [Commit, T] is recorded in the system log [start-transaction, T 1] …. . . [start-transaction, T 2] … [commit, T 1] 12

Rollback Point of a Transaction T Causes the transaction to end, but by aborting. No effects on the database. [Rollback, T] is recorded in the system log Failures like division by 0 can also cause rollback, even if the programmer does not request it [start-transaction, T 1] …. . . [start-transaction, T 2] … [commit, T 2] … [rollback, T 1] 13

Desirable properties: ACID Transactions should possess ACID properties should be enforced by concurrency control and recovery methods of the DBMS Atomicity – Either the whole process is done or none is. . Consistency Preservation – Database constraints are preserved. . Isolation – It appears to the user as if only one process executes at a time. Durability – Effects of a committed process do not get lost if the system crashes. 14

Why Concurrency Control is needed? Transaction processing systems are large databases with multiple users executing database transactions Multiple users are concurrently executing transactions A transaction can have several data access operations, some of which could be accessing the same data item 15

Contd… Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems. Transactions could be run serially, but this limits the degree of concurrency or parallelism in system. Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result. 16

!!! Concurrency Problems Lost update Dirty read Incorrect summary Unrepeatable Read 17

Lost Update Two transactions have their operations interleaved in such a way that it makes the value of some data items incorrect T 1 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); T 2 read-item (X); X: =X+M; write-item (X); Item X has an incorrect value because its update by T 1 is lost 18

Lost update T 1 r[x] w[x] r[y] y: =y-1 w[y] x=5 x: =6 x=6 r[x] x: =x-1 w[x] x=5 x: =4 x=4 Time x: =x+1 T 2 y=2 y: =1 y=1 The update of the item x by T 1 is lost 19

Dirty read (temporary update) A transaction updates a database item and then fails. The updated item is accessed by another transaction before it is restored back to its original value T 1 fails and must restore the value of X; meanwhile T 2 has read the temporary incorrect value of X read-item (X); X: =X-N; write-item (X); T 2 read-item (X); X: =X+M; write-item (X); read-item (Y); Abort; 20

Dirty read (temporary update) T 1 r[x] x: =x+1 w[x] T 2 x=5 x: =6 x=6 r[x] x: =x+2 w[x] x=6 x: =8 x=8 r[y] Abort T 1 The value of x written to the database is equal to: initial value of x + 3, while it should be x+2 21

Incorrect summary A transaction aggregating a number of records may read values of some records before, and some after the update by another transaction T 3 T 1 T 3 reads X after N is subtracted, and reads Y before N is added; a wrong summary is calculated sum: =0; read-item(A) sum: =sum+A; . . . read-item (X); sum: =sum+X; read-item (Y); sum: =sum+Y; read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); 22

Unrepeatable read A transaction reads the value of an item twice, and the value of the item is changed by another transaction in between the reads T 1 reads X again, however T 2 has changed the value of X after the first read-item (X); T 2 read-item (X); X: =X+M; write-item (X); read-item (X) X: =X-N; write-item (X); 23

Unrepeatable read T 1 r[x] x: =3*x w[x] r[y] r[x] y: =y+x/2 w[y] T 2 x=4 x: =12 x=12 y=3 x=14 y: =10 y=10 r[x] x=12 x: =x+2 x: =14 w[x] x=14 Value of x used in T 1 to compute the update of y is not the value of x expected to be used 24

Why Recovery is needed? Two types of storage: volatile (main memory) and nonvolatile. Volatile storage does not survive system crashes. Reasons for the need of recovery • • • Physical problems and catastrophes Disk failure System failure Transaction failure Local error or exception condition Concurrency control enforcement. The system must keep sufficient information to recover from the failure. DBMS should commit changes for successful transactions and reject changes of aborted transactions. 25

Desirable properties: ACID Transactions should possess ACID properties should be enforced by concurrency control and recovery methods of the DBMS Atomicity – Either the whole process is done or none is. . Consistency Preservation – Database constraints are preserved. . Isolation – It appears to the user as if only one process executes at a time. Durability – Effects of a committed process do not get lost if the system crashes. 26

Schedules A Schedule is the order of execution of operations from various transactions. A formal definition of a schedule is: A schedule S of n transactions T 1, T 2, …, Tn is an ordering of the operations of these transactions, given that Ti S, the order of operations of Ti in S is the same as in the original Ti. Schedules consider read-item, write-item, commit and abort operations only and the order of operations in S to be a total order. 27

Example Schedule Sa : r 1 (X); r 2 (X); w 1 (X); r 1 (Y); w 2 (X); w 1 (Y); Notation r read-item w write-item c commit a abort r 1 (X) T 1: read-item (X) a 2 T 2: abort T 1 read-item (X); X: =X-N; write-item (X); read-item (Y); T 2 read-item (X); X: =X+M; write-item (X); Y: =Y+N; write-item (Y); 28

Characterising Schedules Based on Recoverability For some types of schedules it is easy to recover but not for all. Characterise the type of schedules for which recovery is possible and relatively simple: Recoverable and nonrecoverable schedules Cascadeless or Avoid cascading rollback (ACR) schedules Strict schedules 29

Recoverable Schedules that can recover from transaction failures such that once a transaction is committed it should never be necessary to roll back. Non recoverable schedules should not be permitted for execution. Formally A schedule S is recoverable if no transaction T in S commits until all transactions T` that write an item that T reads, have committed. 30

Examples of Recoverable Schedules Sc: r 1(X); w 1(X); r 2(X); r 1(Y); w 2(X); c 2; w 1(Y); a 1; T 2 reads item X from. T 1 and then T 2 commits before T 1 commits. If T 1 aborts after c 2 then X that T 2 read is no longer valid and T 2 must be aborted after it has been committed. - Non-recoverable Schedule Sd: r 1(X); w 1(X); r 2(X); r 1(Y); w 2(X); w 1(Y); c 1; c 2; - Recoverable Schedule 31

More Examples Consider the following schedules for two transaction with interleaved execution. S 1: r 1[x], r 2[y], w 1[x], w 2[y], r 2[x], w 2[x], c 2, r 1[y], a 1 S 2: r 1[x], r 2[y], r 1[y], w 2[y], w 1[x], r 2[x], w 2[x], c 1, c 2 S 1 is not recoverable since T 2 reads item x written by the transaction T 1 that failed. S 2 is recoverable; i. e. T 2 reads items written by T 1 and does not commit before T 1. In a recoverable schedule, no committed transaction ever needs to be rolled back. 32

Cascading Rollback An uncommitted transaction has to be rolled back because it reads an item from a transaction which failed. It can be time consuming! T 8 T 3 T 5 T 9 T 7 T 4 T 6 T 2 Suppose that T 5 has to be aborted. All transactions ‘reachable’ from T 5 are aborted. T 1 33

Cascadeless or ACR Schedules Cascadeless schedules are recoverable schedules that avoid cascading rollbacks. A cascadeless schedule is guaranteed not to be rolled back. Formally A schedule S is cascadeless if every transaction reads only items that were written by committed transactions. 34

Examples of Cascadeless Schedules Recoverable Schedule with cascading rollback Se: r 1(X); w 1(X); r 2(X); r 1(Y); w 2(X); w 1(Y); a 1; a 2; T 2 has to be rolled back because it reads X from T 1 and T 1 then aborted, Cascadeless Schedule ( delaying T 2) Sf: r 1(X); w 1(X); a 1; r 2(X); w 2(X); c 2; 35

Strict Schedules Strict schedules are recoverable and cascadeless schedules that guarantee correct results. Strict schedules simplify the recovery process. Formally A schedule S is strict if transactions can neither read nor write an item X until the last transaction that wrote X has committed (or aborted). 36

Examples of Strict Schedules Recoverable and Cascadeless Schedule with potential incorrect results. Sg: r 1(X); w 2(X); a 1; - not a strict schedule because T 2 writes X before T 1 commits or aborts that last wrote X. Strict Schedule Sh: r 1(X); w 1(X); a 1; w 2(X); 37

Characterization of Schedules Avoidance of cascading rollback Recoverability Strictness 38

Characterising Schedules Based on Serializability Characterise the type of schedules that are considered correct when concurrent transactions are executing. Serial schedules Nonserial schedules Conflict-Serializable schedules 39

Serial Schedules that execute each transaction one by one without any interleaving. Formally A schedule S is serial if for every transaction T participating in S, all operations of T are executed consecutively; otherwise the schedule is called nonserial. There are n! serial schedules for n transactions A serial schedule is always correct, but unacceptable in practice ! 40

Examples of Serial Schedules (a) T 1 T 2 Serial Schedules (b) T 1 T 2 read-item (X); X: =X+M; write-item (X); read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); 41

Examples of Nonserial Schedules Non-serial Schedules (c) T 1 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); T 2 read-item (X); X: =X+M; write-item (X); Non-serial Schedules (d) T 1 read-item (X); X: =X-N; write-item (X); T 2 read-item (X); X: =X+M; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); 42

Results of Example Schedules Initial Values of database items X = 90, Y =90, N = 3, M = 2 Results: Schedule (a): Y = 93, X =89 Schedule (b): Y = 93, X =89 Schedule (c): Y = 93, X =92 Schedule (d): Y = 93, X =89 üWe are interested in schedule like the schedule (d). 43

Serializable Schedules A serializable schedule is a nonserial schedules that is equivalent to some serial schedule. it gives the correct result in spite of interleaving. Formally A schedule S of n transactions is serializable if it is equivalent to some serial schedule of the same n transactions When are two schedules ‘equivalent’? 44

Schedule equivalence Conflict Equivalence The order of any two conflicting operations is the same in both schedules. View Equivalence Each read operation of a transaction reads the result of the same write operation in both schedules. Result Equivalence The two schedules produce the same final state of the database. Other types of Equivalence 45

Conflict Equivalence and Conflicting Operations Schedules are called conflict equivalent if the order of any two conflicting operations is same in both schedules. Operations of a schedule are in Conflict if they satisfy all of the following conditions: • they belong to different transactions; • they access the same item X; and • at least one is a write-item (X). An example Sa : r 1 (X); r 2 (X); w 1 (X); r 1 (Y); w 2 (X); w 1 (Y); the Conflicting operations are: {r 1(X) and w 2(X)}, {r 2(X) and w 1(X)}, {w 2(X) and w 1(X)} but {r 1(X) and r 2(X)}, {w 1(X) and w 2(Y)} are not in conflict. 46

Example of Conflict Equivalence Consider two schedules: S 1: …. r 1(X); w 2(X); …. ; S 2: …. w 2(X); r 1(X); …. ; S 1 and S 2 are not conflict equivalent. r 1(X) and w 2(X) are conflicting operations of transactions T 1 and T 2. Value read by r 1(X) can be different in the two schedules. 47

Conflict Serializable A schedule S is conflict serializable if it is conflict equivalent to some serial schedule S`. Serial Schedule Conflict Serializable Schedule S`: … w 1(X); …; r 1(X); …; T 1 T 2 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); S: … w 1(X); r 1(X); …; T 1 read-item (X); X: =X-N; write-item (X); read-item (X); X: =X+M; write-item (X); T 2 read-item (X); X: =X+M; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); 48

Precedence Graph is used to test for serializability A directed graph G = (N, E), where N is a set of Nodes, N = {T 1, T 2, …, Tn} E is a set of directed edges, E = {e 1, e 2, …, em} Each transaction Ti in the schedule has one node Each edge ei is (Tj Tk) 1 j n, 1 k n The edge ei is created when an operation in Tj is followed by a conflicting operation in Tk The schedule S is serializable iff the graph has no cycles A path is called a cycle if it starts and ends in the same node and contains at least two nodes. If the precedence graph contains cycle, the schedule is not conflict serializable. 49

Precedence Graph for a Serial Schedule Sa: … w 1(X); …; r 2(X); …; T 1 T 2 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); T 2 T 1 X read-item (X); X: =X+M; write-item (X); Precedence Graph for Sa 50

Precedence Graph for a Serializable Schedule Conflict Serializable Schedule Sb: … w 1(X); r 2(X); …; T 1 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); T 2 T 1 read-item (X); X: =X+M; write-item (X); X Precedence Graph for Sb 51

Precedence Graph for a Non-Serializable Schedule Non Serializable Schedule Sc: . . r 2(X); . . w 1(X); . . w 2(X); . . ; T 1 read-item (X); X: =X-N; write-item (X); read-item (Y); Y: =Y+N; write-item (Y); X T 2 read-item (X); X: =X+M; write-item (X); T 2 T 1 X Precedence Graph for Sc 52

Precedence Graph: More complex example r 1[x], r 5[z], r 2[y], r 2[x], r 3[q], r 4[s], r 4[u], w 2[x], w 3[q], r 1[t], r 5[s], w 4[u], w 2[y], w 5[z], r 3[t], r 1[q], r 2[t], r 5[x], r 4[y], r 3[u], r 3[z], r 1[p], r 1[s], r 3[p], w 5[x], w 1[p], w 1[q] 2 1 3 4 5 53

Example…cont. r 1[x], w 1[x], r 5[z], r 2[y], r 2[x], r 3[q], r 4[s], r 4[u], w 2[x], w 3[q], r 1[t], r 5[s], w 4[u], w 2[y], w 5[z], r 3[t], r 1[q], r 2[t], r 5[x], r 4[y], r 3[u], r 3[z], r 1[p], r 1[s], r 3[p], w 5[x], w 1[p], w 1[q] 2 1 3 4 5 54

Example…cont. r 1[x], w 1[x], r 5[z], r 2[y], r 2[x], r 3[q], r 4[s], r 4[u], w 2[x], w 3[q], r 1[t], r 5[s], w 4[u], w 2[y], w 5[z], r 3[t], r 1[q], r 2[t], r 5[x], r 4[y], r 3[u], r 3[z], r 1[p], r 1[s], r 3[p], w 5[x], w 1[p], w 1[q] 2 1 3 4 5 55

Example…cont. r 1[x], w 1[x], r 5[z], r 2[y], r 2[x], r 3[q], r 4[s], r 4[u], w 2[x], w 3[q], r 1[t], r 5[s], w 4[u], w 2[y], w 5[z], r 3[t], r 1[q], r 2[t], r 5[x], r 4[y], r 3[u], r 3[z], r 1[p], w 1[p], r 1[s], r 3[p], w 5[x], w 1[p], w 1[q] 2 1 3 4 5 56

Example…cont. r 1[x], w 1[x], r 5[z], r 2[y], r 2[x], r 3[q], r 4[s], r 4[u], w 2[x], w 3[q], r 1[t], r 5[s], w 4[u], w 2[y], w 5[z], r 3[t], r 1[q], r 2[t], r 5[x], r 4[y], r 3[u], r 3[z], r 1[p], w 1[p], r 1[s], r 3[p], w 5[x], w 1[p], w 1[q] 2 1 3 4 5 Is it serializable? 57

Concurrency Control: What? Process of managing simultaneous operations on the database without having them interfere with one another. Prevents interference when two or more users are accessing database simultaneously and at least one is updating data. - obtaining serializable schedules. 58

Concurrency Control Protocols Testing for serializability after execution is meaningless. Practical solution is to provide methods for ensuring serializability without performing serializability testing. Commercially accepted protocols Locking and Timestamps Both are conservative approaches: delay transactions if they conflict with other transactions. Other protocols Optimistic These methods assume conflict is rare: allow transactions to proceed unsynchronised, and only check for conflicts at commit. 59

Locking is used to synchronize access by concurrent transactions on data items. Transaction uses locks to deny access to other transactions and so prevent incorrect updates. A lock is a variable for a data item, that describes the status of the item with respect to allowable operations. Example: Locking an item X for writing, prohibits other transactions from issuing a write-item (X). 60

Types of Locks Binary Locks Simple but too restrictive Read/Write Locks Used in commercial DBMSs 61

Binary Locks A binary lock can have 2 states: Locked (or 1) Unlocked (or 0) LOCK (X) has the current value of the binary lock on an item X. Item X is locked when LOCK (X) = 1 Item X is unlocked when LOCK (X) = 0 When LOCK(X) = 1, other database transactions cannot perform data access operations on X. 62

Binary Locking Operations lock-item (X) B: if LOCK (X) = 0 then LOCK (X) : = 1 else begin wait (until LOCK (X) = 0 and lock manager wakes the transaction); go to B; end; unlock-item (X) LOCK (X) : = 0; wakeup one of the waiting transactions if any; 63

Binary Locking Scheme 1. A transaction T must issue lock-item (X) before any read-item (X) or write-item (X). 2. A transaction must issue unlock-item (X) after completing all read-item (X) and write-item (X). 3. A transaction T will not issue a lock-item (X) if T already holds the lock on X. 4. A transaction T will not issue unlock-item (X) unless it holds the lock on X. 64

Implementing Binary Locks DBMS has a lock manager sub-system that keeps track of locks. Lock manager maintains: A record of all locked items; (Item-name, LOCK, Locking-transaction) A queue of waiting transactions. 65

Shared/Exclusive Locking. Operations Mutual exclusion enforced by binary locks is too restrictive. Several transactions should be allowed to access X for reading. A read/write lock can have 3 states: read-lock (shared-lock) cannot conflict, so more than one transaction can hold read locks simultaneously on the same item. write-lock (exclusive-lock) gives a transaction exclusive access to that item. unlock A record of locked items is maintained with the following fields: (item-name, LOCK, no-of-reads, lockingtransaction(s)). 66

Read/Write Locking Operations read-lock (X) B: if LOCK (X) : = ‘unlocked’ then begin LOCK (X) : = ‘read-locked’; no-of-reads : = 1; end else if LOCK (X) = ‘read-locked’ then no-of-reads : = no-of-reads (X)+1; else begin wait (until LOCK (X) = ‘unlocked’ and lock manager wakes the transaction; go to B; end; unlock-item (X) if LOCK (X) : = ‘write-lock (X) locked’ then B: if LOCK (X) = begin ‘unlocked’ LOCK (X) : = ‘unlocked’; then wakeup; LOCK (X) : = ‘writeend locked’ else if LOCK (X) = ‘readelse locked’ begin then wait (until LOCK (X) begin = ‘unlocked’ no-of-reads : = no-of-reads (X)-1; and lock manager wakes the if no-of-reads (X) = 0 transaction; then go to B; begin LOCK (X) = ‘unlocked’; end; wakeup; 67

Read/Write Locking Scheme 1. A transaction T must issue read-lock (X) or write-lock before any read-item (X). 2. A transaction T must issue write-lock (X) before any write-item (X). 3. A transaction must issue unlock-item (X) after completing all read-item (X) and write-item (X). 4. A transaction T will not issue a read-lock (X) if T already holds a read/write lock on X. 5. A transaction T will not issue write-lock (X) if T already holds a read/write lock on X. 6. A transaction T will not issue unlock (X) unless it already holds a read/write lock on X. 68

Shared/Exclusive Locking Scheme: Example T 1: r(y); r(x); x: =x+y; w(x). T 2: r(x); r(y); y: =y+x; w(y). T 1 T 2 read_lock[y] r[y] unlock[y] write_lock[x] r[x] x: =x + y w[x] unlock[x] read_lock[x] r[x] unlock[x] write_lock[y] r[y] y : = x + y w[y] unlock[y] 69

Shared/Exclusive Locking Scheme: Example T 1 T 2 read_lock[y] r[y] unlock[y] write_lock[x] r[x] x: =x + y w[x] unlock[x] read_lock[x] r[x] unlock[x] write_lock[y] r[y] y : = x + y w[y] unlock[y] Initial Values: X = 20, Y = 30 Two serial schedules using locks: A: T 1 followed by T 2: X = 50, Y = 80. B: T 2 followed by T 1: X = 70, Y = 50. 70

A nonserial schedule using locks Y unlocked too early T 1 read-lock (Y); read-item (Y); unlock (Y); write-lock (X); T 2 read-lock (X); read-item (X); unlock (X); write-lock (Y); read-item (Y); Y: =X+Y; write-item (Y); unlock (Y); X unlocked too early Final state: X = 50, Y = 50 A Nonserializable schedule Problem: Transactions release locks too soon, resulting in the loss of total 71 isolation and atomicity.

Guaranteeing Serializability: Two-Phase Locking Protocol Locking alone does not ensure serializability ! Positioning the locking and unlocking operations in interleaved transactions is the key task. To guarantee serializability, an additional protocol concerning the positioning of lock and unlock operations in every transaction is needed. Two-Phase Locking Protocol (2 PL) 72

Two-Phase Protocol A transaction follows the two-phase protocol if all locking operations precede the first unlocking operation. read-lock (X) write-lock (Y) Phase 1: Growing unlock (X) unlock (Y) Phase 2: Shrinking 73

Shared/Exclusive Locking Scheme: Example T 1 read_lock[y] r[y] write_lock[x] unlock[y] r[x] x: =x + y w[x] unlock[x] T 2 read_lock[x] r[x] write_lock[y] unlock[x] r[y] y : = x + y w[y] unlock[y] Two transactions obeying two-phase locking 74

The Lost update problem T 1 r[x] T 2 x=5 r[x] x: =6 x: =x-1 x: =4 w[x] x=6 w[x] r[y] y: =y-1 w[y] x=4 Time x: =x+1 x=5 y=2 y: =1 y=1 The update of the item x by T 1 is lost 75

Preventing Lost Update problem using 2 PL T 1 T 2 write_lock[x] r[x] x: =x+1 w[x] write-lock[y] unlock[x] write_lock[x] wait r[x] x: =x-1 w[x] unlock[x] r[y] y: =y-1 w[y] unlock[y] The update of the item x by T 1 is not lost 76

The Dirty read (temporary update) Problem T 1 r[x] x: =x+1 w[x] T 2 x=5 x: =6 x=6 r[x] x: =x+2 w[x] x=6 x: =8 x=8 r[y] Abort T 1 The value of x written to the database is equal to: initial value of x + 3, while it should be x+2 77

Preventing Temporary Update problem using 2 PL T 1 T 2 write_lock[x] r[x] x: =x+1 w[x] write_lock[x] wait Abort T 1 r[x] x: =x+2 w[x] unlock[x] The value of x written to the database is equal to: initial value of x plus 2 78

Variants of Two-Phase Protocol Basic Locking operations precede the first unlocking operation. Conservative Locking operations precede transaction execution. Strict Unlocking of write-locks after commit (or abort). Rigorous Unlocking of all locks after commit (or abort). 79

Limitations of two-phase Some serializable schedules may not be permitted. Locking in general, may cause Deadlocks and Starvation. 80

Deadlocks Each transaction in a set (of >=2 transactions) is waiting for an item which has locked another transaction in the set. Partial Schedule S T 1 read-lock (Y); read-item (Y); write-lock (X); T 2 read-lock (X); read-item (X); T 2 T 1 write-lock (Y); Wait-for Graph for S 81

Dead Lock Handling Only one way to break deadlock: abort one or more of the transactions. Deadlock should be transparent to user, so DBMS should restart the aborted transactions later. 82

Dead Lock Handling contd. . Two general techniques for handling deadlock: Deadlock prevention DBMS looks ahead to see if transaction would cause deadlock, and never allows deadlock to occur. Deadlock detection and recovery DBMS allows deadlock to occur but recognizes it and breaks it.

Deadlock Prevention Protocols 1. Conservative: Every transaction requires all locks before it starts. • • This is a serious limitation of the concurrency. If one lock is not available, the whole process waits. 84

contd. . 2. Another protocol Ordering all the items in the database. Lock the items according to that order if a transaction needs several items. Also limits the concurrency. This also requires that the programmer must be aware about the chosen order of items.

Deadlock Prevention: Using Timestamps Transaction Timestamps – unique identifier assigned to each transaction. Ti wants to lock an item which is currently locked by Tj Wait-die – if TS(Ti)<TS(Tj) (Ti is older than Tj), then Ti is allowed to wait; otherwise (Ti younger than Tj) abort Ti and restart it later with the same timestamp. only an older transaction can wait for younger one, otherwise transaction is aborted (dies) and restarted with same timestamp. 86

Deadlock Prevention: Using Timestamps Ti wants to lock an item which is currently locked by Tj Wound-wait – if TS(Ti)<TS(Tj) (Ti is older the Tj), then abort Tj (Ti wounds Tj) and restart it later with the same timestamp; otherwise (Ti younger than Tj) Ti is allowed to wait. only a younger transaction can wait for an older one. If older transaction requests lock held by younger one, younger one is aborted (wounded). 87

Deadlock Detection Wait-for graphs showing transaction dependencies Create a node for each transaction. Create an edge Ti -> Tj, if Ti is waiting to lock the item locked by Tj. Deadlock exists if and only if WFG contains cycle. WFG is created at regular intervals. Dynamic process - drop/create links. Perform system check based on given parameters. 88

Contd. . Abort deadlock causing transactions through a ‘victim selection’ algorithms. Problem – when to check for the deadlock? A practical solution - Timeouts Abort transactions waiting for a period longer than the system defined ‘time out’ period ( regardless of deadlock!).

Starvation A transaction waits indefinitely, while others continue normally. Usually the result of an unfair waiting scheme Prevention Schemes First come first served queue for locking requested items Dynamic priority increase for waiting transactions or repeated ‘victims’ 90