Database Database


Download 466 b.
bet16/19
Sana14.08.2018
Hajmi466 b.
1   ...   11   12   13   14   15   16   17   18   19

Create code









  • Oracle additions:

  • Tables ALTER, CREATE, DROP

  • User LOGOFF, LOGON

  • Database SERVERERROR, SHUTDOWN, STARTUP













Some transactions result in multiple changes.

  • Some transactions result in multiple changes.

    • These changes must all be completed successfully, or the group must fail.
    • Protection for hardware and communication failures.
    • example: bank customer transfers money from savings account to checking account.
      • Decrease savings balance
      • Increase checking balance
      • Problem if one transaction and machine crashes.
  • Possibly: give users a chance to reverse/undo a transaction.

  • Performance gain by executing transactions as a block.



The computer needs to be told which changes must be grouped into a transaction.

  • The computer needs to be told which changes must be grouped into a transaction.

    • Turn on transaction processing.
    • Signify a transaction start.
    • Signify the end.
  • Must be set in module code

    • Commit
    • Rollback






Concurrent Access

  • Concurrent Access

    • Multiple users or processes changing the same data at the same time.
    • Final data will be wrong!
  • Force sequential



One answer to concurrent access is to prevent it.

  • One answer to concurrent access is to prevent it.

  • When a transaction needs to alter data, it places a SERIALIZABLE lock on the data used, so no other transactions can even read the data until the first transaction is completed.





Deadlock

  • Deadlock

    • Two (or more) processes have placed locks on data and are waiting for the other’s data.
  • Many solutions

    • Random wait time
    • Global lock manager
    • Two-phase commit - messages




Assume that collisions are rare

  • Assume that collisions are rare

  • Improved performance, fewer resources

  • Allow all code to read any data (no locks)

  • When code tries to write a new value

    • Check to see if the existing value is different from the one you were given earlier
    • If it is different, someone changed the database before you finished, so it is a collision--raise an error
    • Reread the value and try again






Atomicity: all changes succeed or fail together.

  • Atomicity: all changes succeed or fail together.

  • Consistency: all data remain internally consistent (when committed) and can be validated by application checks.

  • Isolation: The system gives each transaction the perception that it is running in isolation. There are no concurrent access issues.

  • Durability: When a transaction is committed, all changes are permanently saved even if there is a hardware or system failure.



READ UNCOMMITTED

  • READ UNCOMMITTED

  • READ COMMITTED

    • Problem: Second transaction might change or delete data
    • Restriction: Need optimistic concurrency handling
  • REPEATABLE READ

  • SERIALIZABLE

    • Provides same level of control as if all transactions were run sequentially.
    • But, still might encounter locks and deadlocks












Purpose

  • Purpose

    • Track through table or query one row at a time.
    • Data cursor is a pointer to active row.
  • Why?

    • Performance.
    • SQL cannot do everything.
      • Complex calculations.
      • Compare multiple rows.















Do'stlaringiz bilan baham:
1   ...   11   12   13   14   15   16   17   18   19


Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2019
ma'muriyatiga murojaat qiling