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

Solutions to MySQL batch insert and unique index problems

MySQL batch insert problem When developing a proj...

WeChat applet scroll-view implements a solution to duplicate data loading when pulling up

The scroll-view of WeChat applet has more bugs wh...

Details of Linux file descriptors, file pointers, and inodes

Table of contents Linux--File descriptor, file po...

Teach you how to make cool barcode effects

statement : This article teaches you how to imple...

About VSCode formatting JS automatically adding or removing semicolons

introduction It is okay to add or not add a semic...

Vue recursively implements custom tree components

This article shares the specific code of Vue recu...

Linux touch command usage examples

Detailed explanation of linux touch command: 1. C...

Summary of some tips on MySQL index knowledge

Table of contents 1. Basic knowledge of indexing ...

3 simple ways to achieve carousel effects with JS

This article shares 3 methods to achieve the spec...

Vue implements click feedback instructions for water ripple effect

Table of contents Water wave effect Let's see...

How to install MySQL via SSH on a CentOS VPS

Type yum install mysql-server Press Y to continue...

mysql+mybatis implements stored procedure + transaction + multi-concurrent serial number acquisition

Database stored procedures DROP PROCEDURE IF EXIS...

calc() to achieve full screen background fixed width content

Over the past few years, there has been a trend i...

Detailed explanation of making shooting games with CocosCreator

Table of contents Scene Setting Game Resources Tu...

Tips for using the docker inspect command

Description and Introduction Docker inspect is a ...