Please enable JavaScript.

Coggle requires JavaScript to display documents.

CHAPTER 5: DATABASE TRANSACTION MANAGEMENT, TRANSACTION LOG A DBMS uses a…

- - - - INTERFERENCE PROBLEMS
        Lost Update:
        
        the most serious interference problem because changes to a database are inadvertently lost
        
        in a lost update, one users update overwrites another users update
        
        Uncommitted Dependency:
        
        occurs when one transaction reads data written by another transaction before the other transaction commits
        
        also known as a dirty read because it is caused by one transaction reading dirty data
        
        violates the isolation property of transaction
        
        ~ FABIAN JOEDIN ~
        
        CORRECT EXECUTION OF TWO TRANSACTIONS
        
        -SARRAH AQILAH-
        
        AN UNCOMMITTED DEPENDENCY PROBLEM
        
        -SARRAH AQILAH-
        
        Inconsistent Retrieval Problem
        
        Occur when a transaction calculates some aggregate functions over a set of data while transactions are updating the data.
        
        Some data may be read after they are changed and some before they are changed yielding inconsistent results.
        
        ERITNEY MAJA
        
        Retrieval During Update
        
        ERITNEY MAJA
        
        TRANSACTION RESULTS: DATA ENTRY CORRECTION
        
        NURSYUHADDA AIN
        
        INCONSISTENT RETRIEVALS
        NUSYUHADDA AIN
        
        The Scheduler
        🡲 The scheduler is a special DBMS process that establishes the order in which the operations are executed within concurrent transactions.
        🡲 The scheduler interleaves the execution of database operations to ensure serializability and isolation of transactions.
        🡲 To determine the appropriate order, the scheduler bases its actions on concurrency control algorithms, such as lcking or time stamping.
        The scheduler's main job:
        🡲 Create a serializable schedule of a transaction's operations, which interleaved execution of the transactions (T1, T2, T3).
        🡲 It yields the same results as if the transactions were executed in serial order (one after another).
        MARCELLA
        
        Concurrency Control Tools
        
        To prevent the three interference problems, most DBMS use these two tools:
        
        (1) Lock
        
        (2) Two-phase locking protocol
        
        MARCELLA
        
        -Najmil-
        
        TABLE-LEVEL LOCK
        
        =T1 and T2 can access the same database concurrently as long as they use different tables.
        =Can cause bottlenecks when many transactions are tring to access the same table (even if the transactions want to access different part of the table and would not interfere with each other).
        =Not suitable for multi-user DBMSs.
        -OLIVIA YANA -
        
        PAGE-LEVEL LOCK
        
        =An entire disk page is locked (a table can span several pages and each page can contain several rows of one or more tables).
        =Most frequently used multi-user DBMS locking method.
        -OLIVIA YANA -
        
        Good for batch processing but unsuitable for online multi-user DBMSs.
        T1 and T2 can not access the same database concurrently even if they use different tables.
        
        -Najmil-
        
        LOCK TYPE
        Regardless of the level of locking, the DBMS may use different lock types: binary, shared or exclusive locks.
        MUKHRIZ DANISH
        
        BINARY LOCKS
        -Has only two states: locked (1) or unlocked (0).
        Eliminates “Lost Update” problem – the lock is not released until the write statement is completed.
        Can not use PROD_QOH until it has been properly updated.
        -Considered too restrictive to yield optimal concurrency conditions as it locks even for two READs when no update is being done.
        
        MUKHRIZ DANISH
        
        SHARED/EXCLUSIVE LOCKS
        ~EXCLUSIVE LOCKS
        -Access is specifically reserved for the transaction that locked the object.
        -Must be used when the potential for conflict exist-when a transactions wants to update a data item and no locks are currently held on that item by another transaction
        -Granted if and only if no other locks are held on the data item.
        ~SHARED LOCK
        -Concurrent transactions are granted READ access on the basis of a common lock.
        -Issued when a transaction want to read data and no exclusive lock is held on that data item.
        -Multiple transactions can each have a shared lock on the same data item if they are all just reading it.
        ~MUTUAL EXCLUSIVE RULE
        -Only one transaction at a time can own an exclusive lock in the same object.
        -NURSYAFIQAH-
        
        1 more item...
- - - - TRANSACTION RECOVERY
        
        Recovery procedures generally make use of deferred-write and write-through techniques.
        
        When the recovery procedure uses a deferred-write technique, the transaction operations do not immediately update the physical database. Instead, only the transaction log is updated.
        
        Physically updated only with data from committed transactions, using information from the transaction log.
        
        If the transaction aborts before it reaches its commit point, no changes (no ROLLBACK or undo) need to be made to the database because it was never updated.
        
        -Anastasia Jyntang-
        
        TRANSACTION RECOVERY
        
        When the recovery procedure uses a write-through technique (immediate update), immediately updated by transaction operations during the transaction’s execution, even before the transaction reaches its commit point.
        
        If the transaction aborts before it reaches its commit point, a ROLLBACK or undo operation needs to be done to restore.
        
        In that case, the ROLLBACK operation will use the transaction log “before” values.
        -Anastasia Jyntang-