Lecture7 Adv. DB
Uploaded by
Menna SaedLecture7 Adv. DB
Uploaded by
Menna SaedLecture 7-
Transaction
Processing
By Dr. Shaheera Rashwan
Transaction Definition
• It is a logical unit of database processing that includes
one or more access operations. (read-retrieval, write-
insert or update).
• It is a unit of program execution that accesses and if
required updates various data items.
Transaction Processing Example
• Consider a transaction that involves transferring $1700
from a customer’s savings account to a customer’s
checking account.
• This transaction involves two separate operations:
debiting the savings account by $1700 and crediting the
checking account by $1700.
• If one operation succeeds but the other doesn’t, the
books of the bank will not balance.
How Transactions can be processed?
• In Single user system, only one user at a time can use the system
while in Multi-user system, many users can access the system
concurrently.
• Concurrency can be provided through :
1.Interleaved Processing –
In this, the concurrent execution of processes is interleaved in a
single CPU. The transactions are interleaved, meaning the second
transaction is started before the primary one could finish.
2.Parallel Processing –
It is defined as the processing in which a large task into various
smaller tasks and smaller task also executes concurrently on several
nodes. In this, the processes are concurrently executed in multiple
CPUs.
Transaction Properties
• Transaction has ACID properties.
• ACID stands for Atomicity, Consistency, Isolation and
Durability
ACID Properties
1.Atomicity: All the operations in a transaction are
considered to be atomic and as one unit. If system fails
or any read/write conflicts occur during transaction the
system needs to revert back to its previous state.
Atomicity is maintained by the Transaction Management
Component.
2.Consistency: Every transaction should lead to
database connection from one valid state to other valid
state. If system fails because of invalid data while doing
an operation revert back the system to its previous
state. Consistency is maintained by the Application
manager.
ACID Properties
3. Isolation: If multiple transactions are executing on single
database, each transaction should be isolated from other
transaction. If multiple transactions are performed on single
database, operation from any transaction should not interfere
with operation in other transaction. Isolation is maintained by the
concurrency control manager.
4. Durability: Durability means the changes made during the
transactions should exist after completion of transaction.
Changes must be permanent and must not be lost due to any
database failure. It is maintained by the recovery manager.
ACID Properties example
• A has an account with an amount of Rs 150. B has an account with an
amount of Rs 50. A is transferring amount Rs 100 to B’s account.
1.Atomicity: Deduct amount Rs100 from A’s account. Add amount Rs 100 to
B’s account. If system fails to add amount in B’s account after deducting
from A’s account, revert the operation on A’s account.
2.Consistency: The sum amount in A’s account and B’s account should be
same before and after the transaction completes (Rs 200)
3.Isolation: If there is any other transaction (let between A and C) is going
on, it should not affect the transaction between A and B i.e., both the
transactions should be isolated.
4.Durability: It may happen system gets crashed after the completion of all
operations then, after restarting it should preserve all the changes. The
amount in A’s and B’s account should be same before and after the system
restart.
Transaction Control Commands
• COMMIT: The COMMIT command is used to save
changes made by the transaction in the database.
• ROLLBACK: The ROLLBACK command is used to undo
saved changes made by the transaction in the
database.
• SAVEPOINT: SAVEPOINT is a point in the transaction
where transaction can be rolled back without entire
transaction rollback.
Schedule
• Schedule is process of grouping transactions into one
and executing them in a predefined order.
• It is a sequence of execution of operation from various
transactions.
• Schedule is required in database because when multiple
transactions execute in parallel, they may affect the
result of each other.
• So, to resolve this the order of the transactions are
changed by creating a schedule.
Types of Schedules
1.Serial Schedule: A schedule in which the transactions
are defined to execute one after another is called serial
schedule.
2.Non- Serial Schedule: A schedule in which the
transactions are defined to execute in any order is
called non-serial schedule.
Recoverable and Non-Recoverable Schedule
• Schedules in which transactions commit only after all
transactions whose changes they commit are called
recoverable schedule.
• If some transaction Tj is reading value updated or
written by some other transaction Ti then, the commit of
Tj must occur after the commit of Ti.
Cascading Abort and
Cascadeless Schedule
• If in a schedule, failure of one transaction causes
rollback or abort to other dependent transaction such a
schedule is called Cascading abort. It leads to wastage
of CPU time.
• If in a schedule, a transaction is not allowed to read a
data item until the last transaction that has written it is
committed or aborted, then such a schedule is called
Cascadeless schedule.
Example
Strict Schedule
• If in a schedule, a transaction is neither allowed to read
nor write a data item until the last transaction that has
written it is committed or aborted, then such schedule is
called strict schedule.
• Strict schedule implements more restrictions than
cascadeless schedule.
Example
OLTP
• On-Line Transaction Processing (OLTP)
System refers to the system that manage transaction
oriented applications. These systems are designed to
support on-line transaction and process query quickly
on the Internet.
For example: POS (point of sale) system of any
supermarket is a OLTP System.
Queries accessed by OLTP
• An OLTP system is an online database modifying
system. So it supports database query like INSERT,
UPDATE and DELETE information from the database.
• Consider a POS system of a supermarket, Below are the
sample queries that it can process –
• Retrieve the complete description of a particular product
• Filter all products related to any particular supplier
• Search for the record of any particular customer.
• List all products having price less than Rs 1000.
OLAP vs OLTP
• OLAP stands for Online Analytical Processing. OLAP
systems have the capability to analyze database
information of multiple systems at the current time. The
primary goal of OLAP Service is data analysis and not
data processing.
• OLTP stands for Online Transaction Processing. OLTP has
the work to administer day-to-day transactions in any
organization. The main goal of OLTP is data processing
not data analysis.
References
• https://www.geeksforgeeks.org/introduction-to-transaction-processin
g/
• https://www.geeksforgeeks.org/transaction-control-in-dbms/
• https://www.geeksforgeeks.org/on-line-transaction-processing-oltp-s
ystem-in-dbms/
• https://www.geeksforgeeks.org/difference-between-olap-and-oltp-in-
dbms/
Any Questions?
