How to understand the mysql lock mechanism

Overview of MySQL locks

Compared with other databases, MySQL’s lock mechanism is relatively simple, and its most significant The characteristic is that different storage engines support different lock mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking, but it also supports table-level locks. 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 granularity in MySQL, which means locking the entire table of the current operation. It has low overhead and fast locking; no deadlock will occur; the locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Free learning video tutorial sharing: mysql video tutorial

Row-level lock: Row-level lock is the most granular lock in MySQL, which means it is only for the current The row being operated is locked. The overhead is high and locking is slow; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

Page-level lock: Page-level lock is a lock in MySQL whose locking granularity is between row-level locks and table-level locks. Table-level locks are fast, but have many conflicts. There are fewer row-level conflicts, but it is slower. So a compromised page level was adopted, locking a group of adjacent records at a time. BDB supports page-level locks. The overhead and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average.

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

Examples

1. When purchasing a product, when there is only one product in stock, and two people purchase it at the same time, who buys it? The problem.

2. 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.

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

Classification of locks

By operation:

Read lock (shared lock): multiple reads for the same data 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.

According to granularity:

Table lock, row lock, page lock

Recommended related article tutorials: mysql tutorial

The above is the detailed content of How to understand the mysql lock mechanism. For more information, please follow other related articles on the PHP Chinese website!