Week7 - Concurrency Control, Need for Concurrency / Analyze

The questions can come in any order, so make sure you are selecting right option for all questions.

1. There are two transactions A and B accessing a department database simultaneously. Transaction A is updating the database to give all employees in the department a 3% raise in their salary while transaction B is computing the total salary bill of the department. The two transactions interfere since the total salary bill changes as the transaction A updates the employee records. The total salary retrieved by transaction B may be a sum of some salaries before the raise and others after the raise. This could not be considered an acceptable value of the total salary but the value before the raise or the value after the raise is acceptable. The problem illustrated in the figure is called the inconsistent retrieval anomaly. Label the missing transactions to complete the process.

Transaction 1

(c) Read employee 100

Transaction 2

(b) Sum = 0.0

Transaction 3

(a) Update salary

Transaction 4

(c) Read employee 100

Transaction 5

(d) Write employee 100

Transaction 6

(e) Sum = sum + salary

Transaction 7

(a) Read employee 101

Transaction 8

(d) Update salary

Transaction 9

(b) Write employee 101

Transaction 10

(a) Read employee 101

Transaction 11

(e) Sum = sum + salary

2. Consider an example of two transactions A and B running on the system as shown. Transaction A has read the value of Q that was updated by transaction B but was never committed. The result of transaction A writing Q therefore will lead to an inconsistent state of the database. Also if transaction A does not write Q but only reads it, it would be using a value of Q which never really existed. Yet another situation would occur if the rollback happened after Q was written by transaction A. The rollback would restore the old value of Q and therefore lead to the loss of updated Q by transaction A. This is called the uncommitted dependency anomaly. Represent the above described process in terms of transaction table having two transactions A and B.

Transaction 1

(a) Read Q

Transaction 2

(b) Update Q

Transaction 3

(c) Write Q

Transaction 4

(a) Read Q

Transaction 5

(d) Read R

Transaction 6

(b) Update Q

Transaction 7

(c) Wrire Q

Transaction 8

(e) Faliure (rollback)

3. The given transaction represents lost update anomaly, where every row represents one time instance and one action of one of the transactions. We move down in time as we go down in the figure with time = 1 being the earliest time and time = 6 being the latest. No two actions of the two transactions are carried out at the same time. Also, we assume that these two transactions execute in isolation from all other transactions that might be running at the same time. Label the missing transaction details.

Transaction 1

(a) Read N

Transaction 2

(a) Read N

Transaction 3

(c) N := N – 5

Transaction 4

(d) N := N – 4

Transaction 5

(a) Read N

Transaction 6

(a) Read N