搜尋
首頁資料庫mysql教程MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    前言

    由於我們的業務比較複雜,一個組成事務的相關多個SQL語句是必要的。因此,先解釋什麼是事務。一個事務是指一組SQL語句一起執行,必須要麼全部執行成功,要麼全部執行失敗,不允許存在部分成功或部分失敗的情況。一個事務有ACID特性:

    • 原子性:要嘛全部成功,要嘛全部失敗,這樣才能保證交易的一致性;

    • #一致性:例如銀行的轉賬,扣除一個人的錢肯定要給另一個人加錢,不能光扣除不加,這樣業務就存在問題,數據的一致性就破壞了;

    • 持久性:當我們資料commit以後,資料是先寫到快取當中,快取中的資料還是要慢慢花時間往磁碟上寫,如果此時停電了、宕機或重啟了,我們有redo log重做日誌來保證資料庫的持久性;

    • 隔離性:這塊可以說下事務為什麼要有隔離性,因為事務要允許並發執行,一個業務涉及了很多事務,而我們後台往往有很多業務,要能夠讓他們並發執行,如果所有的事務都是串行執行的話,那這樣我們寫多執行緒程式只有一個執行緒來做事情,這樣效率很低。所以事務要並發執行,但是並發執行涉及了一些問題:事務的安全性&一致性並發的效率問題,我們以這兩個東西為參考點,才得到了MySQL不同等級的同時/隔離,如果交易並發執行時我們完全不隔離的話,就可能會出現臟讀(事務B讀到了事務A還未提交的資料然後,然後用事務A未提交的數據去做計算,得到了很多其他的結果,然後事務A又把那個數據rollback掉,那麼事務B計算出來的都是有問題的數據,髒讀一定會出現問題)、不可重複讀取(以同樣的條件去一個數據,然後再次去查詢的時候發現數據的值有所改變,當然不可重複讀也不一定會有問題,有些業務場景下是允許的,這和業務上資料的安全性和一致性是否嚴格有關)和幻讀(在事務中按照同樣的條件前後兩次查詢的結果資料量不同)這些問題。

    那麼我們為了解決交易並發執行遇到的問題就給了交易的隔離等級:

    • #串行化,串行化完全用鎖來實現,透過鎖給所有事務排序,按順序執行,這樣做數據的安全性高但並發的效率很低,一般我們不會這樣做的。

    • 未提交讀取,對於我們寫的多執行緒程式來說,對於臨界區程式碼片段沒有做任何的並發控制,雖然並發性高但資料安全性很低,未提交讀取還允許髒讀的存在,這是有問題的所以絕對不會使用未提交讀取。 串行化和未提交讀在實際專案中是不會用到的,一般資料庫引擎預設工作在已提交讀取和可重複讀,這兩個隔離級別就結合了資料的安全性&一致性和資料的並發效率,這兩個是由MVCC多版本並發控制機制實現的

    • 已提交讀取,oracle預設工作等級。不允許讀取未commit的數據,這個等級仍然允許不可重複讀取和虛讀產生。

    • 可重複讀取,MySQL預設工作等級。保證事務再次讀取是依然得到相同的數據,部分解決了虛讀,但虛讀是仍然會出現的

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    注意:

    • 交易隔離等級越高,為避免衝突所花費的效能也就越多,也就是效率低。

    • 在「可重複讀」級別,實際上可以解決部分的虛讀問題,但是不能防止update更新產生的虛讀問題,要禁止虛讀產生,還是需要設置串行化隔離等級。

    交易隔離等級的實作原理:鎖定 MVCC。串行化底層實現原理是鎖,鎖有共享鎖、排它鎖、意向共享鎖、意向排它鎖、間隙鎖和死鎖,InnoDB的已提交讀和可重複讀的底層實現原理:MVCC(多版本並發控制),MVCC提供了一種並發讀取方式,包括快照讀取(同一份資料會有多個版本)、目前讀取、undo log和redo log。 MVCC是已提交讀取和可重複讀取的原理,鎖定是串行化的原理

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    交易日誌被用來實現ACID特性,而共用鎖定、排它鎖定和MVCC則被用來實現一致性(I)特性。交易日誌分為undo log(回滾日誌) 和redo log(重做日誌)

    一、表級鎖定&行級鎖定

    • 級鎖:整張表加鎖。開銷小(因為不用去找表的某一行的記錄加鎖,要修改這張表,直接申請加這張表的鎖),加鎖快,不會出

    • 現死鎖定;鎖定粒度大,發生鎖定衝突的機率高,並發度低

    • 行級鎖定:對某行記錄加鎖。開銷大(需要找到表中對應的記錄,有搜表搜尋引的過程),加鎖慢,會出現死鎖;鎖定粒度最小,發生鎖衝突的機率最低,並發度高

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    MyISAM儲存引擎只支援表格級鎖定,InnoDB支援交易處理,支援行級鎖定,並發能力更好

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    二、排它鎖&共享鎖

    • 排它鎖:又稱為X鎖,寫鎖

    • #共享鎖定:又稱為S鎖,讀鎖

    讀讀(SS)之間是可以相容的,但是讀寫(SX、SX )之間,寫寫(XX)之間是互斥的

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    1. 測試不同交易之間排它鎖和共享鎖的兼容性

    #我們先查看表格SQL及內容

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    檢視隔離等級:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #先開啟一個交易A,給id=7的資料加上排它鎖定:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #在另一個客戶端開啟交易B:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    給id=7不管加排它鎖定和共用鎖定都阻塞了並沒有查詢出來,因為A事務給id=7這一行的資料加了排它鎖,就是寫鎖,其他人不能讀也不能寫。

    總結:不同交易之間對於資料的鎖,只有SS鎖定可以共存,XX、SX、XS都不能共存

    2. 測試行鎖加在索引項目上

    其實行鎖定是加在索引樹上的。

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    每次做完測試都把剛做的rollback。

    用表格的無索引欄位作為過濾條件MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    那現在事務2取得不同行chenwei的記錄MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    InnoDB是支援行鎖的,剛才以主鍵id為過濾條件時,事務1和事務2取得不同行的鎖定是可以成功的。然而現在我們發現取得name為chenwei的排它鎖也取得不到了,這是為什麼?讓我們解釋一下:

    InnoDB的行鎖是透過將索引項加鎖來實現的,而不是給表的行記錄加鎖實現的

    而我們用name作為篩選條件沒有用到索引,自然就不會使用行鎖,而是使用表鎖。這就意味著只有透過索引檢索數據,InnoDB才使用行級鎖,否則InnoDB都會使用表鎖!!!

    我們給name欄位加上索引:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    然後再做剛才的動作:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #######

    我們發現,為name加上索引後,兩個交易可以取得到不同行的排它鎖定(for update),再一次證明了InnoDB的行鎖定是加在索引項目上的。

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    因為現在name走的是索引, 透過zhangsan在輔助索引樹上找到它所在行記錄的id是7,然後到主鍵索引樹上,取得對應行記錄的排他鎖定(個人猜測應該是輔助索引樹和主鍵索引樹對應的記錄都加了鎖)

    三、串列化隔離等級測試

    串行化所有交易用的都是共享鎖或排它鎖,不需要用手動添加。 select取得的是共享鎖,insert、delete和update取得的都是排它鎖。

    設定串列化隔離等級:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #兩個交易可以同時取得共享鎖定(SS共存:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    現在讓交易2插入資料;

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #由於Insert需要加排它鎖,但是由於事務1已經對整張表加了共享鎖,因此事務2無法再對錶成功加鎖(sx不共存)

    rollback一下,把所有獲取鎖的狀態都回退掉:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    開啟兩個交易:

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    ##因為我們給name加上了索引,以上的select相當於為name為zhangsan的資料加上了行共享鎖定

    交易2update;

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    事務2不能update,因為此時已經被事務1的共享鎖定鎖住了整個表

    事務2在輔助索引樹上找zhangsan,找到對應的主鍵值,然後去主鍵索引樹找到對應的記錄,但是發現這行記錄已經被共享鎖鎖住了,事務2可以獲取共享鎖,但是不能獲取排他鎖

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼##我們再用主鍵索引試試id能不能update

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼依然阻塞住了,雖然我們where後面的欄位現在使用的id而不是name,但是name也是透過輔助索引樹找到對應的主鍵,再到主鍵索引樹上找相應的記錄,而主鍵索引樹上的記錄加了鎖

    我們update id=8的數據,成功了。因為我們select的時候,只是給id=7的資料加上了行鎖,我們操作id=8的資料當然可以成功

    MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼

    #有索引,則使用行鎖;沒有索引,則使用表鎖。

    表級鎖還是行級鎖說的是鎖的粒度,共享鎖和排他鎖說的是鎖的性質,不管是表鎖還是行鎖,都有共享鎖和排他鎖的區分。

    串行化玩的就是排它鎖和共享鎖,在可重複讀取級別下,不手動加鎖的話,用的就是MVCC機制,實際上並沒有用到鎖,我們也可以手動加鎖。InnoDB如果不建立索引的話,用的是表鎖,如果查詢的時候用到了索引項,它用的就是行鎖了,行鎖是將索引加鎖,而不是單純地為一行資料加鎖。

    以上是MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章!

    陳述
    本文轉載於:亿速云。如有侵權,請聯絡admin@php.cn刪除
    MySQL與Sqlite有何不同?MySQL與Sqlite有何不同?Apr 24, 2025 am 12:12 AM

    MySQL和SQLite的主要區別在於設計理念和使用場景:1.MySQL適用於大型應用和企業級解決方案,支持高性能和高並發;2.SQLite適合移動應用和桌面軟件,輕量級且易於嵌入。

    MySQL中的索引是什麼?它們如何提高性能?MySQL中的索引是什麼?它們如何提高性能?Apr 24, 2025 am 12:09 AM

    MySQL中的索引是數據庫表中一列或多列的有序結構,用於加速數據檢索。 1)索引通過減少掃描數據量提升查詢速度。 2)B-Tree索引利用平衡樹結構,適合範圍查詢和排序。 3)創建索引使用CREATEINDEX語句,如CREATEINDEXidx_customer_idONorders(customer_id)。 4)複合索引可優化多列查詢,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5)使用EXPLAIN分析查詢計劃,避

    說明如何使用MySQL中的交易來確保數據一致性。說明如何使用MySQL中的交易來確保數據一致性。Apr 24, 2025 am 12:09 AM

    在MySQL中使用事務可以確保數據一致性。 1)通過STARTTRANSACTION開始事務,執行SQL操作後用COMMIT提交或ROLLBACK回滾。 2)使用SAVEPOINT可以設置保存點,允許部分回滾。 3)性能優化建議包括縮短事務時間、避免大規模查詢和合理使用隔離級別。

    在哪些情況下,您可以選擇PostgreSQL而不是MySQL?在哪些情況下,您可以選擇PostgreSQL而不是MySQL?Apr 24, 2025 am 12:07 AM

    選擇PostgreSQL而非MySQL的場景包括:1)需要復雜查詢和高級SQL功能,2)要求嚴格的數據完整性和ACID遵從性,3)需要高級空間功能,4)處理大數據集時需要高性能。 PostgreSQL在這些方面表現出色,適合需要復雜數據處理和高數據完整性的項目。

    如何保護MySQL數據庫?如何保護MySQL數據庫?Apr 24, 2025 am 12:04 AM

    MySQL數據庫的安全可以通過以下措施實現:1.用戶權限管理:通過CREATEUSER和GRANT命令嚴格控制訪問權限。 2.加密傳輸:配置SSL/TLS確保數據傳輸安全。 3.數據庫備份和恢復:使用mysqldump或mysqlpump定期備份數據。 4.高級安全策略:使用防火牆限制訪問,並啟用審計日誌記錄操作。 5.性能優化與最佳實踐:通過索引和查詢優化以及定期維護兼顧安全和性能。

    您可以使用哪些工具來監視MySQL性能?您可以使用哪些工具來監視MySQL性能?Apr 23, 2025 am 12:21 AM

    如何有效監控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。 1.使用mysqladmin查看連接數。 2.用SHOWGLOBALSTATUS查看查詢數。 3.PMM提供詳細性能數據和圖形化界面。 4.MySQLEnterpriseMonitor提供豐富的監控功能和報警機制。

    MySQL與SQL Server有何不同?MySQL與SQL Server有何不同?Apr 23, 2025 am 12:20 AM

    MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显著差异,选择时需考虑项目规模和未来扩展性。

    在哪些情況下,您可以選擇SQL Server而不是MySQL?在哪些情況下,您可以選擇SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

    在需要高可用性、高級安全性和良好集成性的企業級應用場景下,應選擇SQLServer而不是MySQL。 1)SQLServer提供企業級功能,如高可用性和高級安全性。 2)它與微軟生態系統如VisualStudio和PowerBI緊密集成。 3)SQLServer在性能優化方面表現出色,支持內存優化表和列存儲索引。

    See all articles

    熱AI工具

    Undresser.AI Undress

    Undresser.AI Undress

    人工智慧驅動的應用程序,用於創建逼真的裸體照片

    AI Clothes Remover

    AI Clothes Remover

    用於從照片中去除衣服的線上人工智慧工具。

    Undress AI Tool

    Undress AI Tool

    免費脫衣圖片

    Clothoff.io

    Clothoff.io

    AI脫衣器

    Video Face Swap

    Video Face Swap

    使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

    熱工具

    SublimeText3 英文版

    SublimeText3 英文版

    推薦:為Win版本,支援程式碼提示!

    記事本++7.3.1

    記事本++7.3.1

    好用且免費的程式碼編輯器

    SublimeText3漢化版

    SublimeText3漢化版

    中文版,非常好用

    mPDF

    mPDF

    mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

    禪工作室 13.0.1

    禪工作室 13.0.1

    強大的PHP整合開發環境