What is Starvation During Granting of Locks

When a transaction requests a lock on a data item in a particular mode, and no other transaction has a lock on the same data item in a conflicting mode, the lock can be granted. However care must be taken to avoid the following scenario.

Suppose a transaction T2 has a shared-mode lock on a data item, and another transaction T1 requests an exclusive mode lock on the data item. Clearly, T1 has to wait for T2 to release the share mode lock. Meanwhile a transaction T3 may request a shared mode lock on the same data item. The lock request is compatible with the lock granted to T2 so T3 may be granted the shared mode lock. At this point T2 may release the lock, but still T1 has to wait for T3 to finish. But again there may be a new transaction T4 that request a shared mode lock on the same data item and is granted the lock before T3 releases it. In fact, it is possible that there is a sequence of transactions that each request a shared mode lock on the data item and each transaction release the lock a short while after it is granted, but T1 never gets the exclusive mode lock on the data item. The transaction T1 may never make progress and is said to be starved.

Solution of Starvation Problem

We can avoid starvation problem of transactions by granting locks as follows:

1.   If a shared lock is requested, the queue of requests is empty, and the object is not currently locked in exclusive mode, the lock manager grants the lock and updates the lock table entry for the object (indicating that the object is locked in shared mode and incrementing the number of transactions holding a lock by one).

2.   If an exclusive lock is requested, and no transaction currently holds a lock on the object (which also implies the queue of requests is empty) the lock manager grants. the lock and updates the lock table entry.

Objective: To visualize the locking procedure in Oracle.

Locking in Oracle: In Oracle default locking is row exclusive lock for Update/ Delete/ Insert.

Step 1: Open two instances of SQL PLUS on your machine and login with user name ‘scott’ and password ‘tiger’ or your own Oracle ID in both.

Step 2: Go to first instance.

Write the following query:

SQL>update EMP set sal=7000 where empno=7369;
Step 3: Go to second instance.
Write the following query:
SQL>update EMP set sal=5000 where empno=7369;
Step 4: Go to first instance and issue COMMIT/ ROLLBACK.
Step 5: Go to second instance. You get the SQL prompt.
Step 6: Issue COMMIT/ ROLLBACK in the second instance as well.
Summary of this exercise:
You have just learnt
• Update/ Delete/ Insert DML statement in Oracle acquire a row exclusive lock.
• The exclusive lock is released only at the end of transaction which is marked by commit/ Rollback.
Objective: To visualize how deadlock occurs in Oracle.
Step 1: Open two instances of SQLPLUS on your machine and login with user name ‘scott’ and password ‘tiger’ or your own Oracle ID in both.
Step 2: Go to first instance.
Write the following query:
SQL>update EMP set sal=7000 where empno=7900;
Step 3: Go to second instance.
Write the following query:
SQL>update EMP set sal=5000 where empno=7902;
Step 4: Go to first instance.
Write the following query:
SQL>update EMP set sal=7000 where empno=7902;
Step 5: Go to second instance
Write the following query:
SQL>update EMP set sal=6000 where empno=7900;
Step 6: Issue COMMIT/ ROLLBACK III both the instances
Summary of this exercise:
You have just learnt
• How to visualize the deadlock.
• Oracle intelligently determines the deadlock situation
3.   Otherwise, the requested lock cannot be immediately granted and the lock request is added to the queue of lock requests for this object. The transaction requesting the lock is suspended.
Note that if T1 has a shared lock on A and T2 requests an exclusive lock, T2 request is queued. Now if T3 requests a shared lock, its request enters the queue behind that of T2, even though the requested lock is compatible with the lock held by T1. This rule ensures that T2 does not starve, that is, wait indefinitely while a stream of other transactions; acquire shared locks and thereby prevent T2 from getting the exclusive lock that is waiting for.
You’ll also like:
Explicit Locks in Java Example 
   
Next →
← Prev