Mysql鎖

mysql 鎖機制

標簽(空格分隔): mysql


參考文檔

  1. https://www.2cto.com/database/201508/429967.html
  2. http://www.cnblogs.com/aipiaoborensheng/p/5767459.html

概念

  1. 共享鎖(S):允許一個事務去讀一行纺裁,阻止其他事務獲得相同的數(shù)據(jù)集的排他鎖玫镐。
  2. 排他鎖(X):允許獲得排他鎖的事務更新數(shù)據(jù),但是組織其他事務獲得相同數(shù)據(jù)集的共享鎖和排他鎖筒溃。
  3. 對于insert置侍、update映之、delete,InnoDB會自動給涉及的數(shù)據(jù)加排他鎖(X)蜡坊;對于一般的Select語句杠输,InnoDB不會加任何鎖,事務可以通過以下語句給顯示加共享鎖或排他鎖秕衙。

共享鎖

select * from table_name where .....lock in share mode

Note left of 事務1: select * from table_1 where id=1 lock in share mode;
事務1-->事務2: 
Note right of 事務2: select * from table_1 where id=1 lock in share mode;
事務2-->事務1: 
Note left of 事務1: update table_1 set age=10 where id=1;
Note left of 事務1: 事務1更新時發(fā)現(xiàn)此行鎖被其他事務享用蠢甲,等待
事務1-->事務2: 
Note right of 事務2: update table_1 set age=12 where id=1;
Note right of 事務2: 事務2更新時發(fā)現(xiàn)此行鎖被其他事務享用,也等待据忘,導致死鎖

排他鎖

select * from table_name where .....for update

Note left of 事務1: select * from table_1 where id=1 for update;
事務1-->事務2: 
Note right of 事務2: select * from table_1 where id=1 for update;
Note right of 事務2: 等待...
事務2-->事務1: 
Note left of 事務1: update table_1 set age=10 where id=1;
Note left of 事務1: 更新完后釋放鎖
事務1-->事務2: 
Note right of 事務2: 獲得鎖后鹦牛,得到其他事務提交的記錄

行鎖的三種形式

  1. Record lock:鎖定一條記錄。
  2. Gap lock
  3. Next-key lock

innoDB鎖問題

事務(Transaction)及其ACID屬性

  • 原子性(Actomicity):事務是一個原子操作單元勇吊,其對數(shù)據(jù)的修改曼追,要么全都執(zhí)行,要么全都不執(zhí)行萧福。
  • 一致性(Consistent):在事務開始和完成時拉鹃,數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應用于事務的修改鲫忍,以操持完整性膏燕;事務結(jié)束時,所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(如B樹索引或雙向鏈表)也都必須是正確的悟民。
  • 隔離性(Isolation):數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制坝辫,保證事務在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。這意味著事務處理過程中的中間狀態(tài)對外部是不可見的射亏,反之亦然近忙。
  • 持久性(Durable):事務完成之后,它對于數(shù)據(jù)的修改是永久性的智润,即使出現(xiàn)系統(tǒng)故障也能夠保持及舍。

并發(fā)事務帶來的問題

  • 更新丟失(Lost Update):當兩個或多個事務選擇同一行,然后基于最初選定的值更新該行時窟绷,由于每個事務都不知道其他事務的存在锯玛,就會發(fā)生丟失更新問題——最后的更新覆蓋了其他事務所做的更新。例如,兩個編輯人員制作了同一文檔的電子副本攘残。每個編輯人員獨立地更改其副本拙友,然后保存更改后的副本,這樣就覆蓋了原始文檔歼郭。最后保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改遗契。如果在一個編輯人員完成并提交事務之前,另一個編輯人員不能訪問同一文件病曾,則可避免此問題
  • 臟讀(Dirty Reads):一個事務正在對一條記錄做修改牍蜂,在這個事務并提交前,這條記錄的數(shù)據(jù)就處于不一致狀態(tài)知态;這時捷兰,另一個事務也來讀取同一條記錄,如果不加控制负敏,第二個事務讀取了這些“臟”的數(shù)據(jù)贡茅,并據(jù)此做進一步的處理,就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系其做。這種現(xiàn)象被形象地叫做“臟讀”顶考。
  • 不可重復讀(Non-Repeatable Reads):一個事務在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了妖泄!這種現(xiàn)象叫做“不可重復讀”驹沿。
  • 幻讀(Phantom Reads):一個事務按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù),卻發(fā)現(xiàn)其他事務插入了滿足其查詢條件的新數(shù)據(jù)蹈胡,這種現(xiàn)象就稱為“幻讀”渊季。

