mysql悲观锁总结和实践

转载于:https://www.iteye.com/blog/chenzhou123520-1860954


最近学习了一下数据库的悲观锁和乐观锁,根据自己的理解和网上参考资料总结如下:

 

悲观锁介绍(百科):

悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

 

使用场景举例:以MySQL InnoDB为例

商品goods表中有一个字段status,status为1代表商品未被下单,status为2代表商品已经被下单,那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。

 

1. 如果不采用锁,那么操作方法如下

//1.查询出商品信息

select status from t_goods where id=1;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

上面这种场景在高并发访问的情况下很可能会出现问题。

前面已经提到,只有当goods status为1时才能对该商品下单,上面第一步操作中,查询出来的商品status为1。但是当我们执行第三步Update操作的时候,有可能出现其他人先一步对商品下单把goods status修改为2了,但是我们并不知道数据已经被修改了,这样就可能造成同一个商品被下单2次,使得数据不一致。所以说这种方式是不安全的。

 

2. 使用悲观锁来实现

在上面的场景中,商品信息从查询出来到修改,中间有一个处理订单的过程,使用悲观锁的原理就是,当我们在查询出goods信息后就把当前的数据锁定,直到我们修改完毕后再解锁。那么在这个过程中,因为goods被锁定了,就不会出现有第三者来对其进行修改了。

注:要使用悲观锁,我们必须关闭mysql数据库的自动提交属性,因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交。

我们可以使用命令设置MySQL为非autocommit模式:

set autocommit=0;

设置完autocommit后,我们就可以执行我们的正常业务了。具体如下:

//0.开始事务

begin;/begin work;/start transaction; (三者选一就可以)

//1.查询出商品信息

select status from t_goods where id=1 for update;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;/commit work;

注:上面的begin/commit为事务的开始和结束,因为在前一步我们关闭了mysql的autocommit,所以需要手动控制事务的提交,在这里就不细表了。

上面的第一步我们执行了一次查询操作:select status from t_goods where id=1 for update;

与普通查询不一样的是,我们使用了select…for update的方式,这样就通过数据库实现了悲观锁。此时在t_goods表中,id为1的 那条数据就被我们锁定了,其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

注:需要注意的是,在事务中,只有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 (将整个数据表单给锁住)。

举例说明:

数据库表t_goods,包括id,status,name三个字段,id为主键,数据库中记录如下;

mysql> select * from t_goods;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
|  2 |      1 | 装备 |  
+----+--------+------+  
2 rows in set  
  
mysql>  

注:为了测试数据库锁,我使用两个console来模拟不同的事务操作,分别用console1、console2来表示。 

 

例1: (明确指定主键,并且有此数据,row lock)

console1:查询出结果,但是把该条数据锁定了

mysql> select * from t_goods where id=1 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
+----+--------+------+  
1 row in set  
  
mysql>  

console2:查询被阻塞

mysql> select * from t_goods where id=1 for update;  

console2:如果console1长时间未提交,则会报错

mysql> select * from t_goods where id=1 for update;  
ERROR 1205 : Lock wait timeout exceeded; try restarting transaction  

 

例2: (明确指定主键,若查无此数据,无lock)

console1:查询结果为空

mysql> select * from t_goods where id=3 for update;  
Empty set  

console2:查询结果为空,查询无阻塞,说明console1没有对数据执行锁定

mysql> select * from t_goods where id=3 for update;  
Empty set  

 

例3: (无主键,table lock)

console1:查询name=道具 的数据,查询正常

mysql> select * from t_goods where name='道具' for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
+----+--------+------+  
1 row in set  
  
mysql>  

console2:查询name=装备 的数据,查询阻塞,说明console1把表给锁住了

mysql> select * from t_goods where name='装备' for update;  

console2:若console1长时间未提交,则查询返回为空

mysql> select * from t_goods where name='装备' for update;  
Query OK, -1 rows affected  

 

例4: (主键不明确,table lock)

console1:查询正常

mysql> begin;  
Query OK, 0 rows affected  
  
mysql> select * from t_goods where id>0 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
|  2 |      1 | 装备 |  
+----+--------+------+  
2 rows in set  
  
mysql>  

console2:查询被阻塞,说明console1把表给锁住了

mysql> select * from t_goods where id>1 for update;  

 

例5: (主键不明确,table lock)

console1:

mysql> begin;  
Query OK, 0 rows affected  
  
mysql> select * from t_goods where id<>1 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  2 |      1 | 装备 |  
+----+--------+------+  
1 row in set  
  
mysql>  

console2:查询被阻塞,说明console1把表给锁住了

mysql> select * from t_goods where id<>2 for update;  

console1:提交事务

mysql> commit;  
Query OK, 0 rows affected  

console2:console1事务提交后,console2查询结果正常

mysql> select * from t_goods where id<>2 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
+----+--------+------+  
1 row in set  
  
mysql>  

以上就是关于数据库主键对MySQL锁级别的影响实例,需要注意的是,除了主键外,使用索引也会影响数据库的锁定级别

举例:

我们修改t_goods表,给status字段创建一个索引

修改id为2的数据的status为2,此时表中数据为:

mysql> select * from t_goods;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
|  2 |      2 | 装备 |  
+----+--------+------+  
2 rows in set  
  
mysql>  

 

例6: (明确指定索引,并且有此数据,row lock)

console1:

mysql> select * from t_goods where status=1 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  1 |      1 | 道具 |  
+----+--------+------+  
1 row in set  
  
mysql>  

