Mysql鎖和死結分析

標籤：

　　在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。

在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的索引值（update時的set），即所謂的next-key locking。

案例分析1：

tab_test 結構如下：

id：主鍵;

state：狀態;

time：時間;

索引：idx_1(state，time)

出現死結的2條sql語句

update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)update tab_test set state=1067,time=now () where id in (9921180)

原因分析：

　　當“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。

　　假設“update tab_test set state=1067,time=now () where id in (9921180)”幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。

　　這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死結就產生了。

在第一條語句給主鍵加鎖前，第二條語句已經給主鍵加了鎖，所以在高並發的資料操作時，死結的情況就容易產生

InnoDB 會自動檢測一個事務的死結並復原一個或多個事務來防止死結。Innodb會選擇代價比較小的交易回復，此次事務（1）解鎖並復原，語句（2）繼續運行直至事務結束。

解決辦法

拆分第一條sql，先查出合格主索引值，再按照主鍵更新記錄：

select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);update tab_test state=1064,time=now() where id in(......);

案例分析2

teamUser表的表結構如下：
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB

出現死結的2條sql語句

insert into teamUser_20110121 select * from teamUserDELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<‘$daysago_1week‘

兩語句加鎖情況
在innodb預設的交易隔離等級下，普通的SELECT是不需要加行鎖的，但LOCK IN SHARE MODE、FOR UPDATE及高序列化層級中的SELECT都要加鎖。有一個例外，此案例中，語句（1）insert into teamUser_20110121 select * from teamUser會對錶teamUser_20110121（ENGINE= MyISAM）加表鎖，並對teamUser表所有行的主鍵索引（即聚簇索引）加共用鎖定。預設對其使用主鍵索引。
而語句（2）DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<‘$daysago_1week‘為刪除操作，會對選中行的主鍵索引加獨佔鎖定。由於此語句還使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的首碼索引，於是，還會對相關行的此非聚簇索引加獨佔鎖定。
鎖衝突的產生
由於共用鎖定與獨佔鎖定是互斥的，當一方擁有了某行記錄的獨佔鎖定後，另一方就不能其擁有共用鎖定，同樣，一方擁有了其共用鎖定後，另一方也無法得到其獨佔鎖定。所 以，當語句（1）、（2）同時運行時，相當於兩個事務會同時申請某相同記錄行的鎖資源，於是會產生鎖衝突。由於兩個事務都會申請主鍵索引，鎖衝突只會發生 在主鍵索引上。

避免死結的方法
InnoDB給MySQL提供了具有提交，復原和崩潰恢複能力的事務安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供非鎖定讀。這些特色增加了多使用者部署和效能。
但其行鎖的機制也帶來了產生死結的風險，這就需要在應用程式設計時避免死結的發生。以單個SQL語句組成的隱含交易來說，建議的避免死結的方法如下：
1.如果使用insert…select語句備份表格且資料量較大，在單獨的時間點操作，避免與其他sql語句爭奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會要求鎖定
2. 一個鎖定記錄集的事務，其操作結果集應盡量簡短，以免一次佔用太多資源，與其他交易處理的記錄衝突。
3.更新或者刪除表格式資料，sql語句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死結。對於where子句較複雜的情況，將其單獨通過sql得到後，再在更新語句中使用。
4. sql語句的巢狀表格格不要太多，能拆分就拆分，避免佔有資源同時等待資源，導致與其他事務衝突。
5. 對定點運行指令碼的情況，避免在同一時間點運行多個對同一表進行讀寫的指令碼，特別注意加鎖且操作資料量比較大的語句。
6.應用程式中增加對死結的判斷，如果事務意外結束，重新運行該事務，減少對功能的影響。

查看死結記錄

show InnoDB STATUS \G語句在mysql用戶端中查看最近一次的死結記錄。

在遇到問題時先執行show processlist找到死結線程號.然後Kill processNo 當然主要解決還是需要去看一下具體的操作.可能產生死結Show innodb status檢查引擎狀態 ,可以看到哪些語句產生死結

Mysql鎖和死結分析