Please enable JavaScript.

Coggle requires JavaScript to display documents.

Transactions - Coggle Diagram

- - - - Node crashes during commit
      - Network timeouts or lost messages
      - Partial commits cause inconsistency
      - Coordinator failure
        
        Participants stuck in uncertain state
        
        Must wait for recovery decision
        
        Locks held during uncertainty
        
        Cannot progress without coordinator
        
        Causes system-wide blocking issues
        
        Must be resolved manually by admin
    - - Attempts non-blocking protocol
      - Requires bounded network delays
      - Rarely practical in real systems
  - - - XA Transactions
        
        SPOF Coordinator
        
        No cross-system deadlock detection
        
        Difficult recovery of in-doubt transactions
  - - - Consensus-based approach
        
        Replicated coordinator and shards
        
        Automatic failover handling
        
        Strong consistency guarantees
- - - - It's aborted
      - All its Ws are discarded
      - Can safely retry
  - - - Statements about your data
      - Must always hold
    - - Starts from a valid state
      - Its writes must preserve validity
      - Invariants may be broken during execution
      - Must hold again at commit
    - - Declare as constraints in schema
        
        FK
        
        Uniqueness
        
        Check (allowed values in a row)
      - More complex cases
        
        Modeled with
        
        Triggers
        
        Materialized views
        
        It's app's responsibility to define its transactions correctly
  - - - Race conditions
    - - One transaction makes several Ws
      - Then another one should see either all or none of those Ws, not a subset!
    - - Each T behaves as the only one running
      - After commits result is as if they had run serially
      - Has a performance cost
      - Many DBs use weaker isolation than serializability
        
        Allow transactions to interfere limitedly
  - - - DB is a safe place for data
      - After transaction commits data should not be lost even after crashes/faults
    - - Data written to nonvolatice storage (disk/SSD)
      - Regular file Ws are buffered; risk of loss on power failure -> fsync to force data to disk
      - use WAL to recover if crash happens mid-write
      - Store data with checksums to detect corruption/incompl log entries and restore to a consistent snapshot
    - - Data copied to multiple nodes
      - Must wait for Ws/replications to complete before reporting commit
      - Perfect DU doesn't exist
      - Use multiple risk-reduction techniques together
        
        Writing to disk
        
        Replication to remote machines
        
        Backups
