Lab 14
Lab 14
Lab 14
FACULTY OF CS & IT
UNIVERSITY OF GUJRAT
Supporting Concurrent Access
Many applications require a lot of users to access the
data simultaneously (e.g. airline booking systems)
Uncontrolled simultaneous access can result in
confusion, so some controlling mechanism is required
A transaction
is a logical unit of work which takes the
DB from one consistent state to another, i.e. obeying
constraints
Itwill probably be made up of smaller operations which
temporarily cause inconsistency
What is a Transaction?
Database transactions are logical units of work which
must ALL be performed to maintain data integrity
A logical unit of work on a database may be a:
◦ An entire program
◦ A portion of a program
◦ A single command
The entire series of steps necessary to accomplish a
logical unit of work
Successful transactions change the database from one
CONSISTENT STATE to another CONSISTENT
STATE
(One where all data integrity constraints are satisfied)
Example Transaction
5
Transaction Support
6
State Transition Diagram for Transaction
7
4 Properties of a Transaction
Atomicity – All or Nothing
All parts of the transaction must be
completed and committed If not, it must
be aborted and rolled back.
The entire transaction is treated as a
single, indivisible unit of work which
must be performed completely or not at
all.
Atomicity
10
4 Properties of a Transaction
Consistency
Each user is responsible to ensure that their
transaction (if executed by itself) would leave
the database in a consistent state
If an operation is executed that violates the
database’s integrity constraints, the entire
transaction will be rolled back.
A successful transaction takes the database from
one state that is consistent with the rules to
another state that is also consistent with the
rules.
Consistency Example
12
4 Properties of a Transaction
Isolation
The final effects of multiple simultaneous
transactions must be the same as if they
were executed one right after the other.
Data used within a transaction cannot be
used by another transaction until the first
transaction is completed. (or it must
appear that this happened!). The partial
effects of incomplete transactions should
not be visible to other transactions.
Isolation Example
14
4 Properties of a Transaction
Durability
If a transaction has been committed, the
DBMS must ensure that its effects are
permanently recorded in the database
(even if the system crashes)
So the effects of a committed transaction
are not lost in a failure.
Need for Concurrency Control
Three examples of potential problems
caused by concurrency:
◦ Lost update problem.
◦ Uncommitted dependency problem.
◦ Inconsistent analysis problem.
16
Lost Update Problem
Successfully completed update is
overridden by another user.
T1 withdrawing £10 from an account with
balx, initially £100.
T2 depositing £100 into same account.
Serially, final balance would be £190.
17
Lost Update Problem
18
Uncommitted Dependency Problem
Occurs when one transaction can see
intermediate results of another transaction
before it has committed.
T updates bal to £200 but it aborts, so bal
4 x x
should be back at original value of £100.
T has read new value of bal (£200) and uses
3 x
value as basis of £10 reduction, giving a new
balance of £190, instead of £90.
19
Uncommitted Dependency Problem
20
Inconsistent Analysis Problem
Occurs when transaction reads several values but
second transaction updates some of them during
execution of first.
Sometimes referred to as dirty read or unrepeatable
read.
T is totaling balances of account x (£100), account
6
y (£50), and account z (£25).
Meantime, T has transferred £10 from bal to
5 x
balz, so T6 now has wrong result (£10 too high).
21
Inconsistent Analysis Problem
22
Transaction Management with SQL
SQL Statements Commit / Rollback
When a transaction sequence is initiated it must continue
through all succeeding SQL statements until:
1. A Commit Statement is Reached
2. A Rollback Statement is Reached
3. The End of the Program is Reached (Commit)
4. The Program is Abnormally Terminated (Rollback)