Transactions - PowerPoint PPT Presentation

About This Presentation
Title:

Transactions

Description:

a logical unit of work was successfully completed. all the updates ... serialisability: an interleaved execution has the same result as some serial execution ... – PowerPoint PPT presentation

Number of Views:67
Avg rating:3.0/5.0
Slides: 36
Provided by: marian5
Category:

less

Transcript and Presenter's Notes

Title: Transactions


1
Transactions
3
2
Outline
  • transactions - generalities
  • concurrency control
  • concurrency problems
  • locking

3
1
4
Transactions example
  • Parts (P_id, P_name, Colour, Weight, Total_qty)
  • Contracted (S_id, P_id, Qty)
  • add a new contract for S4 for 200 pieces of P1

5
Transaction
  • logical unit of work
  • sequence of database operations
  • transforms a consistent state of a db into
    another consistent state
  • between operations the db can be inconsistent

6
Transaction Processing
  • do not allow for
  • one operation to be performed and the other ones
    not
  • principle of transaction processing support
  • if some operations are executed and then a
    failure occurs (before the planned termination)
    then those operations will be undone

7
Transaction Manager
  • COMMIT TRANSACTION
  • a logical unit of work was successfully completed
  • all the updates can be made permanent
  • ROLLBACK TRANSACTION
  • unsuccessful end of transaction
  • all the attempted updates must be rolled back
  • they are issued from applications

8
Example
  • execute(BEGIN TRANSACTION)
  • execute(INSERT (S4, P1, 200) INTO
    Contracted)
  • if(/any error occurred/) then go to undo
  • execute( UPDATE Parts WHERE P_id P1
  • SET Total_qty Total_qty 200)
  • if(/any error occurred/) then go to undo
  • execute(COMMIT TRANSACTION)
  • go to finish
  • undo execute(ROLLBACK TRANSACTION)
  • finish return

9
SQL Support
  • COMMIT and ROLLBACK
  • No BEGIN TRANSACTION (in SQL2 and Oracle)
  • all data definition and data manipulation
    statements are transaction initiating
  • PostgreSQL provides
  • BEGIN TRANSACTION

10
At the COMMIT point
  • all updates, since the previous commit, are made
    permanent (will not be undone)
  • all database positioning and all tuple locks are
    lost

11
ACID Properties of Transactions
  • Atomicity
  • all or nothing
  • Consistency
  • preserve database consistency
  • Isolation
  • transactions are isolated from one another
  • Durability
  • committed transaction ? updates are performed

12
2
13
Concurrency
  • more than one transaction have access to data
    simultaneously

14
Three concurrency problems
  • the lost update
  • the uncommitted dependency
  • the inconsistent analysis

15
The lost update problem
Transaction A time Transaction B RETRIEVE
(t) t1 t2 RETRIEVE (t) UPDATE
(t) TO (t1) t3 t4 UPDATE (t) TO
(t2)
16
The uncommitted dependency problem
Transaction A time Transaction B
t1 UPDATE (t) RETRIEVE (t) t2
t3 ROLLBACK
17
The uncommitted dependency problem
Transaction A time Transaction B
t1 UPDATE p UPDATE p t2
t3 ROLLBACK
18
The inconsistent analysis problem
19
Issue
  • all these problems may lead to an inconsistent
    (incorrect) database
  • is there a criterion based on which to decide
    weather a certain set of transaction, if executed
    concurrently, leads to an incorrect database or
    not?

20
Serialisability
  • criterion for correctness for concurrent
    execution of transactions
  • the interleaved execution of a set of
    transactions is guaranteed to be correct if it is
    serialisable
  • correct ? the DB is not in an inconsistent state
  • serialisability an interleaved execution has the
    same result as some serial execution

21
Serialisable schedule
22
Notes
  • the schedules described in the concurrency
    problems examples were not serialisable
  • neither A-then-B nor B-then-A
  • two different interleaved transactions might
    produce different results, yet both can be
    considered correct

23
3
24
Two-phase locking theorem
  • if all transactions obey the two phase locking
    protocol then all possible interleaved schedules
    are serialisable
  • i.e., they can be executed concurrently, because
    they will leave the database in a consistent state

25
Two-phase locking protocol
  • 1.before operating on an object a transaction
    must acquire a lock on that object
  • 2.after releasing a lock a transaction must not
    go on to acquire any more locks
  • phase1 (growing) acquire locks (not
    simultaneously)
  • phase2 (shrinking) release locks (no further
    acquisitions allowed)
  • usually locks are released by the COMMIT or
    ROLLBACK operation
  • in practice
  • trade-off between release lock early and acquire
    more locks and the two phase locking protocol

26
Locking
  • usually, applicable to tuples
  • types
  • X, exclusive - write
  • S, shared - read
  • rules
  • compatibility matrix

27
Compatibility matrix
28
Data access protocol
  • retrieve tuple ? acquire S lock (on that tuple)
  • update tuple ? acquire X lock (on that tuple), or
    promote the S lock it holds (if it holds one)
  • implicit request
  • if request for lock is denied ? transaction goes
    in wait state until the lock is released
  • livelock - first come first served
  • X locks are held until end of transaction (COMMIT
    or ROLLBACK) (two phase locking protocol)

29
The uncommitted dependency problem OK
Transaction A time Transaction
B t1 UPDATE (t) (X lock on
t) RETRIEVE (t) t2 (request X lock on t)
wait t3 COMMIT / ROLL.. wait (release
X lock on t) resume RETRIEVE (t) t4 (acquire S
lock on t)
30
The lost update problem dead-lock
Transaction A time Transaction
B RETRIEVE p t1 (acquire S lock on
p) t2 RETRIEVE p
(acquire S lock on p) UPDATE p t3
(request X lock on p denied) t4
UPDATE p wait (request X lock on
p wait denied) wait
wait
31
Locking
  • solves the three basic problems of concurrency
  • theorem
  • if all the transactions of a set S of
    transactions comply with the two phase locking
    protocol, then all their possible interleaved
    executions (schedules) are serialisable
  • however, not all schedules produce the same
    result
  • think of examples
  • introduces another problem deadlock

32
Deadlock
  • two or more transaction are waiting for the other
    to release a lock
  • in practice usually two transactions
  • detect a deadlock
  • cycle in the wait-for graph, or
  • timing mechanism
  • break a deadlock
  • rollback a victim transaction
  • what happens to the victim?

33
Further topics
  • two phase locking protocol - not feasible in
    practice (not efficient)
  • levels of isolation
  • degree of interference
  • intent locking
  • locking granularity
  • SQL support
  • no explicit locking facilities
  • it supports different isolation levels (with
    locking behind the scenes)

34

35
Conclusions
  • transactions
  • concurrency
  • concurrency problems
  • locking
Write a Comment
User Comments (0)
About PowerShow.com