Detailed Example of Row-Level Locking in MySQL

Detailed Example of Row-Level Locking in MySQL

Preface

Locks are synchronization mechanisms used to forcibly limit resource access when executing multiple threads. Database locks can be divided into row-level locks, table-level locks, and page-level locks according to the granularity of the locks.

Row-level locks

Row-level locks are the most fine-grained lock mechanism in MySQL, which means that only the currently operated row is locked. The probability of row-level lock conflicts is very low, and its granularity is the smallest, but the cost of locking is the highest. Row-level locks are divided into shared locks and exclusive locks.

Features:

The overhead is high, locking is slow, and deadlock may occur; the locking granularity is small, the probability of lock conflict is the highest, and the concurrency is also high;

Implementation principle:

InnoDB row locks are implemented by locking index items, which is different from MySQL and Oracle. The latter is implemented by locking the corresponding data rows in the database. InnoDB's row-level lock determines that row-level locks can only be used to retrieve data through index conditions. Otherwise, table-level locks are used directly. Special note: When using row-level locks, you must use indexes

For example:

Create table structure

CREATE TABLE `developerinfo` (
 `userID` bigint(20) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `passWord` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`userID`),
 KEY `PASSWORD_INDEX` (`passWord`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Inserting Data

INSERT INTO `developerinfo` VALUES ('1', 'liujie', '123456');
INSERT INTO `developerinfo` VALUES ('2', 'yitong', '123');
INSERT INTO `developerinfo` VALUES ('3', 'tong', '123456');

(1) Query the database using the primary key index and use row locks

Open three command line windows for testing

Command line window 1 Command Line Window 2 Command Line Window 3

mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set 
|mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;
wait|mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '3' for update;
+--------+------+----------+
| userID | name | password |
+--------+------+----------+
| 3 | tong | 123456 |
+--------+------+----------+
1 row in set
|mysql> commit;
Query OK, 0 rows affected mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set

(2) Querying non-indexed fields to query the database using row locks

Open two command line windows for testing

Command line window 1 Command Line Window 2

|mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where name = 'liujie' for update;
+--------+--------+----------+
userID name password
+--------+--------+----------+
1 liujie 123456
+--------+--------+----------+
1 row in set |mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where name = 'tong' for update;
Wait|
mysql> commit;
Query OK, 0 rows affected mysql> select * from developerinfo where name = 'liujie' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set

##### (3) Query the database using non-unique index fields to lock multiple rows using row locks

MySQL row locks are fake locks for indexes, not records, so different records may be locked.

Open three command line windows for testing

Command line window 1 Command line window 2 Command line window 3

mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where password = '123456
' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 1 | liujie | 123456 |
| 3 | tong | 123456 |
+--------+--------+----------+
2 rows in set mysql> set autocommit =0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;

wait

mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '2
' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 2 | yitong | 123 |
+--------+--------+----------+
1 row in set
commit; mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | password |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set

##### (4) When using an index in a condition to operate a database search, MySQL needs to determine whether to use the index by judging different execution plans. If you need to determine how to use explain to judge the index, please listen to the next analysis.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • In-depth analysis of MySQL deadlock issues
  • Summary of MySql index, lock, and transaction knowledge points
  • Example analysis of mysql shared lock and exclusive lock usage
  • In-depth understanding of MySQL global locks and table locks
  • Summary of MySQL lock knowledge points

<<:  Best Practices for Developing Amap Applications with Vue

>>:  Detailed tutorial on installing Tomcat server and configuring environment variables on Win10 (picture and text)

Recommend

MySQL query sorting and paging related

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

Batch replace part of the data of a field in Mysql (recommended)

Batch replace part of the data of a field in MYSQ...

Detailed explanation of nginx server installation and load balancing configuration on Linux system

nginx (engine x) is a high-performance HTTP and r...

How to hide and forge version number in Nginx

1. Use curl command to access by default: # curl ...

Tutorial on installing mysql5.7.18 on mac os10.12

I searched the entire web and found all kinds of ...

HeidiSQL tool to export and import MySQL data

Sometimes, in order to facilitate the export and ...

Pure HTML+CSS to achieve typing effect

This article mainly introduces the typing effect ...

Summary of MySQL InnoDB locks

Table of contents 1. Shared and Exclusive Locks 2...

MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control

Table of contents 1. Concurrent access control 2....

MySQL online log library migration example

Let me tell you about a recent case. A game log l...

Detailed tutorial on how to create a user in mysql and grant user permissions

Table of contents User Management Create a new us...

How to quickly paginate MySQL data volumes of tens of millions

Preface In backend development, in order to preve...

Getting Started Tutorial for Beginners ⑨: How to Build a Portal Website

Moreover, an article website built with a blog pro...

Pure CSS free implementation code for websites to have dark mode switching function

Preface The concept of dark mode originated from ...

Vue realizes the logistics timeline effect

This article example shares the specific code of ...