事務隔離級別

| 隔離級別 | 臟讀| 不可重復讀 | 幻讀 |
| -| - |
| 未提交讀(Read uncommitted) | √ | √ | √ |
| 已提交度(Read committed) | x | √ | √ |
| 可重復讀(Repeatable read) |x | x | √ |
| 可序列化(Serializable) | x | x | x |

mysql行鎖的特性

  1. innodb 的行鎖是在有索引的情況下,沒有索引的表是鎖定全表的.
    實例:
    id是主鍵
    | id| name|
    | -| - |
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    事務1update第一條id=1的數(shù)據(jù),事務不提交罚渐;事務2接著update第二條id=2的數(shù)據(jù)的時候等待却汉,原因是id沒有加上索引,導致事務1鎖的是表鎖而不是行鎖荷并。

  2. 如果是使用相同的索引鍵合砂,會出現(xiàn)鎖沖突。
    示例:tab_with_index表中id字段有索引源织,name字段沒有索引翩伪。
    事務1:

select * from tab_with_index where id = 1 and name = '1' for update;

事務2:

select * from tab_with_index where id = 1 and name = '4' for update;

雖然事務2訪問的是和事務1不同的記錄,但是因為使用了相同的索引谈息,所以需要等待鎖缘屹。

  1. 當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行侠仇,另外囊颅,不論是使用主鍵索引、唯一索引或普通索引,InnoDB都會使用行鎖來對數(shù)據(jù)加鎖踢代。
    示例:表tab_with_index的id字段有主鍵索引,name字段有普通索引嗅骄。
    事務1:
select * from tab_with_index where id = 1 for update;

事務2:

select * from tab_with_index where name = '2' for update;

事務2使用name的索引訪問記錄胳挎,因為記錄沒有被索引,所以也可以獲得鎖溺森。

間隙鎖(Next-Key鎖)

當 我們用范圍條件而不是相等條件檢索數(shù)據(jù)慕爬,并請求共享或排他鎖時,InnoDB會給符合條件 的已有數(shù)據(jù)記錄的索引項加鎖屏积;對于鍵值在條件范圍內(nèi)但并不存在的記錄医窿,叫做“間隙(GAP)”,InnoDB也會對這個“間隙”加鎖炊林,這種鎖機制就是所謂 的間隙鎖(Next-Key鎖)姥卢。
示例:

Select * from  emp where empid > 100 for update;

是一個范圍條件的檢索,InnoDB不僅會對符合條件的empid值為101的記錄加鎖渣聚,也會對empid大于101(這些記錄并不存在)的“間隙”加鎖独榴。
InnoDB 使用間隙鎖的目的,一方面是為了防止幻讀奕枝,以滿足相關(guān)隔離級別的要求棺榔,對于上面的例子,要是不使用間隙鎖隘道,如果其他事務插入了empid大于100的任何 記錄症歇,那么本事務如果再次執(zhí)行上述語句,就會發(fā)生幻讀.
還要特別說明的是谭梗,InnoDB除了通過范圍條件加鎖時使用間隙鎖外忘晤,如果使用相等條件請求給一個不存在的記錄加鎖,InnoDB也會使用間隙鎖默辨!

一致性非鎖定讀

