當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

MySQL数据库：锁机制

發(fā)布時(shí)間：2024/9/30 数据库 21 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL数据库：锁机制小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

當(dāng)數(shù)據(jù)庫(kù)中多個(gè)事務(wù)并發(fā)存取同一數(shù)據(jù)的時(shí)候，若對(duì)并發(fā)操作不加控制就可能會(huì)讀取和存儲(chǔ)不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫(kù)的一致性。MySQL鎖機(jī)制的基本工作原理就是，事務(wù)在修改數(shù)據(jù)庫(kù)之前，需要先獲得相應(yīng)的鎖，獲得鎖的事務(wù)才可以修改數(shù)據(jù)；在該事務(wù)操作期間，這部分的數(shù)據(jù)是鎖定，其他事務(wù)如果需要修改數(shù)據(jù)，需要等待當(dāng)前事務(wù)提交或回滾后釋放鎖。

按照不同的分類方式，鎖的種類可以分為以下幾種：

（1）按鎖的粒度劃分：表級(jí)鎖、行級(jí)鎖、頁(yè)級(jí)鎖；?

（2）按鎖的類型劃分：共享鎖（S鎖）、排他鎖（X鎖）；

（3）按鎖的使用策略劃分：樂(lè)觀鎖、悲觀鎖；

一、Mysql中的表級(jí)鎖、行級(jí)鎖、頁(yè)級(jí)鎖：

（1）表級(jí)鎖：最大粒度的鎖級(jí)別，發(fā)生鎖沖突的概率最高，并發(fā)度最低，但開銷小，加鎖快，不會(huì)出現(xiàn)死鎖；

（2）行級(jí)鎖：最小粒度的所級(jí)別，發(fā)生鎖沖突的概率最小，并發(fā)度最高，但開銷大，加鎖慢，會(huì)發(fā)生死鎖，行鎖是作用在索引的；

（3）頁(yè)級(jí)鎖：鎖粒度界于表級(jí)鎖和行級(jí)鎖之間，對(duì)表級(jí)鎖和行級(jí)鎖的折中，并發(fā)度一般。開銷和加鎖時(shí)間也界于表鎖和行鎖之間，會(huì)出現(xiàn)死鎖；

不同的存儲(chǔ)引擎支持不同的鎖機(jī)制：

（1）InnoDB存儲(chǔ)引擎支持行級(jí)鎖和表級(jí)鎖，默認(rèn)情況下使用行級(jí)鎖，但只有通過(guò)索引進(jìn)行查詢數(shù)據(jù)，才使用行級(jí)鎖，否就使用表級(jí)鎖。

（2）MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖；

（3）BDB存儲(chǔ)引擎使用的是頁(yè)面鎖，但也支持表級(jí)鎖；

二、InnoDB的鎖機(jī)制：

1、InnoDb行鎖的類型：

InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖：

共享鎖（S鎖、讀鎖）：多個(gè)事務(wù)可以對(duì)同一數(shù)據(jù)行共享一把S鎖，但只能進(jìn)行讀不能修改；
排它鎖（X鎖、寫鎖）：一個(gè)事務(wù)獲取排它鎖之后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)行執(zhí)行寫操作，在鎖定期間，其他事務(wù)不能再獲取這部分?jǐn)?shù)據(jù)行的鎖（共享鎖、排它鎖），只允許獲取到排它鎖的事務(wù)進(jìn)行更新數(shù)據(jù)。

對(duì)于update，delete，insert 操作，InnoDB會(huì)自動(dòng)給涉及的數(shù)據(jù)行加排他鎖；對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖。

2、InnoDB的表鎖：意向鎖

而且因?yàn)?span style="color:#3399ea;">InnoDB引擎允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度的鎖機(jī)制，但是表鎖和行鎖雖然鎖定范圍不同，但是會(huì)相互沖突。當(dāng)你要加表鎖時(shí)，勢(shì)必要先遍歷該表的所有記錄，判斷是否有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL引入了意向鎖，來(lái)檢測(cè)表鎖和行鎖的沖突。

