Summary of Knowledge Points of MySQL Lock

The concept of lock

(1) Lock is a tool used for us to hide from the outside world in real life.

2. In a computer, it is a mechanism to coordinate multiple processes or threads to access a certain resource concurrently.

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

4. How to ensure the uniformity and validity of concurrent data access is a problem that all databases must solve.

5. Lock collision is also an important factor affecting the performance of database concurrent access.

Overview of MySQL Lock

Compared with other databases, the locking mechanism of MySQL is relatively simple, and its most remarkable 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 locking (page-level locking), but also supports table-level locking. The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but by default row-level locks are used.

Table-level lock: Table-level lock is the largest lock granularity in MySQL, which means locking the whole table of current operation. It has low overhead and fast locking; There will be no deadlock; The lock granularity is large, the probability of lock collision is the highest, and the concurrency is the lowest.

Row-level lock: Row-level lock is the finest lock granularity in MySQL, which means that only the currently operated row is locked. High overhead and slow locking; There will be deadlocks; The lock granularity is the smallest, the probability of lock collision is the lowest, and the concurrency is the highest.

Page-level lock: Page-level lock is a kind of lock with lock granularity between row-level lock and table-level lock in MySQL. Table-level locking is fast, but there are many conflicts. There are few row-level conflicts, but the speed is slow. Therefore, a compromise page level is taken, and one adjacent group of records is locked at a time. BDB supports page-level locking. Overhead and locking time are between table lock and row lock. There will be deadlocks; Locking granularity is between table lock and row lock, and concurrency is 1.

It can be seen from the above characteristics that it is difficult to say which lock is better in general, but which lock is more suitable according to the characteristics of specific applications! ! From a lock-only perspective: Table-level locks are better suited for applications that focus on queries and have only a small amount of data updated according to index conditions, such as Web applications. Row-level locking is more suitable for applications where a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries are also available, such as some online transaction processing (OLTP) systems.

Illustrate with examples

(1) When buying goods, when there is only one commodity inventory, when two people buy at the same time, who bought it.

② Transactions will be used. First, the data of items will be taken out from the inventory table, and then the order will be inserted. After payment, the payment table information will be inserted.

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

Classification of locks

Divided by operation:

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

By particle size:

Table lock Row lock Page lock

The above is about Mysql lock all relevant knowledge points, thank you for reading and support for this site.