MySQL InnoDB鎖型別及鎖原理範例解析

2022-11-28 22:02:37

鎖

鎖是用來解決事務對資料的並行存取的問題的。MyISAM支援表鎖，InnoDB同時支援表鎖和行鎖。

表加鎖語法：

lock tables xxx read;
lock tables xxx write;
unlock tables;

鎖分類

兩個行級別的鎖：

共用鎖Shared Locks、排他鎖Exclusive Locks

兩個表級別的鎖：

意向共用鎖、意向排他鎖

鎖的演演算法:

三個Record Locks、Gap Locks、Next-Key Locks，把它們叫做鎖的演演算法，也就是分別在什麼情況下鎖定什麼範圍。

插入意向鎖：

是一個特殊的間隙鎖。間隙鎖不允許插入資料，但是插入意向鎖允許多個事務同時插入資料到同一個範圍。比如（4,7),一個事務插入5,一個事務插入6，不會發生鎖等待。

自增鎖：

是一種特殊的表鎖，用來防止自增欄位重複，資料插入以後就會釋放，不需要等到事務提交才釋放。如果需要選擇更快的自增值生成速度或者更加連續的自增值，就要通過修改自增鎖的模式改變。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.01 sec)

0:traditonal：每次都會產生表鎖

1:consecutive：會產生一個輕量鎖，simple insert會獲得批次的鎖，保證連插入，預設值

2:interleaved：不會鎖表，來一個處理一個，並行最高

共用鎖

共用鎖是一個行級別的鎖，它叫Shared Locks

獲取一行資料的讀鎖以後，可以用來讀取資料，所以它也叫做讀鎖

注意不要在加上讀鎖以後去寫資料，不然可能會出現死鎖的情況

多個事務可以共用一把讀鎖。

作用：

因為共用鎖會阻塞其他事務的修改，所以可以用在不允許其他事務修改資料的情況

給一行資料手動加上一把讀鎖：

SELECT SQL LOCK IN SHARE MODE;

釋放鎖：

只要事務結束，鎖就會自動釋放鎖

驗證

驗證共用鎖是否可以重複獲取

事務1：開啟事務，執行查詢，不提交事務

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事務2：開啟事務，執行查詢，正常查詢

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;
COMMIT;

事務3：開啟事務，執行修改，遇到阻塞

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

排他鎖

排他鎖是一個行級別的鎖，叫做Exclusive Locks，它是用來運算元據的，所以又叫做寫鎖。

只要一個事務獲取了一行資料的排它鎖，其他的事務就不能再獲取這一行資料的共用鎖和排它鎖。

加鎖方式

1.自動加排他鎖：在運算元據的時候，包括增刪改，都會預設加上一個排它鎖。

2.手動加鎖：用一個FOR UPDATE給一行資料加上一個排它鎖，這個無論是在程式碼裡還是運算元據的工具裡，都比較常用。

驗證

驗證排它鎖的特性

事務1：開啟事務，執行查詢，不提交事務

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

事務2：開啟事務，執行查詢，出現阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事務3：開啟事務，執行查詢，出現阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務4：開啟事務，執行查詢，出現阻塞

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

意向鎖

意向鎖是表級別的鎖，是由資料庫自己維護的，分為：意向共用鎖、意向排他鎖

當給一行資料加上共用鎖之前，資料庫會自動在這張表上面加一個意向共用鎖

當給一行資料加上排他鎖之前，資料庫會自動在這張表上面加一個意向排他鎖

反過來：

如果一張表上面至少有一個意向共用鎖，說明有其他的事務給其中的某些資料行加上了共用鎖

如果一張表上面至少有一個意向排他鎖，說明有其他的事務給其中的某些資料行加上了排他鎖

驗證

事務1：開啟事務，執行查詢，並手動加上排他鎖，事務不提交

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務2：開啟事務，給表加上鎖，出現阻塞

BEGIN;
LOCK TABLES tableName WRITE;

釋放表鎖

unlock tables;

記錄鎖

當對唯一索引和主鍵索引使用等值查詢，精準匹配一條記錄的時候，使用的就是記錄鎖。

間隙鎖

當查詢記錄不存在，沒有命中任何一行資料，無論是用等值查詢還是範圍查詢，它使用的都是間隙鎖。

