手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL锁学习之UPDATE

[日期:2018-02-05] 来源:Linux社区  作者:TeyGao [字体: ]

MySQL也蛮长时间了,可一直停留在能干活但是不精通的状态,而且很多MySQL知识点受SQL Server的影响存在理解偏差,只能且行且努力吧!

因为不懂源码,而MySQL也没提供很好的视图来验证以下观点,因此只能说说测试过程和实验结果,请各位报怀疑眼光阅读

问题点:

当MySQL做UPDATE操作时,会如何加锁?

测试方法:

通过两个会话执行SQL是否有阻塞来推测。

测试环境:

MySQL:5.5.14-log Source distribution

测试表:

CREATE TABLE "t_test1" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "c1" int(11) DEFAULT NULL,
  "c2" int(11) DEFAULT NULL,
  "c3" int(11) DEFAULT NULL,
  "c4" int(11) DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "idx_c1_c2" ("c1","c2"),
  KEY "idx_c3" ("c3")
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

 当前表数据:

测试1:

回话1执行SQL但不提交:insert into t_test1(c1,c2,c3,c4)select 1,1,1,1;

回话2执行SQL:insert into t_test1(c1,c2,c3,c4)select 1,1,1,1;

实验结果:回话2能正常执行,无阻塞。

测试2:

回话1执行SQL但不提交:update t_test1 set c4=1 where id=8;

回话2执行SQL:update t_test1 set c4=1 where id=9;

实验结果:回话2能正常执行,无阻塞。虽然记录ID为8和9的C1和C2的数据相同且C1和C2上有索引。

回话1执行SQL但不提交:update t_test1 set c4=0 where c1=1 and c2=1;

回话2执行SQL:update t_test1 set c4=0 where c1=1 and c2=1;

实验结果:回话2不能正常执行,被阻塞。

由于回话1和回话2要更新相同的记录,肯定存在锁问题,被阻塞完全可以理解。

测试4:

回话1执行SQL但不提交:update t_test1 set c4=0 where c1=1 and c2=1 and c4=8;

回话2执行SQL:update t_test1 set c4=0 where c1=1 and c2=1 and c4=9;

实验结果:回话2不能正常执行,被阻塞。

猜测:由于索引idx_c1_c2(c1,c2)的存在,回话1先按照条件c1=1 and c2=1在索引idx_c1_c2上找到“第一次匹配”的记录,然后加锁,再根据条件C4=8找到“最终匹配”记录,最后更新该记录,但由于“第一次匹配”时加锁导致回话2被阻塞

测试5:

回话1执行SQL但不提交:update t_test1 set c4=0 where c1=1 and c2=1 and id=8;

回话2执行SQL:update t_test1 set c4=0 where c1=1 and c2=1 and id=9;

实验结果:回话2能正常执行,无阻塞。

由于ID为唯一主键,即使回话1和回话2的WHERE条件中包含c1=1 and c2=1条件,仍不会造成阻塞。

测试6-1:

回话1执行SQL但不提交:update t_test1 force index(idx_c1_c2) set c4=1 where c1=1 and c2=2 and c3=8;

回话2执行SQL:update t_test1 force index(idx_c1_c2) set c4=1 where c1=1 and c2=2 and c3=9;

实验结果:回话2不能正常执行,被阻塞。

因为强制使用idx_c1_c2,先按照条件c1=1 and c2=1在索引idx_c1_c2上加锁,导致回话2被阻塞

测试6-2:

回话1执行SQL但不提交:update t_test1 force index(idx_c3) set c4=1 where c1=1 and c2=2 and c3=8;

回话2执行SQL:update t_test1 force index(idx_c3) set c4=1 where c1=1 and c2=2 and c3=9;

实验结果:回话2能正常执行,无阻塞。

因为强制使用idx_c3,先按照C3=8和C3=9两个条件在idx_c3上加锁,所以回话2没有被回话1阻塞

打完收工。

本文永久更新链接地址http://www.linuxidc.com/Linux/2018-02/150787.htm

linux
相关资讯       MySQL锁  MySQL UPDATE 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款