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

This article introduces how to analyze the lock-related problems in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Lock classification:

From the granularity of data operations:

Table lock: when operating, the entire table is locked. Row lock: the current operation row is locked during the operation.

According to the type of operation on the data:

Read lock (shared lock): for the same data, multiple reads can be performed at the same time without affecting each other. Write lock (exclusive lock): it blocks other clients before the current operation is completed

Row table lock features:

MyISAM table lock:

How to add a table lock

Before executing the query statement (SELECT), MyISAM automatically adds read locks to all tables involved, and performs update operations (UPDATE, DELETE,

INSERT, etc., will automatically add a write lock to the table involved, this process does not require user intervention, therefore, users generally do not need to use LOCK directly

The TABLE command explicitly locks the MyISAM table.

In short, the read lock blocks writing, but not reading. Write locks, on the other hand, block both reading and writing.

In addition, MyISAM's read-write lock scheduling is write-first, which is why MyISAM is not suitable as a storage engine for write-oriented tables. Because after writing the lock, its

His thread cannot do anything, and a large number of updates will make it difficult for the query to get a lock, resulting in permanent blocking.

InnoDB row lock

Introduction to row lock

Row lock features: inclined to InnoDB storage engine, high overhead and slow locking; deadlock will occur; lock granularity is the smallest, lock collision probability is the lowest, and concurrency is the lowest.

And the tallest.

There are three biggest differences between InnoDB and MyISAM: first, it supports transactions; second, it uses row-level locks; and it supports foreign keys.

Transaction:

A transaction is a logical processing unit consisting of a set of SQL statements.

Four characteristics of a transaction (ACID):

On how to analyze the lock in MySQL related issues are shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.