Download Relational Database Management System and more Study notes Relational Database Management Systems (RDBMS) in PDF only on Docsity!
RDBMS
RELATIONAL DATABASE MANAGEMENT
SYSTEM
The Oracle Client/Server Architecture
In the Oracle client/server architecture, the database application and the database are separated into two parts: a front-end or client portion, and a back-end or server portion. The client executes the database application that accesses database information and interacts with a user through the keyboard, screen, and pointing device such as a mouse. The server executes the Oracle software and handles the functions required for concurrent, shared data access to an Oracle database.
Although the client application and Oracle can be executed on the
same computer, it may be more efficient and effective when the client portion(s) and server portion are executed by different computers connected via a network.
Shadow paging is one of the techniques that is used to recover
from failure. We all know that recovery means to get back the information, which is lost. It helps to maintain database consistency in case of failure.
Concept of shadow paging
Now let see the concept of shadow paging step by step − Step 1 − Page is a segment of memory. Page table is an index of pages. Each table entry points to a page on the disk. Step 2 − Two page tables are used during the life of a transaction: the current page table and the shadow page table. Shadow page table is a copy of the current page table. Step 3 − When a transaction starts, both the tables look identical, the current table is updated for each write operation.
Step 4 − The shadow page is never changed during the life of the transaction. Step 5 − When the current transaction is committed, the shadow page entry becomes a copy of the current page table entry and the disk block with the old data is released. Step 6 − The shadow page table is stored in non-volatile memory. If the system crash occurs, then the shadow page table is copied to the current page table.
Advantages
The advantages of shadow paging are as follows − No need for log records. No undo/ Redo algorithm. Recovery is faster. Explore our latest online courses and learn new skills at your own pace. Enroll and become a certified expert to boost your career.
Disadvantages
The disadvantages of shadow paging are as follows − Data is fragmented or scattered. Garbage collection problem. Database pages containing old versions of modified data need to be garbage collected after every transaction. Concurrent transactions are difficult to execute.
Shadow paging is a fundamental recovery technique used in
database management systems (DBMS) to ensure the reliability and consistency of data. It plays a crucial role in maintaining atomicity and durability which are the two core properties of transaction management. Unlike log-based recovery mechanisms that rely on recording detailed logs of changes, shadow paging offers a simpler, log-free approach by maintaining two versions of the database state: the shadow page table and the current page table. This technique is also known as Cut-of-Place updating. This technique ensures that a database can recover seamlessly from failures without losing data integrity. During a transaction, updates are made to a new version of the database pages tracked by the current page table, while the shadow page table preserves the pre-transaction
This replacement makes the changes permanent. Abort : If the transaction is aborted, the current page table is discarded, leaving the shadow page table intact. Since the shadow page table still points to the original pages, no changes are reflected in the database.
Advantages of Shadow Paging
Fewer Disk Accesses: Requires fewer disk operations to perform tasks. Fast Recovery: Crash recovery is quick and inexpensive, with no need for Undo or Redo operations. Improved Fault Tolerance: Transactions are isolated, so a failure in one transaction does not affect others. Increased Concurrency: Changes are made to shadow copies, allowing multiple transactions to run simultaneously without interference. Simplicity: Easy to implement and integrate into existing database systems. No Log Files: Eliminates the need for log files, reducing overhead and improving efficiency.
Disadvantages of Shadow Paging
High Commit Overhead: A large number of pages need to be flushed during the commit process, increasing the overhead. Data Fragmentation: Related pages may become separated, leading to fragmentation. Garbage Collection Required: After each transaction, pages containing old versions of modified data must be garbage collected and added to the list of unused pages. Limited Concurrency: Extending the algorithm to support concurrent transactions is challenging.
Deferred Update: It is a technique for the maintenance of the
transaction log files of the DBMS. It is also called NO-UNDO/REDO technique. It is used for the recovery of transaction failures that occur due to power, memory, or OS failures. Whenever any transaction is executed, the updates are not made immediately to the database. They are first recorded on the log file and then those changes are applied once the commit is done. This is called the “Re-doing” process. Once the rollback is done none of the changes are applied to the database and the changes in the log file are also discarded.
If the commit is done before crashing the system, then after restarting the system the changes that have been recorded in the log file are thus applied to the database.
Immediate Update: It is a technique for the maintenance of
the transaction log files of the DBMS. It is also called UNDO/REDO technique. It is used for the recovery of transaction failures that occur due to power, memory, or OS failures. Whenever any transaction is executed, the updates are made directly to the database and the log file is also maintained which contains both old and new values. Once the commit is done, all the changes get stored permanently in the database, and records in the log file are thus discarded. Once rollback is done the old values get restored in the database and all the changes made to the database are also discarded. This is called the “Un-doing” process. If the commit is done before crashing the system, then after restarting the system the changes are stored permanently in the database.
Deferred Update & Immediate Update
Reference -
Deferred Update
o Immediate Update
In a deferred update, the changes are not applied immediately to the database. o In an immediate update, the changes are applied directly to the database. The log file contains all the changes that are to be applied to the database. o The log file contains both old as well as new values. In this method once rollback is done all the records of log file are discarded and no changes are applied to the database. o In this method once rollback is done the old values are restored to the database using the records of the log file. Concepts of buffering and caching are used in deferred update method. o Concept of shadow paging is used in immediate update method. The major disadvantage of this method is that it requires a lot of time for recovery in case of system failure.
According to the Timestamp ordering protocol TS(T1) TS(T) or if Write Timestamp of the data item X is greater than the timestamp of the transcation i.e W_TS(X) > TS(T), then abort and rollback T and reject the operation. else, Execute the Write_item(X) operation of T and set W_TS(X) to TS(T). When a transaction T performs a Read_item(X) operation check the following conditions : If Write Timestamp of data item X is greater than the timestamp of the transaction T i.e W_TS(X) > TS(T), then abort and reject T and reject the operation, else If Write Timestamp of data item X is less than or equal to the timestamp of the transaction T i.e W_TS(X) <= TS(T), then execute the R_item(X) operation of T and set R_TS(X) to the larger of TS(T) and current R_TS(X). Whenever there is a conflicting operation that violates the timestamp ordering protocol then the later operation is rejected and the transaction is aborted. Schedules created by the Basic timestamp ordering protocol are conflict serializable and deadlock-free. One of the drawbacks of the Basic timestamp ordering protocol is that cascading Rollback is possible in the timestamp ordering protocol. For Example, if transactions T1 and T2 use a value written by T1. If T aborts and rollback before committing the transaction then T2 must also be aborted and rolled back. So cascading problems may occur in the Basic timestamp ordering protocol.
Strict Timestamp Ordering
strict timestamp ordering is a variation of basic timestamp ordering. Strict timestamp ordering ensures that the transaction is both strict and conflicts serializable. In Strict timestamp ordering a transaction T that issues a Read_item(X) or Write_item(X) such that TS(T) > W_TS(X)
has its read or write operation delayed until the Transaction T‘ that wrote the values of X has committed or aborted.
Advantages
Timestamp-based protocols in dbms ensure serializability as the transaction is ordered on their creation timestamp. The precedence graph for the timestamp ordering protocol looks as follows: No deadlock occurs when timestamp ordering protocol is used as no transaction waits. No older transaction waits for a longer period of time so the protocol is free from deadlock. Timestamp based protocol in dbms ensures that there are no conflicting items in the transaction execution.
Disadvantages
Timestamp-based protocols in dbms may not be cascade free or recoverable. In timestamp based protocol in dbms there is a possibility of starvation of long transactions if a sequence of conflicting short transactions causes repeated restarting of the long transaction. Timestamp-based concurrency control is a method used in database systems to ensure that transactions are executed safely and consistently without conflicts, even when multiple transactions are being processed simultaneously. This approach relies on timestamps to manage and coordinate the execution order of transactions. Refer to the timestamp of a transaction T as TS(T).
What is Timestamp Ordering Protocol?
The Timestamp Ordering Protocol is a method used in database systems to order transactions based on their timestamps. A timestamp is a unique identifier assigned to each transaction, typically determined using the system clock or a logical counter. Transactions are executed in the ascending order of their timestamps, ensuring that older transactions get higher priority. For example:
Whenever some Transaction T tries to issue a R_item(X) or a W_item(X), the Basic TO algorithm compares the timestamp of T with R_TS(X) & W_TS(X) to ensure that the Timestamp order is not violated. This describes the Basic TO protocol in the following two cases: Whenever a Transaction T issues a W_item(X) operation, check the following conditions: If R_TS(X) > TS(T) and if W_TS(X) > TS(T), then abort and rollback T and reject the operation. else, Execute W_item(X) operation of T and set W_TS(X) to TS(T) to the larger of TS(T) and current W_TS(X). Whenever a Transaction T issues a R_item(X) operation, check the following conditions: If W_TS(X) > TS(T), then abort and reject T and reject the operation, else If W_TS(X) <= TS(T), then execute the R_item(X) operation of T and set R_TS(X) to the larger of TS(T) and current R_TS(X). Whenever the Basic TO algorithm detects two conflicting operations that occur in an incorrect order, it rejects the latter of the two operations by aborting the Transaction that issued it.
Strict Timestamp Ordering
The Strict Timestamp Ordering Protocol is an enhanced version of the Basic Timestamp Ordering Protocol. It ensures a stricter control over the execution of transactions to avoid cascading rollbacks and maintain a more consistent schedule.
Key Features
Strict Execution Order: Transactions must execute in the exact order of their timestamps. Operations are delayed if executing them would violate the timestamp order, ensuring a strict schedule. No Cascading Rollbacks: To avoid cascading aborts, a transaction must delay its operations until all conflicting operations of older transactions are either committed or aborted. Consistency and Serializability: The protocol ensures conflict- serializable schedules by following strict ordering rules based on transaction timestamps. For Read Operations (R_item(X)): A transaction T can read a data item X only if: W_TS(X), the timestamp of the last transaction that wrote to X, is less than or equal to TS(T), the timestamp of T and the transaction that last wrote to X has committed.
If these conditions are not met, T’s read operation is delayed until they are satisfied. For Write Operations (W_item(X)): A transaction T can write to a data item X only if: R_TS(X), the timestamp of the last transaction that read X, and W_TS(X), the timestamp of the last transaction that wrote to X, are both less than or equal to TS(T) and all transactions that previously read or wrote X have committed. If these conditions are not met, T’s write operation is delayed until all conflicting transactions are resolved.
Locking in a database management system is used for handling
transactions in databases. The two-phase locking protocol ensures serializable conflict schedules. A schedule is called conflict serializable if it can be transformed into a serial schedule by swapping non- conflicting operations.
Two-Phase Locking
Before understanding the two phases of Locking, let's understand the types of locks used in transaction control.
Shared Lock: Data can only be read when a shared lock is applied.
Data cannot be written. It is denoted as lock-S.
Exclusive lock : Data can be read as well as written when an
exclusive lock is applied. It is denoted as lock-X Now, let's understand the two phases of locking. The two phases of Locking are : Growing Phase: In the growing phase, the transaction only obtains the lock. The transaction can not release the lock in the growing phase. Only when the data changes are committed the transaction starts the Shrinking phase. Shrinking Phase : Neither locks are obtained nor they are released in this phase.
