TRANSACTIONS AND CONCURRENCY CONTROL

Solutions

1. What are the potential problems when a DBMS executes multiple transactions concurrently?

(a) Phantom problem (b) The dirty read problem

(c) Unrepeatable read problem (d) All of the above

Solution: Option (d)

The above all and lost update problem are all effects of executing multiple transactions
concurrently.

2. Which of the following schemas are used for ensuring atomicity?

(a) log with deferred modification (b) log with immediate modification
(c) shadow paging (d) All of the above

Solution: Option(d)
Atomic instructions are those instructions in transaction that denote that either the
effect of the whole transaction will be there or no effect will be there.

3. Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on
R, it will:

(a) result in deadlocked situation (b) immediately be granted

(c) immediately be rejected (d) be grateful as soon as it released by A

Solution: Option (b)

2 different transactions can have shared lock on the same data item, without any
deadlock a problem of inconsistency in DBMS.

4. Consider the following schedules involving 2 transactions which one of the following
statements are true?

S1: r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X)

S2: r1(X); r2(X); r2(Y); w2(Y); r1(Y); w1(X)

(a) Both S1 and S2 are conflicted serializable

(b) S1 is conflict serializable and S2 is conflict serializable

1
 (c) S1 is not conflict serializable and S2 is conflict serializable
(d) Both S1 and S2 are not conflict serializable

Solution: Option (c)

Consult the videos for checking whether schedules are conflict serializable.

5. Consider the 2 transactions T1 and T2 and four schedules S1, S2, S3 and S4 of T1 and T2 are
given below:

T1: R1[X] W1[X] W1[Y]

T2: R2[X] R2[Y] W2[Y]
S1: R1[X] R2[X] R2[Y] W1[X] W1[Y] W2[Y]
S2: R1[X] R2[X] R2[Y] W1[X] W2[Y] W1[Y]
S3: R1[X] W1[X] R2[X] W1[Y] R2[Y] W2[Y]
S4: R2[X] R2[Y] R1[X] W1[X] W1[Y] W2[Y]

Which of the following schedules are conflict serializable?

(a) S1 and S2 (b) S2 and S3

(c) S3 only (d) S4 only

Solution: Option (c)

In all other schedules, one transaction overwrites the value of if written by another.

6. Which of the following scenarios may lead to an irrecoverable error in database system?

(a) A transaction writes a data item after it is read by an uncommitted transaction

(b) A transaction read a data item after it is read by an uncommitted transaction
(c) A transaction read a data item after it is written by a committed transaction
(d) A transaction read a data item after it is written by an uncommitted transaction

Solution: Option (d)

(a) read and write (allowed)
(b) read after read (allowed)
(c) read after committed write (allowed)
(d) read after uncommitted write (not allowed)

7. Which of the following concurrency control protocol ensures both conflict serializability
and freedom from deadlock?

2
 (i) 2 phase locking
(ii) time stamp ordering

(a) (i) only (b) (i) and (ii)

(c) (ii) only (d) None of these

Solution: Option (c)

Refer the videos.

8. Consider the following transaction schedule:

T3 T4 T7
R(Q)
W(Q)
W(Q) R(Q)
W(Q)

The labeled precedence graph will be:

3
 Solution: Option (b)
Refer the videos, for finding precedence graph.

9. In case of time stamp ordering R-timestamp (Q) denotes:

(a) the largest timestamp of any transaction that execute read(Q) successfully
(b) the average timestamp of any transaction that execute read(Q) successfully
(c) the average time stamp of any transaction that execute read(Q) unsuccessfully
(d) the smallest timestamp of any transaction that execute read(Q) successfully

Solution: Option (a)

Refer videos.

10. Locking was introduced into database so that

(a) keys can be provided to maintain security

(b) all simultaneous transactions are prevented
(c) passwords can be provided
(d) consistency can be enforced

Solution: Option (d)

A lock prevents data from being corrupted or invalidated when multiple users try to
read while others write to database.

11. Which of the following is true for 2-phase locking?

(a) lock acquisition is the second phase

(b) locks can be acquired at any time
(c) locks are acquired in the first phase
(d) None of the above

Solution: Option (c)

Refer the lectures. Locks are acquired in growing phase (1st phase) and released in

4
 shrinking phase (2nd phase).

12. Choose the False statement:

(a) time stamp protocol is deadlock free

(b) 2- phase locking generates serializability
(c) strict 2-phase locking is deadlock free
(d) time stamp protocol may not result recoverable schedule

Solution: Option (c)

Strict 2-PL prevents cascading rollback but does not prevent deadlock.

13. Consider the following schedule S of the transactions:

T1, T2, T3 and T4.

