Summary of MySQL lock knowledge points

The concept of lock

①. Lock, in real life, is a tool we use when we want to hide from the outside world.

②. In a computer, it is a mechanism for coordinating multiple processes or threads to concurrently access a resource.

③. In the database, in addition to the contention for traditional computing resources (CPU, RAM, I/O, etc.), data is also a resource that is shared and accessed by many users.

④. How to ensure the consistency and effectiveness of concurrent data access is a problem that all databases must solve.

⑤. Lock conflict is also an important factor affecting the concurrent access performance of the database.

Overview of MySQL Locks

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking, but also supports table-level locking. The InnoDB storage engine supports both row-level locking and table-level locking, but row-level locking is used by default.

Table-level lock: Table-level lock is the lock with the largest locking granularity in MySQL, which means locking the entire table of the current operation. It has low overhead and fast locking; deadlock will not occur; the locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Row-level lock: Row-level lock is the most fine-grained lock in MySQL, which means that only the row currently being operated is locked. The overhead is high and locking is slow; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Page-level lock: A page-level lock is a type of lock in MySQL whose locking granularity is between row-level lock and table-level lock. Table-level locks are fast but have many conflicts. There are fewer row-level conflicts, but the speed is slow. Therefore, a compromise page level is adopted to lock a group of adjacent records at a time. BDB supports page-level locking. The overhead and locking time are between table locks and row locks; deadlocks may occur; the locking granularity is between table locks and row locks, and the concurrency is average.

From the above characteristics, it can be seen that it is difficult to say in general which lock is better. We can only say which lock is more suitable based on the characteristics of the specific application! ! From the perspective of locks only: table-level locks are more suitable for applications that are query-oriented and only update data in small amounts based on index conditions, such as Web applications. Row-level locks are more suitable for applications that have a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

Example

① When purchasing a product, if there is only one item in stock and two people are trying to buy it at the same time, who will get it?

②. Transactions will be used to first retrieve the item data from the inventory table, then insert the order, and after payment, insert the payment table information.

③. Update the quantity of goods. In this process, using locks can protect limited resources and resolve the contradiction between isolation and concurrency.

Lock classification

By operation:

1. Read lock (shared lock): For the same data, multiple read operations can be performed simultaneously without affecting each other.
2. Write lock (exclusive lock): Before the current write operation is completed, other write locks and read locks will be blocked.

By particle size:

1. Table Lock
2. Row Lock
3. Page Lock

The above is all the relevant knowledge points about MySQL locks. Thank you for your reading and support for 123WORDPRESS.COM.