icom 6005 database management systems design
Download
Skip this Video
Download Presentation
ICOM 6005 – Database Management Systems Design

Loading in 2 Seconds...

play fullscreen
1 / 21

ICOM 6005 – Database Management Systems Design - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on

ICOM 6005 – Database Management Systems Design. Dr. Manuel Rodr í guez-Mart í nez Electrical and Computer Engineering Department Lecture 16 – Intro. to Transactions Processing and Concurrency Control. Transaction Processing. Read : Chapter 16, sec 16.1-16.6 Chapter 17 ARIES papers

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' ICOM 6005 – Database Management Systems Design' - gillian-livingston


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
icom 6005 database management systems design

ICOM 6005 – Database Management Systems Design

Dr. Manuel Rodríguez-Martínez

Electrical and Computer Engineering Department

Lecture 16 – Intro. to Transactions Processing and Concurrency Control

transaction processing
Transaction Processing
  • Read :
    • Chapter 16, sec 16.1-16.6
    • Chapter 17
    • ARIES papers
  • Purpose:
    • Study different algorithms to support transactions and concurrency control in a DBMS

Dr. Manuel Rodriguez Martinez

introduction
Introduction
  • DBMS software and supporting server machine are a big investment
  • Enterprise wishes to maximize its use
  • If each users get to use the DBMS by itself for a short period of time, it takes a lot of time to run the tasks
  • Multiple user must be allowed to access the DBMS at the same time
    • Concurrent access
  • DBMS might crash
    • Power fails, software bugs appears, hardware fails, soda is spilled …
    • Need recovery mechanism to recover loss data

Dr. Manuel Rodriguez Martinez

multiple users using a dbms
Multiple-Users using a DBMS

T3

T4

T2

T1

Waiting Queue

DBMS

Users wait to get a hold on

DBMS to run their tasks.

Context switches make this

inefficient

Dr. Manuel Rodriguez Martinez

multiple users using a dbms 2
Multiple-Users using a DBMS (2)

T1

T4

T2

T3

DBMS

DBMS executes different

Tasks at the same time.

Maximizes system throughput

Dr. Manuel Rodriguez Martinez

system crash
System Crash

T1

Updates

are lost

T2

Disk is gone

Data

Data

Dr. Manuel Rodriguez Martinez

system crash 2
System Crash (2)

How to

recover?

T1

Updates

are lost

T2

Disk is gone

Data

Data

Dr. Manuel Rodriguez Martinez

concurrency and recovery
Concurrency and Recovery
  • DBMS must support
    • Concurrency
      • Allow different users to access DBMS at the same time
      • Control access to data to prevent inconsistencies in DBMS
    • Recovery
      • Track progress of operations by an users
        • Use a log for this
      • If a crash occurs, must use this log to recover operations that were completed
      • Log must be stored independently of data to prevent losing both
  • Transactions – unit of work used by DBMS to support concurrency and recovery

Dr. Manuel Rodriguez Martinez

relational dbms architecture
Relational DBMS Architecture

Client API

Client

Query Parser

Query Optimizer

Relational Operators

Execution

Engine

File and Access Methods

Concurrency

and Recovery

Buffer Management

Disk Space Management

DB

Dr. Manuel Rodriguez Martinez

the need for concurrency
The need for concurrency
  • Jil and Apu are married and share baking account A.
  • Jil and Apu go to the bank at the same time and use to different ATMs
    • Jil asks to withdraw $300 from the $500 in A
    • Apu ask to withdraw $400 from the $500 in A
  • The following might happen:
    • At ATM 1: System reads $500 in A
    • At ATM 2: System reads $500 in A
    • At ATM 1: System deducts $300 from A
    • At ATM 2: System deducts $400 from A
    • At ATM 1: Systems stored $200 as balance in A
    • At ATM 2: Systems stored $100 as balance in A
  • Jil and Apu got $700 out of their $500 in account A!
  • DBMS must prevent such events via concurrency control

Dr. Manuel Rodriguez Martinez

the need for recovery
The need for recovery
  • Tom goes to bank with a $1,000 deposit for this account A, which currently has $500
  • Tom talks with teller X.
  • The following might happen:
    • Teller X reads A and finds $500 dollars
    • Tom gives $1,000 to teller X in an envelope
    • Teller X changes balance in A to $1,500
    • Teller X sends a request to DBMS to update A to $1,500
    • Power fails at this time
  • What is the balance of A?
    • $500 or $1,500? How do we make sure it is $1,500?
  • DBMS must support recovering correct balance via crash recover

Dr. Manuel Rodriguez Martinez

transactions and acid properties
Transactions and ACID properties
  • Transactions are the unit of work used to submit tasks to the DBMS
    • Selects, inserts, deletes, updates, create table, etc.
  • Transactions must support ACID properties
    • Atomicity – all operations included in a transactions are either completed as a whole or aborted as whole
    • Consistency – each transactions reads a consistent DB and upon completion leaves DB in another consistent state
    • Isolation – transactions running concurrently have the same effect on the DB as if they had been run in serial fashion
      • One at the other
    • Durability – changes made by committed (transactions) survive crashes and can be recovered. Changes made by aborted transactions are undone

Dr. Manuel Rodriguez Martinez

supporting transactions at dbms
Supporting Transactions at DBMS
  • Transaction Manager
    • Module in charge of supporting transaction at DBMS
  • Sub-components
    • Lock Manager
      • Deals with granting locks to transaction to get access to DB objects such as records, data pages, tables or whole databases
    • Log/Recovery Manager
      • Deals with tracking operations done by transactions as well as determining which ones commit and which ones abort. After a crash, it recovers work done by committed transactions.
  • Implementing Transaction Manager
    • Modules integrated with DBMS
    • Separate process from DBMS
      • TP Monitor

Dr. Manuel Rodriguez Martinez

schedules
Schedules
  • We can model operations done by a transaction with a schedule
    • List of operations done: read, write, plus logical operations
    • Often, we just care about
      • Reads
      • Writes
      • Abort requests
      • Commit requests
      • Changes to individual objects (optional, just for clarity).
    • Assumptions:
      • Only inter-transaction interaction is via reads/writes of shared objects

Dr. Manuel Rodriguez Martinez

example schedules
Example Schedules

Schedule 2

Schedule 1

Each row represent an action take a some point

In time. DBMS make one action at a time

Dr. Manuel Rodriguez Martinez

serialization of schedules
Serialization of Schedules
  • Serial schedule:
    • A schedule in which each transaction T1, T2, …, Tk is executed one after the other without interleaving
  • Key idea:
    • Transactions that interleave operations are ok as long as their schedule is equivalent to a serial schedule
  • Serializable schedule on transactions T1, T2, …, Tk
    • Its effect are equivalent to a serial schedule
    • Performance is better
      • Interleaving of operations
  • Not all schedules are serializable
  • System throughput – number of transactions completed per unit of time
    • Increases with serializable transactions

Dr. Manuel Rodriguez Martinez

example of serializability
Example of serializability

Schedule 1

Serial

equivalent

Dr. Manuel Rodriguez Martinez

anomalies due to interleaving
Anomalies due to interleaving
  • You want your schedules to be serializable
  • Otherwise, the following things (considered bad) could happen
    • Write-read (WR) conflicts
    • Read-write (RW) conflicts
    • Write-write (WW) conflicts
  • SQL allows you to decide the level of concurrency you need
    • By default you get serializable support

Dr. Manuel Rodriguez Martinez

write read conflicts
Write-Read conflicts
  • Transaction T1 reads uncommitted data produced by transaction T2.
    • Called a dirty read
    • Now, if T2 aborts, the work done by T1 is inconsistent
  • Example: T1 and T2 access Bank account A
    • T1 reads A with balance $1000
    • T1 substract $100 from A
    • T2 reads A with balance $900
    • T1 aborts
    • T2 substract $200 from A
    • T2 stores A with balance $700
    • T2 commits
  • Problem: Balance should be $800 not $700

Dr. Manuel Rodriguez Martinez

read write conflicts
Read-write conflicts
  • Transaction T1 reads some object A, which is also read and modified by T2.
  • When T1 reads A, the value has changed!!!
    • Called unrepeatable read
  • Example: T1 and T2 access Bank account A
    • T1 reads A with balance $1000
    • T1 checks balance > $500, goes to do other checks
    • T2 reads A with balance $1000
    • T2 subtracts $700
    • T2 writes A
    • T2 commits
    • T1 reads A again
    • T1 subtracts $500 from A
    • T1 writes A
    • T1 commits
  • Balance is - $300.

Dr. Manuel Rodriguez Martinez

write write conflicts
Write-Write Conflicts
  • Transactions T1 reads object A, and T2 writes a new value to object A.
  • T1 then writes A to the DB
    • Called a blind write
  • Example: T1 and T2 access Bank account A
    • T1 reads A with balance $1000
    • T2 sets A to $2000
    • T2 writes A
    • T2 commits
    • T1 subtracts $500 from A
    • T1 writes A
    • T1 commits
  • Balance is $500, but update from T2 is lost

Dr. Manuel Rodriguez Martinez

ad