MySQL表鎖、行鎖、排它鎖及共享鎖怎么使用

這篇文章主要介紹“MySQL表鎖、行鎖、排它鎖及共享鎖怎么使用”的相關(guān)知識，小編通過實(shí)際案例向大家展示操作過程，操作方法簡單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL表鎖、行鎖、排它鎖及共享鎖怎么使用”文章能幫助大家解決問題。

一、事務(wù)隔離機(jī)制的選擇

• 如果我們完全不管，使用未提交讀的事務(wù)隔離機(jī)制，任由這些線程并發(fā)操作數(shù)據(jù)庫，那就會出現(xiàn)臟讀（讀取了未commit的數(shù)據(jù)）、不可重復(fù)讀（兩次查詢值不同）、幻讀（兩次查詢數(shù)據(jù)量不同）等問題，數(shù)據(jù)的安全性最低，優(yōu)點(diǎn)是并發(fā)效率非常高，一般不會使用

• 如果我們串行化（靠鎖實(shí)現(xiàn)），通過鎖給所有的事務(wù)都排個(gè)序，雖然數(shù)據(jù)的安全性提高了，并發(fā)的效率就太低了，一般也不會使用

• 所以我們一般用的是已提交讀、可重復(fù)讀這兩個(gè)隔離級別，平衡了數(shù)據(jù)的安全性，一致性以及并發(fā)的效率 ，是由MVCC多版本并發(fā)控制實(shí)現(xiàn)的（MVCC是已提交讀和可重復(fù)讀的原理，鎖是串行化的原理）

二、表級鎖&行級鎖

表級鎖：對整張表加鎖。開銷?。ㄒ?yàn)椴挥萌フ冶淼哪骋恍械挠涗涍M(jìn)行加鎖，要修改這張表，直接申請加這張表的鎖），加鎖快，不會出現(xiàn)死鎖；鎖粒度大，發(fā)生鎖沖突的概率高，并發(fā)度低

行級鎖：對某行記錄加鎖。開銷大（需要找到表中相應(yīng)的記錄，有搜表搜索引的過程），加鎖慢，會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度高

InnoDB存儲引擎支持事務(wù)處理，表支持行級鎖定，并發(fā)能力更好

1. InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，而不是給表的行記錄加鎖實(shí)現(xiàn)的，這就意味者只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則InnoDB將使用表鎖

2. 由于InnoDB的行鎖實(shí)現(xiàn)是針對索引字段添加的鎖，不是針對行記錄加的鎖，因此雖然訪問的是InnoDB引擎下表的不同行，但如果使用相同的索引字段作為過濾條件，依然會發(fā)生鎖沖突，只能串行進(jìn)行，不能并發(fā)進(jìn)行

3. 即使SQL中使用了索引，但是經(jīng)過MySQL的優(yōu)化器后，如果認(rèn)為全表掃描比使用索引效率高，此時(shí)會放棄使用索引，因此也不會
   使用行鎖，而是使用表鎖，比如對一些很小的表，MySQL就不會去使用索引

三、排它鎖（Exclusive）和共享鎖（Shared）

• 排它鎖，又稱為X鎖，寫鎖

• 共享鎖，又稱為S鎖，讀鎖

讀讀（SS）之間是可以兼容的，但是讀寫（SX、SX）之間，寫寫（XX）之間是互斥的

對事務(wù)加X和S鎖之間有以下的關(guān)系：

• 一個(gè)事務(wù)對數(shù)據(jù)對象A加了 S 鎖，可以對A進(jìn)行讀取操作但不能進(jìn)行update操作，加鎖期間其它事務(wù)能對A加S鎖但不能加 X 鎖

• 一個(gè)事務(wù)對數(shù)據(jù)對象A加了 X 鎖，就可以對A進(jìn)行讀取和更新，加鎖期間其它事務(wù)不能對A加任何鎖

顯示加鎖：select … lock in share mode強(qiáng)制獲取共享鎖，select … for update獲取排它鎖

1. 測試不同事務(wù)之間排它鎖和共享鎖的兼容性

我們先查看表的SQL以及內(nèi)容

查看隔離級別：

首先開啟一個(gè)事務(wù)，給id=7的數(shù)據(jù)加上排它鎖

在用另一個(gè)客戶端開啟事務(wù)

