Learn more about MySQL lock mechanism

Concurrency and isolation control mechanism in 1.MySQL

Meta-data metadata Lock: Implemented in the table cache buffer, provides isolation for DDL (Data Definition Language). A special Meta-data metadata type, called Name Lock. (SQL Layer)

Table-level Table-level data Lock (SQL layer)

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

Global read lock--FLUSH TABLES with read lock (SQL layer)

2. The life cycle of the table in the statement execution

DML (Data manipulation Language) Example:

All tables used by the calculated statement

In each table, open the Open table--Get the Table object from the table cache buffer and add the Meta-data metadata lock to the table

Change data after waiting for global read lock

In each table: Lock lock Table--Add Table-level data lock to table

Execute statement: Call: Handler::write_row ()/read_rnd ()/read_index (), etc., implicitly invoke engine-level engine-level lock mechanism

In each table: releasing data locks on the table

In each table: Frees the table's DDL lock and puts the table back in the table cache

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

3. Get Meta-data metadata Lock

The implementation of the Meta-data metadata lock as a property of the Table object represents the table cache buffer.

The Meta-data metadata lock is any of the following: Shared shared locks-implicitly locked, only "used" by marking the table object; semi-exclusive, also known as the name Lock,rename operation, adds the lock to the source table and target; Exclusive exclusive, also called exclusive name Lock,create TABLE ... The select operation adds this lock to the target table, if not.

4. Watch cache (table cache)

It's a hash variable called Open_cache.

The Table object is a hash element

The hash operation is protected by the Lock_open mutex 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, connected by connected columns (a linked list)

Each table instance has a copy of the cache version of the table--the version saved by the table instance does not match the current table cache version, but saves the old and deleted from the cache

The table instance used by some statements is marked as invalid for other statements-this is the nature of the Meta-data metadata lock

The table instance in the cache typically has a valid handle instance connected to it

Internal operations (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 concept/object combination is not only used for caching or locking: Lock_open Mutex mutexes are also used for other operations, such as the atomic nature of creating tables on disk and in processing

Typical operation, from the importance of isolation level POV (NOTE: Isolation POV did not study what it meant): When the statement query, open and close the table-shared shared lock; Force and wait until all instances of the table are closed-exclusive exclusive (but incomplete); Name Lock--specifically, when there is no table instance on hand, only a special placeholder can be used (even the table may not exist).