Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the knowledge points of MySQL lock, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The concept of lock

①, lock, is a kind of tool that we want to hide from the outside world in real life.

②, in a computer, is a mechanism that coordinates multiple processes or threads to access a resource concurrently.

③, in addition to the contention of traditional computing resources (CPU, RAM, Icano, etc.), data is also a kind of resource for many users to share and access.

④, how to ensure the consistency and effectiveness of concurrent data access is a problem that must be solved in all databases.

The conflict between ⑤ and lock is also an important factor affecting the performance of database concurrent access.

Overview of MySQL locks

Compared with other databases, the locking mechanism of MySQL is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locks (table-level locking). The BDB 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.

Table-level lock: a table-level lock is the largest lock granularity in MySQL, which means that the entire table of the current operation is locked. It has the advantages of low overhead and fast locking; there is no deadlock; the locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Row-level locks: row-level locks are the finest-grained locks in MySQL, indicating that locks are applied only to the rows of the current operation. 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.

Page-level lock: a page-level lock is a lock whose locking granularity is between row-level lock and table-level lock in MySQL. The speed of table-level lock is fast, but there are many conflicts. There are few row-level conflicts, but they are slow. So take the eclectic page level and lock the adjacent set of records one at a time. BDB supports page-level locks. The 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 average.

As can be seen from the above characteristics, it is difficult to say in general which kind of lock is better, but only according to the characteristics of the specific application, which kind of lock is more appropriate! Only from the perspective of locks: table-level locks are more suitable for query-based applications, with only a small number of applications that update data according to index conditions, such as Web applications. 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.

Give examples to illustrate

①, when buying goods, when there is only one item in stock, when two people buy at the same time, it is the question of who bought it.

②, will use the transaction, first take the item data from the inventory table, and then insert the order, after payment, insert the payment table information.

③, update the number of goods, in this process, the use of locks can protect limited resources and solve the contradiction between isolation and concurrency.

Classification of locks

By operation:

Read lock (shared lock): for the same data, multiple read operations can be performed at the same time without affecting each other.

Write lock (exclusive lock): other write and read locks are blocked before the current write operation is completed.

By granularity:

Watch lock

Row lock

Page lock

These are all the contents of the article "what are the knowledge points of MySQL locks?" Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.