One morning the chief information officer (CIO) called an emergency meeting of t

Question

One morning the chief information officer (CIO) called an emergency meeting of the system administration team. As a database administrator, you are part of this team. The meeting was about a data inconsistency problem in a newly launched web application. The database in question deals with items and their prices. Your colleague in charge of the problematic database noticed that the phantom problem is happening while the application is following the strict 2PL protocol. He explained that while a first transaction (T1) has locked all existing items of type 1, a second transaction (T2) was able to add new items of type 1 at the same time, which caused the average price calculated by T1 to be incorrect when displayed to the user.

The CIO asked the rest of you to check whether the same problem is happening in the databases you are in charge of.

Can this Phantom problem occur in any of your databases where the set of database objects is fixed and only the values of objects can be changed? Explain.

Explanation / Answer

Strict 2PL is the most widely used locking protocol where 1) A transaction requests a shared/exclusive lock on the object before it reads/modifies the object. 2) All locks held by a transaction are released when the transaction is completed.

The phantom problem is a situation where a transaction retrieves a collection of objects twice but sees different results, even though it does not modify any of these objects itself and follows the strict 2PL protocol. This problem usually arises in dynamic databases where a transaction cannot assume it has locked all objects of a given type (such as all sailors with rank 1; new sailors of rank 1 can be added by a second transaction after one transaction has locked all of the original ones).

If the set of database objects is fixed and only the values of objects can be changed, the phantom problem cannot occur since one cannot insert new objects into the database.

Phantom Phenomenon: The phantom phenomenon can be explained by using the following example. Consider a transaction T1 that executes the following SQL statement on the employee relation.

SELECT SUM (sal) FROM employee

WHERE deptno=10;

In this statement, transaction T1 requires to access all the tuples of relation employee pertaining to department number 10. Problem will be encountered if there is another transaction, which is run to reflect the receiver of SUM (sal). Let T2 be another transaction which issues an insert statement to the same relation, employee:

INSERT INTO employee

VALUES

(100, ‘Ashok’, 5000, ‘A-34 Ashoka Road’, 10):

Let S be the schedule involving T1 and T2. These two transactions can conflict from following two reasons:

_ If T1 uses the tuple newly inserted by T2 in computing SUM (sal), and then T1 read a value written by T2. Thus in a serial schedule equivalent to S, T2 must come before T1.

_ If T1 does not use the newly created tuple by T2 in computing SUM (sal) then in a serial schedule equivalent to S, T1 must come before T2.

In second case, T1 and T2 do not access any tuple in common, yet they conflict with each other. Here locking of records did not prevent the creation of a new tuple, which was created after the existing records have been locked. If concurrency control is performed at the tuple granularity, this conflict would go undetected. This phenomenon is called as Phantom Phenomenon. The problem could be prevented if the locking of records also prevents the addition of such phantom records. The locking of a record belonging to a record type must guarantee that no new record occurrences of the record type can be added until the lock is released.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.