Answer:
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:
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:
- X-locks must be held till the transaction has ended
- 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:
- For reads, each tuple is locked, read, and lock is immediately released
- X-locks are held till end of transaction.
2. Weak Levels of Consistency in SQL
SQL allows non-serialisable executions:
- Serialisable is the default.
- Repeatable read allows only committed records to be read, and repeating a read should return the same value (so read locks should be retained). (However, the phantom phenomenon need not be prevented) that is, T1 may see some records inserted by T2, but may not see others inserted by T2.
- Read committed same as degree two consistency, but most systems implement it as cursor- stability.
- Read uncommitted allows even uncommitted data to be read. This is the level, which has almost no restriction on concurrency but will result in all sorts of concurrency related problems.
