.png)
Mobiprep has created last-minute notes for all topics of Transaction and Concurrency Control to help you with the revision of concepts for your university examinations. So let’s get started with the lecture notes on Transaction and Concurrency Control.
DBMS
Data Modelling
Database Architecture
Relational Model
Backup and Recovery
Functional Dependencies
Normalization
Deadlock
Transaction and Concurrency Control
Files and Storage
Relational Algebra
Entity Relationship Model
Indexing
Our team has curated a list of the most important questions asked in universities such as DU, DTU, VIT, SRM, IP, Pune University, Manipal University, and many more. The questions are created from the previous year's question papers of colleges and universities.
Question 1 - What do you understand by concurrent execution?
Answer - Concurrent execution of database transactions in a multi-user system is where any number of users can use the same database at the same time. It may lead to inconsistent data. To prevent it Concurrency control is needed in order to avoid inconsistencies in the database.
Question 2 - Define Serializability.
Answer - Serializability is the classical concurrency scheme. It ensures that a schedule for executing concurrent transactions is equivalent to one that executes the transactions serially in some order. It assumes that all accesses to the database are done using read and write operations
Question 3 - What is the basic assumption in serializability?
Answer - Basic assumption in serializability is that each transaction preserves database consistency. Thus serial execution of a set of transactions preserves database consistency.
Question 4 - Explain conflict and view serializability?
Conflict serializability: We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule. If a schedule S can be transformed into a schedule S´ by a series of swaps of non-conflicting instructions, we say that S and S´ are conflict equivalent. That is the order of each pair of conflicting operations in S and S` is the same.
View serializability: A schedule S is view serializable if it is view equivalent to a serial schedule. Let S and S´ be two schedules with the same set of transactions. S and S´ are view equivalent if the following three conditions are met, for each data item Q, 1. If in schedule S, transaction Ti reads the initial value of Q, then in schedule S’ also transaction Ti must read the initial value of Q. 2. If in schedule S transaction Ti executes read(Q), and that value was produced by transaction Tj (if any), then in schedule S’ also transaction Ti must read the value of Q that was produced by the same write(Q) operation of transaction Tj . 3. The transaction (if any) that performs the final write(Q) operation in schedule S must also perform the final write(Q) operation in schedule S’. As can be seen, view Every conflict serializable schedule is also view serializable
Question 5 - Explain recoverable schedule?
Answer - if a transaction Tj reads a data item previously written by a transaction Ti , then the commit operation of Ti appears before the commit operation of Tj. The following schedule is not recoverable if T9 commits immediately after the read . If T8 should abort, T9 would have read (and possibly shown to the user) an inconsistent database state. Hence, database must ensure that schedules are recoverable.
T8 | T9 |
Read (A) | |
Write (A) | |
| Read (A) |
| Commit |
Read (B) | |
Question 6 - Define Transaction.
Answer - A transaction is a unit of program execution that accesses and updates various data items.
Question 7 - What are the ACID Properties?
Answer - In ACID A stands for Atomicity, C stands for Consistency, I stands for Isolation and D stands for Durability .To preserve the integrity of data the database system must ensure:
Atomicity: Either all operations of the transaction are properly reflected in the database or none are.
Consistency: Execution of a transaction in isolation preserves the consistency of the database.
Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. That is, for every pair of transactions Ti and Tj , it appears to Ti that either Tj , finished execution before Ti started, or Tj started execution after Ti finished.
Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
Question 8 - Define Schedule.
Answer - Schedule is nothing but a sequences of instructions that specify the chronological order in which instructions of concurrent transactions are executed.
Question 9 - What are the types of Schedules based on Recoverability?
Answer - 1. Recoverable Schedule – A schedule is said to be recoverable if it is recoverable as name suggest. Only reads are allowed before write operation on same data. Only reads (Ti->Tj) is permissible.
Example –
S1: R1(x), W1(x), R2(x), R1(y), R2(y),
W2(x), W1(y), C1, C2;
Given schedule follows order of Ti->Tj => C1->C2. Transaction T1 is executed before T2 hence there is no chances of conflict occur. R1(x) appears before W1(x) and transaction T1 is committed before T2 i.e. completion of first transaction performed first update on data item x, hence given schedule is recoverable.
Source:geeksforgeeks
2. Cascadeless Schedule – When no read or write-write occurs before execution of transaction then corresponding schedule is called cascadeless schedule.
Example –
S3: R1(x), R2(z), R3(x), R1(z), R2(y), R3(y), W1(x), C1,
W2(z), W3(y), W2(y), C3, C2;
In this schedule W3(y) and W2(y) overwrite conflicts and there is no read, therefore given schedule is cascadeless schedule.
Sorce:geeksforgeeks
3. Strict Schedule – if schedule contains no read or write before commit then it is known as strict schedule. Strict schedule is strict in nature.
Example –
S4: R1(x), R2(x), R1(z), R3(x), R3(y),
W1(x), C1, W3(y), C3, R2(y), W2(z), W2(y), C2;
In this schedule no read-write or write-write conflict arises before commit hence its strict schedule.
Sorce:geeksforgeeks
Question 10 - Define Concurrency Control.
Answer - Concurrency control means the caution which serializes transaction and prevents updating same record at same time by more than one user.
Question 11 - Explain the different methods used for Concurrency Control in database.
Answer - There are three concurrency control techniques which are as follows:
Locking method: Database systems equipped with lock-based protocols use a mechanism by which any transaction cannot read or write data until it acquires an appropriate lock on it. Locks are of two kinds −
Binary Locks − A lock on a data item can be in two states; it is either locked or unlocked.
Shared/exclusive − This type of locking mechanism differentiates the locks based on their uses. If a lock is acquired on a data item to perform a write operation, it is an exclusive lock. Allowing more than one transaction to write on the same data item would lead the database into an inconsistent state. Read locks are shared because no data value is being changed.
There are four types of lock protocols available −
1. Simplistic Lock Protocol: Simplistic lock-based protocols allow transactions to obtain a lock on every object before a 'write' operation is performed. Transactions may unlock the data item after completing the ‘write’ operation.
2. Pre-claiming Lock Protocol: Pre-claiming protocols evaluate their operations and create a list of data items on which they need locks. Before initiating an execution, the transaction requests the system for all the locks it needs beforehand. If all the locks are granted, the transaction executes and releases all the locks when all its operations are over. If all the locks are not granted, the transaction rolls back and waits until all the locks are granted.
Source: javatpoint
3. Two-Phase Locking 2PL: This locking protocol divides the execution phase of a transaction into three parts. In the first part, when the transaction starts executing, it seeks permission for the locks it requires. The second part is where the transaction acquires all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot demand any new locks; it only releases the acquired locks.
Source: javatpoint
Two-phase locking has two phases, one is growing, where all the locks are being acquired by the transaction; and the second phase is shrinking, where the locks held by the transaction are being released. To claim an exclusive (write) lock, a transaction must first acquire a shared (read) lock and then upgrade it to an exclusive lock.
4. Strict Two-Phase Locking: The first phase of Strict-2PL is same as 2PL. After acquiring all the locks in the first phase, the transaction continues to execute normally. But in contrast to 2PL, Strict-2PL does not release a lock after using it. Strict-2PL holds all the locks until the commit point and releases all the locks at a time.
Source: javatpoint
Strict-2PL does not have cascading abort as 2PL does.
1. Timestamp method
i. The most commonly used concurrency protocol is the timestamp based protocol. This protocol uses either system time or logical counter as a timestamp.
ii. Lock-based protocols manage the order between the conflicting pairs among transactions at the time of execution, whereas timestamp-based protocols start working as soon as a transaction is created.
iii. Every transaction has a timestamp associated with it, and the ordering is determined by the age of the transaction. A transaction created at 0002 clock time would be older than all other transactions that come after it. For example, any transaction 'y' entering the system at 0004 is two seconds younger and the priority would be given to the older one.
iv. In addition, every data item is given the latest read and write-timestamp. This lets the system know when the last ‘read and write’ operation was performed on the data item.
2. Optimistic method
i. In optimistic concurrency control techniques, it is assumed that the transactions do not directly update the data items in the database until they finish their execution.
ii. Instead, each transaction maintains local copies of the data items it requires and updates them during execution.
iii. All the data items in the database are updated at the end of the transaction execution. In this technique, each transaction Ti proceeds through three phases, namely, read phase, validation phase, and write phase, depending on whether it is a read-only or an update transaction. The execution of transaction Ti begins with read phase, and the three timestamp values are associated with it during its lifetime. The three phases are explained here.
Read phase: At the start of this phase, transaction Ti is associated with a timestamp Start (Ti). Ti reads the values of data items from the database and these values are then stored in the temporary local copies of the data items kept in the workspace of Ti. All modifications are performed on these temporary local copies of the data items without updating the actual data items of the database.
Validation phase: At the start of this phase, transaction Ti is associated with a timestamp Validation (Ti). The system performs a validation test when Ti decides to commit. This validation test is performed to determine whether the modifications made to the temporary local copies can be copied to the database. In addition, it determines whether there is a possibility of Ti to conflict with any other concurrently executing transaction. In case any conflict exists, Ti is rolled back, its workspace is cleared and Ti is restarted.
Write phase: In this phase, the system copies the modifications made by Ti in its workspace to the database only if it succeeds in the validation phase. At the end of this phase, Ti is associated with a timestamp Finish (Ti).
Question 12 - What are the problems in concurrency control?
Answer - The problems in concurrency control are as follows:
Lost Updates: The lost update problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost (overwritten by the other transaction).
Uncommitted Data: The phenomenon of uncommitted data occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions.
Inconsistent Retrievals: Inconsistent retrievals occur when a transaction accesses data before and after another transaction(s) finish working with such data. For example, an inconsistent retrieval would occur if transaction T1 calculated some summary (aggregate) function over a set of data while another transaction (T2) was updating the same data. The problem is that the transaction might read some data before they are changed and other data after they are changed, thereby yielding inconsistent results.
Question 13 - What is cascading rollback? Explain with an example.
Answer - A single transaction failure leads to a series of transaction rollbacks. Consider the following schedule where none of the transactions has yet committed
source:coddicted
T10 | T11 | T12 |
read (A) | | |
read (B) | | |
write (C) | | |
| read (A) | |
| write (A) | |
| | read (A) |
If T10 fails, T11 and T12 must also be rolled back. That's why it is called as cascading rollback.
Question 14 - What are Cascadeless Schedule?
Answer - Cascadeless schedule is also recoverable. Cascading rollbacks cannot occur; for each pair of transactions Ti and Tj such that Tj reads a data item previously written by Ti , the commit operation of Ti appears before the read operation of Tj. Every It is desirable to restrict the schedules to those that are cascadeless.
Question 15 - What is the difference between Concurrency control and serializability test?
Answer - Concurrency-control protocols allow concurrent schedules, but ensure that the schedules are conflict/view serializable, and are recoverable and cascadeless. Concurrency control protocols generally do not examine the precedence graph as it is being created Instead a protocol imposes a discipline that avoids nonseralizable schedules. Different concurrency control protocols provide different trade offs between the amount of concurrency they allow and the amount of overhead that they incur. Tests for serializability help us understand why a concurrency control protocol is correct.
Comments