我們用另一個(gè)事務(wù)的服務(wù)線程給id=7的數(shù)據(jù)加上排它鎖，阻塞了

我們嘗試給id=7的數(shù)據(jù)加上共享鎖，還是阻塞了

總結(jié)：不同事務(wù)之間對于數(shù)據(jù)的鎖，只有SS鎖可以共存，XX、SX、XS都不能共存

2. 測試行鎖加在索引項(xiàng)上

其實(shí)行鎖是加在索引樹上的

用表的無索引字段作為過濾條件

事務(wù)2現(xiàn)在同樣想獲取這條記錄的排它鎖，可想而知地失敗了；那現(xiàn)在事務(wù)2獲取chenwei的記錄的排它鎖，試試能不能成功

InnoDB是支持行鎖的，剛才以主鍵id為過濾條件時(shí)，事務(wù)1和事務(wù)2獲取不同行的鎖是可以成功的。然而現(xiàn)在我們發(fā)現(xiàn)獲取name為chenwei的排它鎖也獲取不到了，這是為什么？我們解釋一下：

InnoDB的行鎖是通過給索引項(xiàng)加鎖來實(shí)現(xiàn)的，而不是給表的行記錄加鎖實(shí)現(xiàn)的

而我們用name作為過濾條件沒有用到索引，自然就不會使用行鎖，而是使用表鎖。這就意味著只有通過索引檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則InnoDB都將使用表鎖!!!

我們給name字段加上索引

我們發(fā)現(xiàn)，給name加上索引后，兩個(gè)事務(wù)可以獲取到不同行的排它鎖（for update），再一次證明了InnoDB的行鎖是加在索引項(xiàng)上的

因?yàn)楝F(xiàn)在name走的是索引， 通過zhangsan在輔助索引樹上找到它所在行記錄的id是7，然后到主鍵索引樹上，獲取對應(yīng)行記錄的排他鎖（個(gè)人猜測應(yīng)該是輔助索引樹和主鍵索引樹相應(yīng)的記錄都加了鎖）

四、串行化隔離級別測試

（所有的事務(wù)都使用排它鎖或共享鎖，不需要用戶手動加鎖）

設(shè)置串行化隔離級別

兩個(gè)事務(wù)可以同時(shí)獲取共享鎖（SS共存）

現(xiàn)在讓事務(wù)2插入數(shù)據(jù)

此時(shí)由于insert需要加排它鎖，但由于事務(wù)1已經(jīng)對整張表添加了共享鎖，事務(wù)2無法再對表成功加鎖（SX不共存）

rollback一下

因?yàn)槲覀兘oname加上了索引，以上的select相當(dāng)于給name為zhangsan的數(shù)據(jù)加上了行共享鎖

事務(wù)2 update

事務(wù)2不能update，因?yàn)榇藭r(shí)已經(jīng)被事務(wù)1的共享鎖鎖住了整個(gè)表

事務(wù)2在輔助索引樹上找zhangsan，找到對應(yīng)的主鍵值，然后去主鍵索引樹找到相應(yīng)的記錄，但是發(fā)現(xiàn)這行記錄已經(jīng)被共享鎖鎖住了，事務(wù)2可以獲取共享鎖，但是不能獲取排他鎖

我們用主鍵索引id試試能不能update

依然阻塞住了，雖然我們where后面的字段現(xiàn)在使用的id而不是name，但是name也是通過輔助索引樹找到對應(yīng)的主鍵，再到主鍵索引樹上找相應(yīng)的記錄，而主鍵索引樹上的記錄加了鎖（個(gè)人猜想應(yīng)該是輔助索引樹和主鍵索引樹對應(yīng)的數(shù)據(jù)都加了鎖）

我們update id=8的數(shù)據(jù)，成功了。因?yàn)槲覀僺elect的時(shí)候，只是給id=7的數(shù)據(jù)加上了行鎖，我們操作id=8的數(shù)據(jù)當(dāng)然可以成功

有索引，則使用行鎖；沒有索引，則使用表鎖。

表級鎖還是行級鎖說的是鎖的粒度，共享鎖和排他鎖說的是鎖的性質(zhì)，不管是表鎖還是行鎖，都有共享鎖和排他鎖的區(qū)分

關(guān)于“MySQL表鎖、行鎖、排它鎖及共享鎖怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點(diǎn)。