MySQL效能優化（九）-- 鎖機制之行鎖

碼咖發表於2019-07-01
MySql優化

一、行鎖概念及特點

1.概念：給單獨的一行記錄加鎖，主要應用於innodb表儲存引擎

2.特點：在innodb儲存引擎中應用比較多，支援事務、開銷大、加鎖慢；會出現死鎖；鎖的粒度小，併發情況下，產生鎖等待的概率比較低，所以支援的併發數比較高。

二、資料庫事務

1.概念：事務是一系列操作組成的工作單元，該工作單元內的操作是不可分割的，也就是說要麼全部都執行，要麼全部不執行。

2.特性：ACID

  • 原子性：事務是最小的工作單元，不可分割，要麼都做，要麼都不做
  • 一致性：事務執行前和執行後的資料要保證正確性，資料完整性沒有被破壞。
  • 隔離性：在併發事務執行的時候，一個事務對其他事務不會產生影響。
  • 永續性：一個事務一旦提交，它對資料庫中的資料的改變就應該是永久性的

三、多個事務併發執行 問題及解決方案

1.問題

  • 丟失更新：在沒有事務隔離的情況下，兩個事務同時更新一條資料，後一個事務 會 覆蓋前面事務的更新，導致前面的事務丟失更新。
  • 髒讀：事務A先更新資料，但是沒有提交，事務B讀到了事務A沒有提交的資料。
  • 不可重複讀：事務A中，先讀到一條資料，事務A還沒有結束，此時，事務B對該條資料進行了修改操作，事務A又讀到了這條資料，事務A兩次讀到的資料不同。
  • 幻讀：事務A先讀到一批資料，假設讀到10條，事務B插入了一條資料，此時，事務A又讀這一批資料，發現多了一條，好像幻覺一樣。

注：不可重複讀的重點是修改，同樣的條件，你讀取過的資料，再次讀取出來發現值不一樣。

幻讀的重點在於新增或者刪除，同樣的條件，第 1 次和第 2 次讀出來的記錄數不一樣。

2.解決方案--資料庫隔離機制

  1. 未提交讀（read uncommitted）：這是資料庫最低的隔離級別，允許一個事務讀另一個事務未提交的資料。

解決了丟失更新，但是會出現髒讀、不可重複讀、幻讀。

  1. 提交讀（read committed）：一個事務更新的資料 在提交之後 才可以被另一個事務讀取，即一個事務不可以讀取到另一個事務未提交的資料。

解決了丟失更新和髒讀，但是會出現不可重複讀和幻讀。

  1. 可重複讀（repeatale read）：這是資料庫預設的事務隔離級別，保證一個事務在相同條件下前後兩次讀取的資料是一致的。

解決了丟失更新、髒讀和不可重複讀，但是會出現幻讀。

  1. 序列化（serializable）：這是資料庫最高的隔離級別。事務序列執行，不會交叉執行。

解決了所有的問題。

注：樂觀所可以解決幻讀。

四、行鎖的特性

檢視mysql事務隔離級別：show variables like 'tx_iso%';

前提：set autocommit=0; // 設定自動提交事務為手動提交 

/* 行鎖案例*/
create table lock_two(
    id int,
    col int
)engine=innodb;

insert into lock_two(id,col) values (1,1);
insert into lock_two(id,col) values (2,2);
insert into lock_two(id,col) values (3,3);
複製程式碼

1.在session1中執行update : update lock_two set col=11 where id=1;

（1）分別在session1和session2中查詢lock_two，看id為1的記錄的col是否修改了。

img

發現session1 的記錄修改了，session2中的記錄沒有被修改。

（2）在session1中執行commite後，然後再在session2中查詢：

img

發現session2中的表資料改變了。

2.在session1中執行update：update lock_two set col=11 where id=1，不執行commit;

在session2中執行uodate ：update lock_two set col=11 where id=1，不執行commit;

img

發現session2中的update發生阻塞，並且超過一段時間報錯。

3.在session1中執行update：update lock_two set col=22 where id = 2; 不執行commit

在session2中執行另一條update：update lock_two set col=33 where id = 3;

img
 此時，session2中的update發生阻塞，在沒發生錯誤的情況下，session1執行commit，session2中的update會馬上執行。

4.在lock_two中建立索引，

create index idx_id on lock_two(id);
create index idx_col on lock_two(col);
複製程式碼

然後重複第3步，

img

發現session2可以更新，不會產生阻塞。因為用上了索引，相當於行鎖。

結論：如果沒有用上索引，行鎖變成表鎖

五、手動鎖一行記錄格式

begin;

select * from lock_two where id=2 for update;
複製程式碼
  1. 在session1中執行上面語句，在ssesion2中可以檢視，但是不可以修改 sesion1中的for update 的記錄。
  2. 當session1中執行commit後，seesion2中的update立刻執行。

六、間隙鎖

1.定義

在範圍查詢的情況下， innodb會給範圍條件中的資料加上鎖，無論資料是否是否真實存在。

2.例子

在session1中update：update lock_two set col=666 where id>2 and id<8;

1) 在session2中執行insert：insert into lock_two values(9,99);

插入執行成功！

2) 在session2中執行insert：insert into lock_two values(7,77);

插入阻塞，一段時間後報錯！

執行select：select * from lock_two where id=4;

查詢成功！

建議：在innodb中，因為有間隙鎖的存在，最好在where中少使用這種範圍查詢。

七、檢視行鎖的資訊

show status like 'innodb_row_lock%';

img

說明：

  • Innodb_row_lock_current_waits ：當前正在等待的數量
  • Innodb_row_lock_time: 從啟動到現在鎖定的總時長，單位是ms
  • Innodb_row_lock_time_avg :鎖等待的平均時長
  • Innodb_row_lock_time_max：等待鎖時間最長的一個時間
  • Innodb_row_lock_waits：總共的等待次數

歡迎關注我的公眾號，第一時間接收最新文章~ 搜尋公眾號： 碼咖 或者 掃描下方二維碼：

img

相關文章