Chapter 16

1. Chapter 16 Concurrency

2. Topics in this Chapter • Three Concurrency Problems • Locking • Deadlock • Serializability • Isolation Levels • Intent Locking • Dropping ACID • SQL Facilities

3. Concurrency and Concurrency Control • Concurrent database access • Concurrently executing programs • Interactive SQL • Embedded SQL • ODBC, JDBC • One machine, multiple machines • Multiple users • Concurrent transactions

4. Concurrency and Concurrency Control Concurrently executed transactions may interfere with each other Even though each is itself correct, the result could be incorrect because of interleaved interaction

5. Three Concurrency Problems • Three concurrency problems can arise, that any DBMS must account for and avoid: • Lost Updates • Uncommitted Dependency • Inconsistent Analysis

6. Transaction A time Transaction B Retrieve t t1 t2 Retrieve t Update t t3 t4 Update t Fig. 16.1 Transaction A loses an update at time t4

7. Transaction A time Transaction B t1 Update t Retrieve t t2 t3 Rollback t4 Fig. 16.2 Transaction A becomes dependent on an uncommitted change at time t2

8. Transaction A time Transaction B t1 Update t Update t t2 t3 Rollback t4 Fig. 16.3 Transaction A updates an uncommitted change at time t2, loses that update at time t3

9. Acc 1 40 Acc 2 50 Acc 3 30 Transaction A time Transaction B Retrieve Acc 1 t1 sum = 40 Retrieve Acc 2 t2 sum = 90 t3 Retrieve Acc 3 t4 Update Acc 3 30 20 t5 Retrieve Acc 1 t6 Update Acc 1 40 50 t7 Commit Retrieve Acc 3 t8 sum = 110 (not 120) Fig. 16.4 Transaction A performs an inconsistent analysis

10. Three Concurrency Problems –Description • A lost update occurs when a second transaction reads the state of the database prior to the first one writing a change, and then stomps on the first one’s change with its own update • An uncommitted dependency occurs when a second transaction relies on a change which has not yet been committed, which is rolled back after the second transaction has begun • An inconsistent analysis occurs when totals are calculated during interleaved updates

11. Conflicts • A RW (read write) conflict occurs when writes are interspersed with reads (inconsistent analysis) • A WR conflict, or dirty read, occurs when the reader is relying on uncommitted writes • A WW conflict, or dirty write, occurs when the second writer never accounted for the first one (lost update)

12. Locking • The usual solution: locking • A transaction locks (“acquires a lock”) a portion of the database to prevent concurrency problems • Exclusive lock – write lock, will lock out all other transactions (lock type “X”) • Shared lock – read lock, will lock out writes, but allow other reads (lock type “S”)

13. Locking No provision for this in SQL--it is the responsibility of the DBMS to recognize when a lock is required, which type of lock is required, and whether it can be obtained A retrieval requires an S lock An update requires an X lock Once a lock is obtained, it is held until the transaction completes (commit or rollback)

14. if transaction A has lock of type: X S - X N N Y S N Y Y - Y Y Y (no lock) and transaction B requests lock of type: bottom row just for symmetry Fig. 16.5 Compatability matrix for lock types X and S

15. Locking • A second transaction, if locked out by the first, goes into a wait state • First come, first served will make sure the second one is next when the first releases the lock, to avoid livelock, a/k/a starvation • Once the first transaction issues a COMMIT or ROLLBACK, the second one obtains its lock

16. Strict Two-Phase Locking • So how does this apply to our “three problems” • Lost update • Uncommitted dependency • Inconsistent analysis

17. Transaction A time Transaction B Retrieve t t1 (acquire S lock on t) t2 Retrieve t (acquire S lock on t) Update t t3 (request X lock on t) wait t4 Update t wait (request X lock on t) wait wait wait wait wait wait wait wait Fig. 16.6 No update is lost, but deadlock occurs at time t4

18. Deadlock • Strict two-phase locking may result in deadlock if two transactions each take a shared lock before one of them tries to take an exclusive lock • Or if the second one tries to take an exclusive lock where the first already has a shared lock, and the first in turn is waiting for additional shared locks • More about this later

19. Transaction A time Transaction B t1 Update t (acquire X lock on t) Retrieve t t2 (request S lock on t) wait wait t3 Commit/Rollback wait (release X lock on t) resume: Retrieve t t4 (acquire S lock on t) Fig. 16.7 Transaction A is prevented from seeing an uncommitted change at time t2

20. Transaction A time Transaction B t1 Update t (acquire X lock on t) Update t t2 (request X lock on t) wait wait t3 Commit/Rollback wait (release X lock on t) resume: Update t t4 (acquire X lock on t) Fig. 16.8 Transaction A is prevented from updating an uncommitted change at time t2

21. Acc 1 40 Acc 2 50 Acc 3 30 Transaction A time Transaction B Retrieve Acc 1 t1 (acquire S lock on Acc 1) sum = 40 Retrieve Acc 2 t2 (acquire S lock on Acc 2) sum = 90 t3 Retrieve Acc 3 (acquire S lock on Acc 3) t4 Update Acc 3 (acquire X lock on Acc 1) 30 20 t5 Retrieve Acc 1 (acquire S lock on Acc 1) t6 Update Acc 1 (request X lock on Acc 1) wait Retrieve Acc 3 t7 wait (request S lock on Acc 3) wait wait wait wait wait wait wait Fig. 16.9 Inconsistent analysis prevented, but deadlock occurs at time t7

