MySQL中InnoDB鎖機制介紹及一些測試

INNODB 鎖說明：

鎖機制是事務型資料庫中為了保證資料的一致完整性的特性；在對MySQL進行效能調優時，往往需要重點考慮鎖機制對整個事務的影響。MySQL 5.5 版本後InnoDB儲存引擎已是預設儲存引擎，這裡重點介紹InnoDB儲存引擎的鎖機制。

InnoDB 主要使用兩種級別的鎖機制：行級鎖和表級鎖。

Innodb 的行級鎖分為兩種型別: 共享鎖和排他鎖。

而在鎖機制的實現過程中為了讓行級鎖定和表級鎖定共存，Innodb使用了意向鎖（表級鎖）的概念，分意向共享鎖和意向排他鎖這兩種。

對於這幾種鎖模式解釋的場景如下：

事務A想讀某一行資料，需要給這行資料新增一個共享鎖，此時如果發現這行資料上有一個共享鎖鎖著，事務A可以繼續新增一個共享鎖，但無法加排他鎖。如果發現這行資料有個排他鎖鎖著，事務A則需要等該鎖釋放才能進行鎖定，此時，事務A可以在該行資料的表上新增一個意向鎖，如果需要共享鎖，則新增意向共享鎖；如果需要排他鎖，則需要新增意向排他鎖。

所以，可以說Innodb的鎖定模式實際上可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX），可以通過以下表格來總結上面這四種所的共存邏輯關係：

	共享鎖（S）	排他鎖（X）	意向共享鎖（IS）	意向排他鎖（IX）
共享鎖（S）	相容	衝突	相容	衝突
排他鎖（X）	衝突	衝突	衝突	衝突
意向共享鎖（IS）	相容	衝突	相容	相容
意向排他鎖（IX）	衝突	衝突	相容	衝突

根據對資料上鎖所鎖定的範圍不同，又分了以下三種鎖演算法型別：

· 記錄鎖（Record Locks）:記錄鎖鎖定索引中一條記錄。（比如id=5的記錄）

· 間隙鎖（Gap Locks）: 鎖定一個範圍，但不包含記錄本身（比如本身是id=5,鎖住了id=1,2,3,4的記錄）

· Next-Key Locks:Next-Key 鎖是索引記錄上的記錄鎖和在索引記錄之前的間隙鎖的組合，包含記錄本身。（前兩種的組合，比如id=1,2,3,4，5的記錄）

MYSQL 中如何檢視鎖：

通過show engine innodb status\G; 檢視是否存在鎖表情況：

通過information_schema.innodb_trx 檢視事務情況。

select * from information_schema.innodb_trx\G;

通過information_schema.INNODB_LOCKS表查詢鎖情況：

select * from information_schema.INNODB_LOCKS\G;

通過information_schema.INNODB_LOCK_waits檢視鎖阻塞情況：

select * from information_schema.INNODB_LOCK_waits\G;

無索引引起行鎖升級表鎖實驗：

會話1：update t表上i=4記錄，欄位i上沒有索引

mysql> start transaction;

會話2：update t表上i=1記錄，但此時發現在等待鎖。

mysql> start transaction;

由於b列上沒有索引，所以在update的時候自動升級為表鎖，導致後面的update會話雖然操作的不是同一行，但是仍被堵塞。

通過查詢鎖情況，這兩個會話的情況都對錶t請求x模式的鎖。

mysql> select * from information_schema.innodb_locks\G;

*************************** 1. row ***************************

lock_id: 8710:107:3:2

lock_trx_id: 8710

lock_mode: X

lock_type: RECORD

lock_table: `cwdtest`.`t`

lock_index: GEN_CLUST_INDEX

lock_space: 107

lock_page: 3

lock_rec: 2

lock_data: 0x000000000300

*************************** 2. row ***************************

lock_id: 8709:107:3:2

lock_trx_id: 8709

lock_mode: X

lock_type: RECORD

lock_table: `cwdtest`.`t`

lock_index: GEN_CLUST_INDEX

lock_space: 107

lock_page: 3

lock_rec: 2

lock_data: 0x000000000300

2 rows in set, 1 warning (0.00 sec)

