mysql--concurrency control (lock)

Core points of Knowledge:

1. Table and row-level locks represent the level of lock, and read and write locks represent the true type of lock.

2. Read locks are shared locks, share the same resource, do not interfere with each other; write locks are exclusive locks, and for security reasons, write locks block other read and write locks.

3. The cost of table locks is minimal, and row-level locks are the most expensive.

4. Use table locks regardless of the storage engine, row-level locks are implemented by the storage engine, not by the MySQL server.

5. Each lock has a specific purpose, and the seemingly useless table lock is very well tuned to alter table.

Whenever more than one query modifies the data at the same time, concurrency control problems arise. The most straightforward way to solve concurrency problems is to lock them up to limit the start of a transaction.

Thus, what type of locks are created to make system resources more efficient and faster to execute is the main problem.

Of course, this chapter does not explain how to build locks, which is the problem of the optimization level. Here are a few conceptual questions, including read locks, write locks, table locks, and so forth.

1. Read lock/write lock

The system uses a lock system to solve the problem when it handles concurrency or writes. This type of lock consists of two types of locks, often called shared and exclusive locks , or read and write locks.

About the concept of locks: Read locks on a resource are shared, or non-blocking . at the same time, multiple users can read the same resource without interfering with each other.

On the other hand, a write lock is exclusive , which means that a write lock blocks other read and write locks, which are for security reasons

At a given time, only one user can write to the resource to prevent users from reading the same resource while the user is writing.

For a database, locks occur anytime, anywhere. When a user modifies a portion of the data, MySQL prevents other users from reading the consolidated data.

Most of the time, MySQL implements the internal management of locks in a transparent manner.

2. Size of Lock

One way to improve the concurrency of shared resources is to make locked objects more selective . Remember to lock only part of the data that you need to modify, not all resources.

Ideally, only the data slices to be modified are precisely locked. Any time, on a given resource, the smaller the amount of data being locked, the more concurrent modifications can be allowed, as long as there is no conflict between each other.

The problem with this is that locking also consumes system resources . each lock operation, checking whether the lock has been lifted, and releasing the lock, will increase the overhead of the system .

If the system spends a lot of time managing locks rather than reading/writing data, the system integrity may be affected.

The so-called locking strategy is to seek a balance between lock overhead and data security , which can also affect system performance,

Most commercial database servers do not offer more choices, usually by imposing row-level locks on the table and providing sophisticated means to improve the performance of the system in the event of a lock.

MySQL, on the other hand, offers a variety of options. Each MySQL storage engine can implement a unique lock policy or lock particle.

Lock management is a very important issue in the storage engine design.

Adjusting the granularity to a certain level may provide better performance for an application, but it may also make the storage engine unsuitable for other purposes.

Since MySQL can provide a variety of storage engines, it does not require a generic solution. Two of the most important locking strategies are described below.

Table lock

MySQL supports most basic lock policies, where the least expensive lock policy is a table lock .

The table lock locks the entire table. When a user writes to a table (such as INSERT, delete, update), the user can obtain a write lock.

Write locks prohibit read/write operations by other users. In addition, only when no one writes, the user can obtain a read lock, and the read lock is non-conflicting.

in a particular environment, table locks may perform well . For example, a READ local table lock supports some kind of concurrent write operation.

In addition, the write lock has a change priority over the read lock, and even if a read operation user is queued, an applied write lock can still be queued at the forefront of the lock queue (the write lock is placed before the read lock, and the read lock cannot be queued before the write lock).

Although the storage engine manages its own locks, MySQL itself can use a variety of valid table locks for a variety of purposes.

For example, a MySQL server can use a table lock in a statement, such as an ALTER TABLE statement, regardless of the storage engine, and with little expense.

Row-level lock (row locks)

row-level locks can support the maximum concurrent processing (and also the maximum lock overhead). As is well known, row-level locks are implemented in the InnoDB and Falcon storage engines, as well as in some other storage engines.

row-level locks are implemented by the storage engine and not by the MySQL server . All storage engines implement locking mechanisms in their own way.

mysql--concurrency control (lock)