PB

6.5

Page 1: Isolation Levels in MySQL

  • Isolation Levels: Control the visibility of changes in transactions.

    • GLOBAL: Sets isolation level for all transactions across all sessions.

    • SESSION: Sets isolation level for all transactions in the current session.

  • Session Definition: A series of SQL statements from user connection to disconnection.

Page 2: Transaction Control Statements

  • START TRANSACTION: Begins a new transaction.

  • COMMIT: Saves all changes made during the current transaction.

  • ROLLBACK: Reverses changes made in the current transaction.

    • Behavior outside a transaction depends on the auto_commit setting:

    • Auto commit ON: Each statement is its own transaction, committing immediately.

Page 3: Transaction Management

  • ROLLBACK: Default is ON; affects how transactions are managed.

  • SET autocommit = [ OFF | ON ]: Changes the auto commit setting for current session.

  • COMMIT [ AND CHAIN ] [ RELEASE ]: Commits current transaction and can optionally release locks.

  • ROLLBACK [ AND CHAIN ] [ RELEASE ]: Reverses current transaction and can optionally release locks.

Page 4: Savepoints

  • SAVEPOINT: Sets a point within a transaction to which you can later roll back.

    • SAVEPOINT statement: Saves internal transaction data associated with a specified identifier.

  • RELEASE SAVEPOINT statement: Discards the saved data associated with the identifier.

Page 5: Example Transaction Flow

  • Transaction Flow

    • START TRANSACTION;

    • SELECT * FROM Employee;

    • DELETE FROM Employee WHERE Name = 'Lisa Ellison'; // Deleted one row

    • SAVEPOINT First;

    • DELETE FROM Employee WHERE Name = 'Sam Snead'; // Deleted two rows

    • SAVEPOINT Second;

    • ROLLBACK TO First; // Restores table to savepoint First

    • COMMIT; // Finalizes changes to the Employee table.

  • Observations:-Before deletion: 4 rows present. -First Savepoint: 3 rows present after deletion. -Second Savepoint: All deleted, but restored to First savepoint before committed. -Final Table: Three rows saved post transaction completion.

Page 6: Savepoints and Recovery

  • SAVEPOINT: Temporarily saves the state of a transaction.

  • ROLLBACK TO SAVEPOINT: Reverts to the state saved in a specific savepoint, discarding subsequent changes.

  • RELEASE SAVEPOINT: Deletes the specified savepoint and associated data.

  • ROLLBACK: Completely reverses a transaction's changes.

  • Checkpoint Mechanism: Handles dirty blocks and log records:

    1. Suspend database processing.

    2. Write unpaved log records.

    3. Fragment dirty blocks.

    4. Create a checkpoint record.

    5. Resume processing.

Page 7: Checkpoints and System Recovery

  • Checkpoint: Mechanism to prevent loss of data during a failure by saving dirty blocks.

    • Fuzzy Checkpoint: Allows resumption of processing while saving dirty data but complicates recovery.

  • FLUSH: Specific commands like FLUSH TABLES or FLUSH LOGS to save dirty blocks to storage.

  • Transaction Example:

    • Configured checkpoints at 3-second intervals.

    • Updates and log records are processed in main memory, captured every interval, ensuring data is saved reliably to storage.