Issues with locking in MySQL

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.

insert image description here


Row table lock features:

insert image description here


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.

insert image description here

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):

insert image description here
insert image description here

insert image description here

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!

You may also be interested in:
  • Analysis of MySQL lock wait and deadlock problems
  • In-depth analysis of MySQL lock blocking
  • Summary of MySQL lock knowledge points

<<:  Detailed explanation of the process of building an MQTT server using Docker

>>:  JavaScript implements cool mouse tailing effects

Recommend

Detailed explanation of Linux host name modification command

Linux change hostname command 1. If you only need...

Comprehensive explanation of CocosCreator hot update

Table of contents Preface What is Hot Change Coco...

Difference between MySQL update set and and

Table of contents Problem Description Cause Analy...

Detailed explanation of React event binding

1. What is In react applications, event names are...

Steps to restore code from a Docker container image

Sometimes the code is lost and you need to recove...

How to implement email alert in zabbix

Implemented according to the online tutorial. zab...

WeChat applet implements jigsaw puzzle game

This article shares the specific code for impleme...

Example of how to import nginx logs into elasticsearch

The nginx logs are collected by filebeat and pass...

Scoring rules of YSlow, a webpage scoring plugin developed by Yahoo

YSlow is a page scoring plug-in developed by Yaho...

JavaScript to achieve Taobao product image switching effect

JavaScript clothing album switching effect (simil...

Implementation of react automatic construction routing

Table of contents sequence 1. Centralized routing...

Detailed explanation of linux nslookup command usage

[Who is nslookup?] 】 The nslookup command is a ve...

Solution to ERROR 1054 (42S22) when changing password in MySQL 5.7

I have newly installed MySQL 5.7. When I log in, ...