Transaction: a sequence of logical steps that will accomplish a single task or




Yüklə 23.72 Kb.
tarix20.04.2016
ölçüsü23.72 Kb.
INSS 651 Ch 10

Transaction Management and concurrency control


Transaction:
a sequence of logical steps that will accomplish a single task (or

what seems like a single task)


ex:

add an employee

enter an order

enroll a student in a course


A single task may require MANY changes to the database.
If all changes are NOT made database integrity will be lost

==

ex: Enroll a student



MARY to INSS651
steps:
start

1.check to see if student "MARY" exists…read only

2.check to see if class "INSS651" exists..read only

3. access enrollment table ..read only

update enrollment table ..make changes (update)

4. access student record/table ..read only

update student table ..make changes (update)

5. access class record/table

update class table

6.commit transaction


end

Transaction Prop.:

Page 401


ATOMICITY:
all phases (steps) must be completed, if not abort the trans.
DURABILITY:
permanence of DB consistent state achieved only when transaction is complete
SERIALIZABILITY:
Be able to serialize concurrent trans.
ISOLATION:
Be able to isolate data and can not be used by other trans.

Begin transaction:


step 1

step 2


.

.

.



END Transaction

(COMMIT)
if aborted for any reasons, ROLLBACK,

i.e., change back to previous commit
Transaction Mgt:
Recovery Process:
Backup:

create backup and use it to reconstruct DB


Transaction Log(Journal): (p 402)

Keeps track of all transactions that update the DB


Info kept in a typical log:
Trans. ID

time of trans.

type of trans.

object of action

BEFORE image

AFTER image


this allows for FORWARD & BACKWARD recovery

ex:
add student xyz to inss651


transaction ID transaction description
ST1 get student table (check to see if student xyz exists

get class table (check to see if inss651 exists)

get enrollment table

enroll student XYZ in inss651

commit
update student record (ie total number of hours)

commit


update class record (i.e., total number of students)

commit


a log may look like:
TRANS ID TIME ACTION OBJECT OF ACTION BEFORE AFTER

IMAGE IMAGE

ST1 8:00 START

ST1 8:04 INSERT ENROLLMENT(XYZ,INSS651..) NEW VALUE

ST1 8:16 MODIFY STUDENT (XYZ,..) OLD VALUE NEW VALUE

ST1 8:20 MODIFY CLASS(INSS651..) OLD VALUE NEW VALUE

ST1 8:30 COMMIT
Recovery Management (page 416)
FORWARD recovery:
if ALL or PART of the database has been destroyed then start

with most recent backup and update it using AFTER images from COMMITTED

transactions to this copy.

BACKWARD recovery:

if DB is not actually destroyed but trans. was not completed,

then we need to bring Db back to consistent state.


start with current DB and UNDO changes using BEFORE values of uncommitted trans.

CHECKPOINT:

which is most recent correct state?
with large users/transaction it is not clear what is the correct database state.
do we go back to archives?
most systems have CHECKPOINT.

Periodically system will refuse any new requests and will complete transactions

in progress. Usually done every 15 min. or so to match log and DB

Recovery THRU OFFsetting trans.
GIGO, a trans. Is entered incorrectly & commit is done.
ROLLBACK is NOT effective
create a DUMMY offsetting trans RECORD

EX:


IF WE WANT TO REDUCE BALACE BY $30 BUT BY MISTAKE WE PUT $50, an offsetting transaction would require a “dummy” transaction of $20 to ADD to the account
DIFFERENTIAL files:
DB is not updated directly, but a DIFFERENTIAL file containing the

changes is created


DB are periodically updated in batch mode. Similar to accounting systems
CONCURRENCY CONTROL:

two users trying to update a data item


ex: TOM needs to increase product A by $35 and MARY needs to

decrease it by 35



Initial value is 642.50

step 0


TOM reads information

from DB($642.50)step 1TOM updates data in

his work area $677.50


Value is 677.50

step 3

TOM updates DB


step 4

MARY reads info from

DB ($677.50)
step 5

MARY updates data in her work

area($642.50)
step 6:

Mary updates database $642.50

NO Problems!!!
A Different scenario

TOM reads information

from DB($642.50)

Mary read from DB($642.50)


TOM updates it to $677.50 Mary updates it

to $607.50

TOM updates DB $677.50
Mary updates DB

$607.50
An INCORRECT update!!!!

LOST updates: when we lose one of the updates
CONCURRENCY CONTROL with LOCKING (page 408)

LOCK:


Guarantees EXCLUSIVE use of data item to a CURRENT trans.LOCK GRANULARITY:level of lock usage

..DB..Table..Page..Record

..field

more overhead as we go from DB to field LOCK types:


Shared/exclusive locks:
Shared lock allows users to access data for "READ" only mode
Exclusive lock does not allow data access until user releases

data item thru commit, used for updates


exclusive lock is granted iff there are no other locks on the

data item


TWO-PHASE locking:
TOM has finished five steps of a six step transaction, but data

needed for sixth step is locked...


Growing Phase:

a trans. acquires all the locks needed for that transaction


Shrinking Phase:

transaction releases ALL locks


DEADLOCK:
two users are waiting for data that is locked by each other
(p 414)
Solutions:DEADLOCK prevention:

if a user cannot get all the locks, they are not provided ANY



locks
DEADLOCK detection:

break the deadlock by rolling back one user's transactions


Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©azrefs.org 2016
rəhbərliyinə müraciət

    Ana səhifə