意向鎖也是表級(jí)鎖，分為讀意向鎖（IS鎖）和寫意向鎖（IX鎖）。當(dāng)事務(wù)要在記錄上加上行鎖時(shí)，則先在表上加上對(duì)應(yīng)的意向鎖。之后事務(wù)如果想進(jìn)行鎖表，只要先判斷是否有意向鎖存在，存在時(shí)則可快速返回該表不能啟用表鎖，否則就需要等待，提高效率。

意向鎖是InnoDB自動(dòng)加的，不需要用戶干預(yù)。

其中，四種鎖的兼容性如下：

鎖模式	X	IX	S	IS
X	沖突	沖突	沖突	沖突
IX	沖突	兼容	沖突	兼容
S	沖突	沖突	兼容	兼容
IS	沖突	兼容	兼容	兼容

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB就請(qǐng)求的鎖授予該事務(wù)；反之，如果兩者鎖不兼容，該事務(wù)就要等待鎖釋放。

3、InnoDB的加鎖方法：

上面說(shuō)過(guò)，對(duì)于普通的select語(yǔ)句，InnoDB不會(huì)加任何鎖，但是事務(wù)可以通過(guò)以下語(yǔ)句顯示給記錄集添加共享鎖或排他鎖：

（1）select …… for update：排它鎖

select * from table for update 語(yǔ)句：目的是在執(zhí)行這個(gè) select 查詢語(yǔ)句的時(shí)候，會(huì)將對(duì)應(yīng)的索引訪問(wèn)條目進(jìn)行上排他鎖（X 鎖），并且查到后的數(shù)據(jù)只允許自己來(lái)修改。

使用場(chǎng)景：為了讓確保自己查找到的數(shù)據(jù)一定是最新數(shù)據(jù)，并且查找到后的數(shù)據(jù)值允許自己來(lái)修改，此時(shí)就需要用到select for update語(yǔ)句；

性能分析：select for update語(yǔ)句相當(dāng)于一個(gè)update語(yǔ)句。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒(méi)有及時(shí)地commit或者rollback可能會(huì)造成事務(wù)長(zhǎng)時(shí)間的等待，從而影響數(shù)據(jù)庫(kù)的并發(fā)使用效率。

（2）select …… lock in share mode：共享鎖：

select *?from table?lock in share mode 語(yǔ)句：給查找的數(shù)據(jù)加一個(gè)共享鎖（S 鎖）的功能，允許其他的事務(wù)也對(duì)該數(shù)據(jù)上 S鎖，但是不能夠允許對(duì)該數(shù)據(jù)進(jìn)行修改。

使用場(chǎng)景：為了確保自己查詢的數(shù)據(jù)不會(huì)被其他事務(wù)正在修改，也就是確保自己查詢到的數(shù)據(jù)是最新的數(shù)據(jù)，并且不允許其他事務(wù)來(lái)修改數(shù)據(jù)。與select for update不同的是，本事務(wù)在查找完之后不一定能去更新數(shù)據(jù)，因?yàn)橛锌赡芷渌聞?wù)也對(duì)同數(shù)據(jù)集使用了 in share mode 的方式加上了S鎖；

性能分析：select lock in share mode 語(yǔ)句是一個(gè)給查找的數(shù)據(jù)上一個(gè)共享鎖（S 鎖）的功能，它允許其他的事務(wù)也對(duì)該數(shù)據(jù)上S鎖，但是不能夠允許對(duì)該數(shù)據(jù)進(jìn)行修改。如果不及時(shí)的commit 或者rollback 也可能會(huì)造成大量的事務(wù)等待。

4、InnoDB行鎖的實(shí)現(xiàn)與臨鍵鎖：

InnoDB的行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的。只有通過(guò)索引檢索數(shù)據(jù)，才能使用行鎖，否則將使用表鎖。

在InnoDB中，為了解決幻讀的現(xiàn)象，引入了臨鍵鎖(next-key)。根據(jù)索引，劃分為一個(gè)個(gè)左開右閉的區(qū)間。當(dāng)進(jìn)行范圍查詢的時(shí)候，若命中索引且能夠檢索到數(shù)據(jù)，則鎖住記錄所在的區(qū)間和它的下一個(gè)區(qū)間。

