#如果我們完全不管,使用未提交讀取的事務隔離機制,任由這些執行緒並發操作資料庫,那就會出現髒讀(讀取了未commit的資料)、不可重複讀取(兩次查詢值不同)、幻讀(兩次查詢資料量不同)等問題,資料的安全性最低,優點是並發效率非常高,一般不會使用
如果我們序列化(靠鎖定實現),透過鎖定給所有的事務都排序,雖然資料的安全性提高了,並發的效率就太低了,一般也不會使用
所以我們一般用的是已提交讀取、可重複讀這兩個隔離級別,平衡了資料的安全性,一致性以及並發的效率,是由MVCC多版本並發控制實現的(MVCC是已提交讀取和可重複讀取的原理,鎖定是串行化的原理)
#表級鎖:整張表加鎖。開銷小(因為不用去找表的某一行的記錄進行加鎖,要修改這張表,直接申請加這張表的鎖),加鎖快,不會出現死鎖;鎖粒度大,發生鎖衝突的機率高,並發度低
行級鎖定:對某行記錄加鎖。開銷大(需要找到表中對應的記錄,有搜表搜尋引的過程),加鎖慢,會出現死鎖;鎖定粒度最小,發生鎖衝突的機率最低,並發度高
InnoDB儲存引擎支援交易處理,表支援行級鎖定,並發能力更好
InnoDB行鎖是透過給索引上的索引項目加鎖來實現的,而不是給表的行記錄加鎖實現的,這就意味者只有通過索引條件檢索數據,InnoDB才使用行級鎖,否則InnoDB將使用表鎖
由於InnoDB的行鎖實作是針對索引欄位新增的鎖,不是針對行記錄加的鎖,因此雖然存取的是InnoDB引擎下表的不同行,但如果使用相同的索引字段作為過濾條件,仍然會發生鎖定衝突,只能串行進行,不能並發進行
即使SQL中使用了索引,但是經過MySQL的優化器後,如果認為全表掃描比使用索引效率高,此時會放棄使用索引,因此也不會
使用行鎖,而是使用表鎖,例如對一些很小的表,MySQL就不會去使用索引
排它鎖,又稱為X鎖,寫鎖
共享鎖,又稱為S鎖,讀鎖
讀讀(SS)之間是可以相容的,但是讀寫(SX、SX)之間,寫寫(XX)之間是互斥的
對事務加X和S鎖之間有以下的關係:
一個事務對資料物件A加了S 鎖,可以對A進行讀取操作但不能進行update操作,加鎖期間其它事務能對A加S鎖但不能加X 鎖
一個事務對資料物件A加了X 鎖,就可以對A進行讀取和更新,加鎖期間其它事務不能對A加任何鎖
顯示加鎖:select … lock in share mode強制取得共享鎖,select … for update取得排它鎖
我們先查看表格的SQL以及內容
看隔離等級:
##首先開啟一個事務,給id=7的資料加上排它鎖定
在用另一個客戶端開啟事務
我們用另一個事務的服務執行緒為id=7的資料加上排它鎖,阻塞了我們嘗試給id=7的資料加上共享鎖,還是阻塞了
總結:不同事務之間對於資料的鎖,只有SS鎖可以共存,XX、SX、XS都不能共存######2. 測試行鎖加在索引項目上############其實行鎖定是加在索引樹上的#######
用表格的無索引欄位作為過濾條件
#交易2現在同樣想取得這條記錄的排它鎖,可想而知地失敗了;那現在事務2取得chenwei的記錄的排它鎖,試試能不能成功
InnoDB是支援行鎖的,剛才以主鍵id為過濾條件時,事務1和事務2取得不同行的鎖定是可以成功的。然而現在我們發現取得name為chenwei的排它鎖也取得不到了,這是為什麼?讓我們解釋一下:
InnoDB的行鎖是透過將索引項加鎖來實現的,而不是為表的行記錄加鎖實現的
而我們用name作為篩選條件沒有用到索引,自然就不會使用行鎖,而是使用表鎖。這就意味著只有透過索引檢索數據,InnoDB才使用行級鎖,否則InnoDB都會使用表鎖!!!
我們給name欄位加上索引
##我們發現,加上索引後,兩個交易可以取得到不同行的排它鎖定(for update),再一次證明了InnoDB的行鎖定是加在索引項目上的 因為現在name走的是索引, 透過zhangsan在輔助索引樹上找到它所在行記錄的id是7,然後
到主鍵索引樹上,取得對應行記錄的排他鎖(個人猜測應該是輔助索引樹和主鍵索引樹對應的記錄都加了鎖)
四、串列化隔離等級測試(所有的交易都使用排它鎖定或共用鎖定,不需要使用者手動加鎖)設定串列化隔離等級兩個交易可以同時取得共享鎖定(SS共存)
#現在讓事務2插入資料
#依然阻塞住了,雖然我們where後面的欄位現在使用的id而不是name,但是name也是透過輔助索引樹找到對應的主鍵,再到主鍵索引樹上找對應的記錄,而主鍵索引樹上的記錄加了鎖
(個人猜想應該是輔助索引樹和主鍵索引樹對應的資料都加了鎖)我們update id=8的數據,成功了。只為id為7的行資料加上了行鎖,因此我們可以成功操作id為8的資料 #有索引,則使用行鎖;沒有索引,則使用表鎖。 ###表級鎖還是行級鎖說的是鎖的粒度,共享鎖和排他鎖說的是鎖的性質,不管是表鎖還是行鎖,都有共享鎖和排他鎖的區分
以上是MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章!