MySQL 原理與優化：原數據鎖的應用

MySQL 中原數據鎖是系統自動控制添加的，對于用戶來說無需顯示調用，當我們使用一張表的時候就會加上原數據鎖。

創新互聯主要從事網頁設計、PC網站建設（電腦版網站建設）、wap網站建設（手機版網站建設）、成都響應式網站建設公司、程序開發、網站優化、微網站、重慶小程序開發公司等，憑借多年來在互聯網的打拼，我們在互聯網網站建設行業積累了豐富的做網站、網站設計、網站設計、網絡營銷經驗，集策劃、開發、設計、營銷、管理等多方位專業化運作于一體。

原數據鎖的作用是為了保護表原數據的一致性，如果在表上有活動事務的時候，不可以對元數據進行寫入操作。也就是為了避免DML 和DDL 之間的沖突，保證讀寫的正確性。

說白了就是， 在對數據表進行讀寫操作的時候，不能進行修改表結構的操作 。

如上圖所示，在執行select 操作的時候，MySQL 會自動加上shared_read 鎖，在insert，update， delete 以及 select for update 操作的時候會加上shared_write 鎖，這兩類鎖是兼容的。

在執行alter table 操作的時候，會加上 exclusive 鎖，這個鎖與shared_read 和 shared_write 鎖 是互斥的，換句話說在 做查詢和更新表數據的時候，是不能夠修改表結構 的。

來看個例子

首先開啟事務，使用select 語句會針對表加上shared_read的共享鎖

begin;

select * from course;

此時查看原數據鎖的信息

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

通過上圖我們可以發現，course 表加上了shared_read鎖。

接著，開啟另外一個事務，記住剛才的事務不要commit

begin;

update course set name = 'Jason' where id =2;

如上圖所示，此時的update 語句可以執行成功，并沒有被阻塞。說明select 和update 是不沖突的，他們的鎖是兼容的。

再次查看原數據鎖

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

從上面的截圖可以看出，此時原數據鎖的表中記錄了兩條記錄分別是針對course 表的shared_read 和 shared_write 鎖，也剛好對應我們執行的select 和update 操作。

最后，我們再啟動第三個客戶端，并且啟動 第三個事務，執行alter語句,在course 表中加入一個字段hello 如下 。

begin;

alter table course add column hello int;

由于之前的事務沒有提交所以修改表的操作會被阻塞， 因為shared_read 以及 shared_write 這兩個鎖 與 exclusive之間是互斥的，所以會阻塞 。

此時，回到最開始的兩個客戶端，對兩個事務進行commit 操作，再返回到第三個事務執行的alter 語句出，發現語句順利執行。

mysql 的鎖以及間隙鎖

mysql 為并發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的并發讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數據，還有一種是“當前讀”，一般加鎖實現“當前讀”，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低于后面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果后面事務插入數據并提交，那么在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

一文詳解-MySQL 事務和鎖

當多個用戶訪問同一份數據時，一個用戶在更改數據的過程中，可能有其他用戶同時發起更改請求，為保證數據庫記錄的更新從一個一致性狀態變為另外一個一致性狀態，使用事務處理是非常必要的，事務具有以下四個特性：

MySQL 提供了多種事務型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務。為了支持事務，InnoDB 存儲引擎引入了與事務處理相關的 REDO 日志和 UNDO 日志，同時事務依賴于 MySQL 提供的鎖機制

事務執行時需要將執行的事務日志寫入日志文件，對應的文件為 REDO 日志。當每條 SQL 進行數據更新操作時，首先將 REDO 日志寫進日志緩沖區。當客戶端執行 COMMIT 命令提交時，日志緩沖區的內容將被刷新到磁盤，日志緩沖區的刷新方式或者時間間隔可以通過參數 innodb_flush_log_at_trx_commit 控制

REDO 日志對應磁盤上的 ib_logifleN 文件，該文件默認為 5MB，建議設置為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日志的記錄，恢復最新數據，保證已提交事務的持久性

與 REDO 日志相反，UNDO 日志主要用于事務異常時的數據回滾，具體內容就是記錄數據被修改前的信息到 UNDO 緩沖區，然后在合適的時間將內容刷新到磁盤

假如由于系統錯誤或者 rollback 操作而導致事務回滾，可以根據 undo 日志回滾到沒修改前的狀態，保證未提交事務的原子性

與 REDO 日志不同的是，磁盤上不存在單獨的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對應的 .ibd 數據文件中，即使 MySQL 服務啟動了獨立表空間

在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務

MySQL 定義了四種隔離級別，指定事務中哪些數據改變其他事務可見、哪些數據該表其他事務不可見。低級別的隔離級別可以支持更高的并發處理，同時占用的系統資源更少

InnoDB 系統級事務隔離級別可以使用以下語句設置：

查看系統級事務隔離級別：

InnoDB 會話級事務隔離級別可以使用以下語句設置：

查看會話級事務隔離級別：

在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。讀取未提交的數據稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新后的數據，但由于 B 事務回滾，導致 A 事務出現了臟讀現象

所有事務只能看見已經提交事務所做的改變，此級別可以解決臟讀，但也會導致不可重復讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的數據，在 B 事務更新并提交后，A 事務又讀取到了更新后的數據，此時就出現了同一 A 事務中的查詢出現了不同的查詢結果

MySQL 默認的事務隔離級別，能確保同一事務的多個實例在并發讀取數據時看到同樣的數據行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的數據做了修改，這種修改會涉及表中的全部數據行，同時第二個事務也修改這個表中的數據，這次的修改是向表中插入一行新數據，此時就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行

InnoDB 通過多版本并發控制機制（MVCC）解決了該問題：InnoDB 通過為每個數據行增加兩個隱含值的方式來實現，這兩個隱含值記錄了行的創建時間、過期時間以及每一行存儲時間發生時的系統版本號，每個查詢根據事務的版本號來查詢結果

通過強制事務排序，使其不可能相互沖突，從而解決幻讀問題。簡而言之，就是在每個讀的數據行上加上共享鎖實現，這個級別會導致大量的超時現象和鎖競爭，一般不推薦使用

為了解決數據庫并發控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對并發操作進行控制，因此產生了鎖

共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以后，可以對鎖定范圍內的數據執行讀操作

排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以后，可以對鎖定范圍內的數據執行寫操作

有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之后。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之后

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對數據上共享鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時允許的并發量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數據時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執行時使用的也是表鎖

行鎖可以支持最大的并發，InnoDB 存儲引擎使用該鎖機制。如果要支持并發讀/寫，建議采用 InnoDB 存儲引擎