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

The following together to understand the practical application of MySQL row lock, page lock and table lock, I believe we will certainly benefit a lot after reading, the text is not more refined, I hope MySQL row lock, page lock and table lock practical application of this short content is what you want.

MySQL can be subdivided into row locks, page locks, and table locks according to the granularity of locks.

row locks

The locking granularity of row locks is the finest in MySQL and is applied to the InnoDB storage engine, locking only the current row of the operation. Concurrency case, the probability of lock waiting is low, support a large number of concurrency, but the cost is high, locking slow, and deadlock will occur.

2. There is a prerequisite for using row locks in InnoDB: when retrieving data, you need to pass the index! Because InnoDB implements row locking by locking the index entries of the index.

3. When querying without index conditions, InnoDB uses table locks, which may lead to a large number of lock conflicts when concurrency is large. In addition, row locks are locked against indexes, which may occur in cases where different records are accessed, but the same index item is used, and lock conflicts may occur.

Tip: not necessarily using index retrieval will always use row locks, it is also possible to use table locks. Because MySQL compares the costs of different execution plans, InnoDB uses table locks when full table scans are more efficient than indexing. Therefore, lock conflicts need to be analyzed in conjunction with SQL execution plans.

4, row lock will produce deadlock, because in the row lock, the lock is gradually obtained, mainly divided into two steps: lock the primary key index, lock the non-primary key index. For example, when two transactions are executed simultaneously, one locks the primary key index and waits for the other index; the other locks the non-primary key index and waits for the primary key index. This will result in deadlock. InnoDB can generally detect this deadlock and roll back one transaction releasing locks and another acquiring locks to complete the transaction.

table locks

The locking granularity of table lock is the coarsest in MySQL. It is applied in InnoDB and MyISAM engines to lock the entire current table. It is not suitable for high concurrency scenarios, but the overhead is small, locking is fast, deadlock does not occur, and the probability of lock conflict is the largest.

page locks

The granularity of page locks is between row locks and table locks. When applied to BDB engines, the concurrency is moderate, and the overhead and locking speed are also between row locks and table locks.

After reading this article on the practical application of MySQL row locks, page locks and table locks, many readers will definitely want to know more about the relevant content. For more industry information, you can pay attention to our industry information column.

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.