MySQL -- 行鎖

翻身碼農把歌唱發表於2018-07-24

一、行鎖概念及特點

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

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

二、資料庫事務

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

2.特性:ACID

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

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

1.問題

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

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

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

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

  • 未提交讀(read uncommitted):這是資料庫最低的隔離級別,允許一個事務讀另一個事務未提交的資料。

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

  • 提交讀(read committed):一個事務更新的資料 在提交之後 才可以被另一個事務讀取,即一個事務不可以讀取到另一個事務未提交的資料。

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

  • 可重複讀(repeatale read):這是資料庫預設的事務隔離級別,保證一個事務在相同條件下前後兩次讀取的資料是一致的。

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

  • 序列化(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);

插入執行成功!

  1. 在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

相關文章