Database Systems

TRANSACTION
MANAGEMENT
 INTRODUCTION

Transaction management is an integral process

of a DBMS during which transaction
management software oversees, coordinates
and executes any given attempted transaction.
 TRANSACTION

A transaction is a sequence of one or more operations (reads or

writes) which reflects a single real-world transition.
It is a logical unit of work in the database, that must be completed in its
entirety to ensure correctness

It may be an entire program, a part of a program, or

a single command (for example, the SQL command
INSERT or UPDATE), and it may involve any number
of operations on the database.
 TRANSACTION EXAMPLE
When a person attempts to withdraw money from an ATM, transaction
management software processes the necessary database queries and changes
in order to check their account balance, subtract the requested amount,
update the bank’s records and release the dispensed cash.
All of these steps are considered one new transaction, and the transaction
management system ensures that the entire process is completed to prevent
any inconsistencies in the bank’s database and preserve an accurate ledger.
 TRANSACTION
OPERATIONS

BEGIN_TRANSACTION
This marks the beginning of transaction Read _item(X)
execution. Reads a database item named X into
READ or WRITE a program variable
These specify read or write operations on Write_ item(X).
the database items that are executed as Writes the value of program variable
part of a transaction X into the database item named X.
 TRANSACTION
OPERATIONS

END_TRANSACTION. ROLLBACK (or ABORT).

This specifies that READ and WRITE This signals that the transaction has
transaction operations have ended and ended unsuccessfully, so that any
marks the end of transaction execution. changes or effects that the
COMMIT_ TRANSACTION. transaction may have applied to the
This signals a successful end of the database must be undone.
transaction so that any changes (updates)
executed by the transaction can be safely For recovery purposes, the system
committed to the database and will not needs to keep track of when each
be undone transaction starts, terminates, and
commits or aborts
 STATE TRANSITION

A transaction is an atomic unit of work that should either be

completed in its entirety or not done at all.
A transaction should always transform the database from one
consistent state to another.
A transaction can have one of two outcomes.
1. If it completes successfully, the transaction is said to have
committed and the database reaches a new consistent state.
2. On the other hand, if the transaction does not execute successfully,
the transaction is aborted
 STATE TRANSITION

If a transaction is aborted, the database must be restored to the

consistent state it was in before the transaction started. Such a
transaction is rolled back or undone.

A committed transaction cannot be aborted.

If we decide that the committed transaction was a mistake, we
must perform another compensating transaction to reverse its
effects
STATE TRANSITION

State Diagram
 STATE TRANSITION
1. Active
Occurs immediately after the transaction starts its execution, where it
can execute its READ and WRITE operations.
2. Partially committed
Occurs when the transaction ends.
At this point, some recovery protocols need to ensure that a system
failure will not result in an inability to record the changes of the
transaction permanently ( by recording changes in the system log)
3. Committed
Occurs when the transaction is successful.
When a transaction is committed, it has concluded its execution
successfully and all its changes must be recorded permanently in the
database, even if a system failure occurs
 STATE TRANSITION

4.Failed
Occurs if one of the checks fails or if the transaction is aborted during its
active state. This transaction cannot proceed further
5.Aborted
If any of the checks fails and the transaction has reached a failed state, then
the recovery manager rolls back all its WRITE operations on the database
to bring the database back to its original state where it was prior to the
execution of the transaction

State Diagram
 MOTIVATION

1. Recovery & Durability

Keeping the DBMS data consistent and durable in the face of crashes,
aborts, system shutdowns, etc. (Resilience to system failures)
2. Concurrency
Achieving better performance by parallelizing transactions without
creating anomalies
TRANSACTION PROPERTIES

Atomicity Consistency

Isolation Durability

These are summarized as ACID properties

 TRANSACTION PROPERTIES

Atomicity Consistency
“All or Nothing” “Validity of State”
A transaction is an indivisible unit that is A transaction must transform the database
either performed in its entirety or is not from one consistent state to another
performed at all. consistent state.
It is the responsibility of the recovery It moves from one consistent state of
subsystem of DBMS to ensure atomicity integrity to another.
DBMS logs all actions so that it can undo the It is the responsibility of both the DBMS and
actions of an aborted transaction. application developers to ensure consistency
-This logging mechanism ensures atomicity by enforcing all constraints
-Managing constraints ensures consistency
 TRANSACTION PROPERTIES

Isolation Durability
“No side effects” “Permanent change”
A transaction is isolated from the effects The effects of a successfully completed
of other concurrent transactions. (committed) transaction are permanently
Transactions execute independently of recorded in the database and must not be
one another. lost because of subsequent failure
Effects of a transaction is the same as one A transaction commit should be persistence, so
running after another. Partial effects of that recovery is successful even if the system
incomplete transactions should not be crashes immediately after a commit
visible to other transactions. -Logging mechanism ensures durability.
-Serialization and locking ensures isolation replication also occurs in other systems
ACID PROPERTIES
 CONCURRENCY

Concurrency: Multiple transactions are allowed to run simultaneously in the system

ADVANTAGES
Increased processor and disk utilization leading to better transaction throughput.
One transaction can be using the CPU while another transaction reads from the disk.
Reduced average response time for transactions
Short transactions need not to wait for long ones
 CONCURRENCY
CONTROL
The process of managing simultaneous operations on the database without having them
interfere with one another.

The DBMS must handle concurrency such that;

Isolation is maintained: Users must be able to execute each transaction as if
they were the only user.
DBMS handles the details of interleaving various transactions
Consistency is maintained: Transactions must leave the DB in a consistent
state
DBMS handles the details of enforcing integrity constraints
NEED FOR CONCURRENCY
CONTROL
Although two transactions may be correct in themselves, interleaving
of operations may produce an incorrect result.

