BCA- IV

BCA- 403: Data Base Management System

Dr. Aditya Kumar Gupta

Associate Professor

Unit – IV
Database Transaction Processing

Multiprogramming allows executing multiple program or processes at the same time access
database to multiple users simultaneously. If only a single CPU exist, it can execute at most
one process at a time. However, multiprogramming operating system executes some commands
from the next process, and so on. A DBMS is called multi-user if many user many user use the
system and, hence access database concurrently. On the other hand in single-user if at most
one user can use a system at a time. The model of a database is used to explain transaction-
processing concepts is much simplified. A database is basically represented as a collection of
named data items. The size of the data is called granularity, and it can be field of some record
in the database, or it may be large unit such as record or even whole disk. One

A transaction is a logical unit of database processing that includes one or more database access
operation; these may include insertion, deletion, modification, or retrieval operations. The
database operations that form a transaction can either be embedded within an application
program or they can be specified via a high-level language such as SQL. One way of specifying
the transaction boundaries is specifying begin transactions and end transactions explicitly. The
transaction consists of the data, we require that the transaction executed between the begin
transaction and end transaction.
A database system must ensure proper execution of transaction despite failures-either the
entire transaction executes, or none of it execute well. It must be manage concurrent execution
of transaction in a way that avoids the introduction of inconsistency. The transaction consist of
all operations executed between the begin transaction and end transaction. To ensure integrity
of the data, we require that the database system maintain the following properties of the
transactions.

 Atomicity: Either all operations of the transaction are reflected properly in the
database, or none of them reflected.
 Consistency: Execution of a transaction in isolation (that is, with no other
transaction executing concurrently) preserves the consistency of the database.
 Isolation: Even through multiple transactions may execute concurrently, the
system guarantees that, for 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. Thus, each transaction is unaware of other transaction executing
concurrently in the system.
 Durability: After a transaction completes successfully, the changes it has made
to the database persist, even if there are system failures.

BCA: 403: Database Management System by Dr. Aditya K Gupta

 These properties are also called as AICD properties (On the basis of first latter of each
property.) Some access operation that a transaction can include is given as follows:

1. read_item(X): Reads a database item named X into a program variable. Here X also
denotes program variable.
Executing a read_item(X) command includes the following steps:
 Find the address of the disk block that contain item X.
 Copy that disk block into a buffer in main memory.
 Copy item X from the buffer to the program variable named X.

2. write_item (X): Writes the value of program variable X into the database item
named X.
Executing a write_item (X) command includes the following steps:
 Find he address of the disk block that contains item X.
 Copy that disk block into a buffer in main memory.
 Copy item X from the program variable named X into its correct location in the buffer.
 Store the update block from the buffer block to disk.

A transaction includes read_item and write_item operation to access and update the database.
The read-set of a transaction is the set of all items that the transaction reads, and the write-set
is the set of all items that the transaction writes.

T1 T2
read_item (x) read_item (x)
X:= X-N; X:= X-N;
write_item (X); write_item (X);
read_item (Y);
Y:=Y+N;
Write_item (Y);

For example, the read-set of T1 in the above figure is {X, Y} and its write set is also {X, Y}.

BCA: 403: Database Management System by Dr. Aditya K Gupta

Concurrency Control
Concurrency control is concerned with database access commands in a transaction.
Transaction submitted by various users may execute concurrently and may access and
updates the same database items. Uncontrolled concurrency may lead the problem of
inconsistent database. There are following problems that must be controlled via
controlling concurrency.

The Lost Update Problem

This problem occurs when two transactions that access the same database items have
their operations interleaved in a way that makes value of some database item incorrect.
Suppose that transaction T1 and T2 submitted at approximately the same time, and
suppose that their operations are interleaved then the final value of item X is incorrect,
because T2 reads the value of X before T1 changes it in the database, and hence the
update value resulting from T1 is lost.

T1 T2

read_item(X)
X:= X-N; read_item (X);
X:=X+M;

write_item(X);

read_item(Y);

write_item(X);

T1 Time T2

read_item(X)
X:= X-N; Item X has an incorrect value because its

Y=Y+N; update by T1 is “lost” (overwritten)

read_item (X);
X:=X+M;
write_item(X);

Time

read_item(Y);

BCA: 403: Database Management System by Dr. Aditya K Gupta

The Temporary Update Problem/ Dirty Read Problem
This problem occurs when one transaction updates a database item and then the
transaction fails or some reason. The update item is accessed by another transaction
before it is changed back to its original value. Following Figure shows an example
where T1 updates item X and then fails before competition, so the system must change
X back to its original value. Before it can do so, however, transaction T2 reads the
“temporary” value of X, which will not be recorded permanently in the database
because it has been created by a transaction that has not been done yet; this problem is
known as dirty read problem.

T1 T2