一致性非鎖定讀是指InnoDB存儲引擎通過多版本并發(fā)控制技術(shù)來讀取當前數(shù)據(jù)庫的數(shù)據(jù)德频。如果當前讀取的行正在執(zhí)行delete或者update操作,這時讀取操作不會等行鎖的釋放缩幸,而是去讀取行的快照數(shù)據(jù)壹置。


非鎖定一致性讀.png

快照數(shù)據(jù)是指改行之前版本的數(shù)據(jù),該實現(xiàn)是通過undo段來實現(xiàn)的表谊,而undo段用來在事務中保存回滾數(shù)據(jù)钞护,因此使用快照沒有增加額外的開銷。
這是InnoDB存儲引擎的默認讀取方式爆办。

注意

  1. 不同的事務隔離級別下讀取的方式不同难咕,并不是每個事務隔離級別下都是采用非鎖定的一致性讀。四種隔離級別中,READ COMMITTED和REPEATABLE READ這兩種隔離級別使用非鎖定的一致性讀余佃。
  2. 不同的事務即使都使用非鎖定的一致性讀暮刃,但是對于快照數(shù)據(jù)的定義也各不相同。READ COMMITTED級別下非鎖定讀總是讀取鎖定行的最新一份快照數(shù)據(jù)爆土;而REPEATABLE READ級別下非鎖定讀總是讀取事務開始時的數(shù)據(jù)版本椭懊。

例子

事務A 事務B
select * from table where id='1';
. update table set id =3 where id=1;
select * from table where id='1';
. commit;
select * from table where id='1';

上述例子中事務B update以后事務A第一次select的時候RC級別和RR級別獲取的結(jié)果都是id=1的那一條數(shù)據(jù);第二次select的時候步势,由于事務B已經(jīng)提交氧猬,RC級別select的結(jié)果就是id=3,而RR級別讀取的是事務開始時的數(shù)據(jù)坏瘩,id=1盅抚。

一致性鎖定讀

默認配置下事務的隔離級別為REPEATABLE READ,select操作為非一致性鎖定讀倔矾,但某些情況下需要對數(shù)據(jù)庫讀取操作進行加鎖保證數(shù)據(jù)的一致性妄均。select 有兩種一致的鎖定讀:

  • select ... for update
  • select ... lock in share mode

自增長與鎖

InnoDB存儲引擎內(nèi)部對每個含有自增長列的表有一個自增長計數(shù)器,當進行insert操作時破讨,首先獲取計數(shù)器的最大值丛晦,加1后進行insert操作。這個操作會加一個特殊的表鎖提陶,AUTO-INC LOCK烫沙。這個鎖并不是在事務提交后才釋放,而是在insert語句執(zhí)行完后釋放隙笆。

缺點

雖然是insert后就釋放鎖锌蓄,不是事務提交后才釋放,但是必須等前一個insert的完成才能進行下一次insert撑柔,性能較差瘸爽。

改進

TODO...

外鍵與鎖

在對外鍵值進行update和insert操作時首先需要查詢父表的記錄,即select父表铅忿,這個select操作不是使用一致性非鎖定讀剪决,因為會發(fā)生數(shù)據(jù)不一致的問題,為此需要使用一致性鎖定讀檀训,這時使用的select ... lock in share mode方式柑潦。當父表對應記錄加X鎖后,子表的操作將會阻塞峻凫。

例子

TODO...

鎖的算法