其實(shí)，臨鍵鎖(Next-Key)?=?記錄鎖(Record Locks)?+?間隙鎖(Gap Locks)

間隙鎖：當(dāng)使用范圍查詢而不是精準(zhǔn)查詢進(jìn)行檢索數(shù)據(jù)，并請(qǐng)求共享或排它鎖時(shí)，InnoDB會(huì)給符合范圍條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做間隙(GAP)。
記錄鎖：當(dāng)使用唯一索引，且記錄存在的精準(zhǔn)查詢時(shí)，使用記錄鎖

具體的使用體現(xiàn)在哪里呢？如下圖所示：

（1）范圍查詢，記錄存在，使用臨鍵鎖：

（2）當(dāng)記錄不存在時(shí)（不論是等值查詢，還是范圍查詢），臨建鎖將退化成間隙鎖：

（3）當(dāng)條件是精準(zhǔn)匹配（即為等值查詢時(shí)）且記錄存在時(shí)，并且是唯一索引，臨鍵鎖退化成記錄鎖：

（4）當(dāng)條件是精準(zhǔn)匹配（即為等值查詢時(shí)）且記錄存在，但不是唯一索引時(shí)，使用臨鍵鎖，有精準(zhǔn)值的數(shù)據(jù)會(huì)增加記錄鎖?和精準(zhǔn)值前后的區(qū)間的數(shù)據(jù)會(huì)增加間隙鎖

3、利用鎖機(jī)制解決并發(fā)問(wèn)題：

通過(guò)對(duì)InnoDB不同鎖類型的特性分析，可以利用鎖解決臟讀、不可重復(fù)讀、幻讀：

X鎖解決臟讀
S鎖解決不可重復(fù)讀
臨鍵鎖解決幻讀

4、分析數(shù)據(jù)庫(kù)中行鎖情況的命令：

mysql> show status like 'innodb_row_lock%';

+-------------------------------+-------+

| Variable_name | Value |

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 76100 |

| Innodb_row_lock_time_avg | 10871 |

| Innodb_row_lock_time_max | 20552 |

| Innodb_row_lock_waits | 7 |

+-------------------------------+-------+

Innodb_row_lock_current_waits：當(dāng)前等待鎖的數(shù)量

Innodb_row_lock_time：系統(tǒng)啟動(dòng)到現(xiàn)在鎖定的總時(shí)間長(zhǎng)度(重要)

Innodb_row_lock_time_avg：每次等待所花平均時(shí)間(重要)

Innodb_row_lock_time_max：系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間

Innodb_row_lock_waits：系統(tǒng)啟動(dòng)到現(xiàn)在總共等待的次數(shù)(重要)

當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃

三、MyISAM的鎖機(jī)制：

MyISAM存儲(chǔ)引擎使用表級(jí)鎖，表級(jí)鎖兩種模式：表共享讀鎖、表獨(dú)占寫鎖。MyISAM在執(zhí)行查詢語(yǔ)句前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改查操作前，會(huì)自動(dòng)給涉及的表加寫鎖。讀鎖會(huì)阻塞寫鎖，但不會(huì)阻塞讀鎖，而寫鎖則會(huì)把寫鎖和讀鎖都阻塞。

1、MyISAM的鎖調(diào)度:

MyISAM默認(rèn)情況下，認(rèn)為寫請(qǐng)求一般比讀請(qǐng)求要重要，如果有讀寫請(qǐng)求同時(shí)進(jìn)行的話，MyISAM將會(huì)優(yōu)先執(zhí)行寫操作，即使讀請(qǐng)求比寫請(qǐng)求先到達(dá)鎖等待隊(duì)列，寫鎖請(qǐng)求也會(huì)插到讀鎖請(qǐng)求之前！這也是MyISAM不適合做寫為主的引擎的原因。這樣MyISAM表在進(jìn)行大量的更新操作時(shí)，會(huì)造成查詢操作很難獲得讀鎖，從而導(dǎo)致查詢阻塞。

