Transaction Management

1
Transaction Management

• Andrew Marek
• Rade Todorovich
• Elisha Dragen
• Sean Yang
• Liang Shen
• Lei Wang

2
DBMS Functions

• Transaction support
• Concurrency control services
• Recovery services
• Provide reliable and consistent state

3
Transaction Support

• Transaction
• An action, or series of actions, by a single user
  or program, which reads or updates the contents
  of the database
• Example of complete transaction
• Delete a staff member from database
• Assign a new staff member to properties for rent
  that the deleted staff member managed
• Avoid inconsistent state

4
Transaction Support cont

• Transactions have one of two outcomes
• Committed new consistent state
• Aborted did not execute successfully
• Must roll back or undo transaction
• A committed transaction cannot be undone
• Can use a compensating transaction to reverse
  effects

5
Transaction Properties

• Atomicity transactions are indivisible units
  performed in their entirety or not at all
• Consistency one consistent state to another
• Isolation transactions execute independently
  from one another (i.e. partial effects of one
  transaction should not be visible to another)
• Durability successful transactions should be
  recorded and not lost (recovery system)

6
Database Architecture

• Transaction Manager coordinates on behalf of
  applications
• Scheduler concurrency control
• Recovery Manager maintain consistent state if
  failure occurs
• Buffer Manager transfer of data between disk
  and main memory

Transaction Manager
Scheduler
Recovery Manager
Buffer Manager
File Manager
7
Concurrency Control
The process of managing simultaneous operations
on the database without having them interfere
with one another.

• Chapter 19.2

8
Concurrency control
!

• Problems
• Lost Update Problem
• Uncommitted Dependency Problem
• Inconsistent Analysis Problem

9
Lost Update

• Two transactions (T1, T2) are executing at the
  same time.
• T1 withdrawing 10, T2 depositing 100 Initial
  balance is 100.

Solution
10
Uncommitted Dependency

• T4 updates the balance but aborts the transaction
• T3 reads wrong balance

Solution
11
Inconsistent Analysis

• T6 tries to total the account balances. T6 is
  read-only transaction.
• T5 transfers 10 from account x to account z.

Solution
12
Serializability and Recoverability

• Schedule A sequence of the operations by a set
  of concurrent transactions that preserves the
  order of the operations in each of the individual
  transactions.
• Serial Schedule A schedule where the operations
  of each transactions are executed consecutively
  without any interleaved operations from other
  transactions.
• Nonserial Schedule A schedule where the
  operations from a set of concurrent transactions
  are interleaved.
• Serializable Schedule Nonserial schedule that
  produces the same results as serial schedule.
• If two transactions only read a data item, they
  do not conflict and order is not important.
• If two transactions either read or write
  completely separate data items, they do not
  conflict and order is not important.
• If one transaction writes a data item and another
  either reads or writes the same data item, the
  order of execution is important.

13
Serializability
Conflict Serializable Schedule
x
Ti?Tj, if Tj reads after Ti writes Ti?Tj, if Tj
writes after Ti reads Ti?Tj, if Tj writes after
Ti writes
T7
T8
No cycle found !!!
y
14
Serializability
Not conflict serializable schedule
T9 transfers 100 from y to x. T10 increases both
balances by 10. Balance increase needs to be
after the transfer
x
Ti?Tj, if Tj reads after Ti writes Ti?Tj, if Tj
writes after Ti reads Ti?Tj, if Tj writes after
Ti writes
Incorrect locking
T9
T10
Cycle found !!!
y
15
Serializability
View Serializability

• Two schedules S1 and S2 with n transactions are
  View Serializable if
• For each data item x S1(Ti(read(x))) ?
  S1(Ti(read(x))) , initial read.
• For each read in S1(Tj(write(x)),Ti(read(x))) ?
  S2(Tj(write(x)),Ti(read(x)))
• For each data item x S1(Ti(write(x))) ?
  S2(Ti(write(x))), last write.

T12, T13 violate second rule and perform blind
writes
16
Recoverability

• Recoverable Schedule is a schedule where, for
  each pair of transactions Ti and Tj, if Tj reads
  a data item previously written by Ti, then the
  commit operation of Ti precedes the commit
  operation of Tj.

17
Locking Methods

• Locking is a procedure used to control concurrent
  access to data. When one transaction is accessing
  the database, a lock may deny access to other
  transactions to prevent incorrect results.
• Shared Lock If a transaction has a shared lock
  on a data item, it can read but not update the
  item.
• Exclusive lock If a transaction has an
  exclusive lock on a data item, it can both read
  and update the item.

18
Granularity of Data Items
Granularity The size of data items chosen as the
unit of protection by a concurrency control
protocol Tradeoff between size and performance.
Multiple-granularity locking

• The entire DB
• A file
• A page
• A record
• A field

Hierarchy of granularity
IS-Intention Shared lock IX-Intention Exclusive
DB
Level 0

• 2PL is used
• No lock can be granted once any node has been
  unlocked
• No node may be locked until its parent is locked
  by an intention lock
• No node may be unlocked until all its descendants
  are unlocked

Level 1
File1
File2
File3
Page1
Page2
Page3
Level 2
Record1
Record2
Level 3
Field1
Field2
Level 4
19
Locking Methods
Problem definition
Incorrect Locking Schedule
This is what we want !!!
This is wrong !!!
20
Locking Methods

• Two-phase locking (2PL)-A transaction follows the
  two-phase locking protocol if all locking
  operations precede the first unlock operation in
  the transaction.

Preventing the lost update problem using 2PL
Problem

• T2 acquires the exclusive lock and proceeds with
  transaction.
• T1 requests the exclusive lock but gets it when
  it has been released by T2.

21
Two-phase locking
Preventing the uncommitted dependency problem
using 2PL

