Mysql select ...for update

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

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

共享鎖和拍他鎖的鎖對(duì)象是主鍵！

共享鎖（讀鎖，S鎖）

select … from … lock in share mode

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

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

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

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

select … for update;

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

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

演示

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

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儲(chǔ)存引擎，InnoDB既支持行級(jí)鎖，又支持表級(jí)鎖，默認(rèn)情況下采用行級(jí)鎖

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

為測(cè)試的表格存入數(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ù)，報(bào)錯(cuò)

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

結(jié)果：

事務(wù)C首先會(huì)等待共享鎖釋放，待鎖釋放后，可以對(duì)改數(shù)據(jù)進(jìn)行修改，由于事務(wù)A一直沒(méi)有釋放鎖，在長(zhǎng)久的等待后，事務(wù)C拋錯(cuò)：

Lock wait timeout exceeded;try restarting trasacrtion.

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

排他鎖

事務(wù)A：獲取排他鎖進(jìn)行查詢，事務(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;
-- 查詢成功

普通的查詢語(yǔ)句沒(méi)有任何鎖機(jī)制。

總結(jié)

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

最新評(píng)論