MySQL中表鎖和行鎖機制淺析(原始碼篇)

2022-11-05 14:00:08

前言

眾所周知，MySQL的儲存引擎有MyISAM和InnoDB，鎖粒度分別是表鎖和行鎖。

後者的出現從某種程度上是彌補前者的不足，比如：MyISAM不支援事務，InnoDB支援事務。表鎖雖然開銷小，鎖錶快，但高並行下效能低。行鎖雖然開銷大，鎖錶慢，但高並行下相比之下效能更高。事務和行鎖都是在確保資料準確的基礎上提高並行的處理能力。下面分別進行介紹：

行鎖

行鎖的劣勢：

開銷大；
加鎖慢；
會出現死鎖

行鎖的優勢：

鎖的粒度小，發生鎖衝突的概率低；
處理並行的能力強

加鎖的方式：

自動加鎖：對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖；
無鎖：對於普通SELECT語句，InnoDB不會加任何鎖；當然我們也可以顯示的加鎖：
共用鎖：select * from tableName where ... + lock in share more
排他鎖：select * from tableName where ... + for update

InnoDB和MyISAM的最大不同點有兩個：

InnoDB支援事務(transaction)；
預設採用行級鎖。

加鎖可以保證事務的一致性，下面我們來學習一下MySQL的事務知識.

MySQL 事務屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有ACID屬性。 原子性（Atomicity）：事務是一個原子操作單元。在當時原子是不可分割的最小元素，其對資料的修改，要麼全部成功，要麼全部都不成功。 一致性（Consistent）：事務開始到結束的時間段內，資料都必須保持一致狀態。 隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並行操作影響的"獨立"環境執行。 永續性（Durable）：事務完成後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

事務常見問題

更新丟失（Lost Update）原因：當多個事務選擇同一行操作，並且都是基於最初選定的值，由於每個事務都不知道其他事務的存在，就會發生更新覆蓋的問題。類比github提交衝突。

髒讀（Dirty Reads）原因：事務A讀取了事務B已經修改但尚未提交的資料。若事務B回滾資料，事務A的資料存在不一致性的問題。

不可重複讀（Non-Repeatable Reads）原因：事務A第一次讀取最初資料，第二次讀取事務B已經提交的修改或刪除資料。導致兩次讀取資料不一致。不符合事務的隔離性。

幻讀（Phantom Reads）原因：事務A根據相同條件第二次查詢到事務B提交的新增資料，兩次資料結果集不一致。不符合事務的隔離性。

幻讀和髒讀有點類似髒讀是事務B裡面修改了資料，幻讀是事務B裡面新增了資料。

事務的隔離級別

資料庫的事務隔離越嚴格，並行副作用越小，但付出的代價也就越大。這是因為事務隔離實質上是將事務在一定程度上"序列"進行，這顯然與"並行"是矛盾的。根據自己的業務邏輯，權衡能接受的最大副作用。從而平衡了"隔離" 和 "並行"的問題。MySQL預設隔離級別是可重複讀。髒讀，不可重複讀，幻讀，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。

+------------------------------+---------------------+--------------+--------------+--------------+
| 隔離級別                      | 讀資料一致性         | 髒讀         | 不可重複 讀   | 幻讀         |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未提交讀(Read uncommitted)    | 最低階別            | 是            | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+
| 已提交讀(Read committed)      | 語句級              | 否           | 是           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重複讀(Repeatable read)     | 事務級              | 否           | 否           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable)        | 最高階別，事務級     | 否           | 否           | 否           |
+------------------------------+---------------------+--------------+--------------+--------------+

檢視當前資料庫的事務隔離級別：show variables like 'tx_isolation';

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

間隙鎖

當我們用範圍條件檢索資料，並請求共用或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做"間隙(GAP)"。InnoDB也會對這個"間隙"加鎖，這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;

Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

危害(坑)：若執行的條件是範圍過大，則InnoDB會將整個範圍內所有的索引鍵值全部鎖定，很容易對效能造成影響。

排他鎖

排他鎖，也稱寫鎖，獨佔鎖，當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4000 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (9.53 sec)

共用鎖

共用鎖，也稱讀鎖，多用於判斷資料是否存在，多個讀操作可以同時進行而不會互相影響。當如果事務對讀鎖進行修改操作，很可能會造成死鎖。如下圖所示。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

分析行鎖定

通過檢查InnoDB_row_lock 狀態變數分析系統上的行鎖的爭奪情況 show status like 'innodb_row_lock%'

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

innodb_row_lock_current_waits: 當前正在等待鎖定的數量 innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度；非常重要的引數， innodb_row_lock_time_avg: 每次等待所花平均時間；非常重要的引數， innodb_row_lock_time_max: 從系統啟動到現在等待最常的一次所花的時間； innodb_row_lock_waits: 系統啟動後到現在總共等待的次數；非常重要的引數。直接決定優化的方向和策略。

行鎖優化