我們可以通過(guò)一些設(shè)置來(lái)調(diào)節(jié)MyISAM的調(diào)度行為：

通過(guò)指定啟動(dòng)參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請(qǐng)求以優(yōu)先的權(quán)利。
通過(guò)執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低。
通過(guò)指定INSERT、UPDATE、DELETE語(yǔ)句的LOW_PRIORITY屬性，降低該語(yǔ)句的優(yōu)先級(jí)。

上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法。這里要說(shuō)明的就是，不要盲目的給mysql設(shè)置為讀優(yōu)先，因?yàn)橐恍┬枰L(zhǎng)時(shí)間運(yùn)行的查詢操作，也會(huì)使寫進(jìn)程“餓死”。只有根據(jù)你的實(shí)際情況，來(lái)決定設(shè)置哪種操作優(yōu)先。但這些方法還是沒(méi)有從根本上同時(shí)解決查詢和更新的問(wèn)題。

在一個(gè)有大數(shù)據(jù)量高并發(fā)的mysql里，我們還可采用另一種策略來(lái)進(jìn)行優(yōu)化，那就是通過(guò)mysql讀寫分離來(lái)實(shí)現(xiàn)負(fù)載均衡，這樣可避免優(yōu)先哪一種操作從而可能導(dǎo)致另一種操作的堵塞。

2、并發(fā)插入：

一般情況下，當(dāng)數(shù)據(jù)庫(kù)表有一個(gè)讀鎖時(shí)，其它進(jìn)程無(wú)法對(duì)此表進(jìn)行更新操作，但在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進(jìn)行。MyISAM存儲(chǔ)引擎有一個(gè)系統(tǒng)變量concurrent_insert，可以用來(lái)控制其并發(fā)插入的行為，其值分別可以為0、1或2。

當(dāng)concurrent_insert設(shè)置為0時(shí)，不允許并發(fā)插入。

當(dāng)concurrent_insert設(shè)置為1時(shí)，如果MyISAM表中沒(méi)有空洞（即表的中間沒(méi)有被刪除的行），MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。這也是MyISAM的默認(rèn)設(shè)置。

當(dāng)concurrent_insert設(shè)置為2時(shí)，無(wú)論MyISAM表中有沒(méi)有空洞，都允許在表尾并發(fā)插入記錄。

3、表級(jí)鎖情況分析命令：

【查看哪些表被加鎖了】mysql > show open tables;

【查詢表級(jí)鎖爭(zhēng)用情況分析】mysql> show status like 'tables%';

mysql> show open tables;

+----------+-------+--------+-------------+

| dbtest | book | 1 | 0 |

| dbtest | mylock| 1 | 0 |

+----------+-------+--------+-------------+

參數(shù)說(shuō)明：

（1）Database：含有該表的數(shù)據(jù)庫(kù)。

（2）Table：表名稱。

（3）In_use：表當(dāng)前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當(dāng)前沒(méi)有被使用。

（4）Name_locked：表名稱是否被鎖定。名稱鎖定用于取消表或?qū)Ρ磉M(jìn)行重命名等操作。

mysql> show status like 'tables%';

+------------------------+-------+

| Variable_name | TABLE |

| Table_locks_immediate | 105 |

| Table_locks_waited | 1 |

+------------------------+-------+

這里有兩個(gè)狀態(tài)變量記錄MySQL內(nèi)部表級(jí)鎖定的情況，兩個(gè)變量說(shuō)明如下：

（1）Table_locks_immediate: 產(chǎn)生表級(jí)鎖定的次數(shù)，表示可以立即獲取鎖的查詢次數(shù)，每立即獲取鎖值加1

（2）Table_locks_waited: 出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù)(不能立即獲取鎖的次數(shù)，每等待一次鎖值加1)，此值高則說(shuō)明存在著較嚴(yán)重的表級(jí)鎖定爭(zhēng)用情況。

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库：锁机制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： MySQL数据库：通用查询日志和慢查询日
下一篇： MySQL数据库：存储过程Procedu