Logical Architecture
The topmost layer contains the services that aren’t unique to MySQL. The second layer is where the things get interesting. Much of MySQL’s brains are here, including the code for query parsing, analysis, optimization, caching, and all the built-in functions. Any functionality provided across storage engines lives at this level: stored procedures, triggers, and views, for example. The third layer contains the storage engines. They are responsible for storing and retrieving all data stored “in” MySQL. The server communicates with them through the storage engine API. The storage engines don’t parse SQL (One exception is InnoDB, which does parse foreign key definitions, because the MySQL server doesn’t yet implement them itself) or communicate with each other; they simply respond to request from the server.
Connection Management and Security
Each client connection gets its own thread within the server process. The connection’s queries execute within that single thread, which in turn resides on one core or CPU.
Optimization and Execution
The optimizer does not really care what storage engine a particular table uses, but the storage engine does affect how the server optimizes query.
Before even parsing the query, though, the server consults the query cache, which can store only SELECT statements, along with their result sets.
Concurrency Control
MySQL has to do concurrency control at two levels: the server level and the storage engine level.
Read/Write Locks
Lock Granularity
- Table locks
The most basic locking strategy available in MySQL, and the one with the lowest overhead, is table locks.
Although storage engines can manage their own locks, MySQL itself also uses a variety of locks that are effectively table-level for various purpose. For instance, the server uses a table-level lock for statements such as ALTER TABLE, regardless of the storage engine.
- Row locks
Row locks are implemented in the storage engine, not the server. The server is completely unaware of locks implemented in the storage engines, and the storage engines all implement locking in their own ways.
Transactions
Isolation Levels
READ UNCOMMITED
Reading uncommitted data is also known as a dirty read.
READ COMMITED
This level still allows what’s known as a non-repeatable read.
REPEATABLE READ
Solves the problems that READ UNCOMMITTED allows. It guarantees that any rows a transaction reads will “lock the same” in the subsequent reads within the same transaction, but in theory it still allows another tricky problem: phantom reads. Simply put, a phantom read can happen when you select some range of rows, another transaction inserts a new row into the range, and then you select the same range again; you will see the new “phantom” row. InnoDB and Falcon solve the phantom read problem with multi-version concurrency control.
REPEATABLE READ is MySQL’s default transaction isolation level.
SERIALIZABLE
The highest level of isolation, SERIALIZABLE, solves the phantom read problem by forcing transactions to be ordered so that they cannot possibly conflict.
In a nutshell, SERIALIZABLE places a lock on every row it reads. At this level, a lot of timeouts and lock contention may occure.
Deadlocks
The way InnoDB currently handles deadlocks is to roll back the transaction that has the fewest exclusive row locks (an approximate metric for which will be the easier to roll back).
Transaction Logging
Transactions in MySQL
MySQL AB provides three transactional storage engines: InnoDB, NDB Cluster, and Falcon.
AUTOCOMMIT
MySQL operates in AUTOCOMMIT mode by default. This means that unless you’ve explicitly begun a transaction, it automatically executes each query in a separate transaction.
Mixing storage engines in transactions
MySQL doesn’t manage transactions at the server level. Instead, the underlying storage engines implement transaction themselves. This means you cannot reliably mix different engines in a single transaction.
Implicit and explicit locking
InnoDB uses a two-phase locking protocol. It can acquire locks at any time during a transaction, but it does not release them until a COMMIT or ROLLBACK. It releases all the locks at the same time.
InnoDB also supports explicit locking, which the SQL standard does not mention at all:
- SELECT … LOCK IN SHARE MODE
- SELECT … FOR UPDATE
MySQL also supports the LOCK TABLES and UNLOCK TABLES commands, which are implemented in the server, not in the storage engines.
The interaction between LOCK TABLES and transactions is complex, and there are unexpected behaviors in some version. Therefore, we recommend that you never use LOCK TABLES unless you are in a transaction and AUTOCOMMIT is disabled, no matter what storage engine you are using.
Multi-Version Concurrency Control (MVCC)
InnoDB implements MVCC by storing with each row two additional, hidden values that record when the row was created and when it was expired (or deleted). Rather than storing the actual times at which these events occurred, the row stores the system version number at the time each event occurred. This is a number that increments each time a transaction begins. Each transaction keeps its own record of the current system version, as of the time it began. Each query has to check each row’s version numbers against the transaction’s version.
Example: isolation level is set to REPEATABLE READ
SELECT: InnoDB must examine each row to ensure that it meets two criteria:
- InnoDB must find a version of the row that is at least as old as the transaction. This ensures that either the row existed before the transaction began, or the transaction created or altered the row.
- The row’s deletion version must be undefined or greater than the transaction’s version. This ensures that the row wasn’t deleted before the transaction began.
INSERT: InnoDB records the current system version number with the new row.
DELETE: InnoDB records the current system version number as the row’s deletion ID.
UPDATE: InnoDB writes a new copy of the row, using the system version number for the new row’s version. It also writes the system version number as the old row’s deletion version.