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

Locking is a very important technology to realize database concurrency control. When a transaction makes a request to the system and locks it before operating on a data object. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

What is a lock?

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

Locks ensure the consistency and effectiveness of concurrent data access

Lock conflict is also an important factor that affects the performance of database concurrent access.

Locks are the concurrency control of Mysql at the server layer and the storage engine layer.

Why do you need a lock?

The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If there is no control over concurrent operations, incorrect data may be read and stored, and the consistency of the database will be destroyed.

Locks are used to manage concurrency control of common resources. In other words, in the case of concurrency, there will be resource competition, so it needs to be locked.

Locking solves the problem of ensuring database integrity and consistency in a multi-user environment.

The objects of Lock are transactions, which are used to lock objects in the database, such as tables, pages, and rows. And general lock objects are released only after a transaction commit or rollback (the release time may vary with different transaction isolation levels).

Lock classification

Shared lock | | read lock | | S lock (share lock): other transactions can be read, but not written. Allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset.

Exclusive lock | | write lock | | X lock (exclusive): other transactions cannot be read or written. Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset. Type subdivision:

Intention shared lock (IS Lock/intent share lock)

Intention exclusive lock | | Mutex lock (IX Lock/intent exclusive lock)

Pessimistic lock | | conservative lock (pessimistic locking): it is assumed that concurrency conflicts will occur, blocking all operations that may violate data integrity. Pessimistic locks are locks added at the database level, which block to wait for the lock.

Optimistic lock (optimistic locking): assuming that there are no concurrency conflicts, only check whether the data integrity is violated when the operation is submitted. Optimistic locking is an idea that is implemented by having a version field in the table that you get the first time you read it. When you finish processing the business logic and start to update, you need to check again whether the value of this field is the same as the first time. If the same update, otherwise refuse. The reason for optimism is that the schema is not locked from the database until it is updated to determine whether it can be updated. Disadvantages: when concurrency is high, there are a lot of useless retries. Optimistic lock can not solve the problem of dirty reading.

Failed to upload picture

Retry

Lock policy: lock granularity / granularity dimension (lock granularity)

The cost of locking is expensive, and locking strategy is actually a balance strategy between ensuring thread safety and achieving maximum performance.

Row level lock (row-level locking)

Row lock: that is, only transactions are allowed to read one row of data. The granularity of the row lock is indeed each row of data, of course, it also brings the maximum overhead, but the row lock can support concurrent processing to the maximum extent.

The overhead is high and the locking is slow; deadlocks occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

It not only supports concurrency to the maximum extent, but also brings the maximum lock overhead.

Row-level locks are more suitable for applications with a large number of concurrent updates of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

In InnoDB, locks are acquired step by step, except for transactions consisting of a single SQL, which determines that deadlocks are possible in InnoDB.

Row-level locking is implemented only at the storage engine layer, but not at the Mysql server layer.

Table level lock (table-level locking)

Table locks: allow transaction locks at the row level to exist at the same time as locks at the table level. Locking the entire table has the least overhead, but it also blocks the entire table.

Low overhead, fast locking, no deadlock; large locking granularity, the highest probability of lock conflict and the lowest concurrency.

These storage engines avoid deadlocks by always acquiring all the required locks at once and always acquiring table locks in the same order.

Table-level locks are more suitable for applications that are query-based, with few concurrent users, and only a small number of applications that update data according to index conditions, such as Web applications.

If a user is performing a write operation, he will acquire an exclusive "write lock", which may lock the entire table and block other users' read and write operations.

If a user is performing a read operation, he will first acquire the shared lock "read lock", which runs other read locks to read the table concurrently without interfering with each other. As long as there is no entry of the write lock, the read lock can read unified resources concurrently.

Mysql table-level locks are divided into two types: metadata locks (Metadata Lock,MDL) and table locks.

Metadata lock (Metadata Lock,MDL)

Metadata locks (MDL) do not need to be used explicitly and are automatically added when a table is accessed. This feature needs to be above MySQL5.5 version to be supported.

When making additions, deletions and modifications to a table, the table will be locked by MDL.

Add MDL write locks when making structural changes to the table

MDL lock rules:

Read locks are not mutually exclusive, so you can add, delete, modify and check multiple threads in the same table.

Read-write locks and write locks are mutually exclusive, in order to ensure the security of table structure changes, so if multi-threads add fields to the same table and other table structure operations, it will become serialization and need to wait for locks.

