1.鎖的分類
在Mysql中,鎖的分類包括三大類 全局鎖,表級鎖,行級鎖
2.全局鎖
全局鎖:就是對整個資料庫執行個體進行加鎖, 加鎖方法flush tables with read lock(FTWRL),整個庫為隻讀狀态,其他線程的語句 如 資料更新語句,資料定義語句和更新類事務的送出語句 都會被阻塞
使用場景: 做全庫邏輯備份,但是風險比較大
- 如果在主庫上做備份,那麼在備份期間都不能執行更新,業務基本上停擺
- 如果你在從庫上備份,備份期間從庫不能執行同步過來binlog,會導緻主人延遲
既然風險這麼大,在做邏輯備份時,不用可不可以? 可能會出現的問題是 在備份的時候,有更新操作,導緻資料不一緻,
解決辦法如下:
- mysqldump 中的一個參數 --single-transaction 可以在做邏輯備份時,生成一個事務快照視圖,熟悉MVCC的,應該對視圖不陌生,這樣可以保證在做備份時,資料是一緻的,但是有個前提是,資料表得支援 事務才可以,如果是Myisam引擎,就用不了這種 方法
- 還有一種是set global readonly=true的方式 也可以全局鎖的效果,但是不建議用,原因有如下幾點
- readonly的值有的時候會在程式中使用,來判斷是主庫還是從庫,如果修改此值,影響可能比較嚴重
- 在異常處理下有差别 ,如果執行 flush talbes with read lock,在用戶端發 生異常斷開後,那麼Mysql會自動釋放這個全局鎖,整個庫可以回到正常的更新的狀态,如果使用readonly之後,如果用戶端發生異常,則資料庫一直會保持readonly狀态,會導緻整個庫會處理不可寫狀态,風險較高
釋放全局鎖:指令: unlock tables
3.表級鎖
3.1表級鎖分類
表級鎖分類包括 表鎖和中繼資料鎖(MDL)
3.2表鎖
表鎖文法
加鎖: lock tables 表名 read/write
釋放鎖:unlock tables,也可以在用戶端斷開的進修自動釋放
lock tables 文法除了會限制其他線程的讀寫外,也會限制本線程接下來的操作對象
3.3中繼資料鎖(MDL)
中繼資料鎖(metadata lock),MDL不需要顯示指定,在通路一個表時候會被自動加上,作用是保證讀寫的正确性,特點如下:
- 讀鎖之間不互斥,是以可以有多個線程同時對一張表做增删改查
- 讀寫鎖,寫鎖之間是互斥的,用來保證變更表結構操作的安全性
3.4修改表結構,或者加索引有可能會出現的問題
在修改表結構或索引時,需要掃描全表 的資料,如果 線上上給大表 操作的話,可能會對服務有影響,修改小表也有可能出現問題
可以看到 session A 先啟動,會對表 加一個MDL鎖, 由于session2也是讀鎖,是以可以正常執行,但是session3 需要寫鎖,是以被阻塞,同時還會影響session3後面的 讀鎖請求,如果請求很大,這個庫的線程很快就會被占滿,這是很危險的
知識點:
事務中 MDL鎖,在語句執行時開始申請,語句結束後并不會馬上釋放,而是等整個事務送出後再釋放,這也是為什麼不建議出現長事務的原因
那如何安全的給小表添加字段呢?
解決辦法:
在MariaDB 和AliSql中都支援 ddl nowait/wait n 的文法
alter table t1 nowait add column
alter table t1 wait n add column 4.行級鎖
innodb引擎支援行級鎖
4.1 二階段鎖協定
在Innodb中 行鎖是在需要的時間才加上的,但并不是在不需要的時候就釋放了,而是要等到事務結束後才釋放,也就是二階段鎖協定
最佳實踐
如果你的事務中需要鎖多個行,要把最有可能生成鎖沖突,最有可能影響并發度的鎖盡量向後放
解釋一下上面的結論
比方2種扣減庫存的方案如下:
方案1:begin;// 扣減庫存update t_inventory set count=count-5 where id= ${id} and count>=5;// 鎖住使用者賬戶表select * from t_user_account where user_id=123 for update;// 插入訂單記錄insert into t_trans; commit;
方案2:begin;// 鎖住使用者賬戶表select * from t_user_account where user_id=123 for update;// 插入訂單記錄insert into t_trans;// 扣減庫存update t_inventory set count=count-5 where id=${id} and count>=5;commit;
兩者方案的時序如下圖所示:
由于庫存往往是最重要的熱點，是整個系統的瓶頸。那麼如果采用第二種方案的話,tps應該理論上能夠提升3rt/rt=3倍。這還僅僅是業務就隻有三條SQL的情況下，多一條sql就多一次rt,就多一倍的時間。
根據兩階段鎖協定，不論你怎樣安排語句順序，所有的操作需要的行鎖都是在事務送出的時候才釋放的。是以，如果你把更新庫存 安排在最後，那麼庫存這一行的鎖時間就最少。這就最大程度地減少了事務之間的鎖等待，提升了并發度。
4.2死鎖及死鎖檢測
死鎖:當并發系統中不同線程出現循環資源依賴,涉及的線程都在等待别的線程釋放資源時,就會導緻這幾個線程都進入無限等待的狀态,稱為死鎖,如下圖
這時候,事務A在等待事務B釋放id=2的行鎖,而事務B在等待事務A釋放id=1的行鎖,這種情況,就進入了死鎖狀态
4.3 死鎖解決辦法
- 一種政策是,直接進行等待,直到逾時, 由innodb_lock_wait_timeout 參數控制,預設值為50s,生産環境不建議用
- 第二種政策是 發起死鎖檢測,發現死鎖後,主動復原死鎖鍊條中的某一個事務,讓其他事務得以繼續執行 ,由innodb_deadlock_detect參數控制 ,設定on 表示開啟這個邏輯,預設為開
如果用第2種政策時,可能會出現熱點行更新導緻的性能問題,cpu 消息接近100% ,但是整個資料庫,每秒就執行不到100個事務的情況出現,原因是什麼?
原因如下:
每當一個事務被鎖時,都會檢查它所依賴的線程有沒有被别人鎖住,如此循環,最後判斷是否出現死鎖,如果有多個并發線程要同時執行更新同一行,那麼死鎖檢測操作會是非常大的指數級的操作,這期間要消耗大量的cpu資源,是以就會出現上面說的那種情況
解決辦法
- 直接關掉死鎖檢測,風險很大,不建議
- 控制并發度,可以在用戶端,中間件中,服務端做并發度控制
- 從業務方面調整,目标就是為了減少死鎖沖突,讓資源不集中處理,減少鎖沖突