Issues with locking in MySQL

Lock classification:

From the granularity of data operations:

Table lock: When operating, the entire table will be locked. Row lock: When operating, the current operation row will be locked.

According to the type of data operation:

Read lock (shared lock): Multiple read operations can be performed simultaneously on the same data without affecting each other. Write lock (exclusive lock): It blocks other clients from writing until the current operation is completed.

Row table lock features:

MyISAM table locks:

How to add table lock
MyISAM automatically adds read locks to all tables involved before executing a query statement (SELECT).
INSERT, etc.), the write lock will be automatically added to the table involved. This process does not require user intervention. Therefore, users generally do not need to use LOCK directly.
TABLE command explicitly locks a MyISAM table.

In short, read locks block writes but do not block reads. A write lock will 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-dominant tables. Because other threads cannot perform any operations after the write lock is obtained, a large number of updates will make it difficult for the query to obtain the lock, resulting in permanent blocking.

InnoDB row locks

Introduction to row locks Row lock features: biased towards the InnoDB storage engine, high overhead, slow locking; deadlock may occur; minimum locking granularity, lowest probability of lock conflict, and highest concurrency.
There are three biggest differences between InnoDB and MyISAM: one is that it supports transactions; two is that it uses row-level locks; and three is that it supports foreign keys.

Transactions:
A transaction is a logical processing unit consisting of a group of SQL statements.

Four major characteristics of transactions (ACID):

This is the end of this article about MySQL lock related issues. For more information about MySQL lock issues, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!