Transcript Dealing with concurrency (OR: how to handle the pressure!)

More on transactions…
Dealing with concurrency
(OR: how to handle the pressure!)
•
•
•
•
Locking
Timestamp ordering
Multiversion protocols
Optimistic protocols (validation
protocol)
Timestamping:
Wait-Die (1)
If the younger transaction locks first:
T1
T2
T2
T2
T1
- start at time-1 (older)
- start at time-2 (younger)
- request X lock on R
- write R - X lock
- request X lock on R - wait
Timestamping:
Wait-Die (2)
If the older transaction locks first:
T1 T2 T1 T1 T2 T1 T2 so
start at time-1 (older)
start at time-2 (younger)
request X lock on R
write R - X lock
request X lock on R - abort
release X lock on R
start again at time-2 (same timestamp,
it “looks older”)
Timestamping:
Wound-Wait (1)
If the younger transaction locks first:
T1
T2
T2
T2
T1
T2
T1
T2
-
start at time-1 (older)
start at time-2 (younger)
request X lock on R
write R - X lock
request X lock on R
abort
write R - X lock
start again at time-2
Timestamping:
Wound-Wait (2)
If the older transaction locks first:
T1
T2
T1
T1
T2
-
start at time-1 (older)
start at time-2 (younger)
request X lock on R
write R - X lock
request X lock on R - wait
Other concepts
• Cautious waiting – can only have one
transaction waiting for each item
• Live lock – transaction can’t finish
because it is always the youngest
(might occur if tx didn’t keep the
original timestamp)
• Timeout – assume that if waited longer
than X time, deadlock exists, so tx are
aborted
Transactions
• 1+ SQL statements that are performed
completely, or not at all
• ACID properties
– Atomic
– Consistent
– Isolation
– Durability
Transaction Demo
Recovery
Types of Failures
• Transaction failure
– Transaction must be aborted
• System failure
– Hardware or software problem resulting in
volatile memory loss
• Media failure
– Disks or tapes
• Disasters
Transaction Logs
• Before image
– Records previous values of objects before
they were changed
– Can be used to "undo" the transaction
• After image
– Records new values after they've been
changed
– Can be used to "redo" the transaction
Logging changes
• System logs record activity of db (start of tx,
operation performed, end of tx)
• Current block of log is in memory; when full,
block is written to disk
• Changes can be force-written to disk
• "Write-ahead" logging
• Checkpoints are also recorded in log
– Commit-consistent
– Cache-consistent
– Fuzzy
Logging changes (con'd)
• In-place updating
• Out-of-place updating
– Shadowing
• Undo log may also be in volatile
memory, so may need to be written to
disk periodically
Commit processing
• No flush strategy
• Flush strategy
• No steal strategy
• Steal strategy
• Deferred update
• Immediate update
• How these strategies are combined determines what
needs to be done in the event of failure
• Trade offs
Checkpoints in Oracle
• Mark that database is in a consistent
state
• Oracle SCN
• Written to headers of database files and
to control files
The Oracle SGA
(Memory)
Arch1
System TS
(Data Dictionary)
Redo1
Arch2
Redo2
Arch3
Redo3
Arch4
Rollback segments
Temporary
Data1...
Index1...
Arch5
TX recovery in Oracle
• Log scanned backward from end of log
to most recent checkpoint. Undo
uncommitted or aborted transactions.
• Log scanned forward from checkpoint.
Redo all committed transactions.
• Take new checkpoint.
Transaction states
Checkpoint
Failure
T1
C
T2
C
T3
C
T4
T5
TIME