溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章的知識點包括：全局鎖、表級鎖和行鎖的概念和使用、解決死鎖和避免死鎖檢測的損耗，閱讀完整文相信大家對數據庫的鎖有了一定的認識。
全局鎖會讓整個庫處于只讀狀態，其他線程語句（DML，DDL，更新事務類）的語句都被會阻塞。
在做全庫邏輯備份時，會把整庫進行 select 然后保存成文本。
想象這樣一個場景，要備份一個購買系統，其中購買操作設計到更新賬號余額表和用戶課程表。
現在進行邏輯備份，在備份過程中，一位用戶購買了一門課程，這時需要在余額表扣掉余額，然后在購買的課程中加上一門課。正確的順序肯定是先進行購買操作，減少余額和增加課程然后在進行備份。但卻有可能出現這樣的問題：
如果在時間順序上先備份余額表 (u_account)，然后用戶購買(操作兩張表)，再備份用戶課程表(u_course)？
這時用備份的數據做恢復時，會發現用戶沒花錢卻買了一堂課。原因在于，先備份余額表，說明用戶余額不變。之后才進行購買操作，余額表減錢，課程表增加一門課程。接著備份課程表，課程表課程加一。購買操作在已經備份完的余額表后進行。
如果在時間順序上先備份用戶課程表(u_course)，然后用戶購買(操作兩張表)，再備份余額表 (u_account)？
同樣的，如果先備份課程表，課程沒有增加，因為沒有進行購買操作。之后進行購買操作后，余額表減錢，然后被備份。就出現了，用戶花錢卻沒有購買成功的情況。
也就是說，不加鎖的話，備份系統的得到的庫不是一個邏輯時間點，這個視圖是邏輯不一致。
對于不支持事務的引擎，像 MyISAM. 通過使用 Flush tables with read lock (FTWRL) 命令來開啟全局鎖。
但使用 FTWRL 存在的問題是：
對于支持事務并且開啟一致性視圖（可重復讀級別）下配合上 MVCC 的功能的引擎（InnoDB），備份就很簡單了。
使用官方的 mysqldump 工具時，加上 --single-transaction 選項，再導出數據前就會啟動一個事務，來確保拿到一致性視圖。并且由于 MVCC 的支持，同時可以進行更新操作。
為什么不推薦使用 set global readonly=true ，要使用 FTWRL ：
在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變量的方式影響面更大，不建議使用。
在異常處理機制上有差異。
執行 FTWRL 命令之后由于客戶端發生異常斷開，那么 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。
將整個庫設置為 readonly 之后，如果客戶端發生異常，則數據庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處于不可寫狀態，風險較高。
表級鎖的作用域是對某張表進行加鎖，在 MySQL 中表級別的鎖有兩種，一種是表鎖，一種是元數據鎖（meta data lock，MDL）。
與 FTWRL 類似，可以使用 lock tables … read/write 來鎖定某張表。在釋放時，可以使用 unlock tables 來釋放鎖或者斷開連接時，主動釋放。
需要注意的是，這樣方式的鎖表，不但會限制其他線程的讀寫，也限定了自己線程的操作對象。
假如，線程 A 執行 lock tables t1 read, t2 write; 操作。
這時對于表 t1 來說，其他線程只能只讀，線程 A 也只能只讀，不能寫。
對于表 t2 來說，只允許線程 A 讀寫，其他線程讀寫都會被阻塞。
與表鎖手動加鎖不同，元數據鎖會自動加上。
為什么要有 MDL？
MDL 保證的就是讀寫的正確性，比如在查詢一個中的數據時，此時另一個線程改變了表結構，查詢的結果和表結構不一致肯定不行。簡單來說，*MDL 就是解決 DML 和 DDL 之間同時操作的問題。*
在 MySQL 5.5 引入了 MDL，在對一個進行 DML 時，會加 DML 讀鎖。進行 DDL 時，會加 MDL寫鎖。
讀鎖間不互斥，允許多個線程同時對同一張表進行 DML。
讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。
MDL 引發的問題？
給表加字段，卻導致庫掛了？
由于 MDL 是自動加的，并且在給表加字段或者修改字段或者加索引時，需要掃描全表的數據。所以在對大表操作時，要非常小心，以免對線上的服務造成影響。但實際上，操作小表時，也可能出問題。假設 t 是小表。按照下圖所示，打開四個 session.
MySQL 5.7.27
假設有一張叫 sync_test 的表：
mysql> desc sync_test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)開啟事務1, 插入數據。對于事務 1 來說，自動申請了表 sync_test 的 MDL 讀鎖：
開啟事務2，插入數據。對于事務 2 來說，自動申請了表 sync_test 的 MDL 讀鎖：
開啟事務3，改變表結構。對于事務 3 來說，會申請表 sync_test 的 MDL 寫鎖，這時由于讀寫鎖互斥，被阻塞：
開啟事務 4，插入數據。對于事務 4 來說，會申請 sync_test 的 MDL 讀鎖，由于之前事務 3 提前申請了寫鎖，互斥所以被阻塞：
這時如果在這張表上的查詢語句很頻繁，而且客戶端有重連機制，在超時后會再起一個新 session 請求，這個庫的線程就很快會爆滿了。
通過上面的例子也可以看到，MDL 會直到事務提交才釋放，在做表結構變更的時候，一定要小心不要導致鎖住線上查詢和更新。在開啟事務后，并沒有在短時間內結束，也就是由于所謂的長事務造成的。如果想對某個表進行 DDL 的操作時，可以先查詢下是否有長事務的運行（information_schema 下的 innodb_trx 表），可以先 kill 這個事務，然后做 DDL 操作。
但有時 kill 也未必可以，在表被頻繁使用時，新的事務可能馬上就來了。比較理想的情況，在 alter table 中設定等待時間，如果在時間內拿到最好，否則就放棄，不要阻塞語句。之后再重復這個操作。
MariaDB 已經合并了 AliSQL 的這個功能，所以這兩個開源分支目前都支持 DDL NOWAIT/WAIT n 這個語法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...MySQL 的行鎖是由引擎層自己實現的，所有不是所有的引擎都執行行鎖，比如在 MyISAM 引擎就不支持行鎖。不支持行鎖意味著并發控制只能用表鎖，這就造成了在同一時刻只有一個更新在執行，就影響到了業務的并發度。InnoDB 支持行鎖是讓 MyISAM 被取代的重要原因。
行鎖就是對數據庫表中行記錄的鎖。比如事務 A，B 同時想要更新一行數據，在更新時一定會按照一定的順序進行，而不能同時更新。
行鎖的目的就是減少像表級別的鎖沖突，來提升業務的并發度。
在 InnoDB 的事務中，行鎖是在需要的時候在加上，但并不是使用完就釋放，而是在事務結束后才釋放，這就是兩階段鎖協議。
假設有一個表 t，事務 A, B 操作表 t 的過程如下：
在事務 A 的兩條語句更新后，事務 B 更新操作會被阻塞。直到事務 A 中執行 commit 操作后才能執行。
由于兩階段鎖的特點，在事務結束時才會釋放鎖，所以需要遵循的一個原則是事務中需要鎖多個行時，把有可能造成鎖沖突，最可能影響并發度的鎖盡量向后放。
比如購買課程的例子，顧客 A 購買培訓機構 B 一門課程。涉及到操作：
對于第二個操作，當有許多人同時購買時并發度就較高，出現鎖沖突的情況也較高。所以將操作 2 放置一個事務的最后就更好。
當有時并發度過大時，我們會發現一種現象 CPU 的使用率接近 100%，但事務執行數量卻很少。這就可能出現了死鎖。
當并發系統中不同的線程出現循環的資源依賴，等待別的線程釋放資源時，就會讓涉及的線程處于一直等待的情況。這就稱為死鎖。
如上圖中，事務 A 對id =1 的所在行，加入了行鎖。等待 id=2 的行鎖。事務 B 對 id = 2 的行，加入了行鎖。等待 id=1 的行鎖。事務 A，B 等待對方資源的釋放。
方式 一： 設置死鎖的等待時間 innodb_lock_wait_timeout
還是 sync_test 這張表，模擬簡單的鎖等待情況，注意這里并不是死鎖。開啟兩個事務 A，B. 同時對 id=1 這行進行更新。
事務 A 更新操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update sync_test set name="dead_lock_test" where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0事務 B 更新操作：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update sync_test set name="dead_lock_test2" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction可以看到事務 B 拋出了死鎖等待的錯誤。
設置等待時間的問題
在 InnoDB 中，MySQL 默認的死鎖等待時間是 50s. 意味著在出現死鎖后，被鎖住的線程要過 50s 被能退出，這對于在線服務說，等待時間過長。但如果把值設置的過小，如果是像上述例子這樣是簡單的鎖等待呢，并不是死鎖怎么辦，就會出現誤傷的情況。
方式二：發起死鎖檢測，發現死鎖后，主動回滾某個事務，讓其他事務繼續執行。
MySQL 中默認就是打開狀態，能夠快速發現死鎖的情況。
set innodb_deadlock_detect=on事務 A，B 互相依賴，造成死鎖的例子：
開啟事務 A:
mysql> begin;
mysql> update sync_test set name="dead_lock_test1" where id = 1;開啟事務 A:
mysql> begin;
mysql> update sync_test set name="dead_lock_test3" where id = 3;繼續操作事務 A:
mysql> update sync_test set name="dead_lock_test3_1" where id = 3;
# 會出現阻塞的情況繼續操作事務 B:
mysql> update sync_test set name="dead_lock_test1_2" where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction此時事務 A 阻塞取消，執行成功。
不過檢測死鎖也是有額外負擔的，每當一個事務被鎖的時候，就要看看它所依賴的線程有沒有被別人鎖住，如此循環，最后判斷是否出現了循環等待，也就是死鎖。如果是所有事務都要更新同一行的場景呢？每個新來的被堵住的線程，都要判斷會不會由于自己的加入導致了死鎖，這是一個時間復雜度是 O(n) 的操作。假設有 1000 個并發線程要同時更新同一行，那么死鎖檢測操作就是 1000*1000=100 萬這個量級的。
所以，對于更新頻繁并發量大的表，死鎖檢測會導致消耗大量的 CPU.
方法一：如果保證業務一定不會出現死鎖，可以臨時把死鎖檢查關掉。
但這樣存在一定的風險，因為業務設計時不會把死鎖當做嚴重的問題，出現死鎖后回滾后，再重試就沒有問題了。但關掉死鎖檢測后，可能出現大量超時的情況。
方法二：控制并發度。
如果對于并發量能控制，比如同一行同時最多只有 10 個線程在更新，那么死鎖檢測的成本很低，就不會出現這個問題。具體來說在客戶端做并發控制，但對于客戶端較多的應用，也無法控制。所以并發控制在數據庫服務端，如果有中間件，也可以考慮在中間件中實現。
方法三：降低死鎖的概率
將一行統計的結構，拆成多行累計的結構。比如將之前某個教學機構的金額由一行拆成 10 行，總收入就等于這 10 行數據的累計。這樣原來鎖沖突的概率變為原來的 1/10, 也就減少了死鎖檢測的 CPU 消耗。但在一部分行記錄變成0 時，代碼需要特殊處理。
看完上述內容，你們對數據庫的鎖有進一步的了解嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
