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