Three examples of potential problems caused by concurrency:

–Lost update problem
–Uncommitted dependency problem
–Inconsistent analysis problem.
 LOST UPDATE PROBLEM
This is the case where by a successfully completed update is overridden by
another user.

Caused by a WRITE
WRITE conflict

Avoided by hiding the

balx until T2 is done

Transactions T1 and T2 start at nearly the same time, and both read the balance as £100.
T2 increases balx by £100 to £200 and stores the update in the database.
Meanwhile, transaction T1 decrements its copy of balx by £10 to £90 and stores this value
in the database, overwriting the previous update, and thereby ‘losing’ the £100
previously added to the balance
 UNCOMMITTED DEPENDENCY PROBLEM
Occurs when one transaction can see intermediate results of another transaction
before it has committed.
Caused by a WRITE
READ conflict

Preventing T3 from
reading balx until after
the decision has been
made

Transaction T4 updates balx to £200 but it aborts the transaction so that balx should be
restored to its original value of £100.
However, by this time transaction T3 has read the new value of balx (£200) and is using
this value as the basis of the £10 reduction, giving a new incorrect balance of £190, instead
of £90. The value of balx read by T3 is called dirty data, giving rise to the alternative name,
the dirty read problem.
 INCONSISTENT ANALYSIS PROBLEM
Occurs when transaction reads several values but a second transaction updates some
of them during execution of first.
A summary transaction T6 is
executing concurrently with
transaction T5.
Transaction T6 is totaling the
balances of account x (£100),
account y (£50), and account z
(£25).
However, in the meantime,
transaction T5 has transferred £10
from balx to balz, so that T6 now
Avoided by preventing transaction T6 from reading balx has the wrong result (£10 too
and balz until after T5 has completed its updates. high).
 SCHEDULING
Sequences that indicate the order in which instructions of concurrent transactions
are executed
ADVANTAGES
Ensures concurrency control and consistency.
Allows multiple transactions to execute simultaneously without conflicts.
Supports priority management
Scheduling can assign priorities to transactions based on factors such as deadlines,
resource needs, or criticality.
Facilitates recovery
Scheduled transactions maintain a well-ordered log of operations, making recovery
easier in case of failure.
 TYPES OF SCHEDULES

Serial Schedules
Non Serial Schedules
A schedule where the operations of each
A schedule where the operations from a set
transaction are executed consecutively without
of concurrent transactions schedule are
any interleaved operations from other
interleaved.
transactions
Why interleave?
For example, if we have two transactions T1 and
Individual Transactions might be slow
T2, serial order would be T1 followed by T2, or
and block other users during execution
T2 followed by T1.
Disk access may be slow. This lets some
Thus, in serial execution there is no interference
transactions use CPUs while others are
between transactions, since only one is
accessing the disk space
executing at any given time.
A SERIALIZABLE SCHEDULE IS A SCHEDULE THAT IS EQUIVALENT
TO SOME SERIAL EXECUTION OF THE TRANSACTIONS
 TYPES OF SCHEDULES
Serial Schedules Non Serial Schedules
 CONCURRENCY CONTROL
TECHNIQUES

Locking Timestamping
Mechanism Mechanism
 LOCKING

Transaction uses locks to deny access to other transactions and so

prevent incorrect updates.
If the item is not already locked by another transaction, the lock will be
granted.
If a lock request cannot be granted, the transaction must wait.
Lock-based concurrency control is pessimistic
Assumption – conflicts are likely to happen, and locking prevents this
 LOCKING
There are two types of locks;
1. Shared lock - allows a transaction to read the item but not update it.
Since read operations cannot conflict, it is permissible for more than one
transaction to hold shared locks simultaneously on the same item
2. Exclusive lock - allows a transaction to both read and update the item.
As long as a transaction holds the exclusive lock on the item, no other
transactions can read or update that data item

A conflict in serialization can occur. To prevent this, a protocol is applied known

as 2 Phase Locking
 2 PHASE LOCKING
According to the rules of this protocol, every transaction can be divided into
two phases:
Growing phase, in which it acquires all the locks needed but cannot release
any locks.
Shrinking phase, in which it releases its locks but cannot acquire any new
locks.
The rules are;
A transaction must acquire a lock on an item before operating on the item.
The lock maybe read or write, depending on the type of access needed.
Once the transaction releases a lock, it can never acquire any new locks.
PREVENTING LOST UPDATE PROBLEM
PREVENTING UNCOMMITTED
DEPENDENCY PROBLEM
 PREVENTING
INCONSISTENT
ANALYSIS
PROBLEM
 DEADLOCK
Results when two (or more) transactions are each waiting for locks to be
released that are held by the other.

To break the deadlock, one

or more transaction must
be aborted
 DEADLOCK PREVENTION AND RECOVERY
DBMS looks ahead to see if transaction would A transactions that requests a lock will only
cause deadlock and never allows deadlock to wait for a system-defined period of time.
occur. If lock has not been granted within this
The use of timestamps can also prevent deadlocks; period, lock request times out.
Wait-Die - only an older transaction can wait In this case, DBMS assumes transaction may
for younger one, otherwise transaction is be deadlocked, even though it may not be,
aborted (dies) and restarted with same and it aborts and automatically restarts the
timestamp. transaction.
Wound-Wait - only a younger transaction can
wait for an older one. If older transaction
requests lock held by younger one, younger
one is aborted (wounded).
 TIMESTAMPING

This is a concurrency control protocol that orders transactions in such

a way that older transactions, transactions with smaller timestamps,
get priority in the event of conflict.
Conflict is resolved by rolling back and restarting a transaction.
No locks so no deadlock.
TIMESTAMPING
EXAMPLE
 Database Systems

THANK YOU
FOR LISTENING