CS230
Lecture 19
Lecturer
Department of CS
Faculty of
Computing&AI
Content
s
1 Transactions and Concurrency Control
Overview
ACID Properties of Transactions
Atomicity
Consistency
Isolation
Durability
2 Errors
and Recovery
in DBMS
3 Ensuring
ACID in
Transactions
aaEnsuring
Isolation
Overvie
w
move 100 PKR from Account_A to Account_B
Overvie
w
6 ACTIONS TO BE PERFORMED
1 read balance of account A in k_A
2 compute k_A -= 100
3 write new account balance k_A
in A
4 read balance of account B in k_B
5 compute k_B += 100
6 write new account balance k_B in B
Overvie
w
6 ACTIONS TO BE PERFORMED
1 read balance of account A in k_A
2 compute k_A -= 100
3 write new account balance k_A
in A
4 read balance of account B in k_B
5 compute k_B += 100
6 write new account balance k_B
in B
TRANSACTION: set of actions to be performed on a
database
Overvie
w
records are stored on disk
for updates, they are copied into memory and written back to
disk
ACID Properties of Transactions
Atomicity → each transaction shall be ”all or nothing” i.e. if
one part of the transaction fails, the entire transaction fails
Consistency → transaction should bring the database from
one valid state to another valid state
Isolation → concurrent execution of transactions should result
in a system state that would be obtained if transactions
were executed serially
Durability → a committed transaction should remain
committed even in the event of power loss, crashes, or errors
Atomicit
y
each transaction shall be ”all or nothing” i.e. if one part of the
transaction fails, the entire transaction fails
two possible outcomes of executing a transaction
transaction commits after competing all actions
transaction aborts after completing some actions
from user’s point of view, transaction always either executes all its
actions, or executes no actions at all.
Atomicit
y
TRANSACTION
Atomicit
y
TRANSACTION
Consistenc
y
transaction should bring the database from one valid state
to
another valid state
TRANSACTION
CONSTRAINT: account shouldn’t be in negative
if k_a < 0 at end of T:
ROLLBACK
Isolatio
n concurrent execution of transactions should result in a system
state
that would be obtained if transactions were executed serially
at beginning of T1 & T2:
Account_A=1000PKR, Account_B=2000PKR, Account_C=3000PKR
three possible states after T1 & T2 complete execution:
Account_A=900PKR,Account_B=2100PKR,Account_C=2800PKR
Account_A=900PKR,Account_B=2200PKR,Account_C=2800PKR
Account_A=900PKR,Account_B=2300PKR,Account_C=2800PKR
Isolatio
n concurrent execution of transactions should result in a system
state
that would be obtained if transactions were executed serially
at beginning of T1 & T2:
Account_A=1000PKR, Account_B=2000PKR, Account_C=3000PKR
three possible states after T1 & T2 complete execution:
Account_A=900PKR,Account_B=2100PKR,Account_C=2800PKR
Account_A=900PKR,Account_B=2200PKR,Account_C=2800PKR
Account_A=900PKR,Account_B=2300PKR,Account_C=2800PKR
Isolation
SOLUTION: Locking
Isolation
SOLUTION: Locking
Durability
a committed transaction should remain committed even in the
event of power loss, crashes, or errors
Error
s
cause the system to stop abruptly and cause the system to
crash
Power Failure
assume there exist dirty pages in main memory that
contain updates from committed transactions
if those changes are not written to disk, you might loose those
updates
Hardware Error
hard disk crash
for example, disk arm crashes
you won’t be able to restart your hard disk
D-RAM failure
you write data to memory address and you are not able to
recover
you write data to memory address and you get back data that
is wrong
Software Error
error in the DBMS itself
error in the application using the DBMS
Recovery
mechanism that allows us to get back to consistent
state
Und
o
you can to go back to previous versions of your file
kind of a recovery functionality
Backup
many software products create automatic backup
copies
snapshots of file are created in the background
Ensuring
Isolation
Locking
protects the database objects from other
transactions
held for duration of transaction
Ensuring
Isolation
Types of Locks
S-LOCK: shared locks for reads
X-LOCK: exclusive locks for writes
Ensuring
Isolation
transactions request locks
lock managers grant or denies lock request
transactions release locks
lock managers updates it’s internal lock-table
Ensuring
Isolation
