MySQL鎖學習之UPDATE

MySQL鎖學習之UPDATE

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

打完收工。