一聚教程网:一个值得你收藏的教程网站

热门教程

MySQL中的行级如何锁定 MySQL中的行级锁定示例分享

时间:2022-06-29 09:10:50 编辑:袖梨 来源:一聚教程网

本篇文章小编给大家分享一下MySQL中的行级锁定示例详解,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

前言

锁是在执行多线程时用于强行限定资源访问的同步机制,数据库锁根据锁的粒度可分为行级锁,表级锁和页级锁

行级锁

行级锁是mysql中粒度最细的一种锁机制,表示只对当前所操作的行进行加锁,行级锁发生冲突的概率很低,其粒度最小,但是加锁的代价最大。行级锁分为共享锁和排他锁。

特点:

开销大,加锁慢,会出现死锁;锁定粒度最小,发生锁冲突的概率最大,并发性也高;

实现原理:

InnoDB行锁是通过给索引项加锁来实现的,这一点mysql和oracle不同,后者是通过在数据库中对相应的数据行加锁来实现的,InnoDB这种行级锁决定,只有通过索引条件来检索数据,才能使用行级锁,否则,直接使用表级锁。特别注意:使用行级锁一定要使用索引

举个栗子:

创建表结构

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;

插入数据

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

(1)通过主键索引来查询数据库使用行锁

打开三个命令行窗口进行测试

命令行窗口1

命令行窗口2

命令行窗口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;
等待
|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)查询非索引的字段来查询数据库使用行锁

打开两个命令行窗口进行测试

命令行窗口1

命令行窗口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;
等待|
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)查询非唯一索引字段来查询数据库使用行锁锁住多行

mysql的行锁是针对索引假的锁,不是针对记录,所以可能会出现锁住不同记录的场景

打开三个命令行窗口进行测试

命令行窗口1

命令行窗口2

命令行窗口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;

等待

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

热门栏目