IT工程師數位筆記本

##==============================================================================##

學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阻塞

##==============================================================================##

打完收工