Data Protection - Lecture Note VII (Concurrency)

• Database concurrency and recovery are two inter-related subjects, both being parts of the more general topic of transaction processing.
• Database systems typically provide multi-user access to a shared database. As such concurrency control is critical in ensuring that concurrent operations are carried out correctly and efficiently.

Concurrency Problems
There are three concurrency problems, that is three types of potential mistakes which could occur if concurrency control is not properly enforced in the database system.

The Lost Update Problem
This relates to a situation where two concurrent transactions, say A and B are allowed to update an uncommitted change on the same data item, say X. The second update by transaction B replaces the value of the first update by transaction A. consequently, the updated value of X by A is lost following the second update by B.

Uncommitted Dependency

• The uncommitted dependency problem arises if a transaction is allowed to retrieve or, worse, update - a tuple that has been updated by another transaction but has not yet been committed by that other transaction.
• Therefore if it has not yet been committed, there is always a possibility that it never will be committed but will be rolled back instead - in which case the first transaction will have seen some data that now no longer exist
• According to the above diagram transaction A sees an uncommitted update at time T2. That update is then undone at time T3. Transaction A is therefore operating on a false assumption - namely, the assumption that tuple P has the value seen at time T2, where as in fact it has whatever value it had prior to time T1. As a result transaction A might well produce incorrect output
• Here the Roll Back of transaction B might be due to no fault of B's, it might for example be the result of a system crash. (and transaction A might already have terminated by that time, in which case the crash would not cause a Roll Back to be issued for A also.)
• The second example is even worse. Not only does transaction A becomes dependent on an uncommitted change at time T2, but it actually loses an update at time T3 - because Roll Back at time T3 causes tuple P to be restored to its value prior to time T'1. This is another version of lost update problem.

Locking
Locking is the most common mechanism of concurrency control. The idea of locking is quite simple when a transaction item will not change during the course of processing, it requires a lock, which locks other transactions out of the item preventing them changing it. The first transaction knows that the data item remains stable for its duration.

Locking granularity:- The size of the object to be locked is referred to as the degree of granularity (locking granularity). It could be:

• The entire database
• A disk block
• A database relation
• A tuple in a relation, or field of a tuple.

The larger the data item the lower is the degree of concurrency, the fewer the number of locks to be set, and the lower the processing overhead.

Two types of locks

There are two types of locks available

• Exclusive locks. (X locks, or write locks):
• Grant read/write access to the transaction which holds the lock.
• Prevent any other transactions reading or writing the data.
• Shared locks (S locks, or read locks):
• Give read-only access to the transaction which holds the lock.
• Prevent any transaction writing the data.
• Several transactions may hold a shared lock on an item.

Locking protocol:

• A transaction must acquire an S lock on the data item it wishes to retrieve.
• A transaction must acquire an X lock on the data item it wishes to update.
• If a lock request is denied, the transaction goes into Wait State.
• A transaction in Wait State resumes operation only when the lock request is released.
• X locks are held until commit or Roll Back, S locks are normally the same.

Dead lock problem

A system is in a state of dead lock if there exists a set of transactions as such that every transaction in the set is waiting for another transaction in the set.

In other words there exist a set of waiting transactions (Ts, T1 ,Tz, - - Tn) such that T0 is waiting for a data item which is held by T,, Tr is waiting for a data item held by Tz and Tn is waiting for a data item held by T0.