sum:=0;
read_item(X)

read_item (X);
X:=X-M;
write_item(X);
T3 reads X after N is subtracted and
reads Y before N is added; a wrong
read_item(Y); is result (off by N)
sum:=sum+ X;
write_item(X);
sum:=sum+ Y:

read_item (Y);
X:=X-M;
write_item(Y);

The Incorrect Summary Problem

If one transaction is calculating an aggregate summary function one number of records
while other transaction are updating some of these record, the aggregation function
may calculate some values before they are updated. Another problem that may occur is
called unrepeatable problem, where a transaction T reads an item twice and the item is
changed by another transaction T between the two reads. Hence, T receives different
values for its two reads of the same item.

BCA: 403: Database Management System by Dr. Aditya K Gupta

Transaction State
In the absence of failures, all transaction complete successfully. However, as we noted
earlier, a transaction may not always complete its execution successfully. Such a
transaction is termed aborted. If we are to ensure the atomicity property, an aborted
transaction must have no effect on the state of the database. A transaction that
completes its execution successfully is said to be committed. A committed transaction
that has committed, we cannot undo its effects by aborting it. The only way to undo the
effects of a committed transaction is to execute a compensating transaction. We need to
be more precise about what we mean by successful completion of a transaction. We
therefore establish a simple abstract transaction model. A transaction must be in one of
the following states:

Active: The initial state; the transaction stays in this state while it is executing.
Partially committed: After the final statement has been executed.
Failed: After the discovery that normal execution has been rolled back and the
database have been restored to its state prior to start of the transaction.
Committed: After successful completion of transaction itself. We can say that a
transaction has committed only if it has entered the committed only if it has entered
into committed state.

Partially Committe
Committe d
d

Active

Failed
Aborted

BCA: 403: Database Management System by Dr. Aditya K Gupta

A transaction state in the active state, when it finishes its final statement, it enters the
partially committed state. At this point, the transaction has completed its execution,
but it is still possible that it may have to be aborted, since the actual output may still
be temporarily, residing in main memory, and thus a hardware failure may preclude its
successful competition.
The database system then writes out enough information to disk that, even in the event
of a failure, the updates performed by the transaction can be re-created when the
system restarts after the failure. When the last of this information is written out, the
transaction enters the committed state.

A enters the failed state after the system determines that the transaction can no longer
proceed with its normal execution. Such a transaction must be rolled back. Then, it
enters the aborted state. At this point, the system has two options:
 Restart: It can restart the transaction, but only if the transaction was aborted as
a result of some hardware or software error that was not created through the
internal logic of the transaction. A restarted transaction is considered to be a
new transaction.
 Kill: It can kill transaction. It usually does so because of some internal logical
error that can be corrected only by rewriting the application program, or because
the input was bad, or because the desired data were not found in the database.
We must be careful when dealing with observable external writers, such as writers to a
terminal or printer Once such a write has occurred, it cannot be erased, since it may
have been seen external to the database system.

Recoverability
The Recovery Management Component of a database system can support atomicity and
durability by a variety of scheme. The simple, but extremely inefficient scheme called
the Shadow copy scheme. This Scheme, which is based on making copies of the
database, called shadow copies; assume that only one transaction is active at a time.
The scheme also assumes that the database is simply a file on disk. A pointer called db-
pointer is maintains on disk; it points to the current copy of the database. In the
shadow-copy scheme, a transaction that wants to update the database first creates a
complete copy of the database. All updates are done on new database copy, living the
original copy of the Shadow copy, untouched, if at any point the transaction has to be
aborted, the system merely deletes the new copy. The old copy of the database has not
been affected.

BCA: 403: Database Management System by Dr. Aditya K Gupta

db pointer db pointer

Old copy of
Old copy of Database New copy of
Database (to be deleted) Database

System Failure
The transaction is said to have been committed at the point where the updated db pointer is
written to disk. If the transaction fails at any time before db-pointer is updated, the old content
of the database re not affected. We can abort the transaction by just deleting the new copy of
database. Once the transaction has been committed, all the updates that it performed are in the
database pointed to by db pointer. Thus, either all updates of the transaction are reflected, or
none of the effects are reflected regardless of transaction failure.

Consider that the system fails at any time before the update db pointer is written to
disk. Then, when the system restarts, it will read db pointer and will thus see the
original contents of the database, and none of the effects of the transaction will be
visible on the database. Again, suppose that the system fails after db pointer has been
updated on disk. Before the pointer is updated, all update pages of the new copy of
database were written to disk, Next we assume that, once a file is written to disk, its
contents will not be damaged even if there is a system failure. Therefore when the
system restarts, it will read db pointer and will thus see the contents of the database
after all the updates performed by the transaction.

End of Unit-4

End of Notes

BCA: 403: Database Management System by Dr. Aditya K Gupta