The priority of the MDL write lock is higher than that of the MDL read lock, but the max_write_lock_count system variable can be set to change this situation. When the write lock request exceeds the number set by this variable, the MDL read lock has a higher priority than the MDL read lock. (by default, this number is large, so you don't have to worry about the write lock falling in priority.)

Lock release for MDL must not be released until the end of the transaction

Page lock (page-level locking)

Page-level locking is a unique locking level in MySQL, and it is not very common in other database management software.

Page lock overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

The characteristic of page-level locking is that the locking granularity is between row-level lock and table-level lock, so the resource overhead required to obtain lock and the concurrent processing power that can be provided are also between the above two. In addition, page-level locking is the same as row-level locking, deadlocks occur.

In the process of database resource locking, with the decrease of the granularity of locking resources, the amount of memory needed to lock the same amount of data is more and more, and the implementation algorithm will become more and more complex.

However, with the decrease of the granularity of locking resources, the possibility of lock waiting for application access requests decreases, and the overall concurrency of the system increases.

Page-level locking is mainly used by the BerkeleyDB storage engine.

Global lock

MySQL provides global locks to lock the entire database instance.

FLUSH TABLES WITH READ LOCK

This statement is usually used for backup. When this statement is executed, all open tables in the database will be closed, and all tables in the database will be locked with a global read lock. At the same time, update statements (additions, deletions and modifications) from other threads, data definition statements (table creation, table structure modification) and transaction commits of update classes will be blocked.

After mysql 8.0, mysql can use backup locks directly for backups.

LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE

This lock has a broader scope, and it prevents file creation, renaming, and deletion, including REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE operations, and account management. Of course, these operations can be performed for memory temporary tables, so why are memory tables not subject to these restrictions? Because memory tables do not need to be backed up, there is no need to meet these conditions.

Different storage engines of MySQL support different locking mechanisms.

All storage engines show the locking mechanism in their own way, and the server layer is completely ignorant of the lock implementation in the storage engine:

MyISAM, MEMORY, and CSV storage engines use table-level locks (table-level locking)

The BDB (Berkeley DB) storage engine uses page locks (page-level locking), but also supports table-level locks

The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

InnoDB row locking is achieved by locking index items on the index. The implementation feature of InnoDB means that InnoDB uses row-level locks only when data is retrieved through index conditions, otherwise InnoDB will use table locks!

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use an index. The disadvantage of row-level locking is that it needs to request a lot of lock resources, so it is slow and consumes a lot of memory.

By default, both table and row locks are acquired automatically and no additional commands are required.

However, in some cases, the user needs to explicitly lock the table or control the transaction in order to ensure the integrity of the whole transaction, which needs to be done by using transaction control and locking statements.

There are two biggest differences between InnoDB and MyISAM: one is to support transactions (TRANSACTION); the other is to use row-level locks.

Because Innodb storage engine implements row-level locking, although the performance loss caused by the implementation of locking mechanism may be higher than that of table-level locking, it is much better than MyISAM's table-level locking in terms of overall concurrency processing capacity. When the system concurrency is high, the overall performance of Innodb will have obvious advantages compared with MyISAM.

However, Innodb's row-level locking also has its fragile side, and when we use it incorrectly, it may not only make the overall performance of Innodb not better than MyISAM, but may even be worse.

InnoDB row level lock and table level lock

InnoDB lock mode:

InnoDB implements the following two types of row locks:

Shared lock (S-shared): allows one transaction to read a row, preventing other transactions from acquiring an exclusive lock on the same dataset.

X-exclusive: allows transaction update data to be acquired with exclusive locks, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset.

To support locking operations at different granularities (allowing row locks and table locks to co-exist and implement a multi-granularity locking mechanism), InnoDB also has two internally used Intention Locks locks, both of which are table locks:

Intention shared Lock (IS- intent share lock) transaction to acquire a shared lock for several rows in a table the transaction intends to add a row shared lock to the data row, the transaction must acquire the IS lock of the table before adding a shared lock to the data row.

The intention IX-intent exclusive lock transaction wants to acquire the exclusive lock of several rows in a table. The transaction intends to add an exclusive lock to the data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.

Because the InnoDB storage engine supports row-level locks, intentional locks do not actually block any request except a full table scan. So the compatibility between table-level intention locks and row-level locks is as follows

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; conversely, if the two are not compatible, the transaction waits for the lock to be released

If you regard the locked object as a tree, then lock the lowest-level object, that is, the finest-grained object, then you need to lock the coarse-grained object first. In the figure above, if you need to lock the record r on the page, you need to IX the database A, the table, the page, and finally the record r. If any of these parts cause a wait, the operation needs to wait for the coarse-grained lock to complete. For example, before adding an X lock to record r, a transaction has already applied an S table lock to Table 1, then an S lock already exists on Table 1, and then the transaction needs to add IX to record r to Table 1. Due to incompatibility, the transaction needs to wait for the completion of the table lock operation.

What exactly is the function of intention lock?

Innodb's intention to lock the situation where the primary user has multiple granularity locks. For example, transaction A should add an S lock to a table, and if a row in the table has been locked by transaction B, then the application for that lock should also be blocked. If there is a lot of data in the table, it will be expensive to check the lock flag row by row, and the performance of the system will be affected. In order to solve this problem, a new lock type can be introduced at the table level to represent the locking of the row to which it belongs, which leads to the concept of "intention lock".

For example, if there are 100 million records in the table and transaction A locks several of the records, transaction B needs to add a table-level lock to the table, and if there is no intention lock, then go to the table to find out whether the 100 million records are locked. If there is an intention lock, then if transaction A first adds an intention lock and then an X lock before updating a record, transaction B first checks whether there is an intention lock on the table and whether the existing intention lock conflicts with the lock it is going to add. If there is a conflict, wait until transaction An is released without having to detect it one by one. When transaction B updates the table, it doesn't need to know which row is locked, it just needs to know that one row is locked anyway.

The main function is to deal with the contradiction between row locks and table locks, showing that "a transaction is holding a lock on a row, or is ready to hold a lock."

InnoDB row lock implementation:

The row lock is added to the index

The index data structure in Innodb is a B + tree, and the data is arranged in order, and the corresponding data is found layer by layer from the root node to the leaf node.

A normal index, also known as a secondary index, the leaf node holds the primary key value. The index on the primary key is called a clustered index, and each record in the table is stored on the leaf node of the primary key. When querying data through the secondary index select, the corresponding primary key value is found in the secondary index, and then the primary key value is used to find the record in the clustered index.

For example, when you query with name=Alice, you will find that the corresponding primary key value is 18, and then use 18 to find that the record content of name=Alice in the following clustered index is 77 and Alice.

The data of each row in the table is stored by the organization in the clustered index, so it is called the index organization table.

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

Whether using a primary key index, a unique index, or a normal index, InnoDB uses row locks to lock the data.

Row locks can be used only if indexes are actually used in the execution plan: even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks full table scans are more efficient, such as for very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL (you can check the execution plan of SQL through explain) to confirm that the index is actually used. (read more: MySQL Index Summary)

Because MySQL's row locks are locks for indexes, not records, although multiple session accesses records of different rows, lock conflicts occur if you use the same index key (session that uses these indexes later needs to wait for the lock to be released using the session of the index before acquiring the lock). You should pay attention to this when applying design.

Algorithm of row lock (algorithm dimension)

Record Lock (single line record)

Gap Lock (gap lock, which locks a range, but does not contain locked records)

Next-Key Lock (Record Lock + Gap Lock, locks a range and locks the record itself, MySql prevents phantom reading, which is implemented using this lock)

Record lock, gap lock and key lock are all exclusive locks.

Record lock (Record Lock)

Only one record of the table is locked after the transaction is locked.

Failed to upload picture

Retry

Record lock occurrence condition: accurate conditional hit, and the hit condition field is the only index

For example: update user_info set name=' Zhang San 'where id=1, where id is the only index.

Record Lock always locks the index records, and if the InnoDB storage engine table is created without any indexes, then the InnoDB storage engine uses an implicit primary key to lock.

The function of the record lock: after adding the record lock, it can avoid the repeated reading problem that the data is modified during the query, and it can also avoid the dirty reading problem of being read by other transactions before the modified transaction is committed.

Key lock (Next-Key Lock)

Key lock is the default algorithm of INNODB row lock, it is the combination of record lock and gap lock, key lock will lock the query records, and also lock all the gap space in this range query, and it will lock the next adjacent interval.

Key lock occurrence condition: range query and hit, query hit index.

For example, the data in the following table executes select * from user_info where id > 1 and id

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.