6.5

Transaction Isolation Level

The degree to which the isolation within a transaction operates, affecting the visibility of changes made by one transaction to other transactions.

SESSION

Sets the isolation level for all transactions in the current session, which starts when a user connects to the MySQL server and ends when disconnected.

GLOBAL

Sets the isolation level for all transactions submitted to the MySQL server across different sessions.

START TRANSACTION

A statement that begins a new transaction.

COMMIT

A statement that commits the current transaction, saving all changes made.

ROLLBACK

A statement that undoes all actions of the current transaction and returns to the last commit.

SAVEPOINT

A point in a transaction that allows you to roll back to that state without affecting the entire transaction.

ROLLBACK TO

A statement that undoes changes made after a specified savepoint and restarts processing from that savepoint.

RELEASE SAVEPOINT

A statement that discards a specific savepoint, erasing all data associated with it.

Auto-commit

A mode in which each SQL statement is treated as a transaction and committed immediately after execution.

Checkpoint

A mechanism that saves the current state of transactions and data, aiding in recovery and consistency.

Fuzzy Checkpoint

Allows processing to resume before all dirty blocks are saved, improving system availability but complicating recovery.

FLUSH TABLES

A statement that writes all dirty blocks to storage media.

Transaction Commit

The finalization of a transaction that saves all changes made during the transaction to the database.

ACID Properties

A set of properties that ensure reliable processing of database transactions: Atomicity, Consistency, Isolation, Durability.

Atomicity

The property that ensures transactions are all-or-nothing; if one part of the transaction fails, the entire transaction fails.

Consistency

The property that ensures a transaction brings the database from one valid state to another, maintaining data accuracy.

Isolation

The property that ensures that transactions occur independently without interference, maintaining transaction integrity.

Durability

The property that guarantees that once a transaction has been committed, it will remain so, even in the event of a system failure.

Isolation Levels

Different settings that control how transaction integrity is visible to other transactions, including Read Uncommitted, Read Committed, Repeatable Read, Serializable.

Read Uncommitted

The lowest isolation level, where transactions may read data that has been modified but not yet committed by other transactions.

Read Committed

A transaction isolation level where a transaction can only read data that has been committed, preventing dirty reads.

Repeatable Read

An isolation level that ensures if a transaction reads a row, it can read that row again and find the same values.

Serializable

The highest isolation level, which ensures complete isolation from other transactions, effectively making transactions execute serially.