- - - - Two transactions modify same data
      - Race conditions from unlucky timing
      - One transaction reads concurrent writes
  - - - Lead to subtle concurrency bugs
    - - Weak isolation has caused money loss
        
        ankrupt Bitcoin exchange
      - Triggered financial audits and corruption
    - - Even “ACID” DBs may be weakly isolated
  - - - Most basic level of Tr isolation
      - Makes two guarantees
        
        No dirty reads allowed
        
        Only committed data visible
        
        Prevents partial updates seen
        
        Avoids cascading aborts
        
        No reading of artially written data to be deleted
        
        No dirty writes allowed
        
        No OW of uncommitted data
        
        Writes wait for prior commit
        
        Prevents inconsistent multi-row updates
        
        Still allows lost updates
      - Implementation
        
        Row-level write locks
        
        One writer per row
        
        Lock held until commit/abort
        
        Read locks (rarely used)
        
        MVCC old value served
        
        Switch to new after commit
      - Cons
        
        Allows non-repeatable / fuzzy reads
        
        If I re-read this row, do I get the same value?
        
        Same row, different values between reads.
        
        Does not prevent race conditions
        
        Allows read skew
        
        Inconsistent cross-row view
        
        If I read several related rows as a group, does the group reflect a consistent state that ever existed?
    - - Snapshot Isolation
        
        Reads one consistent snapshot
        
        Latest values before transaction start
        
        Benefits
        
        Good for long reads
        
        Long running queries don't produce lock contention
        
        Useful for analytics
        
        Useful for integrity checks
        
        Useful for backups
        
        Popular DB feature
        
        DB-specific behavior
        
        Implementation by MVCC
        
        Multiversion Concurrency Control
        
        Keeps several committed versions
        
        Different transactions need different times
        
        Transaction IDs
        
        Each gets a unique ID
        
        Written data tagged with txid
        
        IDs increase over time
        
        Vacuum handles overflow cleanup
        
        Row Metadata
        
        nserted_by stores creator txid
        
        deleted_by marks logical deletion
        
        Delete does not remove row immediately, just hides them
        
        Garbage collection frees deleted rows
        
        Storage of Versions
        
        All versions in same heap
        
        Versions linked as a list
        
        Linked newest-to-oldest or reverse
        
        Only a small overhead
        
        Updates
        
        Old version marked deleted_by updater
        
        Becomes delete plus insert
        
        Typically use write locks to prevent dirty writes
        
        RRs never block WRs and vice-versa
        
        Visibility rules
        
        In-Progress Transactions
        
        Snapshot lists active transactions
        
        Ignores their writes entirely
        
        Even if they later commit
        
        Later Transactions
        
        Later txids treated as future
        
        Ignores writes from later transactions
        
        Aborted Transactions
        
        Ignores all aborted transaction writes
        
        Visible Writes
        
        All other writes are visible
        
        Inserter committed before reader start
        
        Deleter not committed at reader start
        
        Indexes and snapshot isolation
        
        Index points to one row version
        
        Oldest or
        
        Newest
        
        Query walks versions for visibility
        
        Garbage Collection
        
        GC removes invisible row versions
        
        Index entries for them removed
        
        Frees space and keeps index clean
        
        Optimizations
        
        Avoid index updates on same page
        
        Store diffs instead of full rows
        
        Confusion
        
        Repeatable read in PostreGQL
        
        Serializable isolation in Oracle
        
        Definitions are ambiguous, imprecise
        
        Guarantees are vendor-specific
    - - Prevents dirty writes only
      - Allows dirty reads
  - - - Read-modify-write races unsafe
      - Clobbered write
    - - Atomic Operations
        
        Examples
        
        Local mods for JSON doc in MongoDB
        
        Mod priority queues in Redis
        
        UPDATE...SET...WHERE
        
        ORM pitfalls
        
        Encourages read-modify-write patterns
        
        Skips DB atomic operations
        
        Hard to reproduce subtle bugs
        
        Avoid read-modify-write in app!
      - Explicit Locks
        
        App locks rows before update
        
        Read-modify-write under lock
        
        Deadlock
        
        2 Ts wait for each other to release locks
        
        Many DBs auto-detect such and abort one
        
        Retry aborted one in code
        
        FOR UPDATE clause
      - Auto Detection
        
        DB detects lost updates
        
        Aborts offending transaction
        
        Requires application retries
      - Conditional Writes
        
        Compare-and-set pattern
        
        Update only if value unchanged
        
        Also called optimistic locking
        
        MVCC has exception to visibility for this case
        
        UPDATE/DELETE see newer values
      - Replication & Conflicts
        
        Multi-node concurrent writes -> siblings
        
        Use conflict resolution, CRDTs
        
        LWW prone to lost updates
        
        Locks and conditional writes don't suffice
  - - - Concurrent writes, different rows
    - - Multiple rows involved
      - Lost-update detection insufficient
      - Need serializable or explicit locks
    - - SELECT finds no matching rows
      - INSERT later creates matching row
      - Search result changes after write
      - Concurrent transaction doesn't see the updated result
    - - Last resort if no alternative
      - Steps
        
        Define the invariant
        
        State the rule and its predicate
        (COUNT/EXISTS/SUM over rows)
        
        Find shared state
        
        Represent it explicitly
        
        Add a table/row storing that variable,
        keyed by entity (user_id, shift_id).
        
        Serialize all writes through it
        
        Transaction: lock that row → check rule →
        update row → update detail rows → commit
        
        Use it for decisions and keep it consistent
- - - - Can safely retry
- - - - Limit retries, use exponential backoff
    - - Permanent errors make retry pointless
    - - Avoid repeating external actions on retry
      - Coordinated commits or aborts may need two-phase commit
