Transactions
KALINGA INSTITUTE OF INDUSTRIAL
TECHNOLOGY
School Of Computer
Engineering
Dr. Hrudaya Kumar Tripathy
Professor
School of Computer Engineering,
Kalinga Institute of Industrial Technology (KIIT),
Deemed to be University,Odisha
3 Credit Lecture Note 14
Chapter Contents
2
qTransaction Concept
qACID Properties
qTransaction States
Transaction Concept
3
Ø A transaction is a unit of program execution that accesses
and possibly updates various data items.
Ø A transaction is a logical unit of work that contains one or
more SQL statements. The effects of all the SQL statements in
a transaction can be either all committed or all rolled back.
Ø A transaction that changes the contents of the database must
alter the database from one consistent database state to another.
Transaction Concept
4
• A transaction is a unit of program execution that accesses and
possibly updates various data items.
• E.g., transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
• Two main issues to deal with:
– Failures of various kinds, such as hardware failures and system
crashes
– Concurrent execution of multiple transactions
ACID Properties
5
Ø A transaction is a unit of program execution that accesses and possibly updates various
data items.
ü Atomicity: Either all operations of the transaction are reflected properly in the
database, or none are. Atomicity requires that all operations of a transaction be
completed; if not, the transaction is aborted by rolling back all the updates done
during the transaction.
ü Consistency: Consistency means execution of a transaction should preserve the
consistency of the database, i.e. a transaction must transform the database from one
consistent state to another consistent state.
ü Isolation: Though multiple transactions may execute concurrently, the system
guarantees that, for every 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 transactions 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. Durability ensures that once
transaction changes are done or committed, they can’t be undone or lost, even in the
event of a system failure.
6
• Consider a transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
• Atomicity requirement
– If the transaction fails after step 3 and before step 6, money will be “lost”
leading to an inconsistent database state
• Failure could be due to software or hardware
– The system should ensure that updates of a partially executed transaction are
not reflected in the database
7
• Consistency requirement in above example:
– The sum of A and B is unchanged by the execution of the
transaction
• In general, consistency requirements include
• Explicitly specified integrity constraints such as primary keys
and foreign keys
• Implicit integrity constraints
– e.g., sum of balances of all accounts, minus sum of loan
amounts must equal value of cash-in-hand
• A transaction, when starting to execute, must see a consistent
database.
• During transaction execution the database may be temporarily
inconsistent.
• When the transaction completes successfully the database must be
consistent
– Erroneous transaction logic can lead to inconsistency
8
• Isolation requirement — if between steps 3 and 6 (of the fund transfer
transaction) , another transaction T2 is allowed to access the partially updated
database, it will see an inconsistent database (the sum A + B will be less than it
should be)
• T1 T2
1. read(A)
2. A := A – 50
3. write(A)
read(A), read(B), print(A+B)
4. read(B)
5. B := B + 50
6. write(B
• Isolation can be ensured trivially by running transactions serially
– That is, one after the other.
• However, executing multiple transactions concurrently has significant benefits, as
we will see later.
9
• Durability requirement — once the user has
been notified that the transaction has
completed (i.e., the transfer of the $50 has
taken place), the updates to the database by the
transaction must persist even if there are
software or hardware failures.
9
Transaction States
10
Ø Active state: This state is the initial state of a transaction. The
transaction stays in this state while it is executing
Ø Partial Committed state: A transaction is partial committed after
its final statement has been executed. A transaction enters this
state immediately before the commit statement
Ø Failed state: A transaction enters the failed state after the
discovery that normal execution can no longer proceed
Ø Aborted state: A transaction is aborted after it has been rolled
back and the database is restored to its prior state before the
transaction
Ø Committed state: Committed state occurs after successful
completion of the transaction
Ø Terminate: Transaction is either committed or aborted
Transaction States
11
Ø When a transaction enters the aborted state, the system has two options:
ü Restart the transaction: If the transaction was aborted as a result of a
hardware failure or some software error (other than logical error), it can be
restarted
ü Kill the transaction: If the application program that initiated the
transaction has some logical error
12