Page 169 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes The information should be locked.
One solution to such a problem may be:
1. Associating a data item with the relation helps represent the information about the tuples
the relation contains.
2. Transactions scanning the relation acquires a shared lock in the data item.
3. Transactions inserting or deleting a tuple acquires an exclusive lock on the data item.

Notes Locks on the data item do not conflict with locks on individual tuples.

The above mentioned protocol provides very low concurrency for insertions/deletions. Index
locking protocols provide higher concurrency while preventing the phantom phenomenon, (by
requiring locks on certain index buckets).
Index Locking Protocol: Every relation must have at least one index. Access to a relation must be
made only through one of the indices on the relation. A transaction T that performs a lookup
i
must lock all the index buckets that it accesses, in S-mode. A transaction T may not insert a tuple
i
t into a relation r without updating all indices to r. T must perform a lookup on every index to
i i
find all index buckets that could have possibly contained a pointer to tuple t , had it existed
i
already, and obtain locks in X-mode on all these index buckets. T must also obtain locks in
i
X-mode on all index buckets that it modifies. The rules of the two-phase locking protocol must
be observed for index locking protocols to be effective.
9.14 Weak Level of Consistency

The protocols such as strict two phase locking protocol restricts concurrency while transactions
are being execution. Can we allow more concurrency by compromising on correctness/
accurateness, which now needs to be ensured by database programmers rather than by the
DBMS? We can operate on weak levels of consistency using the following mechanism:

9.14.1 Degree-two Consistency

Degree-two consistency differs from two-phase locking in that S-locks may be released at any
time, and locks may be acquired at any time, however:

1. X-locks must be held till the transaction has ended
2. Serialisability is not guaranteed. The programmer must ensure that no erroneous database
state will occur.

One of the degree two-consistency level protocols is Cursor stability. It has the following rules:
1. For reads, each tuple is locked, read, and lock is immediately released
2. X-locks are held till end of transaction.

162 LOVELY PROFESSIONAL UNIVERSITY