Shulou(Shulou.com)05/31 Report--

This article mainly explains "the lock mechanism of MySQL and the life cycle of the table". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "the lock mechanism of MySQL and the life cycle of the table".

Concurrency and isolation Control Mechanism in 1.MySQL

Meta-data metadata lock: implemented in the table cache cache to provide isolation operations for DDL (Data Definition Language). A special meta-data metadata type is called Name Lock. (SQL layer)

Table-level table-level data lock (SQL layer)

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

Global read lock-FLUSH TABLES WITH READ LOCK (SQL layer)

two。 The lifecycle of the table during statement execution

DML (Data Manipulation Language) example:

All tables used in the calculation statement

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

Change the data after waiting for the global read lock

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

Execute statements: call: handler::write_row () / read_rnd () / read_index (), etc.; implicitly invoke the engine-level engine-level lock mechanism

In each table: release the data lock of the table

In each table: release the DDL lock on the table and put the table back in the table cache cache

The same is true of the DDL statement, which has no typical execution plan.

3. Acquire meta-data metadata lock

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

Meta-data metadata locks are any of the following: shared shared locks, implicitly locked and "used" only by marking TABLE objects; semi-exclusive semi-exclusive locks, also known as Name Lock,RENAME operations, add this lock to the source table and destination; exclusive exclusive, also known as exclusive name lock,CREATE TABLE. The SELECT operation adds this lock to the target table, if not.

4. Table cache (table cache)

Is a HASH variable called open_cache

The TABLE object is a HASH element

HASH operations are protected by LOCK_open mutex mutexes

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 by connected columns (a linked list)

Each TABLE instance has a copy of the table cache cached version-the TABLE instance saves a version that is not the same as the current table cache cached version, but saves the old and deleted from the cache

TABLE instances used by some statements are marked as invalid for others-- this is the nature of meta-data metadata locks

A TABLE instance in the cache usually has a valid handle instance attached 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 concept / object combination is not only used for caching or locking: LOCK_open mutex mutexes also use other operations, such as atomicity of table creation on disk and in processing.

Typical operations, important from isolation level Pov: turn on and off tables when queried by statements-- shared shared locks; force and wait until all instances of the table are closed-- exclusive exclusive (but not completely); Name Lock-- in special cases, when there is no instance of TABLE on hand, you can only use a special placeholder (even the table may not exist).

Thank you for your reading, the above is the content of "the lock mechanism of MySQL and the life cycle of the table". After the study of this article, I believe you have a deeper understanding of the lock mechanism of MySQL and the life cycle of the table. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.