MySQL 中有哪些鎖?
資料庫中鎖的設計初衷處理并發問題,作為多使用者共享資源,當出現并發通路的時候,資料庫需要合理控制資源通路規則。鎖就是實作這些通路規則中的重要資料。
鎖的分類
根據加鎖範圍,MySQL 裡面的鎖可以分成全局鎖、表級鎖、行鎖三類。
全局鎖
全局鎖,就是對整個資料庫執行個體加鎖,MySQL 提供了一個加全局讀鎖的方法,指令是:
Flush tables with read lock (FTWRL)
當需要整個庫隻讀狀态的時候,可以使用這個指令,之後其他線程的:資料更新語句(增删改),資料定義語句(建表,修改表結構)和更新事務的送出語句将會被阻塞。
全局鎖的使用場景
全局鎖的定型使用場景,做全庫邏輯備份。也就是把整個庫每個表都 Select 出來,然後存成文本。
如何整個庫都隻讀,會有什麼問題?
- 如果你在主庫上備份,那麼在備份期間都不能執行更想,業務就基本上停擺。
- 如果在從庫上備份,那麼備份期間從庫不能執行主庫同步過來的 binlog ,會導緻從延遲。
既然要全庫隻讀, 為什麼不使用set global readonly=true的方式呢?
readonly 方式也可以讓全庫進入隻讀狀态,但我還是會建議你用FTWRL方式, 主要有兩個原因:
- 一是, 在有些系統中, readonly的值會被用來做其他邏輯,比如用來判斷一個庫是主庫還是備庫。是以,修改global變量的方式影響面更大, 我不建議你使用。
- 二是, 在異常處理機制上有差異。如果執行FTWRL指令之後由于用戶端發生異常斷開, 那麼MySQL會自動釋放這個全局鎖, 整個庫回到可以正常更新的狀态。而将整個庫設定為readonly之後, 如果用戶端發生異常, 則資料庫就會一直保持readonly狀态, 這樣會導緻整個庫長時間處于不可寫狀态, 風險較高
表級别鎖
MySQL 裡面表級别的鎖有兩種:一種是表鎖,一種是中繼資料鎖(meta data lok, MDL)。表鎖的文法是 :
lock tables ... read/write
與 FTWRL 類似,可以使用 unlock tables 主動釋放鎖,也可以在用戶端斷開的時候自動釋放。需要注意的是,lock tables文法除了會限制别的線程的讀寫外,也限定了本線程接下來的操作對象。
MDL 表級鎖
MDL 不需要顯示使用,在通路一個表的時候自動加上, MDL 保證讀寫的正确性,也就是說在查詢資料時,不允許有其他線程對這個表結構做變更。
什麼操作會加 MDL 鎖?
在MySQL 5.5版本中引入了MDL, 當對一個表做增删改查操作的時候,加MDL讀鎖;當要對表做結構變更操作的時候,加MDL寫鎖。
- 讀鎖之間不互斥,是以可以有多個線程同時對一張表增删改查。
- 讀寫之間、寫鎖之間是互斥的,用來保證變更表結構操作的安全性,如果有兩個線程要同時給一個表加字段,其中一個要等另外一個執行完才能執行。
更改表結構要注意哪些?
給一個表加字段, 或者修改字段, 或者加索引, 需要掃描全表的資料。在對大表操作的時候, 你肯定會特别小心, 以免對線上服務造成影響。而實際上, 即使是小表, 操作不慎也會出問題,導緻整個庫的線程爆滿。
舉個例子
我們來看一下下面的操作序列, 假設表t是一個小表。
image
- session A先啟動, 這時候會對表t加一個MDL讀鎖。由于session B需要的也是MDL讀鎖, 是以可以正常執行。
- session C會被blocked, 是因為session A的MDL讀鎖還沒有釋放, 而session C需要MDL寫鎖, 是以隻能被阻塞,讀寫鎖互斥。
- 如果隻有session C自己被阻塞還沒什麼關系, 但是之後所有要在表t上新申請MDL讀鎖的請求也會被session C阻塞。前面我們說了,所有對表的增删改查操作都需要先申請MDL讀鎖, 就都被鎖住, 等于這個表現在完全不可讀寫了。
如果某個表上的查詢語句頻繁, 而且用戶端有重試機制,也就是說逾時後會再起一個新session 再請求的話, 這個庫的線程很快就會爆滿。事務中的MDL鎖, 在語句執行開始時申請, 但是語句結束後并不會馬上釋放, 而會等到整個事務送出後再釋放。
怎麼解決這個 更改表結構問題
比較理想的機制是, 在alter table語句裡面設定等待時間, 如果在這個指定的等待時間裡面能夠拿到MDL寫鎖最好, 拿不到也不要阻塞後面的業務語句, 先放棄。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
程式員開發者社群
微信号:程式員開發者社群
部落格:王小明