間隙鎖主要是阻塞插入insert。相同的間隙鎖之間不衝突。

臨鍵鎖

當使用範圍查詢，不僅僅命中Record記錄，還包含間隙，在這種情況下使用的是臨鍵鎖，它是MySQL裡面預設的行鎖演演算法，相當於記錄鎖加上間隙鎖。

唯一性索引，等值查詢匹配到一條記錄的時候，退化成記錄鎖。沒有匹配到任何記錄的時候，退化成間隙鎖。

死鎖

死鎖的發生需要滿足一定的條件，在發生死鎖時，InnoDB一般都能通過演演算法（wait-for graph)自動檢測到。

死鎖產生條件

同一時刻只能有一個事務持有這把鎖

其他事務需要在這個事務釋放鎖之後才能獲取鎖，而不可以強行剝奪

當多個事務形成等待環路的時候，即發生死鎖

說到底就是因為鎖本身是互斥的

行鎖發生死鎖

模擬1

事務1

BEGIN;
1.SELECT * FROM tableName WHERE id=1 FOR UPDATE;
3.UPDATE tableName SET column1='test'  WHERE id=2;

事務2

BEGIN;
2.DELETE FROM tableName WHERE id=2;
4.DELETE FROM tableName WHERE id=1;

在第一個事務中，檢測到死鎖，馬上退出釋放鎖，第二個事務獲得鎖，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

模擬2

事務1

BEGIN;
1.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;
3.UPDATE tableName SET column1='aa'  WHERE id=1;

事務2

BEGIN;
2.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;
4.UPDATE tableName SET column1='bb'  WHERE id=1;

在第二個事務中，檢測到死鎖，馬上退出釋放鎖，第一個事務獲得鎖，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

表鎖發生死鎖

BEGIN;
1.LOCK TABLES tableName1 WRITE;
3.LOCK TABLES tableName2 WRITE; 阻塞，直到4執行，釋放tableName1鎖，獲取tableName2的鎖

BEGIN;
2.LOCK TABLES tableName2 WRITE;
4.LOCK TABLES tableName1 WRITE; 鎖機制檢測到死鎖，自動釋放鎖，獲取tableName1

鎖的釋放

死鎖在事務結束（commit、rollback）、或使用者端斷開連線時釋放鎖。

事務阻塞

如果一個事務一直未釋放鎖，其他事務會被阻塞50秒，通過引數控制獲取鎖的等待時間，預設是50秒。

mysql> show VARIABLES like 'innodb_lock_wait_timeout' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

死鎖的避免

在程式中，操作多張表時，儘量以相同的順序來存取（避免形成等待環路）

批次操作單張表資料的時候，先對資料進行排序（避免形成等待環路）

申請足夠級別的鎖，如果要運算元據，就申請排它鎖

儘量使用索引存取資料，避免沒有where條件的操作，避免鎖表

如果可以，大事務化成小事務

使用等值查詢而不是範圍查詢查詢資料，命中記錄，避免間隙鎖對並行的影響

鎖的紀錄檔

檢視行鎖資訊

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 436657 |
| Innodb_row_lock_time_avg      | 15057  |
| Innodb_row_lock_time_max      | 51578  |
| Innodb_row_lock_waits         | 29     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:當前正在等待鎖定的數量
Innodb_row_lock_time:從系統啟動到現在鎖定的總時間長度，單位ms
Innodb_row_lock_time_avg:每次等待所花平均時間
Innodb_row_lock_time_max:從系統啟動到現在等待最長的一次所花的時間
Innodb_row_lock_waits:從系統啟動到現在總共等待的次數

檢視當前執行的所有事務，還有具體的語句

select * from information_schema.INNODB_TRX;

當前出現的鎖

select * from information_schema.INNODB_LOCKS;

鎖等待的對應關係

select * from information_schema.INNODB_LOCK_WAITS;

