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 8.0.12 winx64 download and installation tutorial

MySQL 8.0.12 download and installation tutorial f...

Docker completes the implementation of FTP service construction with one line of command

One line command docker run -d \ -v /share:/home/...

How to use CSS style to vertically center the font in the table

The method of using CSS style to vertically cente...

Introduction to using data URI scheme to embed images in web pages

The data URI scheme allows us to include data in a...

Ideas and codes for realizing magnifying glass effect in js

This article example shares the specific code of ...

JavaScript implements H5 gold coin function (example code)

Today I made a Spring Festival gold coin red enve...

Problems and solutions encountered when installing mininet on Ubuntu 16.04.4LTS

Mininet Mininet is a lightweight software defined...

How to install grafana and add influxdb monitoring under Linux

Install grafana. The official website provides an...

The difference between storing full-width characters and half-width characters in MySQL

Unfortunately, the MYSQL_DATA_TRUNCATED error occ...

How to find and delete duplicate rows in MySQL

Table of contents 1. How to find duplicate rows 2...

How to install PHP7.4 and Nginx on Centos

Prepare 1. Download the required installation pac...

Detailed explanation of how to implement login function by combining React with Antd's Form component

Table of contents 1. React combined with Antd to ...

JavaScript to achieve accordion effect

This article shares the specific code for JavaScr...

Detailed graphic explanation of mysql query control statements

mysql query control statements Field deduplicatio...

Detailed explanation of CocosCreator Huarongdao digital puzzle

Table of contents Preface text 1. Panel 2. Huaron...