InnoDB存儲引擎有三種行鎖的算法

  • Record Lock:單行記錄上鎖
  • Gap Lock:間隙鎖渗鬼,鎖定一個范圍
  • Next-Key Lock
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市荧琼,隨后出現(xiàn)的幾起案子譬胎,更是在濱河造成了極大的恐慌差牛,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,681評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異,居然都是意外死亡超棺,警方通過查閱死者的電腦和手機,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評論 3 399
  • 文/潘曉璐 我一進店門夹孔,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人析孽,你說我怎么就攤上這事≈辉酰” “怎么了袜瞬?”我有些...
    開封第一講書人閱讀 169,421評論 0 362
  • 文/不壞的土叔 我叫張陵,是天一觀的道長身堡。 經(jīng)常有香客問我邓尤,道長,這世上最難降的妖魔是什么贴谎? 我笑而不...
    開封第一講書人閱讀 60,114評論 1 300
  • 正文 為了忘掉前任汞扎,我火速辦了婚禮,結(jié)果婚禮上擅这,老公的妹妹穿的比我還像新娘澈魄。我一直安慰自己,他們只是感情好仲翎,可當我...
    茶點故事閱讀 69,116評論 6 398
  • 文/花漫 我一把揭開白布痹扇。 她就那樣靜靜地躺著,像睡著了一般溯香。 火紅的嫁衣襯著肌膚如雪鲫构。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,713評論 1 312
  • 那天玫坛,我揣著相機與錄音结笨,去河邊找鬼。 笑死湿镀,一個胖子當著我的面吹牛炕吸,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播肠骆,決...
    沈念sama閱讀 41,170評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼算途,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了蚀腿?” 一聲冷哼從身側(cè)響起嘴瓤,我...
    開封第一講書人閱讀 40,116評論 0 277
  • 序言:老撾萬榮一對情侶失蹤扫外,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后廓脆,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體筛谚,經(jīng)...
    沈念sama閱讀 46,651評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,714評論 3 342
  • 正文 我和宋清朗相戀三年停忿,在試婚紗的時候發(fā)現(xiàn)自己被綠了驾讲。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,865評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡席赂,死狀恐怖吮铭,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情颅停,我是刑警寧澤谓晌,帶...
    沈念sama閱讀 36,527評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站癞揉,受9級特大地震影響纸肉,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜喊熟,卻給世界環(huán)境...
    茶點故事閱讀 42,211評論 3 336
  • 文/蒙蒙 一柏肪、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧芥牌,春花似錦烦味、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,699評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至扇商,卻和暖如春凤瘦,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背案铺。 一陣腳步聲響...
    開封第一講書人閱讀 33,814評論 1 274
  • 我被黑心中介騙來泰國打工蔬芥, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人控汉。 一個月前我還...
    沈念sama閱讀 49,299評論 3 379
  • 正文 我出身青樓笔诵,卻偏偏與公主長得像,于是被迫代替她去往敵國和親姑子。 傳聞我的和親對象是個殘疾皇子乎婿,可洞房花燭夜當晚...
    茶點故事閱讀 45,870評論 2 361

推薦閱讀更多精彩內(nèi)容

  • 當一個系統(tǒng)訪問量上來的時候,不只是數(shù)據(jù)庫性能瓶頸問題了街佑,數(shù)據(jù)庫數(shù)據(jù)安全也會浮現(xiàn)谢翎,這時候合理使用數(shù)據(jù)庫鎖機制就顯得異...
    初來的雨天閱讀 3,579評論 0 22
  • MySQL的事務支持 MySQL的事務支持不是綁定在MySQL服務器本身捍靠,而是與存儲引擎相關(guān): MyISAM:不支...
    但莫閱讀 493評論 0 6
  • MySQL技術(shù)內(nèi)幕:InnoDB存儲引擎(第2版) 姜承堯 第1章 MySQL體系結(jié)構(gòu)和存儲引擎 >> 在上述例子...
    沉默劍士閱讀 7,426評論 0 16
  • MyISAM 和 MEMORY 存儲引擎采用的是表級鎖;InnoDB 存儲引擎即支持行級鎖森逮,也支持表級鎖榨婆,但默認情...
    微日月閱讀 897評論 0 0
  • 前言 數(shù)據(jù)庫鎖定機制是數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性而使各種共享資源在并發(fā)訪問時變的有序的一種規(guī)則。MySQL數(shù)據(jù)庫的...
    Justlearn閱讀 1,676評論 0 4