右側(cè)是已加的鎖 | X | S | U |
---|---|---|---|
X | - | - | - |
S | - | + | + |
U | - | + | - |
MySQL支持不同級(jí)別的鎖,其鎖定的數(shù)據(jù)的范圍也不同,也即我們常說的鎖的粒度。MySQL有三種鎖級(jí)別:行級(jí)鎖、頁級(jí)鎖、表級(jí)鎖。不同的存儲(chǔ)引擎支持不同的鎖粒度,例如MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖,頁級(jí)鎖僅被BDB存儲(chǔ)引擎支持,InnoDB存儲(chǔ)引擎支持行級(jí)鎖和表級(jí)鎖,默認(rèn)情況下是采用行級(jí)鎖。
特點(diǎn)
表級(jí)鎖:開銷小,加鎖快;不會(huì)出現(xiàn)死鎖;鎖定粒度大,發(fā)生鎖沖突的概率最高,并發(fā)度最低。數(shù)據(jù)庫(kù)引擎總是一次性同時(shí)獲取所有需要的鎖以及總是按相同的順序獲取表鎖從而避免死鎖。
行級(jí)鎖:開銷大,加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度也最高。行鎖總是逐步獲得的,因此會(huì)出現(xiàn)死鎖。
頁面鎖:開銷和加鎖時(shí)間界于表鎖和行鎖之間;會(huì)出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間,并發(fā)度一般。
下面詳細(xì)介紹行鎖和表鎖,頁鎖由于使用得較少就不介紹了。
按行對(duì)數(shù)據(jù)進(jìn)行加鎖。InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的,Innodb一定存在聚簇索引,行鎖最終都會(huì)落到聚簇索引上,通過非聚簇索引查詢的時(shí)候,先鎖非聚簇索引,然后再鎖聚簇索引。如果一個(gè)where語句里面既有聚簇索引,又有二級(jí)索引,則會(huì)先鎖聚簇索引,再鎖二級(jí)索引。由于是分步加鎖的,因此可能會(huì)有死鎖發(fā)生。
MySQL的行鎖對(duì)S、X鎖上做了一些更精確的細(xì)分,使得行鎖的粒度更細(xì)小,可以減少?zèng)_突,這就是被稱為“precise mode”的兼容矩陣。(該矩陣沒有出現(xiàn)在官方文檔上,是有人通過Mysql lock0lock.c:lock_rec_has_to_wait源代碼推測(cè)出來的。)
右側(cè)是已加的鎖(+ 代表兼容, -代表不兼容) | G | R | N | I |
---|---|---|---|---|
G | + | + | + | + |
R | + | – | – | + |
N | + | – | – | + |
I | – | + | – | + |
S鎖和S鎖是完全兼容的,因此在判別兼容性時(shí)不需要對(duì)比精確模式。精確模式的檢測(cè),用在S、X和X、X之間。從這個(gè)矩陣可以看到幾個(gè)特點(diǎn):
只有正確通過索引條件檢索數(shù)據(jù)(沒有索引失效的情況),InnoDB才會(huì)使用行級(jí)鎖,否則InnoDB對(duì)表中的所有記錄加鎖,也就是將鎖住整個(gè)表。注意,這里說的是鎖住整個(gè)表,但是Innodb并不是使用表鎖來鎖住表的,而是使用了下面介紹的Next-Key Lock來鎖住整個(gè)表。網(wǎng)上很多的說法都是說用表鎖,然而實(shí)際上并不是,我們可以通過下面的例子來看看。
假設(shè)我們有以下的數(shù)據(jù)(MySQL8):
mysql> select * from users; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | a | 1 | | 2 | a | 1 | | 3 | a | 1 | | 4 | a | 1 | | 5 | a | 1 | +----+------+-----+
方法一:
我們使用表鎖鎖表,并查看引擎的狀態(tài)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> lock tables users write; Query OK, 0 rows affected (0.00 sec) mysql> show engine innodb status\G ... ------------ TRANSACTIONS ------------ Trx id counter 4863 Purge done for trx's n:o 4862 undo n:o 0 state: running but idle History list length 911 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281479760456232, not started mysql tables in use 1, locked 1 ###############注意這里 0 lock struct(s), heap size 1136, 0 row lock(s) ...
然后我們?cè)偻ㄟ^非索引的字段查詢來加鎖,并查看引擎的狀態(tài)
## 先解鎖上次的表鎖 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from users where name = 'a' for update; mysql> show engine innodb status\G ... ------------ TRANSACTIONS ------------ Trx id counter 4864 Purge done for trx's n:o 4862 undo n:o 0 state: running but idle History list length 911 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 4863, ACTIVE 37 sec 2 lock struct(s), heap size 1136, 6 row lock(s) ###############注意這里 ...
然后我們?cè)賱h除id為2,3,4的數(shù)據(jù),然后在通過非索引的字段查詢來加鎖,并查看引擎的狀態(tài)
mysql> delete from users where id in (2,3,4); Query OK, 3 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from users where name = 'a' for update; mysql> show engine innodb status\G ... ------------ TRANSACTIONS ------------ Trx id counter 4870 Purge done for trx's n:o 4869 undo n:o 0 state: running but idle History list length 914 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 4869, ACTIVE 9 sec 2 lock struct(s), heap size 1136, 3 row lock(s) ###############注意這里 ...
可以看到這里使用了表鎖和因?yàn)闆]法用索引鎖定特定行而轉(zhuǎn)而鎖住整個(gè)表是不一樣的。從第二次和第三次的操作來看,lock住的row也是不同的,這是因?yàn)閮烧唛g隙的個(gè)數(shù)不同,所以可以看到使用的并不是表鎖,而是Next-Key Lock。第一次鎖住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞],第二次鎖住了(-∞,1],(1,5],(5,∞]。
方法二:
也可以通過以下語句來查看鎖的信息,也可以知道用的是行鎖,且是鎖住了區(qū)間(插入不了數(shù)據(jù))和記錄,所以是Next-Key Lock。
mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (你的事務(wù)id); +-----------------------+-----------+-----------+ | ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE | +-----------------------+-----------+-----------+ | 4889 | TABLE | IX | | 4889 | RECORD | X | | 4889 | RECORD | X | | 4889 | RECORD | X | +-----------------------+-----------+-----------+ 10 rows in set (0.00 sec)
LOCK_TYPE:對(duì)于InnoDB,可選值為 RECORD(行鎖), TABLE(表鎖)
LOCK_MODE:對(duì)于InnoDB,可選值為S[,GAP], X[,GAP], IS[,GAP],IX[,GAP], AUTO_INC和UNKNOWN。除了AUTO_INC和UNKNOWN,其他鎖定模式都包含了GAP鎖(如果存在)。
具體可見 MySQL文檔:https://dev.mysql.com/doc/ref...
直接對(duì)整個(gè)表加鎖,影響表中所有記錄,表讀鎖和表寫鎖的兼容性見上面的分析。
MySQL中除了表讀鎖和表寫鎖之外,還存在一種特殊的表鎖:意向鎖,這是為了解決不同粒度的鎖的兼容性判斷而存在的。
因?yàn)殒i的粒度不同,表鎖的范圍覆蓋了行鎖的范圍,所以表鎖和行鎖會(huì)產(chǎn)生沖突,例如事務(wù)A對(duì)表中某一行數(shù)據(jù)加了行鎖,然后事務(wù)B想加表鎖,正常來說是應(yīng)該要沖突的。如果只有行鎖的話,要判斷是否沖突就得遍歷每一行數(shù)據(jù)了,這樣的效率實(shí)在不高,因此我們就有了意向表鎖。
意向鎖的主要目的是為了使得 行鎖 和 表鎖 共存,事務(wù)在申請(qǐng)行鎖前,必須先申請(qǐng)表的意向鎖,成功后再申請(qǐng)行鎖。注意:申請(qǐng)意向鎖的動(dòng)作是數(shù)據(jù)庫(kù)完成的,不需要開發(fā)者來申請(qǐng)。
意向鎖是表級(jí)鎖,但是卻表示事務(wù)正在讀或?qū)懩骋恍杏涗洠皇钦麄€(gè)表, 所以意向鎖之間不會(huì)產(chǎn)生沖突,真正的沖突在加行鎖時(shí)檢查。
意向鎖分為意向讀鎖(IS)和意向?qū)戞i(IX)。
右側(cè)是已加的鎖(+ 代表兼容, -代表不兼容) | IS | IX | S | X |
---|---|---|---|---|
IS | + | + | + | – |
IX | + | + | – | – |
S | + | – | + | – |
X | – | – | – | – |
以上就是MySQL 鎖的相關(guān)知識(shí)總結(jié)的詳細(xì)內(nèi)容,更多關(guān)于MySQL 鎖的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
標(biāo)簽:黑河 吉林 錦州 滄州 隨州 資陽 甘南 荊州
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL 鎖的相關(guān)知識(shí)總結(jié)》,本文關(guān)鍵詞 MySQL,鎖,的,相關(guān),知識(shí),總結(jié),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。