ConcurrencyControl

Question 1

What are the two types of locks in database systems?

Answer 1

Shared (S) lock for reading and Exclusive (X) lock for both reading and writing

Question 2

What is a Shared (S) lock?

Answer 2

A lock that allows a transaction to read a data item but prohibits any updates

Question 3

What is an Exclusive (X) lock?

Answer 3

A lock that allows a transaction to both read and write a data item preventing access by other transactions in either mode

Question 4

What are the two phases in Two-Phase Locking (2PL)?

Answer 4

1) Growing Phase: acquire locks no releases 2) Shrinking Phase: release locks no acquisitions

Question 5

What is Strict 2PL?

Answer 5

A variant of 2PL where transactions hold all exclusive locks until they commit or abort preventing others from reading uncommitted changes

Question 6

What is Rigorous 2PL?

Answer 6

A variant of 2PL where transactions hold all locks (both shared and exclusive) until commit or abort

Question 7

What causes a deadlock in database systems?

Answer 7

When a set of transactions form a cycle of dependencies each waiting for a resource held by another

Question 8

What are the two main approaches to handle deadlocks?

Answer 8

1) Deadlock Detection using wait-for graphs 2) Deadlock Prevention using timestamp-based ordering

Question 9

What information does a lock table contain?

Answer 9

Data Item Transaction ID Lock Mode and Queue of Requests

Question 10

What is the Tree Protocol in graph-based protocols?

Answer 10

A protocol where data items are organized hierarchically first lock can be on any item subsequent locks must follow hierarchy and items can be unlocked anytime but not relocked

Question 11

What information does Timestamp-Based Protocol maintain for each data item?

Answer 11

R-timestamp (largest timestamp of successful read) and W-timestamp (largest timestamp of successful write)

Question 12

When is a read operation allowed in Timestamp-Ordering Protocol?

Answer 12

Only if the transaction’s timestamp is greater than or equal to the write timestamp of the data item

Question 13

When is a write operation allowed in Timestamp-Ordering Protocol?

Answer 13

Only if the transaction’s timestamp is greater than both the read and write timestamps of the data item

Question 14

What is MVCC (Multiversion Concurrency Control)?

Answer 14

A scheme that maintains multiple versions of each data item to improve concurrency and reduce conflicts

Question 15

What are the main advantages of MVCC?

Answer 15

1) High concurrency as reads never block writes 2) Reduced likelihood of deadlocks

Question 16

What are the main disadvantages of MVCC?

Answer 16

1) Increased storage and memory requirements 2) Complexity in conflict resolution

Question 17

How does the lock table handle lock requests?

Answer 17

Checks compatibility with existing locks grants if compatible adds to queue if not compatible removes all requests if transaction aborts

Question 18

What happens when a lock is released in the lock table?

Answer 18

System checks the queue to determine if pending requests can now be granted ensuring fairness by granting locks in order

Question 19

What is the main limitation of the Tree Protocol?

Answer 19

It can increase overhead by requiring transactions to lock additional data items that they do not access directly

Question 20

What is the key feature of snapshot reads in MVCC?

Answer 20

Transactions access the version of the data item that matches their timestamp ensuring reads are non-blocking