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

Today, I will talk to you about the lock level of mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Lock level

Lock level is what we usually call lock granularity.

The type of lock is divided into read lock and write lock, which is easy to distinguish. You can think of it this way: if there are additions, deletions and changes, it is to write the lock. If it is a query, it is a read lock. The granularity of the lock is the scope of the lock, which is divided into row lock and table lock. The scope of the lock is related to a number of factors, including the transaction isolation level and whether or not indexes are used.

The database engine has multiple granularity locks that allow a transaction to lock different types of resources. To minimize locking overhead, the database engine automatically locks resources at a level appropriate for the task.

Locking at a smaller granularity (such as rows) can increase concurrency, but it is more expensive because if many rows are locked, you need to hold more locks. Locking at a larger granularity, such as a table, reduces concurrency because locking the entire table restricts other transactions' access to any part of the table. However, it is less expensive because there are fewer locks to maintain.

Lock hierarchy

The database engine usually must acquire locks at multiple granularity levels in order to fully protect resources. The locks on this set of multi-granularity levels are called lock hierarchies. For example, in order to fully protect the reading of the index, the database engine instance may have to acquire shared locks on rows and intended shared locks on pages and tables.

MySQL has three levels of locks: page level, table level, and row level

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.

The characteristics of MySQL locks can be roughly summarized as follows:

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: 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 general.

After reading the above, do you have any further understanding of the lock level in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.