console2:查询status=1的数据时阻塞,超时后返回为空,说明数据被console1锁定了

mysql> select * from t_goods where status=1 for update;  
Query OK, -1 rows affected  

console2:查询status=2的数据,能正常查询,说明console1只锁住了行,未锁表

mysql> select * from t_goods where status=2 for update;  
+----+--------+------+  
| id | status | name |  
+----+--------+------+  
|  2 |      2 | 装备 |  
+----+--------+------+  
1 row in set  
  
mysql>  

 

例7: (明确指定索引,若查无此数据,无lock)

console1:查询status=3的数据,返回空数据

mysql> select * from t_goods where status=3 for update;  
Empty set  

console2:查询status=3的数据,返回空数据

mysql> select * from t_goods where status=3 for update;  
Empty set  

以上就是关于我对数据库悲观锁的理解和总结,有不对的地方欢迎拍砖,下一次会带来数据库乐观锁的总结和实践

 

参考资料

MySQL事务与锁定命令:http://www.docin.com/p-16805970.html

悲观锁:http://www.cnblogs.com/chenwenbiao/archive/2012/06/06/2537508.html 

 

MySQL数据库从入门实战课

12-31
限时福利1:购课进答疑群专享柳峰(刘运强)老师答疑服务。 限时福利2:购课后添加学习助手(微信号:csdn590),按消息提示即可领取编程大礼包! 注意:原价129的课程,最后2天限时秒杀仅需49元!! 为什么说每一个程序员都应该学习MySQL? 根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。 使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。 学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能! 【课程设计】 在本课程中,刘运强老师会结合自己十多年来对MySQL的心得体会,通过课程给你分享一条高效的MySQL入门捷径,让学员少走弯路,彻底搞懂MySQL。 本课程包含3大模块:  一、基础篇: 主要以最新的MySQL8.0安装为例帮助学员解决安装与配置MySQL的问题,并对MySQL8.0的新特性做一定介绍,为后续的课程展开做好环境部署。 二、SQL语言篇: 本篇主要讲解SQL语言的四大部分数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL,学会熟练对库表进行增删改查等必备技能。 三、MySQL进阶篇: 本篇可以帮助学员更加高效的管理线上的MySQL数据库;具备MySQL的日常运维能力,语句调优、备份恢复等思路。  

Java进阶高手课-核心篇

04-17
<p> <br> </p> <p> Java进阶的必经之路!<span></span> </p> <p> <br> </p> <p> <b>【超实用课程内容】</b><b></b> </p> <p> 本课程囊括了<span>Java</span>语言进阶的核心知识点,以真实场景项目实战为导向,循序渐进,深入浅出的了解Java并发编程、JVM虚拟机、网络编程和MySQL应用,讲解<span>Java</span>这门使用广泛的编程语言,助你能够游刃有余地游走在这些技术之中。<span> </span> </p> <p> <br> </p> <p> 套餐中一共包含<span>4</span>门<span>Java</span>进阶必学的核心知识(共<span>57</span>讲) </p> <p> 课程<span>1</span>:《<span>Java</span>进阶高手课<span>-</span>并发编程透彻理解》 </p> <p> 课程<span>2</span>:《<span>Java</span>进阶高手课<span>-</span>深入<span>JVM</span>虚拟机》 </p> <p> 课程<span>3</span>:《<span>Java</span>进阶高手课<span>-</span>深入浅出<span>Java</span>网络编程》 </p> <p> 课程<span>4</span>:《<span>Java</span>进阶高手课<span>-</span>必知必会<span>MySQL</span>》 </p> <p> <br> </p> <p> <strong>【</strong><strong>哪些人适合学习这门课程?</strong><strong>】</strong><strong></strong> </p> <p> 1)大学生,平时只接触了语言基础,并未学习深入语言内核; </p> <p> 2)对<span>Java</span>掌握程度薄弱的人,课程可以让你更好的理解<span>Java</span>语言原理及应用 </p> <p> 3)想修炼更好的<span>Java</span>内功,工作中遇到<span>Bug</span>可以游刃有余 </p> <p> 4)被面试官打破沙锅问到底的问题问到怀疑人生的应聘者 </p> <p> <br> </p> <p> <strong>【</strong><strong>你能收获到什么?</strong><strong>】</strong> </p> <p> 1.基础再提高,针对<span>Java</span>核心知识点学透,用对<span> </span> </p> <p> 2.能力再提高,日常工作中的代码换新貌,不怕问题<span> </span> </p> <p> 3.面试再加分,巴不得面试官打破沙锅问到底,竞争力<span>MAX</span> </p> <p> <br> <strong>【课程如何观看?】</strong> </p> <p> 1、登录<span>CSDN</span>学院<span> APP </span>在我的课程中进行学习; </p> <p> 2、移动端:<span>CSDN </span>学院<span>APP</span>(注意不是<span>CSDN APP</span>哦) </p> <p> 本课程为录播课,课程<span>2</span>年有效观看时长 </p> <p> <br> </p> <p class="ql-long-24357476"> <strong>【</strong><strong>资料开放</strong><strong>】</strong><strong></strong> </p> <p class="ql-long-24357476"> 课件、课程案例代码完全开放给你,你可以根据所学知识,自行修改、优化 </p> <p class="ql-long-24357476"> 下载方式:电脑登录课程观看页面,点击右下方课程资料、代码、课件等打包下载 </p> <p class="ql-long-24357476"> <img src="https://img-bss.csdn.net/202004200153008539.png" alt=""> </p> <p> <br> </p>
©️2020 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值