mysql> select * from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                     | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 517172          | LOCK WAIT | 2022-10-09 22:28:59 | 517172:809:3:6        | 2022-10-09 22:33:20 |          4 |                 185 | SELECT * FROM tableName WHERE id=1 FOR UPDATE | starting index read |                 1 |                 2 |                4 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 329261206788832 | RUNNING   | 2022-10-09 22:28:12 | NULL                  | NULL                |          2 |                 203 | NULL                                          | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select * from information_schema.INNODB_LOCKS;
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 517172:809:3:6          | 517172          | X         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
| 329261206788832:809:3:6 | 329261206788832 | S         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id        |
+-------------------+-------------------+-----------------+-------------------------+
| 517172            | 517172:809:3:6    | 329261206788832 | 329261206788832:809:3:6 |
+-------------------+-------------------+-----------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

開啟標準監控和鎖監控，得到更加詳細的鎖資訊

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

行鎖的原理

在InnoDB中，行鎖是通過鎖住索引來實現的。因此，當一個事務鎖住一行資料的時候，其他的事務不能操作這一行資料，是因為它鎖住了這行資料對應的索引。

不帶任何索引的表

不帶任何索引的表中，在沒有索引或者沒有用到索引的情況下，會鎖住整張表

事務1：開啟事務，執行查詢，不提交事務

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務2：開啟事務，執行修改，進入阻塞

BEGIN;
UPDATE tableName SET column1='test'  WHERE id=3;

事務3：開啟事務，執行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=2 FOR UPDATE;

帶主鍵索引的表

在帶主鍵索引的表中，使用相同id加鎖會衝突，使用不同id加鎖，可以成功

事務1：開啟事務，執行查詢，不提交事務

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務2：開啟事務，執行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務3：開啟事務，執行修改、查詢，正常執行

BEGIN;
UPDATE tableName SET column1='test'  WHERE id=3;
SELECT * FROM tableName WHERE id=3 FOR UPDATE;

帶唯一索引的表

在帶唯一索引的表中，使用相同唯一值會加鎖會衝突，使用不同唯一值加鎖，可以成功。

事務1：開啟事務，執行查詢，不提交事務

BEGIN;
SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事務2：開啟事務，執行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事務3：開啟事務，執行查詢，查詢的是上述事務操作的加鎖的那條資料，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務4：開啟事務，執行查詢，正常執行

BEGIN;
SELECT * FROM tableName WHERE column1='column2' FOR UPDATE;
SELECT * FROM tableName WHERE id=2 FOR UPDATE;

結論

1.表必定有索引

鎖是基於索引進行鎖資料的，因此，一張表必定有索引

如果定義主鍵（PRIMARYKEY)，那麼InnoDB會選擇主鍵作為聚集索引

如果沒有顯式定義主鍵，則 InnoDB 會選擇第一個不包含有NULL值的唯一索引作為主鍵索引

如果也沒有這樣的唯一索引，則 InnoDB 會選擇內建6位元組長的ROWID作為隱藏的聚集索引，它會隨著行記錄的寫入而主鍵遞增

一張不帶任何索引的表，造成鎖表，是因為查詢沒有使用索引，會進行全表掃描，然後把每一個隱藏的聚集索引都鎖住。

2.唯一索引資料行加鎖，主鍵索引同樣被鎖

聚集索引就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄資料。

輔助索引，也叫非聚集索引，和聚集索引相比，葉子節點中並不包含行記錄的全部資料，而是包含二級索引和主鍵的值。例如column1的索引和主鍵id值1

主鍵索引裡面除了索引之外，還儲存了完整的資料。所以通過輔助索引鎖定一行資料的時候，它跟檢索資料的步驟是一樣的，會通過主鍵值找到主鍵索引，因此會鎖定。

本質上是因為鎖定的是同一行資料，所以會相互衝突。

以上就是MySQL InnoDB鎖型別及鎖原理範例解析的詳細內容，更多關於MySQL InnoDB鎖型別鎖原理的資料請關注it145.com其它相關文章！

MySQL InnoDB鎖型別及鎖原理範例解析

目錄

鎖

共用鎖

排他鎖

意向鎖

記錄鎖

間隙鎖

臨鍵鎖

死鎖

死鎖產生條件

行鎖發生死鎖

表鎖發生死鎖

鎖的釋放

事務阻塞

死鎖的避免

鎖的紀錄檔

行鎖的原理

不帶任何索引的表

帶主鍵索引的表

帶唯一索引的表

結論

1.表必定有索引

2.唯一索引資料行加鎖，主鍵索引同樣被鎖

熱門文章