關(guān)于Mysql的InnoDB鎖概述
一 概述
InnoDB與MyISAM有兩處不同:
1)InnoDB支持事務(wù);
2)默認(rèn)采用行級(jí)鎖(也可以支持表級(jí)鎖)
對(duì)于更新操作(UPDATE、INSERT、DELETE),InnoDB會(huì)自動(dòng)給涉及到的數(shù)據(jù)集加排他鎖(X);對(duì)于普通的SELECT語句,InnoDB不加任何鎖(所以即使有一個(gè)線程的寫操作在占用鎖,不影響其他線程的讀,但是如果某個(gè)線程試圖加共享鎖則不行)。
InnoDB的行鎖模式及加鎖方法 InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖。 共享鎖(S):允許一個(gè)事務(wù)去讀一行,阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖; 排他鎖(X):允許獲得排他鎖的事務(wù)更新數(shù)據(jù),阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
另外,為了允許行鎖和表鎖共存,InnoDB還有兩張內(nèi)部使用的意向鎖,都是表鎖: 意向共享鎖(IS):事務(wù)打算給數(shù)據(jù)行加行共享鎖,事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前先必須取得該表的意向共享鎖; 意向排他鎖(IX):事務(wù)打算給數(shù)據(jù)行加行排他鎖,事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的意向排他鎖。 上述幾種鎖的兼容性如下: 表20-6 InnoDB行鎖模式兼容性列表
如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容,InnoDB就將請(qǐng)求的鎖授予該事務(wù);反之,如果兩者不兼容,該事務(wù)就要等待鎖釋放。 意向鎖是InnoDB自動(dòng)加的,不需用戶干預(yù)。 對(duì)于更新操作(UPDATE、INSERT、DELETE),InnoDB會(huì)自動(dòng)給涉及到的數(shù)據(jù)集加排他鎖(X);對(duì)于普通的SELECT語句,InnoDB不加任何鎖(所以即使有一個(gè)線程的寫操作在占用鎖,不影響其他線程的讀,但是如果某個(gè)線程試圖加共享鎖則不行)。
顯式的給記錄集加共享鎖:
- 共享鎖: SELECT * FROM tableName WHERE …. LOCK IN SHARE MODE
- 排他鎖: SELECT * FROM tableName WHERE …. FOR UPDATE
二、 共享鎖中執(zhí)行update操作容易導(dǎo)致死鎖
注意:**用共享鎖然后執(zhí)行了update操作,則有可能和別的線程的update操作發(fā)生鎖沖突,從而死鎖。死鎖后Mysql會(huì)自動(dòng)關(guān)閉一個(gè)線程的事務(wù)操作,讓鎖被一個(gè)線程使用。**如下所示:
1)線程A和線程B對(duì)同一行記錄使用了共享鎖,兩個(gè)線程讀都沒有問題(讀不需要加鎖,不管當(dāng)前記錄加了共享鎖還是排他鎖,都不影響單獨(dú)的讀操作);
2)線程A進(jìn)行更新操作,因?yàn)楦虏僮餍枰营?dú)占鎖,而線程B還對(duì)當(dāng)前記錄保留了共享鎖,故線程A無法獲得當(dāng)前線程的獨(dú)占鎖,要等待線程B釋放共享鎖;
3)線程B也進(jìn)行了更新操作,它也要對(duì)當(dāng)前記錄加獨(dú)占鎖。那么顯然它也無法獲得到該記錄的獨(dú)占鎖,兩個(gè)線程都會(huì)等待下去,也就是死鎖。
4)此時(shí)Mysql會(huì)自動(dòng)根據(jù)一定規(guī)則把鎖交給某個(gè)線程,另一個(gè)線程失去鎖重新啟動(dòng)事務(wù)。
另外,注意,默認(rèn)情況下單行執(zhí)行后就會(huì)自動(dòng)提交事務(wù),此時(shí)鎖也就被自動(dòng)釋放了。需要關(guān)閉事務(wù)的自動(dòng)提交。
set autocommit = 0;
對(duì)于需要更新的操作,應(yīng)當(dāng)直接使用排他鎖。這種情況下,因?yàn)榫€程A已經(jīng)占有了排他鎖,線程B無法獲得共享鎖和排他鎖,只能等待。但是注意,InnoDB的讀操作不需要加鎖,所以可以照常的讀。 當(dāng)使用SELECT…FOR UPDATE加鎖后再更新記錄,出現(xiàn)如表20-8所示的情況。 表20-8 InnoDB存儲(chǔ)引擎的排他鎖例子
三、 InnodDB行鎖實(shí)現(xiàn)方式
InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的。這一點(diǎn)Mysql和Oracle不同,Oracle是通過直接在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。 InnoDB的這種特性意味著:只有通過索引條件檢索數(shù)據(jù),InnoDB才使用行級(jí)鎖;否則InnoDB將使用表鎖。
1)非索引字段加鎖變成表鎖
表20-9 InnoDB存儲(chǔ)引擎的表在不使用索引時(shí)使用表鎖例子
注意,對(duì)于表沒有加索引,線程A僅要求獲取id=1的記錄的獨(dú)占鎖,但是因?yàn)闆]有加索引,所以該語句鎖住了整個(gè)表,使用了表鎖。
當(dāng)我們對(duì)id行添加索引 alter table tab_with_index add index id(id); 則會(huì)有下面的例子:
2)相同索引鍵導(dǎo)致阻塞
由于Mysql的行鎖是針對(duì)索引加的鎖,而不是針對(duì)記錄加的,所以即使是訪問不同行,但是如果使用了相同的索引鍵,依然會(huì)沖突:
mysql> select * from tab_with_index where id = 1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | 1 |
| 1 | 4 |
±-----±-----+
例如對(duì)于上表,如果對(duì)id加了索引,但是有兩個(gè)記錄的id相同,也就是索引相同。此時(shí)兩個(gè)線程分別試圖獲取兩個(gè)記錄的獨(dú)占鎖依然會(huì)導(dǎo)致阻塞,因?yàn)閙ysql的行鎖是加在索引上的。
3)不同索引鍵指向同一行記錄也會(huì)導(dǎo)致阻塞
- mysql> alter table tab_with_index add index name(name);
- alter table tab_with_index add index id(id);
假設(shè)我們分別對(duì)id和name增加索引,那么不管是什么索引,InnoDB都會(huì)使用行鎖來鎖定不同的行。
如果是不同的索引,但是指向了同一條記錄,那么依然會(huì)導(dǎo)致阻塞。 我的理解是不同索引最后指向了同一條主鍵id,鎖住了注解id,故依然會(huì)阻塞,應(yīng)該不是鎖住記錄。
4)間隙鎖當(dāng)我們使用范圍條件而不是相等條件來檢索數(shù)據(jù),并請(qǐng)求共享或排他鎖時(shí),InnoDB會(huì)給所有符合條件的已有數(shù)據(jù)記錄的索引加鎖;對(duì)于鍵值在條件范圍內(nèi)但是并不存在的記錄,叫做間隙gap,InnoDB也會(huì)對(duì)這些間隙加鎖。這種鎖機(jī)制就是間隙鎖。
舉例來說,假如emp表中只有101條記錄,其empid的值分別是 1,2,…,100,101,下面的SQL: Select * from emp where empid > 100 for update; 這是一個(gè)范圍條件的檢索,InnoDB不僅會(huì)對(duì)empid為101的記錄加鎖,對(duì)于大于101的不存在間隙也會(huì)加鎖。
**Mysql使用間隙鎖的目的是防止幻讀(應(yīng)該只是一部分滿足,不能完全回避),以滿足相關(guān)隔離級(jí)別的要求。**比如對(duì)于上面的情況,如果不加鎖,那么其他事務(wù)插入了empid為102的記錄,則會(huì)導(dǎo)致本事務(wù)內(nèi)再次執(zhí)行上述語句時(shí)得到empid為102的記錄,也就導(dǎo)致了幻讀。另一方面,也是為了滿足其回復(fù)和復(fù)制的需要。 因此,在使用范圍條件檢索并鎖定記錄時(shí),InnoDB的這種間隙加鎖機(jī)制會(huì)阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入,從而導(dǎo)致嚴(yán)重的鎖等待。因此,對(duì)于并發(fā)插入較多的應(yīng)用,我們要盡量優(yōu)化業(yè)務(wù)邏輯,盡量用相等條件來訪問更新數(shù)據(jù),避免使用范圍條件。
還要特別說明的是,InnoDB除了通過范圍條件加鎖時(shí)使用間隙鎖外,如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖,InnoDB也會(huì)使用間隙鎖!
5)關(guān)于恢復(fù)和復(fù)制的需要,對(duì)InnoDB鎖機(jī)制的影響
Mysql通過BINLog記錄執(zhí)行成功的INSERT、UPDATE、DELETE等更新數(shù)據(jù)的SQL語句,并由此實(shí)現(xiàn)MySQL數(shù)據(jù)庫的回復(fù)和主從復(fù)制。Mysql的恢復(fù)記錄(復(fù)制實(shí)際就是在Slave Mysql不斷的做基于BINLOG的恢復(fù))有以下特點(diǎn): 一是MySQL的恢復(fù)是SQL語句級(jí)的,也就是重新執(zhí)行BINLOG中的SQL語句。 二是MySQL的Binlog是按照事務(wù)提交的先后順序記錄的,恢復(fù)也是按這個(gè)順序進(jìn)行的。
**根據(jù)上述的特點(diǎn),Mysql的恢復(fù)機(jī)制要求:在一個(gè)事務(wù)未提交前,其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄,也就是不允許出現(xiàn)幻讀。****這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級(jí)別的要求,實(shí)際上是要求事務(wù)要串行化。這也是許多情況下,InnoDB要用到間隙鎖的原因。**比如在用范圍條件更新記錄時(shí),無論是Read Commited還是Repeatable Read隔離級(jí)別,InnoDB都要使用間隙鎖,這并不是隔離級(jí)別的要求,而是由于Mysql恢復(fù)和復(fù)制的要求。
到此這篇關(guān)于關(guān)于Mysql的InnoDB鎖概述的文章就介紹到這了,更多相關(guān)Mysql的InnoDB鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
textarea標(biāo)簽(存取數(shù)據(jù)庫mysql)的換行方法
textarea標(biāo)簽本身不識(shí)別換行功能,回車換行用的是\n換行符,輸入時(shí)的確有換行的效果,但是html渲染或者保存數(shù)據(jù)庫mysql時(shí)就只是一個(gè)空格了,這時(shí)就需要利用換行符\n和br標(biāo)簽的轉(zhuǎn)換進(jìn)行處理2023-09-09Linux下MySQL5.7.18二進(jìn)制包安裝教程(無默認(rèn)配置文件my_default.cnf)
這篇文章主要介紹了Linux下MySQL5.7.18二進(jìn)制包安裝教程(無默認(rèn)配置文件my_default.cnf) ,需要的朋友可以參考下2017-05-05mysql max 與 where 間的執(zhí)行問題小結(jié)
這篇文章主要介紹了mysql max 與 where 間的執(zhí)行問題小結(jié),需要的朋友可以參考下2018-01-01mysql8.0數(shù)據(jù)庫無法被遠(yuǎn)程連接問題排查小結(jié)
本文主要介紹了mysql8.0數(shù)據(jù)庫無法被遠(yuǎn)程連接問題排查小結(jié)2024-07-07Windows7下安裝使用MySQL8.0.16修改密碼、連接Navicat問題
這篇文章主要介紹了Windows7下安裝使用MySQL8.0.16修改密碼、連接Navicat問題,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-06-06詳解MySQL如何實(shí)現(xiàn)數(shù)據(jù)批量更新
最近需要批量更新大量數(shù)據(jù),習(xí)慣了寫sql,所以還是用sql來實(shí)現(xiàn),下面這篇文章主要給大家總結(jié)介紹了關(guān)于MySQL批量更新的方式,需要的朋友可以參考下2023-10-10