1 / 20

More on transactions…

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 - start at time-1 (older)

kat
Download Presentation

More on transactions…

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More on transactions…

  2. Dealing with concurrency (OR: how to handle the pressure!) • Locking • Timestamp ordering • Multiversion protocols • Optimistic protocols (validation protocol)

  3. Timestamping:Wait-Die (1) If the younger transaction locks first: T1 - start at time-1 (older) T2 - start at time-2 (younger) T2 - request X lock on R T2 - write R - X lock T1 - request X lock on R - wait

  4. Timestamping:Wait-Die (2) If the older transaction locks first: T1 - start at time-1 (older) T2 - start at time-2 (younger) T1 - request X lock on R T1 - write R - X lock T2 - request X lock on R - abort T1 - release X lock on R T2 - start again at time-2 (same timestamp, so it “looks older”)

  5. Timestamping:Wound-Wait (1) If the younger transaction locks first: T1 - start at time-1 (older) T2 - start at time-2 (younger) T2 - request X lock on R T2 - write R - X lock T1 - request X lock on R T2 - abort T1 - write R - X lock T2 - start again at time-2

  6. Timestamping:Wound-Wait (2) If the older transaction locks first: T1 - start at time-1 (older) T2 - start at time-2 (younger) T1 - request X lock on R T1 - write R - X lock T2 - request X lock on R - wait

  7. 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

  8. Transactions • 1+ SQL statements that are performed completely, or not at all • ACID properties • Atomic • Consistent • Isolation • Durability

  9. Transaction Demo

  10. Recovery

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. Checkpoints in Oracle • Mark that database is in a consistent state • Oracle SCN • Written to headers of database files and to control files

  17. DBWR LGWR ARCH Arch1 Redo1 Arch2 System TS (Data Dictionary) Arch3 Rollback segments Temporary Arch4 Data1... Arch5 Index1... The Oracle SGA (Memory) User PMON SMON Redo2 Redo3

  18. 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.

  19. Transaction states Checkpoint Failure T1 C T2 C T3 C T4 T5 TIME

More Related