前言
由於我們的業務比較複雜,一個組成事務的相關多個SQL語句是必要的。因此,先解釋什麼是事務。一個事務是指一組SQL語句一起執行,必須要麼全部執行成功,要麼全部執行失敗,不允許存在部分成功或部分失敗的情況。一個事務有ACID特性:
原子性:要嘛全部成功,要嘛全部失敗,這樣才能保證交易的一致性;
#一致性:例如銀行的轉賬,扣除一個人的錢肯定要給另一個人加錢,不能光扣除不加,這樣業務就存在問題,數據的一致性就破壞了;
持久性:當我們資料commit以後,資料是先寫到快取當中,快取中的資料還是要慢慢花時間往磁碟上寫,如果此時停電了、宕機或重啟了,我們有redo log重做日誌來保證資料庫的持久性;
隔離性:這塊可以說下事務為什麼要有隔離性,因為事務要允許並發執行,一個業務涉及了很多事務,而我們後台往往有很多業務,要能夠讓他們並發執行,如果所有的事務都是串行執行的話,那這樣我們寫多執行緒程式只有一個執行緒來做事情,這樣效率很低。所以事務要並發執行,但是並發執行涉及了一些問題:事務的安全性&一致性和並發的效率問題,我們以這兩個東西為參考點,才得到了MySQL不同等級的同時/隔離,如果交易並發執行時我們完全不隔離的話,就可能會出現臟讀(事務B讀到了事務A還未提交的資料然後,然後用事務A未提交的數據去做計算,得到了很多其他的結果,然後事務A又把那個數據rollback掉,那麼事務B計算出來的都是有問題的數據,髒讀一定會出現問題)、不可重複讀取(以同樣的條件去一個數據,然後再次去查詢的時候發現數據的值有所改變,當然不可重複讀也不一定會有問題,有些業務場景下是允許的,這和業務上資料的安全性和一致性是否嚴格有關)和幻讀(在事務中按照同樣的條件前後兩次查詢的結果資料量不同)這些問題。
那麼我們為了解決交易並發執行遇到的問題就給了交易的隔離等級:
#串行化,串行化完全用鎖來實現,透過鎖給所有事務排序,按順序執行,這樣做數據的安全性高但並發的效率很低,一般我們不會這樣做的。
未提交讀取,對於我們寫的多執行緒程式來說,對於臨界區程式碼片段沒有做任何的並發控制,雖然並發性高但資料安全性很低,未提交讀取還允許髒讀的存在,這是有問題的所以絕對不會使用未提交讀取。 串行化和未提交讀在實際專案中是不會用到的,一般資料庫引擎預設工作在已提交讀取和可重複讀,這兩個隔離級別就結合了資料的安全性&一致性和資料的並發效率,這兩個是由MVCC多版本並發控制機制實現的。
已提交讀取,oracle預設工作等級。不允許讀取未commit的數據,這個等級仍然允許不可重複讀取和虛讀產生。
可重複讀取,MySQL預設工作等級。保證事務再次讀取是依然得到相同的數據,部分解決了虛讀,但虛讀是仍然會出現的
注意:
交易隔離等級越高,為避免衝突所花費的效能也就越多,也就是效率低。
在「可重複讀」級別,實際上可以解決部分的虛讀問題,但是不能防止update更新產生的虛讀問題,要禁止虛讀產生,還是需要設置串行化隔離等級。
交易隔離等級的實作原理:鎖定 MVCC。串行化底層實現原理是鎖,鎖有共享鎖、排它鎖、意向共享鎖、意向排它鎖、間隙鎖和死鎖,InnoDB的已提交讀和可重複讀的底層實現原理:MVCC(多版本並發控制),MVCC提供了一種並發讀取方式,包括快照讀取(同一份資料會有多個版本)、目前讀取、undo log和redo log。 MVCC是已提交讀取和可重複讀取的原理,鎖定是串行化的原理
交易日誌被用來實現ACID特性,而共用鎖定、排它鎖定和MVCC則被用來實現一致性(I)特性。交易日誌分為undo log(回滾日誌) 和redo log(重做日誌)
一、表級鎖定&行級鎖定
級鎖:整張表加鎖。開銷小(因為不用去找表的某一行的記錄加鎖,要修改這張表,直接申請加這張表的鎖),加鎖快,不會出
現死鎖定;鎖定粒度大,發生鎖定衝突的機率高,並發度低
行級鎖定:對某行記錄加鎖。開銷大(需要找到表中對應的記錄,有搜表搜尋引的過程),加鎖慢,會出現死鎖;鎖定粒度最小,發生鎖衝突的機率最低,並發度高
MyISAM儲存引擎只支援表格級鎖定,InnoDB支援交易處理,支援行級鎖定,並發能力更好
二、排它鎖&共享鎖
排它鎖:又稱為X鎖,寫鎖
#共享鎖定:又稱為S鎖,讀鎖
讀讀(SS)之間是可以相容的,但是讀寫(SX、SX )之間,寫寫(XX)之間是互斥的
1. 測試不同交易之間排它鎖和共享鎖的兼容性
#我們先查看表格SQL及內容
檢視隔離等級:
#先開啟一個交易A,給id=7的資料加上排它鎖定:
#在另一個客戶端開啟交易B:
給id=7不管加排它鎖定和共用鎖定都阻塞了並沒有查詢出來,因為A事務給id=7這一行的資料加了排它鎖,就是寫鎖,其他人不能讀也不能寫。
總結:不同交易之間對於資料的鎖,只有SS鎖定可以共存,XX、SX、XS都不能共存2. 測試行鎖加在索引項目上
其實行鎖定是加在索引樹上的。每次做完測試都把剛做的rollback。
用表格的無索引欄位作為過濾條件
那現在事務2取得不同行chenwei的記錄
InnoDB是支援行鎖的,剛才以主鍵id為過濾條件時,事務1和事務2取得不同行的鎖定是可以成功的。然而現在我們發現取得name為chenwei的排它鎖也取得不到了,這是為什麼?讓我們解釋一下:
InnoDB的行鎖是透過將索引項加鎖來實現的,而不是給表的行記錄加鎖實現的而我們用name作為篩選條件沒有用到索引,自然就不會使用行鎖,而是使用表鎖。這就意味著只有透過索引檢索數據,InnoDB才使用行級鎖,否則InnoDB都會使用表鎖!!!
我們給name欄位加上索引:
然後再做剛才的動作:
我們發現,為name加上索引後,兩個交易可以取得到不同行的排它鎖定(for update),再一次證明了InnoDB的行鎖定是加在索引項目上的。
因為現在name走的是索引, 透過zhangsan在輔助索引樹上找到它所在行記錄的id是7,然後到主鍵索引樹上,取得對應行記錄的排他鎖定(個人猜測應該是輔助索引樹和主鍵索引樹對應的記錄都加了鎖)
三、串列化隔離等級測試
串行化所有交易用的都是共享鎖或排它鎖,不需要用手動添加。 select取得的是共享鎖,insert、delete和update取得的都是排它鎖。
設定串列化隔離等級:
#兩個交易可以同時取得共享鎖定(SS共存:
現在讓交易2插入資料;
#由於Insert需要加排它鎖,但是由於事務1已經對整張表加了共享鎖,因此事務2無法再對錶成功加鎖(sx不共存)
rollback一下,把所有獲取鎖的狀態都回退掉:
開啟兩個交易:
交易2update;
##我們再用主鍵索引試試id能不能update
依然阻塞住了,雖然我們where後面的欄位現在使用的id而不是name,但是name也是透過輔助索引樹找到對應的主鍵,再到主鍵索引樹上找相應的記錄,而主鍵索引樹上的記錄加了鎖
我們update id=8的數據,成功了。因為我們select的時候,只是給id=7的資料加上了行鎖,我們操作id=8的資料當然可以成功
表級鎖還是行級鎖說的是鎖的粒度,共享鎖和排他鎖說的是鎖的性質,不管是表鎖還是行鎖,都有共享鎖和排他鎖的區分。
串行化玩的就是排它鎖和共享鎖,在可重複讀取級別下,不手動加鎖的話,用的就是MVCC機制,實際上並沒有用到鎖,我們也可以手動加鎖。InnoDB如果不建立索引的話,用的是表鎖,如果查詢的時候用到了索引項,它用的就是行鎖了,行鎖是將索引加鎖,而不是單純地為一行資料加鎖。
以上是MySQL的表級鎖,行級鎖,排它鎖和共用鎖是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章!

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

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

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

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

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

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

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

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


熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

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

熱門文章

熱工具

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

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

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

禪工作室 13.0.1
強大的PHP整合開發環境