1 儘可能讓所有資料檢索都通過索引來完成，避免無索引行或索引失效導致行鎖升級為表鎖。 2 儘可能避免間隙鎖帶來的效能下降，減少或使用合理的檢索範圍。 3 儘可能減少事務的粒度，比如控制事務大小，而從減少鎖定資源量和時間長度，從而減少鎖的競爭等，提供效能。 4 儘可能低階別事務隔離，隔離級別越高，並行的處理能力越低。

表鎖

表鎖的優勢：開銷小；加鎖快；無死鎖表鎖的劣勢：鎖粒度大，發生鎖衝突的概率高，並行處理能力低加鎖的方式：自動加鎖。查詢操作（SELECT），會自動給涉及的所有表加讀鎖，更新操作（UPDATE、DELETE、INSERT），會自動給涉及的表加寫鎖。也可以顯示加鎖：共用讀鎖：lock table tableName read; 獨佔寫鎖：lock table tableName write; 批次解鎖：unlock tables;

共用讀鎖

對MyISAM表的讀操作（加讀鎖），不會阻塞其他程序對同一表的讀操作，但會阻塞對同一表的寫操作。只有當讀鎖釋放後，才能執行其他程序的寫操作。在鎖釋放前不能取其他表。

Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
8 rows in set (0.01 sec)

mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)

獨佔寫鎖

對MyISAM表的寫操作（加寫鎖），會阻塞其他程序對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其他程序的讀寫操作。在鎖釋放前不能寫其他表。

Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)

mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)

總結：表鎖，讀鎖會阻塞寫，不會阻塞讀。而寫鎖則會把讀寫都阻塞。

檢視加鎖情況

show open tables; 1表示加鎖，0表示未加鎖。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

分析表鎖定

可以通過檢查table_locks_waited 和 table_locks_immediate 狀態變數分析系統上的表鎖定：show status like 'table_locks%'

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

table_locks_immediate: 表示立即釋放表鎖數。 table_locks_waited: 表示需要等待的表鎖數。此值越高則說明存在著越嚴重的表級鎖爭用情況。

此外，MyISAM的讀寫鎖排程是寫優先，這也是MyISAM不適合做寫為主表的儲存引擎。因為寫鎖後，其他執行緒不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永久阻塞。

什麼場景下用表鎖

InnoDB預設採用行鎖，在未使用索引欄位查詢時升級為表鎖。MySQL這樣設計並不是給你挖坑。它有自己的設計目的。即便你在條件中使用了索引欄位，MySQL會根據自身的執行計劃，考慮是否使用索引(所以explain命令中會有possible_key 和 key)。如果MySQL認為全表掃描效率更高，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

第一種情況：全表更新。事務需要更新大部分或全部資料，且表又比較大。若使用行鎖，會導致事務執行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖衝突。

第二種情況：多表級聯。事務涉及多個表，比較複雜的關聯查詢，很可能引起死鎖，造成大量事務回滾。這種情況若能一次性鎖定事務涉及的表，從而可以避免死鎖、減少資料庫因事務回滾帶來的開銷。

頁鎖

開銷和加鎖時間介於表鎖和行鎖之間；會出現死鎖；鎖定粒度介於表鎖和行鎖之間，並行處理能力一般。只需瞭解一下。

補充：行級鎖與死鎖

　　MyISAM中是不會產生死鎖的，因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼全部等待。而在InnoDB中，鎖是逐步獲得的，就造成了死鎖的可能。

　　在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。

　　當兩個事務同時執行，一個鎖住了主鍵索引，在等待其他相關索引。另一個鎖定了非主鍵索引，在等待主鍵索引。這樣就會發生死鎖。

　　發生死鎖後，InnoDB一般都可以檢測到，並使一個事務釋放鎖回退，另一個獲取鎖完成事務。

有多種方法可以避免死鎖，這裡只介紹常見的三種

　　1、如果不同程式會並行存取多個表，儘量約定以相同的順序存取表，可以大大降低死鎖機會。

　　2、在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

　　3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

總結

1 InnoDB 支援表鎖和行鎖，使用索引作為檢索條件修改資料時採用行鎖，否則採用表鎖。 2 InnoDB 自動給修改操作加鎖，給查詢操作不自動加鎖 3 行鎖可能因為未使用索引而升級為表鎖，所以除了檢查索引是否建立的同時，也需要通過explain執行計劃查詢索引是否被實際使用。 4 行鎖相對於表鎖來說，優勢在於高並行場景下表現更突出，畢竟鎖的粒度小。 5 當表的大部分資料需要被修改，或者是多表複雜關聯查詢時，建議使用表鎖優於行鎖。 6 為了保證資料的一致完整性，任何一個資料庫都存在鎖定機制。鎖定機制的優劣直接影響到一個資料庫的並行處理能力和效能。

到此這篇關於MySQL中表鎖和行鎖機制淺析的文章就介紹到這了,更多相關MySQL表鎖和行鎖機制內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！