T4

• Transaction is a logical unit of work
• Transaction must be entirely completed or aborted
• Transaction must have the properties of atomicity, consistency, isolation and durability.
• Atomicity is the transaction property that requires all parts of transaction to be treated as a single, indivisible, logical unit of work. All parts of transaction must be completed or the entire transaction is aborted.
• Consistency is a database condition which all data integrity constraints are satisfied.
• Consistency is to ensure that ever transaction must begin with the database in a known consistent state, if not the transaction will yield an inconsistent database that violates its integrity and business rules.
• Isolation is a database transaction property in which a data item used by one transaction is not available to other transactions until the first one ends.
• Durability is the transaction property that ensures that once transaction changes are done and committed, they cannot be undone or lost even in event of a system failure.
• Serializability ensures that the schedule for the concurrent execution of the transactions yield consistent results.
• The american national standards institute (ANSI) defined standards that govern SQL database transactions
• Transaction support is provided by two SQL statements: COMMIT and ROLLBACK
• Concurrency control is coordinating the simultaneous execution of transactions in a multiuser database system.
• Concurrency control ensure the serializability of transactions in a multiuser database environment.
• Concurrency Control most techniques are oriented toward preserving the isolation property of concurrently executing transactions.
• Concurrency control is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency.
• Three main problems that could be solved by Concurrency Control

  • Lost updates
  • Uncommitted data
  • Inconsistent retrievals
• Locking methods are one of the most common techniques used in concurrency control because they facilitate the isolation of data items used in concurrently executing transactions.
• Lock guarantees exclusive use of a data item to a current transaction
• Transaction acquires a lock prior to data access
• Unlocked when the transaction is completed so that another transaction can lock the data item
• Pessimistic locking is referred when the use of locks are based on the assumption that conflict between transactions is likely to happen.
• Most multiuser DMBSs automatically initiate and enforce locking procedures.
• All lock information is handled by a lock manager, responsible for assigning and policing the locks used by the transactions.'
• Timestamp value produces an explicit order in which transactions are submitted to the DBMS
• Uniqueness to ensure no equal time stamp values can exist
• Monotonicity ensures that time stamp values always increases
• Majority of database operations do not conflict.
• Optimistic approach requires neither locking nor time stamping techniques
• Transaction is executed without restrictions until it is committed is optimistic methods.
• Type of read operations are

  • Dirty Read
  • Nonrepeatable read
  • Phantom read
• Database recovery restores a database from a given state to previously consistent state.
• A database recovery management might be required when something occurs like:

  • Hardware/software failures
  • Human-caused incidents
  • Natural Disasters
• COMMIT is in which case all changes are permanently recorded within the database.
• COMMIT statement automatically ends the SQL transaction
• ROLLBACK is in which case all changes are aborted and the database is rolled back to its previous consistent state.
• Concurrency control is to ensure the serializability of transactions in a multiuser database environment.
• Lost update is a concurrency control problem in which a data update is lost during the concurrent execution of transactions
• Uncommitted Data is a concurrency control problem in which a transaction accesses uncommitted data from another transaction
• Inconsistent Retrievals is a concurrency control problem that arises when a transaction calculating summary functions over a set of data while other transactions are updating the data.
• All lock information is handled by a lock manager