Skip to content

Latest commit

 

History

History
148 lines (118 loc) · 4.12 KB

CSCI_4400_NOTES_4_18_2019.md

File metadata and controls

148 lines (118 loc) · 4.12 KB
Raw

CSCI_4400_NOTES_4_18_2019.md

SQL Triggers

CREATE TRIGGER <name>
ON
  <table>
FOR
  <operations>
AS
  <declare variables>
  <select to use variables>
IF
  <condition>
BEGIN
  rollback transaction
  RAISEERROR('Message', 16, 10)
END

5 props of database transactions

  • Atomicity
    • All operations of a transaction must be completed; if not the transaction is aborted
  • Consistency
    • Permanence of database’s consistent state
  • Isolation
    • Data used during transaction cannot be used by second transaction until the first is completed
  • Durability
    • Ensures that once transactions are committed they cannot be undone or lost
  • Serializability
    • Ensures that the schedule for the concurrent execution of several transactions should yield consistent result

Concurrency Control

  • coordinate many connections
    • put into a Queue.
    • isolation is important
  • problems caused
    • lost udates
    • uncommited data
    • inconsistent retreivals

Problems with concurrency

  • Lost Update
    • the data is as it was last updated
  • Uncommitted data
  • Inconsistent retrievals

The Scheduler

  • Establishes the order in which the ops are executed
  • Bases actions on concurrent control alogorithms
  • Creates serializable schedule
    • interleaved communication

Concurrency Control with Locking Methods

  • Locking methods facilitate isolation of data items used in concurrently executing transactions
    • Lock: exclusive data
    • Pessimistic locking: use of locks on assumption that conflict between transaction is likely
    • Lock manager: responsible for preventing collision

Lock Granularity (1 of 5)

  • Indicaties the level lock use
    • DB lock
    • table
    • page
    • row
    • field

Lock Types (1 of 2)

  • Binary Lock
    • two states, T F
  • Exclusive lock
    • related to transaction
  • shared lock
    • related to a pool of things
    • thread group can access

Two-Phase Locking to Ensure Serializability

  • Governing rules
    • 2 transaction cannot have conflicting locks
    • No unlock operation can precede a lock operation
    • No data are affected until locks are obtained

Two-Phase Locking to Ensure Serializability 2

  • Image
  • Growing phase and shrinking phrase

Deadlock

  • Occur when 2 transactions wait indefinitely for each other to unlock data
    • A.K.A. "deadly embrace"
  • Control techniques
    • Deadlock prevention
    • Deadlock detection
    • Deadlock avoidance
  • Choice of deadlock control method depends on database environment
  • Dining Philosophers
    • 5 phils
    • 5 forks
    • server solution
    • Queue solution
    • random decision solution
      • surprisingly works

Concurrency Control with Time Stamping Methods (1 of 2)

  • Time stamping assigns global, unique time stamp to each transaction
    • Produces explicit order in which transactions are submitted to DBMS
  • Properties
    • Uniqueness: ensures no equal time stamp values exist
    • Monotonicity: ensures time stamp values always increases

Concurrency Control with Time Stamping Methods (2 of 2)

  • Disadvantages
    • Each value stored in the database requires two additional stamp fields
    • Increases memory needs
    • Increases the database’s processing overhead
    • Demands a lot of system resource

Wait/die or Wound and Wait (1 of 2)

  • Wait/die
    • A concurrency control scheme in which an older transaction must wait for the younger transaction to complete and release the locks before requesting the locks itself-Otherwise, the newer transaction dies and is rescheduled
  • Wound/wait
    • A concurrency control scheme in which an older transaction can request the lock, preempt the younger transaction, and reschedule it-Otherwise, the newer transaction waits until the older transaction finis

Concurrency Control with Optimisitc Methods (1 of 2)

  • Optimisitc
    • Don't require locking
    • Transaction is exe
  • Phases
    • Read phase
      • Transaction: Reads the database-Executes the needed computations-Makes the updates to a private copy of the database values
    • Validation phase
      • Transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database
    • Write phase
      • Changes are permanently applied to the database