MySQL Architecture basics

1. Lock mechanism

1) When dealing with concurrent reads and writes, a set of locking systems is usually used to solve the problem. The lock system consists of a read lock (also known as a shared lock) and a write lock (also known as an exclusive lock).

2) Each type of lock operation, such as acquiring a lock, checking whether the lock has been lifted, and releasing the lock, increases the system overhead. The lock strategy is to find a balance between lock overhead and data security. Each of MySQL's storage engines can implement a single lock policy or lock granularity. Lock management is a very important issue in the storage engine design. the two most important locking strategies:

(1) row-level lock: can support the maximum concurrency, but also bring the maximum lock overhead. row-level locks are implemented by the storage engine (such as InnoDB), and the server has no knowledge of how locks are implemented in the storage engine.

(2) Table-level lock: minimal overhead.

Because table updates are generally considered more important than table retrievals, write locks have higher precedence than read locks.

　　Although the storage engine manages its own locks, MySQL itself can use a variety of valid table-level locks, such as the MySQL server using table-level locks in ALTER TABLE statements, regardless of the storage engine.

The following content is only used to discuss MyISAM (e.g.). for tables that use the InnoDB storage engine , readers can test themselves .

　　

　　when using table-level locks, read and write are generally serial. However, using a read local table-level lock can support some type of concurrent write operations , such as the following illustration:

I, at this time the Concurrent_insert variable is 1, and the table does not appear empty (since the table was created, there is no delete operation). , in session 1, the table using the Read local table-level lock, in session 2 can perform the insert operation, but session 1 to unlock tables before you see session 2 just inserted data . The sequence number in the figure identifies the order of operations.

　　

The following three cases can be used as a comparison, entries:

If session 1 uses the Read local table-level lock, Session 2 performs an update operation and is blocked.

If session 1 uses only the read table level lock, naturally, Session 2 will block when inserting data until the read lock is released.

If session 1 obtains a read lock (read and read local), it will error when inserting or updating the operation.

II, at this time the Concurrent_insert variable is 1. Delete the id=4 record, the table appears empty. , the insert operation for session 2 is blocked by using the Read local table-level lock on the table in session 1.

　　

You can use optimize table to defragment it at this point. , the above concurrent insert operation is no longer blocked after the command is used.

　　　　

III, or the table above (with an empty table before optimize table), but set Concurrent_insert to 2. , Session 1 uses a read local table-level lock on the table and session 2 can perform an insert operation.

　　

Summarize:

　　Read local and read are table-level read locks, but the former allows concurrent insert operations on read-locked tables under certain conditions.

　　Concurrent_insert Global variables can be obtained in two ways: Execute show global variables like '%concurrent_insert% ' on the MySQL command line; Alternatively, execute the mysqladmin-u username-p passwd variables |grep concurrent_insert in the shell command line, in addition to the set global Concurrent_insert = 0/ 1/2 set the variable. It is primarily used to control concurrent insert behavior:

When it is 0 , concurrent insertions are not allowed (inconsistent with the actual test?). );

When it is 1 (the default), concurrent insertions are allowed if there is no hole in the table, otherwise concurrent insertions are not allowed ;

When it is 2 , concurrent insertions are allowed, even if the table has holes.

Why is there a hole in the table that affects concurrent insertions? Refer to the Help optimize table command on the MySQL command line ("Deleted rows are maintained in a linked list, and subsequent insertions will reuse the location of the old lines "), and in conjunction with the above discussion, the approximate organization chart is as follows.

　　When there is no hole in the table, the inserted data will be in the bottom-most area of the graph, whether the Concurrent_insert is 1 or 2, the insert operation will not be "competitive" with the lock, so it will not block; When the table is empty, because the system will reuse the empty area, Therefore, the following situation is distinguished: Concurrent_insert is 1 o'clock, it is considered to reuse the empty area, but because it is locked, so the insertion operation is blocked, Concurrent_insert is 2 o'clock, it is considered that the empty area is not used for the first time, but is inserted into the bottom area of the graph, and the locking structure is not "Competitive" relationship, so the insert operation is not blocked.

Optimize table defragmentation, the empty area of the diagram is reused, i.e. no more voids, so concurrent_insert is 1 o'clock, and concurrent insertions can be performed.

Additionally, the updateoperation is blocked because it is a record in the zone that updates the read local lock, and the lock constitutes a "race" relationship.

　　

Resources:

"High Performance MySQL"

Continue to learn in ...

MySQL Architecture Basics