Insert and delete operation in RDBMS

·       Some transactions requires not only access to existing data items, but also the ability to create new data items.

·       Others require the ability to delete data items.

·       To examine how such transaction affect concurrency control, we introduce these additional operations.

1)    Delete (Q) deletes data item Q from the database.

2)    Insert (Q) inserts a new data item Q into the database and assigns Q and initial value.

1)    DELECTION:-

To understand how the presence of delete instructions affects concurrency control, we must decide when a delete instructions conflicts with another instruction. Let Ii and Ij be instructions of Ti and Tj, respectively, that appear in schedule S in consecutive order. Let Ii = delete (Q). We consider several instructions Ij.

·       Ij =read(Q). Ii and Ij conflict. if Ii comes before Ij, Tj will have a logical error. If Ij comes before Ii, Tj can execute the read operation successfully.

·       Ij =write(Q). Ii and Ij conflict. if Ii comes before Ij, Tj will have a logical error. If Ij comes before Ii, Tj can execute the write operation successfully.

·       Ij =delete(Q). Ii and Ij conflict. if Ii comes before Ij, Ti will have a logical error. If Ij comes before Ii, Ti will have a logical error.

·       Ij =Inset(Q). Ii and Ij conflict. Suppose that data item Q did not exist prior to the execution of Ii and Ij. Then if Ii comes before Ij, a logical error results for Ti. If Ij comes before Ii, then no logical error results. Likewise, if Q existed prior to the execution of Ii and Ij, then a logical error results if Ij comes before Ii, but not otherwise.

2)    Insertion :_

We have already seen that an insert(Q) operation conflicts with a delete(Q) operation. similarly, insert(Q) conflicts with a read(Q) operation or a write(Q) operation; no read or write can be performed on a data item before it exists.

Since an insert(Q) assigns a value to data item Q, an insert is treated similarly to a write for concurrency-control purposes:

·       Under the two-phase locking protocol, if Ti performs an insert(Q) operation, Ti is given an exclusive lock on the newly created data item Q.

·       Under the timestamp-ordering protocol, if Ti performs an insert(Q) operation, the values R-timestamp(Q) and W-timestamp(Q) are set to TS(Ti).

3)    The Phantom Phenomenon :-

·       Consider transaction T29 that executes the following SQL query on the bank database:

                              Select sum (balance)

                              From account

                              Where branch_name=’perryridge’

·       Transaction T29 requires access to all tuples of the account relation pertaining to the perryridge branch.

·       Let T30 be a transaction that executes the following SQL insertion.

                              Insert into account

                              Values (A-201, ‘perryridge’,900)

·       Let S be a schedule involving T29 and T30. We expect there to be potential for a conflict for the following reasons:

o   If T29 uses the tuple newly inserted by T30 in computing sum(balance), then T29 reads a value written by T30. Thus, in a serial schedule equivalent to S, T30 must come before T29.

o   If T29 does not use the tuple newly inserted by T30 in computing sum(balance), then in a serial schedule equivalent to S, T29 must come before T30.

·       The second of these two cases in curious. T29 and T30 do not access any tuple in common, yet they conflict with each other.

·       In effect T29 and T30 conflict on a phantom tuple.

·       If concurrency control is performed at the tuple granularity, this conflict would go undetected.

·       As a result, the system could fail to prevent a non-serializable schedule. This problem is called the phantom phenomenon.

·       The simplest solution to this problem is to associate a data item with the relation; the data item represents the information used to find the tuples in the relation.

·       Transaction, such as T29, that read the information about what tuplesare in a relation would then have to lock the data item corresponding to the relation in shared mode.

·       Transaction, such as T30, that update the information about what tuples are in relation would have to lock the data item in exclusive mode.

·       Thus, T29 and T30 would conflict on a real data item, rather than on a phantom.

·       The major disadvantage of locking a data item corresponding to the relation is the low degree of concurrency- 2 transactions that inset different tuples into a relation are prevented from executing concurrenctly.

·       A better solution is the index-locking technique.

·       Any transaction that inserts a tuple into a relation must insert information into every index maintained on the relation.

·       We eliminate the phantom phenomenon by imposing a locking protocol for indices.

·       The index-locking protocol  takes advantage of the availability of indices on a relation, by turning instance of the phantom phenomenon into conflicts on locks on index leaf nodes. The protocol operates as follows:

1)    Every relation must have at least one index.

2)    A transaction Ti can access tuples of relation only after first findig them through one or more of the indices on the relation.

3)    A transaction Ti that performs a lookup must acquire a shared lock on all the index leaf nodes that it accesses.

4)    A transaction Ti may not insert, delete, or update a tuple Ti, in a relation r without updating all indices on R. the transaction must obtain exclusive locks on all index leaf nodes that are affected by the insertion, deletion, or update.

5)    The rules of the two-phase locking protocol must be observed.