What are the Transaction properties/components?
Atomicity
- ensures either all operations of a transaction reflect in the database or not
- all operations treated as logical
- e.g T1 is completed while T2 is not. Unacceptable. This is a failed transaction
Consistency
- indicates permanence of the database consistent state
- execution should take lace in isolation as no other transaction should run concurrently when there is already a transaction running
Isolation
- for every pair of transactions, one should start execution only when other is finished
- transactions must not interfere with each other
Durability
- once isolation is complete, changes made to the database must be in permanent consistent state regardless of failure. Cannot COMMIT
- recovery management component of DB ensures durability of the transaction
Explain the Transaction Management with SQL
- consists of ANSI which defines standards that govern SQL database transactions
- transaction support provides 2 SQL statements: COMMIT & ROLL BACK
- transaction sequence cannot continue until COMMIT, ROLL BACK and end of program are reached and program is abnormally terminated
What is the definition of a Transaction Log and what is its function?
A DBMS table that contains a description of all database transactions. The information stored in the log is used by the DBMS to recover the database after a transaction is aborted or after a system failure.
What are the 3 main problems of Concurrency Control?
Lost Updates
- two concurrent transactions update the same data element
- one of updates is lost, leading to overwritten by other transaction
Uncommitted Data
- two transactions executed concurrently
- first transaction rolled back after second already accessed uncommitted data
Inconsistent Retrievals
- first trans access data, second alters, first access again
- transaction might read some data before they are changed and other data after changed
- leading to inconsistent results
What is the Scheduler?
DBMS component that establishes the order in which concurrent database
operations are executed. The scheduler interleaves the execution of the database operations to ensure the serializability of transactions.
What is a Lock?
a mechanism used in concurrency control to guarantee the exclusive use of a data element
to the transaction that owns the lock.
e.g. if the data element X is currently locked by
transaction T1, transaction T2 will not have access to the data element X until T1 releases its lock.
A data item can only be in two states: locked (used by transaction) unlocked (free to use)
What is Lock Granularity and what are the different levels?
Refers to the size of the database object that a single lock is placed upon.
Its different levels:
- Database level (DB is locked)
- Table level (table locked)
- Page-level (diskpage locked)
- Row level ( one row locked)
- Field level (one field in one row locked)
What are the last 2 Lock types?
Exclusive lock
- one of two lock types used to enforce concurrency control
- exists when access to a data item is specifically reserved for the transaction that
locked the object.
- must be used when a potential for conflict exists
- e.g one or more transactions must update (WRITE) a data item. Therefore, an exclusive lock is issued
only when a transaction must WRITE (update) a data item and no locks are currently held on that
data item by any other transaction.
What is a Deadlock?
A condition where two transactions wait for each other to access/unlock a data source
What are the 3 Deadlock Control techniques?
Prevention
- transaction requesting a new lock is aborted if there is a possibility that a deadlock may occur
- if aborted, all changes made are rolled back and all locks released = transaction rescheduled
Detection
- if deadlock found, one of the transactions is aborted while other continues
Avoidance
- avoids rollback of conflicting transactions by requiring that locks be obtained in succession
What is the Optimistic Approach with Concurrency Control?
- makes the assumption that conflict from concurrent transactions is
unlikely - does nothing to avoid conflicts or control the conflicts
- only test for conflict occurs during the validation phase
- if conflict detected, transaction restarts
What is Database Recovery Management?
- restores database to previous consistent state
- based on atomic transaction property where all portions of transaction treated as singe logical unit of work
- all operations applied and completed to produce consistent database
- database must recover from possible risks to maintain consistency
