1.33 InnoDB Transactions

1. What Is a Transaction

Database Transactions.md

2. Using Transactions

• Start a transaction: begin or start transaction.
• Commit a transaction: commit.
  • Automatically committed transactions:
    • SHOW VARIABLES LIKE 'autocommit'
    • By default, each statement is an independent transaction.
• Roll back a transaction: rollback.

3. Consistency

3.1. How It Is Implemented

• Database Transactions.md

4. Durability

4.1. How It Is Implemented

• Through REDO logs.
• InnoDB redo log.md

5. Atomicity

• In MySQL, executing a statement happens inside a transaction, and the statement is atomic. If we want to define our own atomic operation, multiple SQL statements can be wrapped in a transaction.

5.1. How It Is Implemented

• Through UNDO logs.
• InnoDB undo log.md

6. Isolation

6.1. Isolation Levels

6.1.1. Read Uncommitted

• Solves dirty writes; dirty reads, non-repeatable reads, and phantom reads can occur.

6.1.2. Read Committed

• Can solve dirty writes and dirty reads; non-repeatable reads can occur.

6.1.3. Repeatable Read

• Can solve dirty writes, dirty reads, non-repeatable reads, and phantom reads.

6.1.4. Serializable

• Can solve dirty writes, dirty reads, non-repeatable reads, and phantom reads.

6.2. How It Is Implemented

• Database Granularity Locks.md
• InnoDB MVCC.md

6.2.1. Read Uncommitted

• MVCC: because records modified by uncommitted transactions can be read, just read the latest version of the record directly.

6.2.2. Read Committed

• MVCC: create a ReadView when each SQL statement starts executing. For example, each select generates an independent ReadView.

6.2.3. Repeatable Read

• MVCC + GapLock
  • The ReadView is created when the transaction starts and is used throughout the lifetime of the transaction.
    • For example, a ReadView is generated during the first select.
  • MySQL solves the phantom-read problem under the RR level through GapLock.
• The core of repeatable read is a consistent read; when a transaction updates data, it can only use a current read. If the row lock of the current record is occupied by another transaction, it needs to enter lock wait.

6.2.4. Serializable

Gap Lock