Database Integrity, Concurrency, and Transactions

The flashcards below were created by user Kimmiey on FreezingBlue Flashcards.

  1. What is a database transaction
    any action that reads from and/or writes to a database
  2. Describe a successful transaction
    • one is which all of the SQL statements are completed successfully
    • A successful transaction changes the database from one consistent state to another
  3. A _______ database state is one in which all data integrity constraints are satisfied
  4. 4 properties of a transaction
    • atomicity
    • Consistency
    • isolation
    • durability
  5. Describe the atomicity property
    • "all or nothing" property
    • All transaction operations must be completed
  6. Describe the Consistency property
    when a transaction is completed, the database must be in a consistent state
  7. Describe the isolation property
    Data used during the execution of a transaction cannot be used by a second transaction until the first one is complete
  8. Describe the durability property
    Once transaction changes are committed they cannot be undone or lost to a subsequent failure
  9. Describe COMMIT (ANSI SQL standard)
    • Permanently records all changes in the database
    • Automatically ends the transaction
  10. Describe ROLLBACK (ANSI SQL standard)
    • aborts all uncommitted changes
    • Database is rolled back to its previous state
    • a rolled back transaction can typically be restarted later
  11. What do transaction logs store
    • A record for the beginning of the transaction
    • A record for each transaction component (i.e. types of operation, tables/attributes affected, before and after values for attributes effected)
    • A record for the end of the transaction (COMMIT)
  12. Transaction logs are ______ __________ when the DBMS executes transactions that modify the database
    automatically updates
  13. What are some of the tradeoffs with transaction logs
    • increases DBMS processing overhead
    • but...provides ability to restore a corrupted database
  14. When can you implement multi-step transactions
    • When connecting from ...
    • other office applications via VBA
    • a .NET application
    • a web-based application
  15. MS Access only supports ________ transaction swhen using _____ to access the DBMS
    Multistep, code
  16. What is concurrency control
    the process of managing simultaneous operations on the database without having them interfere with one another
  17. Three potential problems caused by concurrency
    • lost updates
    • uncommitted data
    • inconsistent retrievals
  18. How does a lost update problem happen
    when a successfully completed update is overwritten by another transaction
  19. How does an uncommitted data problem happen
    When one transaction accesses the intermediate results of another transaction before they are committed - and the second transaction is then rolled back
  20. How does an inconsistent retrieval problem happen
    When a transaction reads several values, but a different transaction updates some of the them in the midst o this process
  21. What is a serializable schedule
    A schedule of a transaction's operations in which the interleaved execution of all active transactions yields the same results as if those transactions were executedi n serial order
  22. What are some methods for scheduling conflicting operations in concurrent transactions
    • locking methods
    • timestamping
  23. refers to the size of the locked resource
    • lock granularity
    • Image Upload 1
  24. prohibits other users from reading the locked resource
    exclusive lock
  25. allows other users to read the locked resource but they can't update it
    shared loc
  26. Describe optimistic locking
    • assumes that no transaction conflicts will occur
    • DBMS processes a transaction to a temporary file, checks whether conflict occurred, if not it's finished if so the transaction is repeated until there is no conflict
    • Acceptable for applications with few update operations
  27. Describe pessimistic locking
    • assumes that conflicts will occur
    • locks are issued before a transaction is processed, then the locks are released
  28. Two-phase locking(2PL)
    • guarantees serializability
    • transactionsa re allowed to obtain as many locks as necessary (growing phase)
    • Once the first lock is released (shrinking phase) no additional locks can be obtained
    • doesn't prevent deadlocks
  29. What is a deadlock
    an impasse that may result when two (or more) transactions are waiting for locks held by the other to be released
  30. 3 basic techniques for controlling deadlocks
    • Deadlock prevention
    • Deadlock detection
    • Deadlock avoidance
  31. Deadlock prevention
    • abort a transaction if possible of deadlock
    • Reschedule transaction for later execution
  32. Deadlock detection
    • DBMS periodically tests databasse for deadlocks
    • If found, one transaction ("victim") is rolled back
  33. Deadlock avoidance
    transactions obtain all needed locks before execution
  34. What is timestamping
    • A unique identifier created by DBMS that indicates the relative starting time of a transaction
    • Transactions ordered globally so that older transaction (with smaller timestamps) get priority in the event of conflict
    • conflict resolved by rolling back and restarting the associated transaction - no locks, no deadlocks
    • Demands a lot of system resources (memory and processing overhead)
Card Set:
Database Integrity, Concurrency, and Transactions
2016-11-30 04:53:00

Show Answers: