阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL锁学习之UPDATE

209次阅读
没有评论

共计 1876 个字符,预计需要花费 5 分钟才能阅读完成。

学 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

 当前表数据:

MySQL 锁学习之 UPDATE

测试 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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计1876字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中