嘗試通過GDB除錯，抓取會話被阻塞時執行的程式碼，可以發現最終處於隔離級別的判定，此次需要深入解讀具體原始碼。

(gdb) p *(trx_sys->rw_trx_list->start->lock->trx_locks->start)

$1 = {trx = 0x7f2c0823c980, trx_locks = {prev = 0x0, next = 0x0}, index = 0x0, hash = 0x0, un_member = {tab_lock = {table = 0x7f2b98013468, locks = {prev = 0x0,

next = 0x0}}, rec_lock = {space = 2550215784, page_no = 32555, n_bits = 0}}, type_mode = 17}

(gdb) p trx_sys->rw_trx_list->start->lock->trx_locks->start->un_member->tab_lock->table->name

$2 = {m_name = 0x7f2b98010c88 "cwdtest/t"}

5467 } else if (match_mode == ROW_SEL_EXACT_PREFIX) {

(gdb)

5516 if (prebuilt->select_lock_type != LOCK_NONE) {

(gdb)

5533 || dict_index_is_spatial(index)) {

(gdb)

5530 || srv_locks_unsafe_for_binlog

(gdb)

5531 || trx->isolation_level <= TRX_ISO_READ_COMMITTED

(gdb) // 隔離級別小於等於讀已提交

5532 || (unique_search && !rec_get_deleted_flag(rec, comp)) // 唯一鍵掃描

(gdb)

5533 || dict_index_is_spatial(index)) {

(gdb)

此時需要會話1提交完成後，會話2才能正常獲取到鎖。

因為間隙鎖被堵塞：

會話1：

mysql> update t set i=i+9 where i < 4 and i > 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

會話2：

mysql> insert into t values(4);

------------

TRANSACTIONS

------------

Trx id counter 8727

Purge done for trx's n:o < 8720 undo n:o < 0 state: running but idle

History list length 16

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421302068564688, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 8726, ACTIVE 194 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 4, OS thread handle 139826788828928, query id 76 localhost root update

insert into t values(4)

------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 107 page no 4 n bits 80 index i of table `cwdtest`.`t` trx id 8726 lock_mode X locks gap before rec insert intention waiting

------------------

---TRANSACTION 8725, ACTIVE 208 sec

4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1

MySQL thread id 5, OS thread handle 139826788558592, query id 71 localhost root

--------

mysql> select * from information_schema.innodb_locks\G;

*************************** 1. row ***************************

lock_id: 8726:107:4:3

lock_trx_id: 8726

lock_mode: X,GAP

lock_type: RECORD

lock_table: `cwdtest`.`t`

lock_index: i

lock_space: 107

lock_page: 4

lock_rec: 3

lock_data: 5, 0x000000000304

*************************** 2. row ***************************

lock_id: 8725:107:4:3

lock_trx_id: 8725

lock_mode: X

lock_type: RECORD

lock_table: `cwdtest`.`t`

lock_index: i

lock_space: 107

lock_page: 4

lock_rec: 3

lock_data: 5, 0x000000000304

2 rows in set, 1 warning (0.00 sec)

ERROR:

No query specified

由於行級鎖產生時是通過在指向資料記錄的第一個索引鍵之前和最後一個索引鍵之後的空域空間上標記鎖定資訊而實現的，因此在過濾條件中含有範圍查詢，會造成範圍內的行都被鎖定。

從上述的實驗來看，Innodb引擎行級鎖的實現效果遠遠不及Oracle，由於行級鎖的實現嚴重依賴於索引，所以在SQL優化時需要重點關注索引的資訊。

因此，針對Innodb的優化建議，可以簡單歸納為以下幾點：

（一）儘可能讓所有的查詢都通過索引來完成，從而避免Innodb因為無法通過索引鍵加鎖而升級為表級鎖定；

（二）避免大事務的產生，減少被鎖的資料、索引和鎖定時間長度；

（三）減少基於範圍的資料查詢過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；

死鎖實驗：

mysql> use cwdtest;

Database changed

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

+------+

| i |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> use cwdtest;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2216730/，如需轉載，請註明出處，否則將追究法律責任。

MySQL中InnoDB鎖機制介紹及一些測試

相關文章