Learn more about MySQL Lock Mechanism

1. MySQL concurrency and Isolation Control Mechanism

Meta-data metadata lock: Implemented in the table cache and provides isolation for DDL (Data Definition Language. A special meta-data metadata type called Name Lock. (SQL layer)

Table-level data lock (SQL layer)

Storage engine-specific mechanisms-row locks, page locks, table locks, versioning version (implemented in the engine)

Global read lock -- flush tables with read lock (SQL layer)

2. Table lifecycle during statement execution

DML (Data Manipulation Language) Example:

All tables used by calculation statements

In each table: open the open TABLE -- get the table object from the TABLE cache, and add the meta-data metadata lock to the table.

Wait for the global lock to change the data

In each table: lock table -- add table-level data lock to the table

Execute the statement: Call: handler: write_row ()/read_rnd ()/read_index (); call the engine-level Lock Mechanism implicitly.

In each table: Release the table data lock

In each table: Release the table DDL lock and put the table back into the table cache.

The same is true for DDL statements. There is no typical execution plan.

3. Get the meta-data metadata lock

The metadata lock Implementation of meta-data is an attribute of the TABLE object. The TABLE object represents the table cache.

Meta-data metadata locks are any of the following types: shared locks-implicitly locked, only by marking the TABLE object "used"; semi-exclusive locks, also called Name Lock, the RENAME operation adds this lock to the source TABLE and target. exclusive, also called exclusive name lock, create table... the SELECT Operation adds this lock to the target table, if not.

4. table cache)

Is a HASH variable called open_cache

The TABLE object is a HASH element.

The HASH operation is protected by LOCK_open mutex.

Internal structure (The table cache: internal structure)

In the cache, each physical TABLE may be represented by multiple TABLE instances.

All TABLE instances of the same TABLE are connected through a linked list.

Each TABLE instance has a copy of the table cache version. The version saved by the TABLE instance is not the same as that of the current table cache version. Instead, the old and deleted

TABLE instances used by some statements are marked as invalid for other statements. This is the essence of the meta-data metadata lock.

A table instance in the cache usually has a valid handle instance connected to it.

Internal Operation (The table cache: operations)

The main code is: SQL/SQL _base.cc, SQL/lock. cc, SQL/table. h, SQL/SQL _table.cc

Main Methods: open_table (), close_thread_tables (), close_cached_table (), lock_table_names ()

In fact, a combination of concepts and objects is not only used for caching or locking: LOCK_open mutex also uses other operations, such as atomicity created on disks and tables in processing.

Typical operations, from the importance of the isolation level povs (Note: isolation povs did not find out what it means): when a statement is queried, open and close the table-shared lock; force and wait until all instances of the TABLE are closed -- exclusive (but not completely); Name Lock -- in special cases, when there is no TABLE instance on hand, only one special placeholder can be used (or even the table may not exist ).