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

This article shows you how to achieve logical architecture and concurrency control in MYSQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The logical architecture of Mysql is as follows:

Photo source: geek time

The Server layer includes connectors, query caching, parsers, optimizers, executors, etc., covering most of the core service functions of MySQL

And all built-in functions (such as date, time, math, and encryption functions, etc.), all cross-storage engine functions are implemented at this layer, such as stored procedures, triggers, views, and so on.

Connector

Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections.

Query cache

After MySQL gets a query request, it will first go to the query cache to see if this statement has been executed before.

If it is in the cache, the result is returned directly.

If the statement is not in the query cache, the later stages of execution will continue, and when the execution is complete, the execution results will be stored in the query cache.

Analyzer

The analyzer will first do "lexical analysis, grammatical analysis".

What you enter is a SQL statement made up of multiple strings and spaces, and MySQL needs to identify what the string is and what it represents.

Optimizer

The optimizer decides which index to use when there are multiple indexes in the table.

Or when a statement has multiple table associations (join), determine the join order of each table.

Actuator

The executor starts executing the statement and returns the result set to the client.

The storage engine layer is responsible for data storage and extraction.

Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on.

The most commonly used storage engine today is InnoDB, which has been the default storage engine since MySQL version 5.5.5.

Concurrency control

Let's take a look at what features Mysql has in terms of concurrency control.

Read-write lock

The read lock is shared, and multiple clients can read the same resource at the same time, complementary interference.

Write locks are exclusive, which means that one write lock blocks other write and read locks.

Lock granularity

Table lock, which locks the entire table, is a server-high lock that ignores the locking mechanism of the storage engine.

Row-level locking, locking data rows, row-level locking is only implemented at the storage engine layer.

Transaction ACID

Atomicity (atomicity)

A transaction must be regarded as an indivisible minimum unit of work, the whole transaction is either executed successfully, or all failed rollback, it is impossible to partially succeed and partially fail, this is atomicity.

Consistency (consistency)

A database always transitions from one consistent state to another, for example, integrity constrains a+b=10, and a transaction changes a, then b should change with it.

Isolation (isolation)

In general, changes made by a firm are not visible to its transaction until it is finally committed.

Persistence (durability)

Once the transaction commits, its changes are permanently saved to the database.

Upgrading the lock granularity and implementing the ACID feature will increase the overhead of the system.

A database that implements ACID usually requires stronger cup processing power, more memory, and more disk space than a database that does not implement ACID.

You can choose the appropriate storage engine according to whether the business needs transaction processing or not.

Isolation level

RED UNCOMMITTED (read not submitted)

At the RED UNCOMMITTED level, changes in a transaction are visible to other transactions, even if they are not committed.

Transactions can read uncommitted data, which is called "Dirty Read", because it is likely to read dirty data from the intermediate process, not the final data.

RED COMMITTED (submit for read)

The default isolation level for most database systems is RED COMMITTED, but MYSQL is not.

What RED COMMITTED says is that a transaction can only read data that has been committed by another transaction, so it is called commit read.

This transaction level is also called unrepeatable read (nonrepeatableread), because two identical queries may result in different results.

REPEATABLE READ (repeatable)

REPEATABLE READ solves the problem of dirty reading.

This level ensures that the results of reading the same records multiple times in the same transaction are consistent.

But the problem of phantom reading cannot be solved. The so-called phantom reading means that when a transaction reads a record in a certain range, another transaction inserts a new record in that range, and when the previous transaction reads the record in that range again, it is found that there is an extra row, resulting in a phantom row.

SERIALIZABLE (serializable)

SERIALIZABLE is the highest level of isolation.

It avoids the problem of phantom reading mentioned earlier by forcing the serial execution of transactions.

To put it simply, SERIALIZABLE locks every row of data read, so it can cause a lot of timeouts and lock contention problems.

The default isolation level for Mysql's InnoDB storage engine is REPEATABLE READ (repeatable readable) and prevents phantom reads through a gap lock (next-key locking) policy.

Gap locks cause InnoDB to lock not only the rows involved in the query, but also gaps in the index to prevent the insertion of magic rows.

The above is how to implement logical architecture and concurrency control in MYSQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.