Database Administration and Management

Transaction Processing
o The transaction is a set of logically related operation. It contains a group of
tasks.
o A transaction is an action or series of actions. It is performed by a single
user to perform operations for accessing the contents of the database.

Example: Suppose an employee of bank transfers Rs 800 from X's account to Y's
account. This small transaction contains several low-level tasks:

X's Account

1. Open_Account(X)
2. Old_Balance = X.balance
3. New_Balance = Old_Balance - 800
4. X.balance = New_Balance
5. Close_Account(X)

Y's Account

1. Open_Account(Y)
2. Old_Balance = Y.balance
3. New_Balance = Old_Balance + 800
4. Y.balance = New_Balance
5. Close_Account(Y)

Operations of Transaction:

Read(X): Read operation is used to read the value of X from the database and
stores it in a buffer in main memory.

Write(X): Write operation is used to write the value back to the database from
the buffer.

Let's take an example to debit transaction from an account which consists of

following operations:
Database Administration and Management

1. R(X);

2. X = X - 500;

3. W(X);

Let's assume the value of X before starting of the transaction is 4000.

o The first operation reads X's value from database and stores it in a buffer.

o The second operation will decrease the value of X by 500. So buffer will
contain 3500.

o The third operation will write the buffer's value to the database. So X's final
value will be 3500.

But it may be possible that because of the failure of hardware, software or power,
etc. that transaction may fail before finished all the operations in the set.

For example: If in the above transaction, the debit transaction fails after
executing operation 2 then X's value will remain 4000 in the database which is not
acceptable by the bank.

To solve this problem, two important operations are available :

Commit: It is used to save the work done permanently.

Rollback: It is used to undo the work done.

Transaction property
The transaction has the four properties. These are used to maintain consistency in
a database, before and after the transaction.
Database Administration and Management

Property of Transaction

1. Atomicity
• It states that all operations of the transaction take place at once if not,
the transaction is aborted.
• There is no midway, i.e., the transaction cannot occur partially. Each
transaction is treated as one unit and either run to completion or is not
executed at all.

Atomicity involves the following two operations:

 Database Administration and Management

Abort: If a transaction aborts then all the changes made are not visible.

Commit: If a transaction commits then all the changes made are visible.

Example: Let's assume that following transaction T consisting of T1 and T2. A

consists of Rs 600 and B consists of Rs 300. Transfer Rs 100 from account A to
account B.

T1 T2

Read(A) Read(B)
A:=A-100 Y:=Y+100
Write(A) Write(B)

After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.

If the transaction T fails after the completion of transaction T1 but before

completion of transaction T2, then the amount will be deducted from A but not
added to B. This shows the inconsistent database state. In order to ensure
correctness of database state, the transaction must be executed in entirety.

2. 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.For example: The total amount must be
maintained before or after the transaction.

1. Total before T occurs = 600+300=900

2. Total after T occurs= 500+400=900
Database Administration and Management

Therefore, the database is consistent. In the case when T1 is completed but T2

fails, then inconsistency will occur.

3.Isolation
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.
o In isolation, if the transaction T1 is being executed and using the data item
X, then that data item can't be accessed by any other transaction T2 until
the transaction T1 ends.
o The concurrency control subsystem of the DBMS enforced the isolation
property.

4.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.

Transaction States
A transaction may go through a subset of five states, active, partially committed,
committed, failed and aborted.
Database Administration and Management

• Active − The initial state where the transaction enters is the active state.
The transaction remains in this state while it is executing read, write or
other operations.
• Partially Committed − The transaction enters this state after the last
statement of the transaction has been executed.
• Committed − The transaction enters this state after successful completion
of the transaction and system checks have issued commit signal.
• Failed − The transaction goes from partially committed state or active state
to failed state when it is discovered that normal execution can no longer
proceed or system checks fail.
• Aborted − This is the state after the transaction has been rolled back after
failure and the database has been restored to its state that was before the
transaction began.
Database Administration and Management

Schedule
A schedule is a series of operations from one or more transactions. A schedule
can be of two types:
Serial Schedule: When one transaction completely executes before starting
another transaction, the schedule is called a serial schedule. A serial schedule is
always consistent. A serial schedule has low throughput and less resource
utilization.
Concurrent Schedule: When operations of a transaction are interleaved with
operations of other transactions of a schedule, the schedule is called a
Concurrent schedule. But concurrency can lead to inconsistency in the
database.

Concurrency Control

When several transactions execute concurrently without any rules and protocols,
various problems arise that may harm the data integrity of several databases.
These problems are known as concurrency control problems. Therefore several
rules are designed, to maintain consistency in the transactions while they are
executing concurrently which are known as concurrency control protocols.

A transaction is a single reasonable unit of work that can retrieve or may change
the data of a database. Executing each transaction individually increases
the waiting time for the other transactions and the overall execution also gets
delayed. Hence, to increase the throughput and to reduce the waiting time,
transactions are executed concurrently.

