MySQL 鎖概述

歡迎來到藍隊云技術小課堂，每天分享一個技術小知識。今天介紹關于mysql鎖的相關概念和發生的原因。

鎖是計算機協調多個進程或線程并發訪問某一資源的機制。

在數據庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。

如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。

從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜。

本文他對于將著重討論MySQL鎖機制的特點，常見的鎖問題，以及解決MySQL鎖問題的一些方法或建議：↓

- - -

相對其他數據庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。

比如：

　　MyISAM：和 MEMORY存儲引擎采用的是表級鎖（table-level locking）；

　　BDB：存儲引擎采用的是頁面鎖（page-level locking），但也支持表級鎖；

　　InnoDB：存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖。

MySQL 這 3 種鎖的特性可大致歸納如下：↓ ↓ ↓

開銷、加鎖速度、死鎖、粒度、并發性能

l 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高,并發度最低。

l 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。

l 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適！

僅從鎖的角度來說：表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；

而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統。

一切取決于應用程序，應用程序的不同部分可能需要不同的鎖類型。

為了確定是否想要使用行級鎖定的存儲引擎，應看看應用程序做什么并且混合使用什么樣的選擇和更新語句。

例如，大多數Web應用程序執行許多選擇，而很少進行刪除，只對關鍵字的值進行更新，并且只插入少量具體的表。

基本MySQL MyISAM 設置已經調節得很好。

在 MySQL 中對于使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。

InnoDB：

　　對于普通SELECT語句，InnoDB不會加任何鎖；

　　InnoDB 行鎖是通過給索引項加鎖實現的：這就意味著只有通過索引條件檢索數據時，InnoDB才使用行鎖，否則使用表鎖。

　　在mysql中，如果查詢條件帶有主鍵，會鎖行數據，如果沒有，會鎖表。

　　當where條件中的字段沒有加索引時，會鎖住整張表。

　　在有索引的情況下，更新不同的行，innodb 默認的行鎖是不會阻塞的。

　　where 后面的索引失效時，行鎖變表鎖，其他事物操作會有阻塞。

注意：在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發性能。

　　行鎖建議：↓

　　　　盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖

　　　　合理設計索引，盡量縮小索引的范圍

　　　　盡可能較少檢索條件，避免間隙鎖

　　　　盡量控制事務大小，減少鎖定資源量和時間長度

　　　　盡可能低級別事務隔離

　　　　varchar 不加單引號會引發行鎖。

導致鎖表原因：↓

1、鎖表發生在insert update 、delete 中

2、鎖表的原理是 數據庫使用獨占式封鎖機制，當執行上面的語句時，對表進行鎖住，直到發生commite 或者 回滾 或者退出數據庫用戶

　　第一、 A程序執行了對 tableA 的 insert ，并還未 commite時，B程序也對tableA 進行insert 則此時會發生資源正忙的異常 就是鎖表

　　第二、鎖表常發生于并發而不是并行（并行時，一個線程操作數據庫時，另一個線程是不能操作數據庫的，cpu 和i/o 分配原則）

3、減少鎖表的概率：

　　減少insert 、update 、delete 語句執行 到 commite 之間的時間。

　　具體點批量執行改為單個執行、優化sql自身的非執行速度 如果異常對事物進行回滾

范例：↓ ↓ ↓

1）：使用 update

　　假設kid 是表table 的 一個索引字段 且值不唯一：

　　1）：如果kid 有多個值為12的記錄那么：

　　　　update table set name=’feie’ where kid=12; #會鎖表

　　2）：如果kid有唯一的值為1的記錄那么：

　　　　update table set name=’feie’ where kid=1; #不會鎖

總結：用索引字段做為條件進行修改時， 是否表鎖的取決于這個索引字段能否確定記錄唯一，當索引值對應記錄不唯一，會進行鎖表，相反則行鎖。

2）：使用 delete

　　如果有兩個delete ： kid1 與 kid2 是索引字段

　　1）：語句1 delete from table where kid1=1 and kid2=2;

　　2）：語句2 delete from table where kid1=1 and kid2=3;

　　　　 # 這樣的兩個delete 是不會鎖表的

　　1）：語句1 delete from table where kid1=1 and kid2=2;

　　2）：語句2 delete from table where kid1=1 ;

　　　　 # 這樣的兩個delete 會鎖表

總結：同一個表，如果進行刪除操作時，盡量讓刪除條件統一，否則會相互影響造成鎖表

以上就是藍隊云的全部分享，藍隊云官網上擁有完善的技術支持庫可供參考，大家可自行查閱，更多技術問題，可以直接咨詢。同時，藍隊云整理了運維必備的工具包免費分享給大家使用，需要的朋友可以直接咨詢。

更多技術知識，藍隊云期待與你一起探索。