Unit 4
What is a transaction DBMS?
Transactions refer to a set of operations that are used for performing a set of logical work.
Usually, a transaction means the data present in the DB has changed. Protecting the user data
from system failures is one of the primary uses of DBMS.
A transaction is a single logical unit of work that accesses and possibly modifies the
contents of a database. Transactions access data using read and write operations.
Why do we need transactions?
Data integrity is the primary benefit of using transactions in DBMS. Usage of many
databases requires the storage of data in multiple tables or in multiple rows of the same table
to maintain consistency in a data set. The usage of transactions would ensure that other
connections to a similar database either see all the updates or see none of them.
Transaction property
In order to maintain consistency in a database, before and after the transaction, certain
properties are followed. These are called ACID properties.
Property of Transaction
1. Atomicity
2. Consistency
3. Isolation
4. Durability
Atomicity:
By this, we mean that either the entire transaction takes place at once or doesn’t happen at
all. There is no midway i.e. transactions do not occur partially. Each transaction is
considered as one unit and either runs to completion or is not executed at all. It involves the
following two operations.
—Abort: If a transaction aborts, changes made to the database are not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Consistency
o The integrity constraints are maintained so that the database is consistent before and
after the transaction.
o The execution of a transaction will leave a database in either its prior stable state or a
new stable state.
o The consistent property of database states that every transaction sees a consistent
database instance.
o The transaction is used to transform the database from one consistent state to another
consistent state. It refers to the correctness of a database.
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, the database is consistent. Inconsistency occurs in case T1 completes
but T2 fails. As a result, T is incomplete.
Isolation:
This property ensures that multiple transactions can occur concurrently without leading to
the inconsistency of the database state. Transactions occur independently without
interference. Changes occurring in a particular transaction will not be visible to any other
transaction until that particular change in that transaction is written to memory or has been
committed. This property ensures that the execution of transactions concurrently will result
in a state that is equivalent to a state achieved these were executed serially in some order.
o It shows that the data which is used at the time of execution of a transaction cannot be
used by the second transaction until the first one is completed.
Durability
o The durability property is used to indicate the performance of the database's consistent
state. It states that the transaction made the permanent changes.
o They cannot be lost by the erroneous operation of a faulty transaction or by the system
failure. When a transaction is completed, then the database reaches a state known as the
consistent state. That consistent state cannot be lost, even in the event of a system's
failure.
o The recovery subsystem of the DBMS has the responsibility of Durability property.
The ACID properties, in totality, provide a mechanism to ensure the correctness and
consistency of a database in a way such that each transaction is a group of operations
that acts as a single unit, produces consistent results, acts in isolation from other
operations, and updates that it makes are durably stored.
States of Transactions
Active state
o The active state is the first state of every transaction. In this state, the transaction is
being executed.
o For example: Insertion or deletion or updating a record is done here. But all the records
are still not saved to the database.
Partially committed
o In the partially committed state, a transaction executes its final operation, but the data
is still not saved to the database.
o In the total mark calculation example, a final display of the total marks step is executed
in this state.
Committed
A transaction is said to be in a committed state if it executes all its operations successfully. In
this state, all the effects are now permanently saved on the database system.
Failed state
o If any of the checks made by the database recovery system fails, then the transaction is
said to be in the failed state.
o In the example of total mark calculation, if the database is not able to fire a query to
fetch the marks, then the transaction will fail to execute.
Aborted
o If any of the checks fail and the transaction has reached a failed state then the database
recovery system will make sure that the database is in its previous consistent state. If
not then it will abort or roll back the transaction to bring the database into a consistent
state.
o If the transaction fails in the middle of the transaction then before executing the
transaction, all the executed transactions are rolled back to its consistent state.
o After aborting the transaction, the database recovery module will select one of the two
operations:
1. Re-start the transaction
2. Kill the transaction
So basically there are two types of operations that can be performed on the database.
o 1. Read operation:
o Read the data value from the database. It is a safe operation, as it does not update any
information in the database.
o 2. Write operation:
o It writes data into the database and saved it for further use. After writing data, data has
to be committed (Commit Operation) to making updated information available for
further operations. This operation is more prone to vulnerability as it
involves modifying database information.
There are different types of data conflict happens during reading and writing
operations called as “Read-Write conflict”.
Different Types of Read Write Conflict in DBMS:
there are three types of conflict in the database transaction.
• Write-Read (WR) conflict
• Read-Write (RW) conflict
• Write-Write (WW) conflict
Write-Read (WR) conflict?
This conflict occurs when a transaction read the data which is written by the other transaction
before committing.
Here, the transaction T2 is reading the data which is written by the T1 before T2 commits. It
is also called as Dirty Read.
It violates the ACID property of data consistency rule.
Expected behavior:
The new transaction (says T2) should not read the value if any other another transaction (say
T1) already has written the data and has not committed.
Read-Write (RW) conflict?
Transaction T2 is Writing data which is previously read by transaction T1.
Here if you look at the diagram above, data read by transaction T1 before and after T2
commits is different.
Write-Write (WW) conflict?
Here Transaction T2 is writing data which is already written by other transaction T1. T2
overwrites the data written by T1. It is also called as a blind write operation.
Data written by T1 has vanished. So it is data update loss.
Schedule
A series of operation from one transaction to another transaction is known as schedule. It is
used to preserve the order of the operation in each of the individual transaction.
Schedule, as the name suggests, is a process of lining the transactions and executing them
one by one. When there are multiple transactions that are running in a concurrent manner
and the order of operation is needed to be set so that the operations do not overlap each
other, Scheduling is brought into play and the transactions are timed accordingly.
1 Serial Schedule
The serial schedule is a type of schedule where one transaction is executed completely before
starting another transaction. In the serial schedule, when the first transaction completes its
cycle, then the next transaction is executed.
For example: Suppose there are two transactions T1 and T2 which have some operations. If it
has no interleaving of operations, then there are the following two possible outcomes:
1. Execute all the operations of T1 which was followed by all the operations of T2.
2. Execute all the operations of T1 which was followed by all the operations of T2.
o In the given (a) figure, Schedule A shows the serial schedule where T1 followed by T2.
o In the given (b) figure, Schedule B shows the serial schedule where T2 followed by T1.
2. Non-serial Schedule
o If interleaving of operations is allowed, then there will be non-serial schedule.
o It contains many possible orders in which the system can execute the individual
operations of the transactions.
o In the given figure (c) and (d), Schedule C and Schedule D are the non-serial schedules.
It has interleaving of operations.
3. Serializable schedule
o The serializability of schedules is used to find non-serial schedules that allow the
transaction to execute concurrently without interfering with one another.
o It identifies which schedules are correct when executions of the transaction have
interleaving of their operations.
o A non-serial schedule will be serializable if its result is equal to the result of its
transactions executed serially.
Conflict Serializable Schedule
o A schedule is called conflict serializability if after swapping of non-conflicting
operations, it can transform into a serial schedule.
o The schedule will be a conflict serializable if it is conflict equivalent to a serial schedule.
Conflicting Operations
The two operations become conflicting if all conditions satisfy:
1. Both belong to separate transactions.
2. They have the same data item.
3. They contain at least one write operation.
Conflict Equivalent
In the conflict equivalent, one can be transformed to another by swapping non-conflicting
operations. In the given example, S2 is conflict equivalent to S1 (S1 can be converted to S2 by
swapping non-conflicting operations).
Two schedules are said to be conflict equivalent if and only if:
1. They contain the same set of the transaction.
2. If each pair of conflict operations are ordered in the same way.
Schedule S2 is a serial schedule because, in this, all operations of T1 are performed before
starting any operation of T2. Schedule S1 can be transformed into a serial schedule by swapping
non-conflicting operations of S1.
After swapping of non-conflict operations, the schedule S1 becomes:
Since, S1 is conflict serializable.
• Conflicting operations pair (R1(A), W2(A)) because they belong to two different
transactions on same data item A and one of them is write operation.
• Similarly, (W1(A), W2(A)) and (W1(A), R2(A)) pairs are also conflicting.
• On the other hand, (R1(A), W2(B)) pair is non-conflicting because they operate
on different data item.
• Similarly, ((W1(A), W2(B)) pair is non-conflicting.
Consider the following schedule:
S1: R1 (A), W1 (A), R2 (A), W2 (A), R1 (B), W1 (B), R2 (B), W2 (B)
If i and j are two operations in a transaction and i< j (i.e. i is executed before j), the same
order will follow in the schedule as well. Using this property, we can get two transactions of
schedule S1 as:
T1: R1 (A), W1 (A), R1 (B), W1 (B)
T2: R2 (A), W2 (A), R2 (B), W2 (B)
T1->T2 or T2->T1 are possible serial schedules
• Interchanging R2 (A) and R1 (B) non-conflicting operations in S1, the schedule
becomes,
S11: R1 (A), W1 (A), R1 (B), W2 (A), R2 (A), W1 (B), R2 (B), W2 (B)
• In the same way, interchainging W2 (A) and W1 (B) non-conflicting operations in
S11, the schedule becomes,
S12: R1 (A), W1 (A), R1 (B), W1 (B), R2 (A), W2 (A), R2 (B), W2 (B)
• S12 is a serial schedule in which all operations of T1 are performed before starting
any operation of T2. Since S1 has been transformed into a serial schedule S12 by
interchanging non-conflicting operations of its or , S1 is conflict serializable.
Let us take another Schedule:
S2: R2 (A), W2 (A), R1 (A), W1 (A), R1 (B), W1 (B), R2 (B), W2 (B)
The two transactions are:
T1: R1 (A), W1 (A), R1 (B), W1 (B)
T2: R2 (A), W2 (A), R2 (B), W2 (B)
T1->T2 or T2->T1 are possible serial schedules.
• Original Schedule is:
S2: R2 (A), W2 (A), R1 (A), W1 (A), R1 (B), W1 (B), R2 (B), W2 (B)
• Interchanging R1 (A) and R2 (B) non-conflicting operations in S2, the schedule
becomes,
S21: R2 (A), W2 (A), R2 (B), W1 (A), R1 (B), W1 (B), R1 (A), W2 (B)
• In the same way, interchanging W1 (A) and W2 (B) non-conflicting operations in
S21, the schedule becomes,
S22: R2 (A), W2 (A), R2 (B), W2 (B), R1 (B), W1 (B), R1 (A), W1 (A)
• In schedule S22, even though all operations of T2 are performed first, but the
operations of T1 are not in order (the order should be R1 (A), W1 (A), R1 (B), W1
(B)). So S2 is not conflict serializable.
Conflict Equivalent: Two schedules are said to be conflict equivalent when one can be
transformed into another by swapping non-conflicting operations.
View serializability is a concept that is used to compute whether schedules are View-
Serializable or not. A schedule is said to be View-Serializable if it is view equivalent to
a Serial Schedule (where no interleaving of transactions is possible).
o If a schedule is conflict serializable, then it will be view serializable.
o The view serializable which does not conflict serializable contains blind writes.
Blind write: Performing the Writing operation (updation), without reading operation, such
write operation is known as a blind write.
Why do we need to use View-Serializability ?
There may be some schedules that are not Conflict-Serializable but still gives a consistent
result because the concept of Conflict-Serializability becomes limited when the Precedence
Graph of a schedule contains a loop/cycle. In such a case we cannot predict whether a
schedule would be consistent or inconsistent. As per the concept of Conflict-Serializability,
We can say that a schedule is Conflict-Serializable (means serial and consistent) iff its
corresponding precedence graph does not have any loop/cycle.
But, what if a schedule’s precedence graph contains a cycle/loop and is giving consistent
result/accurate result as a conflict serializable schedule is giving?
So, to address such cases we brought the concept of View-Serializability because we did
not want to confine the concept serializability only to Conflict-Serializability.
View Equivalent
Two schedules S1 and S2 are said to be view equivalent if they satisfy the following conditions:
1. Initial Read
An initial read of both schedules must be the same. Suppose two schedule S1 and S2. In
schedule S1, if a transaction T1 is reading the data item A, then in S2, transaction T1 should
also read A.
Above two schedules are view equivalent because Initial read operation in S1 is done by T1
and in S2 it is also done by T1.
2. Updated Read
In schedule S1, if Ti is reading A which is updated by Tj then in S2 also, Ti should read A
which is updated by Tj.
Above two schedules are not view equal because, in S1, T3 is reading A updated by T2 and
in S2, T3 is reading A updated by T1.
3. Final Write
A final write must be the same between both the schedules. In schedule S1, if a transaction T1
updates A at last then in S2, final writes operations should also be done by T1.
Above two schedules is view equal because Final write operation in S1 is done by T3 and in
S2, the final write operation is also done by T3.
Example:
Schedule S
With 3 transactions, the total number of possible schedules.
As 3! = 6
S1 = <T1 T2 T3>
S2 = <T1 T3 T2>
S3 = <T2 T3 T1>
S4 = <T2 T1 T3>
S5 = <T3 T1 T2>
S6 = <T3 T2 T1>
Taking first schedule S1:
Schedule S1
Step 1: final updation on data items
In both schedules S and S1, there is no read except the initial read that's why we don't need to
check that condition.
Step 2: Initial Read
The initial read operation in S is done by T1 and in S1, it is also done by T1.
Step 3: Final Write
The final write operation in S is done by T3 and in S1, it is also done by T3. So, S and S1 are
view Equivalent.
The first schedule S1 satisfies all three conditions, so we don't need to check another schedule.
Hence, view equivalent serial schedule is:
T1 → T2 → T3
Recoverability of Schedule
Sometimes a transaction may not execute completely due to a software issue, system crash or
hardware failure. In that case, the failed transaction has to be rollback. But some other
transaction may also have used value produced by the failed transaction. So we also have to
rollback those transactions.
The above table 1 shows a schedule which has two transactions. T1 reads and writes the value of A
and that value is read and written by T2. T2 commits but later on, T1 fails. Due to the failure, we have
to rollback T1. T2 should also be rollback because it reads the value written by T1, but T2 can't be
rollback because it already committed. So this type of schedule is known as irrecoverable schedule.
The above table 2 shows a schedule with two transactions. Transaction T1 reads and writes A, and
that value is read and written by transaction T2. But later on, T1 fails. Due to this, we have to rollback
T1. T2 should be rollback because T2 has read the value written by T1. As it has not committed
before T1 commits so we can rollback transaction T2 as well. So it is recoverable with cascade
rollback.
Recoverable with cascading rollback: The schedule will be recoverable with cascading rollback if
Tj reads the updated value of Ti. Commit of Tj is delayed till commit of Ti.
The above Table shows a schedule with two transactions. Transaction T1 reads and write A and
commits, and that value is read and written by T2. So this is a cascade less recoverable schedule.
Log based Recovery in DBMS
Log is nothing but a file which contains a sequence of records, each log record refers to a write
operation. All the log records are recorded step by step in the log file. We can say, log files
store the history of all updates activities.
Log contains start of transaction, transaction number, record number, old value, new value,
end of transaction etc. For example, mini statements in bank ATMs.
If within an ongoing transaction, the system crashes, then by using log files, we can return
back to the previous state as if nothing has happened to the database.
The log is kept on disk so that it is not affected by failures except disk and failures.
o The log is a sequence of records. Log of each transaction is maintained in some stable
storage so that if any failure occurs, then it can be recovered from there.
o If any operation is performed on the database, then it will be recorded in the log.
o But the process of storing the logs should be done before the actual transaction is
applied in the database.
Example
Different types of log records are as follows −
• <Ti, Xi, V1, V2> − update log record, where Ti=transaction, Xi=data, V1=old
data, V2=new value.
• <Ti, start> − Transaction Ti starts execution.
• <Ti, commit> − Transaction Ti is committed.
• <Ti, abort> − Transaction Ti is aborted
• Create a log for the given transaction T1 and T2.
• T1 T2 Log
• Read A Read A <T1, start>
• A=A-2000 A=A+5000 <T1,A,5000, 3000>
• Write A Write A <T1, B, 8000, 10000>
• Read B Read B <T1, commit>
• B=B+2000 B= B+7000 <T2, start>
• Write B Write B <T2, A, 3000, 8000>
Log based recovery techniques
Undo and Redo Operations –
Because all database modifications must be preceded by creation of log record,
the system has available both the old value prior to modification of data item
and new value that is to be written for data item. This allows system to
perform redo and undo operations as appropriate:
1. Undo: using a log record sets the data item specified in log record to
old value.
2. Redo: using a log record sets the data item specified in log record to
new value.
Deferred database modification
It modifies the database after completion of transaction. The database modification is deferred
or delayed until the last operation of the transaction is executed. Update log records maintain
the new value of the data item.
If the transaction does not modify the database until it has partially committed,
it is said to use deferred modification technique
Recover system uses one operation which is as follows −
• Redo(Ti) − All data items updated by the transaction Ti are set to a new value.
Immediate database modification
It modifies the database after a write operation, database modification is immediately done
when a transaction performs an update/ write operation. Update log records maintain both old
and new values of data items.
If database modification occur while transaction is still active, it is said to use
immediate modification technique
The recovery system uses two operations, which are as follows −
• Undo(Ti) − All data items updated by the transaction Ti, are set to old value.
• Redo(Ti) − All data items updated by the transaction Ti are set to a new value.
Use of Checkpoints –
When a system crash occurs, user must consult the log. In principle, that need
to search the entire log to determine this information. There are two major
difficulties with this approach:
1. The search process is time-consuming.
2. Most of the transactions that, according to our algorithm, need to be
redone have already written their updates into the database. Although
redoing them will cause no harm, it will cause recovery to take
longer.
Why do we need Checkpoints ?
Whenever transaction logs are created in a real-time environment, it eats up
lots of storage space. Also keeping track of every update and its maintenance
may increase the physical space of the system. Eventually, the transaction log
file may not be handled as the size keeps growing. This can be addressed with
checkpoints. The methodology utilized for removing all previous transaction
logs and storing them in permanent storage is called a Checkpoint.
What is a Checkpoint ?
The checkpoint is used to declare a point before which the DBMS was in the
consistent state, and all transactions were committed. During transaction
execution, such checkpoints are traced. After execution, transaction log files
will be created.
Upon reaching the save-point /checkpoint, the log file is destroyed by saving
its update to the database. Then a new log is created with upcoming execution
operations of the transaction and it will be updated until the next checkpoint
and the process continues.
Recovery using Checkpoint
o The recovery system reads log files from the end to start. It reads log files from T4 to
T1.
o Recovery system maintains two lists, a redo-list, and an undo-list.
o The transaction is put into redo state if the recovery system sees a log with <Tn, Start>
and <Tn, Commit> or just <Tn, Commit>. In the redo-list and their previous list, all the
transactions are removed and then redone before saving their logs.
o For example: In the log file, transaction T2 and T3 will have <Tn, Start> and <Tn,
Commit>. The T1 transaction will have only <Tn, commit> in the log file. That's why
the transaction is committed after the checkpoint is crossed. Hence it puts T1, T2 and
T3 transaction into redo list.
o The transaction is put into undo state if the recovery system sees a log with <Tn, Start>
but no commit or abort log found. In the undo-list, all the transactions are undone, and
their logs are removed.
o For example: Transaction T4 will have <Tn, Start>. So T4 will be put into undo list
since this transaction is not yet complete and failed amid.
Deadlock in DBMS
A deadlock is a condition where two or more transactions are waiting indefinitely for one
another to give up locks. Deadlock is said to be one of the most feared complications in DBMS
as no task ever gets finished and is in waiting state forever.
For example: In the student table, transaction T1 holds a lock on some rows and needs to
update some rows in the grade table. Simultaneously, transaction T2 holds locks on some rows
in the grade table and needs to update the rows in the student table held by Transaction T1.
Now, the main problem arises. Now Transaction T1 is waiting for T2 to release its lock and
similarly, transaction T2 is waiting for T1 to release its lock. All activities come to a halt state
and remain at a standstill. It will remain in a standstill until the DBMS detects the deadlock
and aborts one of the transactions.
Deadlock Avoidance
o When a database is stuck in a deadlock state, then it is better to avoid the database rather
than aborting or restating the database. This is a waste of time and resource.
o Deadlock avoidance mechanism is used to detect any deadlock situation in advance. A
method like "wait for graph" is used for detecting the deadlock situation but this method
is suitable only for the smaller database. For the larger database, deadlock prevention
method can be used.
Deadlock Detection
In a database, when a transaction waits indefinitely to obtain a lock, then the DBMS should
detect whether the transaction is involved in a deadlock or not. The lock manager maintains a
Wait for the graph to detect the deadlock cycle in the database.
Wait for Graph
o This is the suitable method for deadlock detection. In this method, a graph is created
based on the transaction and their lock. If the created graph has a cycle or closed loop,
then there is a deadlock.
o The wait for the graph is maintained by the system for every transaction which is
waiting for some data held by the others. The system keeps checking the graph if there
is any cycle in the graph
Deadlock Prevention
o Deadlock prevention method is suitable for a large database. If the resources are
allocated in such a way that deadlock never occurs, then the deadlock can be prevented.
o The Database management system analyzes the operations of the transaction whether
they can create a deadlock situation or not. If they do, then the DBMS never allowed
that transaction to be executed.
Wait-Die scheme
In this scheme, if a transaction requests for a resource which is already held with a conflicting
lock by another transaction then the DBMS simply checks the timestamp of both transactions.
It allows the older transaction to wait until the resource is available for execution.
Let's assume there are two transactions Ti and Tj and let TS(T) is a timestamp of any transaction
T. If T2 holds a lock by some other transaction and T1 is requesting for resources held by T2
then the following actions are performed by DBMS:
1. Check if TS(Ti) < TS(Tj) - If Ti is the older transaction and Tj has held some resource,
then Ti is allowed to wait until the data-item is available for execution. That means if
the older transaction is waiting for a resource which is locked by the younger
transaction, then the older transaction is allowed to wait for resource until it is available.
2. Check if TS(Ti) < TS(Tj) - If Ti is older transaction and has held some resource and if
Tj is waiting for it, then Tj is killed and restarted later with the random delay but with
the same timestamp.
Wound wait scheme
o In wound wait scheme, if the older transaction requests for a resource which is held by
the younger transaction, then older transaction forces younger one to kill the transaction
and release the resource. After the minute delay, the younger transaction is restarted but
with the same timestamp.
o If the older transaction has held a resource which is requested by the Younger
transaction, then the younger transaction is asked to wait until older releases it.
Distributed Database System
A distributed database is basically a database that is not limited to one system, it is spread
over different sites, i.e, on multiple computers or over a network of computers. A distributed
database system is located on various sites that don’t share physical components. This may
be required when a particular database needs to be accessed by various users globally. It
needs to be managed such that for the users it looks like one single database.
A distributed database is a collection of multiple interconnected databases, which are
spread physically across various locations that communicate via a computer network.
Features
• Databases in the collection are logically interrelated with each other. Often they
represent a single logical database.
• Data is physically stored across multiple sites. Data in each site can be managed
by a DBMS independent of the other sites.
• The processors in the sites are connected via a network. They do not have any
multiprocessor configuration.
• A distributed database is not a loosely connected file system.
• A distributed database incorporates transaction processing, but it is not
synonymous with a transaction processing system.
Distributed Data Storage:
There are 2 ways in which data can be stored on different sites. These are:
1. Replication –
In this approach, the entire relationship is stored redundantly at 2 or more sites. If the entire
database is available at all sites, it is a fully redundant database. Hence, in replication,
systems maintain copies of data.
This is advantageous as it increases the availability of data at different sites. Also, now
query requests can be processed in parallel.
However, it has certain disadvantages as well. Data needs to be constantly updated. Any
change made at one site needs to be recorded at every site that relation is stored or else it
may lead to inconsistency. This is a lot of overhead. Also, concurrency control becomes
way more complex as concurrent access now needs to be checked over a number of sites.
2. Fragmentation –
In this approach, the relations are fragmented (i.e., they’re divided into smaller parts) and
each of the fragments is stored in different sites where they’re required. It must be made
sure that the fragments are such that they can be used to reconstruct the original relation
(i.e, there isn’t any loss of data).
Fragmentation is advantageous as it doesn’t create copies of data, consistency is not a
problem.
Fragmentation of relations can be done in two ways:
• Horizontal fragmentation – Splitting by rows –
The relation is fragmented into groups of tuples so that each tuple is assigned to
at least one fragment.
• Vertical fragmentation – Splitting by columns –
The schema of the relation is divided into smaller schemas. Each fragment must
contain a common candidate key so as to ensure a lossless join.
Applications of Distributed Database:
• It is used in Corporate Management Information System.
• It is used in multimedia applications.
• Used in Military’s control system, Hotel chains etc.
• It is also used in manufacturing control system.