GNU Info

(mysql.info)InnoDB transaction model

---

Next: Implementation Prev: Moving Up: InnoDB

---

Enter node , (file) or (file)node

InnoDB transaction model
------------------------

In the InnoDB transaction model the goal has been to combine the best
sides of a multiversioning database to traditional two-phase locking.
InnoDB does locking on row level and runs queries by default as
non-locking consistent reads, in the style of Oracle.  The lock table
in InnoDB is stored so space-efficiently that lock escalation is not
needed: typically several users are allowed to lock every row in the
database, or any random subset of the rows, without InnoDB running out
of memory.

In InnoDB all user activity happens inside transactions. If the auto
commit mode is used in MySQL, then each SQL statement will form a
single transaction. If the auto commit mode is switched off, then we
can think that a user always has a transaction open. If he issues the
SQL `COMMIT' or `ROLLBACK' statement, that ends the current
transaction, and a new starts. Both statements will release all InnoDB
locks that were set during the current transaction. A `COMMIT' means
that the changes made in the current transaction are made permanent and
become visible to other users. A `ROLLBACK' on the other hand cancels
all modifications made by the current transaction.

InnoDB consistent read

Consistent read

InnoDB locking reads

Locking reads

InnoDB Next-key locking

Next-key locking: avoiding the phantom problem

InnoDB Locks set

Locks set by different SQL statements in InnoDB

InnoDB Deadlock detection

Deadlock detection and rollback

InnoDB Consistent read example

An example of how the consistent read works in InnoDB