Example: Suppose, between two railway stations, A and B, 5 trains have to travel,
if all the trains are set in a row and only one train is allowed to move from
station A to B and others have to wait for the first train to reach its destination
Database Administration and Management

then it will take a lot of time for all the trains to travel from station A to B. To
reduce time all the trains should be allowed to move concurrently from
station A to B ensuring no risk of collision between them.

When several transactions execute simultaneously, then there is a risk of violation

of the data integrity of several databases. Concurrency Control in DBMS is a
procedure of managing simultaneous transactions ensuring their atomicity,
isolation, consistency and serializability.

Concurrent Execution in DBMS

• In a multi-user system, multiple users can access and use the same
database at one time, which is known as the concurrent execution of the
database. It means that the same database is executed simultaneously on a
multi-user system by different users.

• While working on the database transactions, there occurs the requirement

of using the database by multiple users for performing different operations,
and in that case, concurrent execution of the database is performed.

Concurrency Control Problems

Several problems that arise when numerous transactions execute simultaneously

in a random manner are referred to as Concurrency Control Problems.

1. Dirty Read Problem

Database Administration and Management

The dirty read problem in DBMS occurs when a transaction reads the data
that has been updated by another transaction that is still uncommitted. It
arises due to multiple uncommitted transactions executing simultaneously.

• Example: Consider two transactions A and B performing read/write

operations on a data DT in the database DB. The current value of DT is
1000: The following table shows the read/write operations in A and B
transactions.
Database Administration and Management

Transaction A reads the value of data DT as 1000 and modifies it to 1500 which
gets stored in the temporary buffer. The transaction B reads the data DT as 1500
and commits it and the value of DT permanently gets changed to 1500 in the
database DB. Then some server errors occur in transaction A and it wants to get
rollback to its initial value, i.e., 1000 and then the dirty read problem occurs.

Unrepeatable Read Problem

The unrepeatable read problem occurs when two or more different values of the
same data are read during the read operations in the same transaction.

Example: Consider two transactions A and B performing read/write operations on

a data DT in the database DB. The current value of DT is 1000: The following table
shows the read/write operations in A and B transactions.
Database Administration and Management

Transaction A and B initially read the value of DT as 1000. Transaction A modifies

the value of DT from 1000 to 1500 and then again transaction B reads the value
and finds it to be 1500. Transaction B finds two different values of DT in its two
different read operations.

Lost Update Problem

The Lost Update problem arises when an update in the data is done over another
update but by two different transactions.

Example: Consider two transactions A and B performing read/write operations on

a data DT in the database DB. The current value of DT is 1000: The following table
shows the read/write operations in A and B transactions.
Database Administration and Management

Transaction A initially reads the value of DT as 1000. Transaction A modifies the

value of DT from 1000 to 1500 and then again transaction B modifies the value to
1800. Transaction A again reads DT and finds 1800 in DT and therefore the update
done by transaction A has been lost.

Advantages of Concurrency

In general, concurrency means, that more than one transaction can work on a
system. The advantages of a concurrent system are:

• Waiting Time: It means if a process is in a ready state but still the process
does not get the system to get execute is called waiting time. So,
concurrency leads to less waiting time.

• Response Time: The time wasted in getting the response from the cpu for
the first time, is called response time. So, concurrency leads to less
Response Time.

• Resource Utilization: The amount of Resource utilization in a particular

system is called Resource Utilization. Multiple transactions can run parallel
in a system. So, concurrency leads to more Resource Utilization.

• Efficiency: The amount of output produced in comparison to given input is

called efficiency. So, Concurrency leads to more Efficiency.

Disadvantages of Concurrency

• Overhead: Implementing concurrency control requires additional

overhead, such as acquiring and releasing locks on database objects. This
Database Administration and Management

overhead can lead to slower performance and increased resource

consumption, particularly in systems with high levels of concurrency.

• Deadlocks: Deadlocks can occur when two or more transactions are waiting
for each other to release resources, causing a circular dependency that can
prevent any of the transactions from completing. Deadlocks can be difficult
to detect and resolve, and can result in reduced throughput and increased
latency.

• Reduced concurrency: Concurrency control can limit the number of users

or applications that can access the database simultaneously. This can lead
to reduced concurrency and slower performance in systems with high
levels of concurrency.

• Complexity: Implementing concurrency control can be complex,

particularly in distributed systems or in systems with complex transactional
logic. This complexity can lead to increased development and maintenance
costs.

• Inconsistency: In some cases, concurrency control can lead to

inconsistencies in the database. For example, a transaction that is rolled
back may leave the database in an inconsistent state, or a long-running
transaction may cause other transactions to wait for extended periods,
leading to data staleness and reduced accuracy.
Database Administration and Management