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: