MySQL InnoDB鎖類型及鎖原理實例解析

Posted on by

鎖

鎖是用來解決事務對數據的並發訪問的問題的。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鎖類型鎖原理的資料請關註WalkonNet其它相關文章！

推薦閱讀：