This assignment verifies that you understand how shared and exclusive locks work. Suppose that Session 1, Session 2, and Session 3 are sessions connected concurrently to the same database instance. Suppose further that X, Y, and Z are data objects that can be locked with a shared and exclusive lock.

Before you address each of the scenarios below, let us take a look at two examples.

Example 1: If Session 1 has a shared lock on X, and Session 2 has a shared lock on Y, what would happen if Session 3 attempts to procure a shared lock on X? The answer is that Session 3 would be granted the shared lock, because by the nature of a shared lock, it is acceptable for both Session 1 and Session 3 to have a shared lock on the same data item at the same time.

Example 2: If Session 1 has an exclusive lock on X, and Session 2 has an exclusive lock on Y, what would happen if Session 3 attempts to procure a shared lock on X? The answer is that Session 3 would need to wait until Session 1 releases the lock on X, because by the nature of an exclusive lock, only one session at a time may have the lock. Therefore Session 3 cannot be granted the lock at the same time Session 1 has the lock, and Session 3 will need to wait until Session 1 releases the lock.

Now that we have taken a look at a couple of examples, it is your turn to describe what will happen in each scenario below. For each scenario, indicate, for each session, whether it procures the requested lock, waits, or deadlocks. Make sure to explain your reasoning.

Scenario 3: Session 1 has an exclusive lock on X and attempts to procure an exclusive lock on Y. Session 2 has an exclusive lock on Y and attempts to procure an exclusive lock on Z. Session 3 has an exclusive lock on Z and attempts to procure an exclusive lock on X.