Shulou(Shulou.com)06/01 Report--

1.MySQL server logical architecture

Photo Source: MySQL official website

Each connection generates a thread on the MySQL server (threads are managed internally through a thread pool). For example, when a select statement enters, MySQL will first find out whether the select result set is cached in the query cache, and if not, continue to parse the process of → optimizing → execution; otherwise, it will get the result set directly from the cache.

2.MySQL lock shared lock and exclusive lock (Shared and Exclusive Locks)

Both shared and exclusive locks are standard row-level locks.

1) shared lock S: a standard read lock that allows multiple connections to read the same resource concurrently at the same time without interfering with each other.

2) exclusive lock X: also known as write lock, a write lock blocks other write or read locks, ensuring that only one connection can write data at a time, while preventing other users from reading and writing to this data.

Lock type English name also known as common at the same time shared lock SShared Locks read lock at the same time, allowing multiple connections to read the same resource concurrently without interference with each other. Both are the policies of the lock mechanism itself, and the locks are distinguished by these two strategies. The XExclusive Locks write lock has only one connection to write data at a time, while preventing other users from reading and writing to this data. Dit.2.2 intention lock (Intention Locks)

Background: the coexistence scene of multi-granularity lock.

InnoDB supports multi-granularity locks (lock granularity: can be divided into row locks and table locks), allowing row locks and table locks to coexist. In order to achieve multiple levels of granularity locking, InnoDB uses intent locks.

Intention lock: a table-level lock. First declare an intention in advance and acquire a table-level intention lock (shared intention lock IS or exclusive intention lock IX). If the acquisition is successful, some rows of the table will be locked (S or X) later or will be in the process of being (only allowed).

Note: except for LOCK TABLES...WRITE, all rows in the table are locked, and the other scenario intention locks do not actually lock any rows.

Intention protocol lock: before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or a stronger lock on the table. Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

The background of the intention lock implementation is the coexistence of multi-granularity locks, and the compatibility is as follows:

XIXSISXConflictConflictConflictConflictIXConflictCompatibleConflictCompatibleSConflictConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible

Conflict: mutually exclusive

Compatible: compatible

The intention lock is a weak lock that only expresses intention and is compatible with parallelism between the locks (IS,IX is compatible with parallelism). X and IS,IX are mutually exclusive, S and IX are mutually exclusive. Intention lock is a weaker lock than Xamp S, which has a predictive meaning. First acquire a weaker IS,IX lock, and if the acquisition fails, you do not have to acquire a stronger Smeme X lock.

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.