Mysql select ...for update

這幾天遇到了select … for update的sql語句，決定整理一下mysql的兩種鎖機制。

Mysql數(shù)據(jù)庫有兩種鎖，一種是共享鎖，一種是排他鎖，這兩種鎖是針對InnoDB的，如果是MyISM不是這樣的鎖機制。

共享鎖和拍他鎖的鎖對象是主鍵！

共享鎖（讀鎖，S鎖）

select … from … lock in share mode

多個事務(wù)共享一把鎖，但是只能讀，不能修改。

當(dāng)有事務(wù)拿到共享鎖的時候且鎖未釋放時，另一個事務(wù)不能去修改加鎖的數(shù)據(jù)。

共享鎖的最大特點是可以共享，多個事務(wù)可以同時獲取鎖并且讀到數(shù)據(jù)，并且，在鎖沒有釋放時，數(shù)據(jù)不能被修改，這樣可以避免數(shù)據(jù)庫臟讀和不可重復(fù)讀的問題。

排他鎖(互斥鎖，寫鎖，X鎖)

select … for update;

排他鎖 不能被多個事務(wù)共享，如果一個事務(wù)獲取了一行數(shù)據(jù)的排他鎖，那么其他事務(wù)就不能獲取這行數(shù)據(jù)的鎖，包括共享鎖和排他鎖。

獲取到排他鎖的事務(wù)可以對數(shù)據(jù)進行讀取和修改，事務(wù)提交后，鎖釋放。

演示

創(chuàng)建一張用于測試的表格

CREATE TABLE `emipe_user_entity` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '主鍵',
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用戶名',
  `pass_word` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用戶密碼',
  `status` int(11) DEFAULT NULL COMMENT '用戶狀態(tài)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

使用InnoDB儲存引擎，InnoDB既支持行級鎖，又支持表級鎖，默認(rèn)情況下采用行級鎖

MySQL InnoDB引擎默認(rèn)的update，delete，insert語句會自動給涉及到的數(shù)據(jù)加上排他鎖。

為測試的表格存入數(shù)據(jù)

INSERT INTO `emipe_user_entity` VALUES ('1', '小紅帽', '123456', 0);
INSERT INTO `emipe_user_entity` VALUES ('2', '路人甲', '523456', 0);

共享鎖

事務(wù)A：獲取共享鎖，但不提交事務(wù)

begin;
select * from emipe_user_entity where id = 1 lock in share mode;

事務(wù)B：獲取共享鎖，可以查詢到數(shù)據(jù)

select * from emipe_user_entity where id = 1 lock in share mode;

事務(wù)C：嘗試修改帶有共享鎖的數(shù)據(jù)，報錯

update emipe_user_entity set pass_word = '222222' where id = 1;

結(jié)果：

事務(wù)C首先會等待共享鎖釋放，待鎖釋放后，可以對改數(shù)據(jù)進行修改，由于事務(wù)A一直沒有釋放鎖，在長久的等待后，事務(wù)C拋錯：

Lock wait timeout exceeded;try restarting trasacrtion.

等待鎖超時；嘗試重啟這個事務(wù)

排他鎖

事務(wù)A：獲取排他鎖進行查詢，事務(wù)不提交

begin;
select * from emipe_user_entity where id = 1 for update;

事務(wù)B：嘗試獲取排他鎖，被阻塞

select * from emipe_user_entity where id = 1 for update;

事務(wù)C: 嘗試獲取共享鎖，被阻塞

select * from emipe_user_entity where id = 1 lock in share mode;

事務(wù)D：在不使用鎖的情況下，可以查詢數(shù)據(jù)

select * from emipe_user_entity where id = 1;
-- 查詢成功

普通的查詢語句沒有任何鎖機制。

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論