• T4 acquires exclusive lock and updates the
  balance
• T4 aborts transaction and rollbacks while
  releasing the lock.
• Since T3 requested the lock it will get it after
  it has been released by T4

Problem statement
22
Two-phase locking
Preventing the inconsistent analysis problem
using 2PL
Problem statement
23
Two-phase Locking
Cascading rollback
Depends on T15 so it must rollback as well
Exclusive Lock
Depends on T14 so it must rollback as well
Shared Lock
Aborts and rollback
24
Deadlock

• Deadlock may occur when two (or more)
  transactions are each waiting for locks to be
  released that are held by the other.

• Deadlock prevention
• Timeouts
• Transaction timestamps
• Wait-Die algorithm
• Wound-Wait algorithm

25
Deadlock

• Deadlock Detection handled by wait-for graph
  (WFG).

• Create a node for each transaction.
• Create a directed edge Ti?Tj, if transaction Ti
  is waiting to lock an item that is currently
  locked by Tj.

y
T17
T18
x

• Deadlock Recovery one or more transaction
  abortion.
• Choice of deadlock victim
• How far to roll a transaction back
• Avoiding starvation

26
Concurrency Control Timestamping Methods

• Timestamp A unique identifier created by the
  DBMS that indicates the relative starting time of
  a transaction.
• Timestamping A concurrency control protocol
  that orders transactions in such a way that older
  transactions, transactions with smaller
  timestamps, get priority in the event of a
  conflict.
• Basic timestamp ordering
• Thomass write rule

27
Basic Timestamp ordering
ts(T19)ltts(T20)ltts(T21)
28
Concurrency Control Method Comparison
CS-Conflict Serializability VS-View
Serializability 2PL- 2 Phase Locking TS -
Timestamping
VS
CS
TS
2PL
29
Optimistic Techniques
Assumption Conflict is rare Two or three phases
depending if it is update or read-only transaction

• Read phase reads into local variables and
  updates are stored also into local variables.
• Validation phase If read-only, check if the
  data is still current, and if no interference
  occurred, commit, otherwise restart. If update,
  check the DB state and abort if serializability
  not maintained.
• Write phase Updates made to local variables are
  now stored into DB.

30
Database Recovery
The process of restoring the database to a
correct state in the event of a failure.

• Elisha Dragen

31
Why?

• Because bad things do happen to good people.
• System Crashes
• Software Errors
• User Carelessness
• Natural Disasters
• Media Failures
• Sabotage

Haikus Three things are certain Death, taxes and
lost data. Guess which has occurred. Chaos
reigns within. Reflect, repent, and reboot. Order
shall return.
32
Storage

• Describing Storage Media
• Volatile vs. Non-Volatile
• Online vs. Offline
• Primary vs. Secondary
• Stable Storage
• 4 Types of Storage Media
• Main Memory Primary, Online, Volatile
• Magnetic Disk Secondary, Online, Non-Volatile
• Magnetic Tape Secondary, Offline, Non-Volatile
• Optical Disk Secondary, Offline, Non-Volatile

33
Log File (a.k.a. journal)
34
Damage Control
35
UNDO/REDO
36
Advanced Transaction Models

• Chapter 19.4

37
Nested Transaction Model

• Moss 1981-Subtransactions
• Main Advantages
• Modularity
• A finer level of granularity for concurrency
  control and recovery
• Intra-Transaction Parallelism
• Intra-Transaction Recovery
• Savepoint
• Sagas

38
Multilevel Transaction Model

• Weikum 1991
• Closed Nested Transaction
• Open Nested Transaction

39
Dynamic Restructuring

• Split-transaction
• Join-transaction
• Main Advantages
• Adaptive Recovery
• Reducing Isolation

40
Workflow Models

• Task Specification
• Task Coordination Requirements
• Execution Correctness Requirements

41
Case StudyOracl8i
42
Oracles Isolation Level

• Isolation Level
• -READ COMMITTED
• -SERIALIZABLE
• -READ ONLY
• Setting Isolation Level
• - SET TRANSACTION
• - ALTER SESSION

43
Multiversion Read Consistency

• Rollback segments
• -Store undo information
• System change number
• - A logical timestamp
• - Use it to redo transactions
• Locks
• -Implicit locking and store row-locking
  information
• within the actual data block where the row
  is stored
• -Never escalate locks
• -Lock types DDL locks, DML locks, internal
  locks, internal latches
• Distributed
  locks, PCM locks

44
Deadlock Detection

• Automatically detects deadlock
• Roll back one of the statements involved in the
  deadlock

45
Backup and Recovery

• Recovery manager
• Instance recovery
• Point-in-time recovery
• Standby database

46
More on Transaction Control

• Realtime system
• Multidatabase system
• Distributed database system
• D. Hong, T. Johnson, S. Chakravarthy Real-Time
  Transaction Scheduling
• A Cost Conscious Approach', Proceedings of the
  ACM SIGMOD International Conference on the
  Management of Data, 1993, pp.197-206. 26

47
More on Recovery Control

• High-level view
• Catch and Log management
• Formalization
• Y. Gurevich, N. Soparkar, and C. Wallace.
  Formalizing database recovery. Journal of
  Universal Computer Science, 3(4), 1997.

48
Concurrency Control and Recovery

• Impact of recovery on concurrency control
• Semantic based transaction control
• Abstraction in of concurrency control an recovery
  management
• Unifying concurrency control and recovery
• G. Alonso, R. Vingralek, D. Agrawal, Y.
  Breitbart, A. El Abbadi, H.-J. Schek, and G.
  Weikum. Unifying Concurrency Control and Recovery
  of Transactions. Information Systems,
  19(1)101--115, March 1994.