22. Transaction A time Transaction B Lock r1 Exclusive t1 t2 Lock r2 Exclusive Lock r2 Exclusive t3 wait wait t4 Lock r1 Exclusive wait wait wait wait wait wait wait wait Fig. 16.10 An example of deadlock

23. Deadlock Solutions • The DBMS may detect a deadlock (difficult), or assume deadlock (detect a lack of progress--a transaction has been idle for some time), and roll back a “victim” • The locking protocol may be modified to avoid deadlock by using Wait-Die or Wound-Wait

24. A waiting for a lock held by B Transaction A Transaction B B waiting for a lock held by A “wait for” graph

25. A waiting for a lock held by B Transaction B B waiting for a lock held by C Transaction A Transaction C C waiting for a lock held by A “wait for” graph--must form a cycle for deadlock to occur

26. A waiting for a lock held by B Transaction B B waiting for a lock held by C Transaction A Transaction C if no cycle, then when C finishes, B gets the lock, when B finishes, A gets the lock “wait for” graph--must form a cycle for deadlock to occur

27. Deadlock Avoidance • Each transaction is timestamped with its start time • When transaction A requests a lock on a tuple already locked by transaction B then: • Wait-Die: A waits if it is older than B; otherwise it dies (does rollback and restart) • Wound-Wait: A waits if it is younger than B; otherwise it wounds B (B is rolled back and restarted) • No cycles, so no deadlocks, but too many rollbacks

28. Two Phase Locking Protocol • A transaction must acquire a lock on an object before operating on it. • After releasing a lock, the transaction must not acquire any new ones

29. Serializability • A transaction is assumed to be “correct” (maintains consistency) • A transaction is considered to be independent (doesn’t depend on any other transaction) • Thus, running a set of transactions, one at a time, serially, in any order is also assumed correct

30. Serializability • An interleaved execution of the set of transactions is correct if it is equivalent to some serial execution • It is then said to be “serializable” • A “schedule” is an order of execution of a set of transactions • May be a serial schedule • May be an interleaved schedule • Two schedules are equivalent if they produce the same result

31. Serializability • An interleaved execution is considered correct if and only if it is serializable • A set of transactions is serializable if and only if it is guaranteed to produce the same result as when each transaction is completed prior to the following one being started • If all transactions obey two phase locking, then all possible interleaved schedules are serializable

32. Recovery Redux • Interleaved transactions can suffer from uncommitted dependency during recovery • If a first transaction attempts to roll back after a second one commits, the schedule could be unrecoverable • The danger of unlimited rollback of interleaved transactions is that it could cascade indefinitely • The second transaction must wait until the first commits before terminating

33. Isolation Levels • In the ideal world of isolation, a transaction completes before another begins • In the real world, isolation has levels • The higher the level of isolation, the less interference, and the greater concurrency • Isolation level defines the “level of violation” of serializability that will be tolerated • Defines acceptance of • “dirty reads” • “non-repeatable reads” • “phantoms”

34. Isolation Levels • In the ideal world of isolation, a transaction completes before another begins • In the real world, isolation has levels • The higher the level of isolation, the less interference, and the greater concurrency • Cursor stability permits shared locks for reading, that are released before the transaction completes • Repeatable read ensures that a read is repeatable throughout the transaction

35. Transaction A time Transaction B t1 Update t Retrieve t t2 t3 Rollback t4 has a row that “never existed” “dirty read”

36. Transaction A time Transaction B Retrieve t t1 t2 Update t Retrieve t t3 t4 retrieves the same row a second time and it’s different “non-repeatable read”

37. Transaction A time Transaction B Retrieve all rows t1 t2 Insert a row Retrieve all rows t3 (again) t4 surprise! new row has appeared “phantom”

38. SQL Facilities • No explicit locking provided • Transactions expected to be protected according to level specified in START TRANSACTION

39. SQL Facilities • READ UNCOMMITTED: permits dirty reads, nonrepeatable reads, phantoms • In READ ONLY access mode only • READ COMMITTED: permits nonrepeatable reads and phantoms, but prohibits dirty reads • REPEATABLE READ: permits phantoms, but not dirty reads, nor nonrepeatable reads • SERIALIZABLE: true two phase locking, it keeps all transactions serializable

40. Phantoms • Phantoms can’t be prevented by locking at the tuple (or row) level (it is caused by the insertion of a new one) • Prevention requires locking at the relvar (or table) level • Locking granularity refers to the level at which locking takes place

41. Intent Locking • Locking granularity: locks can be taken at the tuple level, or by relvar, database, or attribute • To avoid examining every tuple to determine if any are locked, the intent locking protocol declares a lock at the relvar to forecast conflicts • Modes are intent shared, intent exclusive and shared intent exclusive

42. Intent Locking –Shared and Exclusive • Before a given transaction can acquire a shared lock on a given tuple, it must first acquire an intent shared or stronger lock on the relvar containing the tuple • Before a given transaction can acquire an exclusive lock on a given tuple, it must first acquire an intent exclusive lock on the relvar containing the tuple • For shared intent exclusive, a transaction can tolerate other readers, but not other updaters, and reserves the right to take a lock to update