- - - - Definition
        
        Еxecute only one transaction at a time
        
        In serial order
        
        On a single thread
      - What makes it possible since the 2000s
        
        RAM cheap enough to fit entire active dataset in memory
        
        Serializability for OLTP only
        
        OLAP are run on a consistent snapshot
      - Execution Model Changes
        
        No Interactive Transactions
        
        No client round-trips mid-transaction
        
        Avoid network latency stalls
        
        Transaction does not span across multiple HTTP requests
        
        New transaction = new HTTP request
      - Stored Procedures
        
        Entire transaction logic pre-submitted
        
        Executes fully inside DB
        
        Eliminates I/O waits
        
        No locking overhead → High throughput per core
        
        Pros
        
        Fast execution (memory + no I/O)
        
        Reduced network overhead
        
        Enables deterministic replication
        
        No locks or deadlocks
        
        Predictable behavior
        
        Cons
        
        Harder debugging and deployment
        
        Vendor/language fragmentation (Past)
        
        Risky in shared/multitenant environments
        
        Bound to single CPU core per shard
        
        One slow transaction blocks all
        
        Modern improvements
        
        Use general-purpose languages (Java, Lua, JS)
        
        Better tooling and flexibility
      - Sharding
        
        Single-Shard Transactions
        
        Linear scaling with CPU cores
        
        Cross-Shard Transactions
        
        Require coordination
        
        Much slower
        
        Hard to scale
        
        Design Impact
        
        Data model determines shardability
        
        Secondary indexes complicate sharding
    - - Core idea
        
        Stronger than basic locking
        
        Readers and writers block each other
        
        Guarantees correctness by preventing race conditions
        
        2PL/SS2PL
      - Locking Rules
        
        Locking types
        
        Exclusive (write)
        
        Shared (read)
        
        Can upgrade to exclusive
        
        Read Behavior
        
        Blocked by exclusive lock
        
        Multiple shared locks allowed
        
        Write Behavior
        
        Requires exclusive lock
        
        Blocks all other access
        
        Lock lifetime
        
        Held until commit/abort
      - Two Phases
        
        Growing phrase
        
        Acquire locks
        
        No releasing
        
        Shrinking Phase
        
        Release all locks
        
        No new locks allowed
        
        Phases must not overlap
      - Cons
        
        High locking overhead
        
        Reduced concurrency
        
        Blocking delays
        
        Long reads block writes
        
        Large transactions stall system
        
        Unstable under load
        
        Poor high-percentile performance
      - Predicate Locks
        
        Prevent phantom reads → Ensure serializability
        
        Behavior
        
        Lock matches query condition
        
        Applies to future rows
        
        Expensive to manage
      - Index-Range Locks
        
        Aka next-key locking
        
        Approximation of predicate locks
        
        Implementation
        
        Lock index entries or ranges
        
        Based on query access path
        
        Trade-off
        
        Less precise
        
        Much better performance
        
        Fallback to Table-level lock if no index
    - - Serializable snapshot isolation
        
        Full serializability
        
        Near snapshot isolation performance
        
        Built on Snapshot Isolation
        
        Reads
        
        Transaction-wide consistent snapshot
        
        MVCC, readers don’t block writers
        
        Writes
        
        First-committer-wins style conflicts
        
        Extra checks for anomalies
        
        Optimistic Concurrency Control
        
        If anomaly detected → abort
        
        Application retries transaction
        
        Don’t block, let transactions run
        
        Check for problems at commit
        
        Detecting Outdated Premises
        
        Read data → decide → write
        
        Premise may change before commit
        
        Track read–write dependencies
        
        Abort if premise invalidated
        
        Two Core Detection Cases
        
        Stale Snapshot Reads
        
        Reads ignore concurrent uncommitted writes
        
        Those writes later commit before your commit
        
        Premise of your decision becomes stale
        
        Writes Affecting Prior Reads
        
        Later writes change earlier query results
        
        Conflicts cause commit-time abort/retry
        
        Tracked via SIREAD predicate/range metadata
        
        Performance
        
        Compared to 2PL
        
        No lock-based deadlocks
        
        No blocking on reads
        
        Compared to Serial Execution
        
        Conflict detection distributed
        
        Scales across cores and shards
        
        Compared to Plain Snapshot
        
        Extra bookkeeping and aborts
        
        Small overhead in many workloads
        
        Workload Constraints
        
        Good For
        
        Short read–write transactions
        
        Long read-only analytics
        
        Less Ideal
        
        Very high contention
        
        Many conflicting long transactions
        
        Read/Write Transactions
        
        Should be short-lived
        
        Reduce conflict and abort rate
        
        Read-Only Transactions
        
        Can be long-running analytics
        
        Safe on consistent snapshots
- - - - Use the client connection to group ops
      - On a given connection, everything between
        
        BEGIN TRANSACTION and
        
        COMMIT
        
        is one transaction
      - If the connection breaks (TCP interrupted), the TR is aborted
    - - Lack a built-in way to group mult ops into a TR
      - May offer multi-obj commands (e.g., multi-put on several keys), but
        
        These don't necessarily have transaction semantics
        
        Op can succeed for some keys, fail for others, leaving DB partially updated
    - - Relational / graph references
        
        Need valid, up-to-date references
        
        Otherwise conflicting data
      - Document model
        
        Single-doc Us need no transactions
        
        But no joins encourages denorm usage
        
        Denorm data spans across mult docs
        
        Need multi-doc U in one go
        
        Transactions prevent denorm data drift (out-of-sync)
      - Secondary indexes
        
        Must update indexes on every change
      - Without transactions
        
        Error handling much more complex
        
        Complicated recovery logic
        
        Concurrency bugs
  - - - Prevent partial or corrupted writes
      - Avoid mixed old and new data
      - Ensure complete or no update
    - - Network interruption mid-write
      - Power loss during overwrite
      - Concurrent read during write
    - - Atomicity via log for crash recovery
      - Isolation via object-level locking
    - - Atomic increment
        
        Avoids read-modify-write cycles
      - Conditional write
        
        Allows W only if values hasn't been concur changed by smbd else
      - Ther're not transactions since operate on one key/row/doc at a time