T2
T1 T3 T4
Read(X)
Write(X)
Commit
Write(X)
Commit
Write(Y)
Read(Z)
Commit
Read(X)
Read(Y)
Commit
Read (X)
Read (Y)
Commit

Which of the following statements are correct?

(a) S is conflict-serializable but not recoverable

(b) S is not conflict-serializable and recoverable
(c) S is both conflict-serializable and recoverable
(d) S is neither conflict-serializable nor is it recoverable

Solution: Option (c)

Refer the videos to check for conflict serializability and recoverable schedule.

5
14. Consider the following transactions with data items P and Q initialized to ‘0’

T1: read (P);

Read (Q);
if P=0, then Q= Q+1
write (Q);

T2: read (Q)

Read (P)
if Q=0, then P= P+1;
write (P);

Any non-serial interleaving of T1 and T2 for concurrent execution leads to:

(a) a serializable schedule

(b) a schedule that is not conflict serializable
(c) a conflict serializable schedule
(d) a schedule for which precedence graph cannot be drawn.

Solution: Option (b)

The statements can be transformed is:

So, it is not conflict serializable.

15. Consider the following schedules for transaction T1, T2, T3:
S1: R1(X); R2(Y); R3(Y); W2(Y); W1(X); W3(X); R2(X); W2(X)

Which one of the above schedules below is the correct serialization of the above?

(a) T1 T3 T2 (b) T2 T1 T3

(c) T2 T3  T1 (d) T3 T1  T2

Solution: Option (a)

Refer the videos for finding out precedence graph.

6
16. Consider the following log sequence of 2 transactions on a bank account, with initial balance
12,000; that transfer 2000 to a mortgage payment, then apply 5% interest.

1. T1 start
2. T1 Bold= 1200, new= 10,000
3. T1 Mold=0, new= 2000
4. T1 commit
5. T1 start
6. T2 Bold= 10,000, new= 10,500
7. T2 commit

Suppose the database system crashes just before log record is written. When the system is
started which one statement is true for recovery procedure?

(a) we must redo log record 6 to set B to 10,500

(b) we must redo log record 6 to set B to 10,000 and redo log records 2 and 3
(c) we need not redo log records 2 and 3 because transaction T1 has committed
(d) we can apply redo and undo operations arbitrarily because they are idempotent

Solution: Option (c)

As it is given that the transaction is already committed, the changes are already
made. Now, since the changes are made, they are permanent so we do need to
restore them as the failure has no effect on the transaction. We need not redo log
records 2 and 3 because T1 has committed.

17. When a deadlock occurs the performance of the system degrades i.e. throughput decreases.
In order to increase the through, which of the following may help to the programmer?

(i) Lock the smallest size objects possible

(ii) Reduce the time that transaction hold locks
(iii)Reduce hotspots
(iv)Buy a faster system

(a) (i), (ii) and (iii) (b) (ii), (iii) and (iv)
(c) (i), (ii) and (iv) (d) (i), (ii), (iii) and (iv)

Solution: Option (d)

All the steps will help the programmer to increase throughput of database system.
Note:- A hotspot is a database object that is frequently accessed and caused a lot of
blocking delays. Hot spots can significantly affect performance.

7
18. There are 2 transactions T1 and T2. T1 has 5 transactions and T2 has 3 transactions. Find the
number of concurrent schedules for the given 2 transactions.

(a) 15 (b) 25
(c) 44 (d) 56

Solution: Option (d)

(n1 +n2 )!
No. of concurrent schedule are: n1 !n2 !
whereas, n1= 5, n2= 3

19. 2PL generates serializability, but it does not prevent deadlocks. 2PL has 2 phases: growing
and shrinking. Which of the following rules are used to govern the 2PL protocol?

(a) 2 transactions cannot have conflicting locks

(b) No unlock operation can precede a lock operation in the same transaction
(c) No data are affected until all locks are obtained i.e., until the transaction is in its locked
point.
(d) All of these

Solution: Option (d)

The above are the properties if 2PL protocol.

20. Consider below lock compatible table over locks:

X, Y, Z

Which of the following are correct statements?

(i) It is possible for different transactions to hold locks on the same data item in x and z node at
the same time.
(ii) It is possible for more than one transaction to hold lock on same database element in 3
8
 mode, at the same time another transaction holds a lock on the same element in mode y.
(iii)It is possible for different transactions to hold locks on the same element in all 3 lock
modes at the same time.

(a) (i) and (ii)

(b) (i) and (iii)
(c) (ii) and (iii)
(d) (i), (ii) and (iii)

Solution: Option (b)

By studying the matrix, it can be seen those cells of form (a, b) are compatible else
they are not. By studying only (i) and (iii) can be found correct.