MySQL 悲观锁

robch

已于 2023-06-20 15:24:42 修改

阅读量450

点赞数

分类专栏： # 并发 Django 数据库文章标签： mysql 数据库

于 2020-01-17 20:12:31 首次发布

本文链接：https://blog.csdn.net/cpxsxn/article/details/104024110

版权

Django 同时被 3 个专栏收录

48 篇文章 2 订阅

订阅专栏

数据库

35 篇文章 0 订阅

订阅专栏

并发

20 篇文章 0 订阅

订阅专栏

悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在应用层中实现了加锁机制，也无法保证外部系统不会修改数据）。需要注意的是，在事务中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 相同数据时会等待其它事务结束后才执行，一般SELECT … 则不受此影响。

拿上面的实例来说，当我执行select status from t_goods where id=1 for update;后。我在另外的事务中如果再次执行select status from t_goods where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_goods where id=1;则能正常查询出数据，不会受第一个事务的影响。

补充：MySQL select…for update的Row Lock与Table Lock
　　上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

测试悲观锁：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性。

在 Console 1:
a. 执行 set autocommit = 0; 即关闭mysql数据库的自动提交属性,其实这步不是必须的
b. 执行 begin; 即开启事务(这个 begin 不是必须的，好像设置了 autocmmit 为 0 就会开启事务, 两者的区别参考：MySQL 的事务隔离级别)
c. 执行 select * from student where id = 3 for update; 即锁住数据
d. update student set name = concat(name,‘3’) where id = 3;
在 Console 2:
e. 执行 update student set name = concat(name,‘3’) where id = 3; 可以看到会被 block 住
在 Console 1:
f. 执行 commit; 即提交事务
在 Console 2:
g. 可以看到步骤 2 的 block 解除了

示例：

Console1:
	mysql> 
	mysql> show variables like '%autocommit%';
	+-----------------------------------------+-------+
	| Variable_name                           | Value |
	+-----------------------------------------+-------+
	| autocommit                              | ON    |
	+-----------------------------------------+-------+
	8 rows in set, 1 warning (0.00 sec)
	mysql> 
	mysql> set autocommit = 0 ;
	Query OK, 0 rows affected (0.00 sec)
	mysql> 
	mysql> show variables like '%autocommit%';
	+-----------------------------------------+-------+
	| Variable_name                           | Value |
	+-----------------------------------------+-------+
	| autocommit                              | OFF   |
	+-----------------------------------------+-------+
	8 rows in set, 1 warning (0.01 sec)
	mysql> 
	mysql> use robertdb;
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A
	
	Database changed
	mysql> 
	mysql> 
	mysql> select * from student;
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	| id  | name         | gender | address      | create_date                | update_date                | country_id |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	|   1 | c'ppp\'ppp"x |      2 | HeNan        | 2018-11-12 22:15:48.000000 | 2019-10-30 15:50:50.452264 |          1 |
	|   3 | rob          |      4 | BeiJing      | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	|   4 | gww          |      1 | BeiJing      | 2018-11-12 22:16:06.000000 | 2020-01-10 14:58:19.914886 |          1 |
	|   5 | sxn          |      2 | HeNan        | 2018-11-12 22:16:15.000000 | 2018-11-12 22:16:15.000000 |          1 |
	|   7 | syy          |      2 | MoNaShi      | 2018-11-14 10:21:24.179000 | 2018-11-14 10:21:24.179000 |          3 |
	|   8 | cjs          |      2 | ShanXi       | 2018-11-14 13:52:41.193000 | 2018-11-14 13:52:41.193000 |          1 |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	13 rows in set (0.00 sec)
	mysql> 
	mysql> 
	mysql> 
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	mysql> 
	mysql> select * from student where id = 3 for update;
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	| id | name | gender | address | create_date                | update_date                | country_id |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	|  3 | rob  |      4 | BeiJing | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	1 row in set (0.00 sec)
	mysql> 
	mysql> 
	mysql> 
	mysql> update student set name = concat(name,'3') where id  = 3;
	Query OK, 1 row affected (0.06 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	mysql> 
	mysql> commit;
	Query OK, 0 rows affected (0.10 sec)
	mysql>

Console 2: 可以看到 一个简单的 update SQL 居然执行了 34.70 sec
	mysql> 
	mysql> show variables like '%autocommit%';
	+-----------------------------------------+-------+
	| Variable_name                           | Value |
	+-----------------------------------------+-------+
	| autocommit                              | ON    |
	+-----------------------------------------+-------+
	8 rows in set, 1 warning (0.09 sec)	
	mysql> 
	mysql> use robertdb;
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A
	
	Database changed
	mysql> 
	mysql> select * from students;
	ERROR 1146 (42S02): Table 'robertdb.students' doesn't exist
	mysql> select * from student;
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	| id  | name         | gender | address      | create_date                | update_date                | country_id |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	|   1 | c'ppp\'ppp"x |      2 | HeNan        | 2018-11-12 22:15:48.000000 | 2019-10-30 15:50:50.452264 |          1 |
	|   3 | rob          |      4 | BeiJing      | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	|   4 | gww          |      1 | BeiJing      | 2018-11-12 22:16:06.000000 | 2020-01-10 14:58:19.914886 |          1 |
	|   5 | sxn          |      2 | HeNan        | 2018-11-12 22:16:15.000000 | 2018-11-12 22:16:15.000000 |          1 |
	|   7 | syy          |      2 | MoNaShi      | 2018-11-14 10:21:24.179000 | 2018-11-14 10:21:24.179000 |          3 |
	|   8 | cjs          |      2 | ShanXi       | 2018-11-14 13:52:41.193000 | 2018-11-14 13:52:41.193000 |          1 |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	13 rows in set (0.01 sec)
	mysql> 
	mysql> 
	mysql> update student set name = concat(name,'3') where id  = 3;
	Query OK, 1 row affected (34.70 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from student;
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	| id  | name         | gender | address      | create_date                | update_date                | country_id |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	|   1 | c'ppp\'ppp"x |      2 | HeNan        | 2018-11-12 22:15:48.000000 | 2019-10-30 15:50:50.452264 |          1 |
	|   3 | rob33        |      4 | BeiJing      | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	|   4 | gww          |      1 | BeiJing      | 2018-11-12 22:16:06.000000 | 2020-01-10 14:58:19.914886 |          1 |
	|   5 | sxn          |      2 | HeNan        | 2018-11-12 22:16:15.000000 | 2018-11-12 22:16:15.000000 |          1 |
	|   7 | syy          |      2 | MoNaShi      | 2018-11-14 10:21:24.179000 | 2018-11-14 10:21:24.179000 |          3 |
	|   8 | cjs          |      2 | ShanXi       | 2018-11-14 13:52:41.193000 | 2018-11-14 13:52:41.193000 |          1 |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	13 rows in set (0.00 sec)
	mysql> 
	mysql>

robch

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 悲观锁

悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在应用层中实现了加锁机制，也无法保证外部系统不会修改数据）。需要注意的是，在事务中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 相同数据时会等待其它事务结束后才执行，一般SELECT … 则不受此影响。拿上面的实例来说，当我执行select stat...
复制链接

扫一扫

专栏目录