Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Other Concurrency Control Issues
In this article, we will discuss some other issues related to concurrency control. These are problems related to the insertion and deletion of records. These are known as phantom problems that can occur when records are joined. We will also discuss problems that can occur when a transaction outputs some data before monitoring, and then the transaction is later aborted.
Insertion, Deletion, and Phantom Records
In DBMS, when a new data item is inserted, it is not immediately accessible until the insert operation is completed. To maintain data consistency and integrity, in a locking environment, an exclusive (write) lock for the item can be created. The lock is released at the same time as other write locks based on the concurrency control protocol being used.
For a timestamp-based protocol, the read and write timestamps of the new item are set to the timestamp of the creating transaction. When a deletion operation is applied to an existing data item, an exclusive (write) lock must be obtained before the transaction can delete the item in locking protocols. In timestamp ordering, the protocol must ensure that no later transaction has read or written the item before allowing it to be deleted to ensure consistency and prevent conflicts.
A situation known as the phantom problem may occur when a new record is being inserted by a transaction T and satisfies a condition that a set of records accessed by another transaction T must satisfy. In this scenario, T must read the new record, while T should not include it in the sum calculation. Even though the transactions logically conflict, in reality, there is no data item in common between the two transactions, since T may have locked all the relevant records before T inserted the new record. However, the phantom record causes conflict, which may not be recognized by the concurrency control protocol.
One solution to this problem is to use index locking, which involves locking the index entry for the record's attribute value before accessing the actual record. In the example given, an index on Dno of EMPLOYEE would include an entry for each distinct Dno value and a set of pointers to all EMPLOYEE records with that value. By locking the index entry before accessing the record, the conflict on the phantom record can be detected.
Another technique called predicate locking could lock access to all records that satisfy an arbitrary predicate or condition in a similar manner. However, it is challenging to implement predicate locking efficiently.
Interactive Transactions
A problem can arise when interactive transactions read input and write output to an interactive device, such as a monitor screen, before they are committed. This can create a dependency between two transactions, T and T', where a user inputs a value of a data item to transaction T based on some value written to the screen by transaction T', which may not have committed yet. Since this dependency is not modeled by the system concurrency control method, it can lead to inconsistencies in the database.
To address this problem, one approach is to postpone the output of transactions to the screen until they have committed. This ensures that the output seen by the user is consistent with the committed state of the database. However, this approach may also affect the responsiveness of the system, as users may need to wait for the commit operation to complete before seeing any output on the screen. Therefore, a trade-off needs to be made between consistency and responsiveness.
Latches
Locks held for a short duration are typically called latches. Unlike regular locks, such as those used in two-phase locking protocols, latches do not necessarily follow a specific concurrency control protocol. Instead, they are used to ensure the physical consistency of certain operations.
For instance, a latch can be used to protect the physical integrity of a page when that page is being written from the buffer to disk. In this scenario, a latch is acquired for the page. Page is written to disk, and then the latch is released. By using a latch in this way. We can ensure that no other operation can access or modify the page while it is being written to disk. This guarantees the integrity of the data and helps to avoid race conditions.
Conclusion
We have discussed various issues related to concurrency control in DBMS. The first issue is related to insertion, deletion, and phantom records, where conflicts can arise due to the addition or removal of records that satisfy certain conditions. To overcome this problem, the article suggests using index locking or predicate locking techniques.
The second issue discussed in the article is related to interactive transactions, where dependencies can arise between transactions that read input and write output to an interactive device before they are committed. To address this problem, the article suggests postponing the output of transactions to the screen until they are committed.
Finally, we have discussed latches. These are locks held for a short duration to ensure the physical integrity of certain operations. Latches are not subject to specific concurrency control protocols but can guarantee the integrity of data by preventing race conditions.
Overall, concurrency control is a critical aspect of DBMS to ensure the consistency and integrity of data in a multi-user environment. The issues we have discussed in this article highlight the importance of careful design and implementation of concurrency control protocols to prevent conflicts and ensure the correct functioning of the database system.
275 Views
