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

Introduction to new features of ECMAscript

Table of contents 1. Default values ​​for functio...

Use of Linux cal command

1. Command Introduction The cal (calendar) comman...

Summary of the differences between Vue's watch, computed, and methods

Table of contents 1 Introduction 2 Basic usage 2....

Realize three-level linkage of year, month and day based on JavaScript

This article shares the specific code for JavaScr...

Detailed explanation of importing/exporting MySQL data in Docker container

Preface We all know that the import and export of...

MySQL query sorting and paging related

Overview It is usually not what we want to presen...

Summarize the common application problems of XHTML code

Over a period of time, I found that many people d...

A brief analysis of the use of watchEffect in Vue3

Preface Everyone should be familiar with the watc...

CentOS uses expect to remotely execute scripts and commands in batches

Sometimes we may need to operate servers in batch...

How does Vue3's dynamic components work?

Table of contents 1. Component Registration 1.1 G...

IDEA graphic tutorial on configuring Tomcat server and publishing web projects

1. After creating the web project, you now need t...

JavaScript implements asynchronous acquisition of form data

This article example shares the specific code for...

Interpreting MySQL client and server protocols

Table of contents MySQL Client/Server Protocol If...

MySQL extracts Json internal fields and dumps them as numbers

Table of contents background Problem Analysis 1. ...

docker cp copy files and enter the container

Enter the running container # Enter the container...