Question

Question on Database Systems - Concurrency Control

A popular update operation is increment/decrement the numeric value of data object by 1. Suppose we are now to create a lock mode I, along with usual shared and exclusive lock modes. Consider a locking protocol that will require a request for a lock with mode I on a data object Q (with numeric value) before proceeding with the increment/decrement operation on Q.
(i) Provide a lock compatibility matrix with these three modes.
(ii) Will this protocol provide more concurrency (data sharing) than the one with only shared and exclusive lock modes?
(iii) Suppose the increment/decrement operation is modified such that the operation will fail if the value of data object will become negative after the operation. Are your answers to (i) and (ii) still valid? Explain your answer.

Answer 1

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control.

Update(U) lock:Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

Intent lock: The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

• To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

• To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table.

Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Lock Compatability:

Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. For example, no lock modes are compatible with exclusive locks.

If a transaction Ti holds a data item N in IS’ mode and transaction Tj requests the data item N in IS mode then Tj is allowed to lock it in IS mode. i.e.
If Ti holds a data item N in IS mode means that there is some node below N in the tree which is in shared mode
And Tj requests the data item N in IS mode means that Tj is requesting some node below N in shared mode, then the request for Tj is allowed.

Lock modes in intent lock:

Intent Shared(IS):Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

Intent Exclusive(IX):Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

Shared with Intent EXclusive(SIX):Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

Intent Update(IU):Protects requested or acquired update locks on all resources lower in the hierachy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.

Shared Intent Update(SIU):A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.

Update Intent Exclusive(UIX):A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

Multiple Granularity Locking Protocol: It is the hierarchically breaking up the database into portions which are lockable and maintaining the track of what to be lock and how much to be lock so that it can be decided very quickly either to lock a data item or to unlock a data item.

In order to get advantages of both ( high level granularity + low level granularity), we use Multiple Granularity which provides more concurrency.

To decrease the search a little bit, we can maintain information about the file whose any record is locked by a transaction, is maintained in lock compatible table or say some traces must be left along the path(i.e. height of the tree). So that whenever a request of Lock is arrived, we can check the table directly.