Please enable JavaScript.

Coggle requires JavaScript to display documents.

Transactions (Race Conditions (Read Skew (Nonrepeatable Reads) Client sees…

- - - - Each transaction reads from a consistent snapshot of the database. Readers never block writers, and writers never block readers. Addresses Read Skew, Lost Updates
      - Implementation
        
        Db needs to keep several different committed versions of an object since there may be several transactions running concurrently. Does this using MVCC
        
        Uses transaction ids for each transaction. Updates are handled as deletes and creates
        
        Lost Updates Handling
        
        Atomic Write Operations - for e.g. when incrementing counter use value in row to update as opposed to reading, incrementing and then updating. These take an exclusive lock on row
        
        Explicit Locking - Application locks the row by doing select.....for update
    - - Guarantees no dirty reads while reading and no dirty writes while writing
      - Implementation
        
        Dirty Reads can be prevented by using row level locks but affects performance
        
        Dirty Reads - db stores old value before transaction starts and for any other transactions (prior to first one being committed) provides the old value
        
        Dirty Writes - prevented by using Row Level locks
  - - - Two Phase Locking (2PL)
        
        Several transactions are allowed to concurrently read the same object as long as nobody is writing to it. But as soon as anyone wants to write (modify or delete) an object, exclusive access is required. Pessimistic concurrency control
        
        Implementation
        
        If a transaction wants to write to an object, it must first acquire the lock in exclusive mode. No other transaction may hold the lock at the same time (either in shared or in exclusive mode), so if there is any existing lock on the object, the transaction must wait.
        
        If a transaction first reads and then writes an object, it may upgrade its shared lock to an exclusive lock. The upgrade works the same as getting an exclusive lock directly.
        
        If a transaction wants to read an object, it must first acquire the lock in shared mode. Several transactions are allowed to hold the lock in shared mode simultaneously, but if another transaction already has an exclusive lock on the object, these transactions must wait.
        
        After a transaction has acquired the lock, it must continue to hold the lock until the end of the transaction (commit or abort). This is where the name “two-phase” comes from: the first phase(while the transaction is executing) is when the locks are acquired, and the second phase (at the end of the transaction) is when all the locks are released.
        
        A lock on each object in the database. The lock can either be in shared mode or in exclusive mode
        
        Since so many locks are in use, it can happen quite easily that transaction A is stuck waiting for transaction B to release its lock, and vice versa. This situation is called deadlock. The database automatically detects deadlocks between transactions and aborts one of them so that the others can make progress. The aborted transaction needs to be retried by the application
        
        Issues
        
        Performance - one slow transaction can hold up others
        
        Deadlocks
        
        Predicate Locks - locks based on condition, applies to objects that may be added in future - phantoms
        
        Index Range Locks - applied on a range of values in an index. An approximation of the search condition is attached to one of the indexes. Now, if another transaction wants to insert, update, or delete a booking for the same room and/or an overlapping time period, it will have to update the same part of the index. In the process of doing so, it will encounter the shared lock, and it will be forced to wait until the lock is released.
      - Serializable Snapshot Isolation (SSI)
        
        It uses an optimistic approach, allowing transactions to proceed without blocking. When a transaction wants to commit, it is checked, and it is aborted if the execution was not serializable.
      - Actual Serial Execution - single threaded
        
        Limited to use cases where active dataset can fit in memory
        
        Write throughput must be low enough to fit on a single core
        
        Each transaction must be small and fast
        
        Cross partition transactions are possible but limited
      - Solves Write Skews