MySQL锁学习之UPDATE

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

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

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

问题点：

当 MySQL 做 UPDATE 操作时，会如何加锁？

测试方法：

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

测试环境：

MySQL：5.5.14-log Source distribution

测试表：

 当前表数据：

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