mysql 行鎖的實現

標籤：mysql   mysql 鎖   mysql gap   mysql 間隙鎖   mysql 鎖詳解   

mysql鎖的方式根據隔離等級不同而不同，因為預設隔離等級為repeatable-read可重複讀，我們普遍理解為mysql鎖實現方式為行鎖，行鎖就是利用索引實現完成的，mysql的支援的隔離等級有四種，這網上很多介紹，平常用的最多的也就是read-committed和repeatable-read兩個，今天就對這兩個隔離等級下鎖的實現做下對比

    

首先我們就用預設層級repeatable-read的隔離等級來做測試：

mysql> CREATE TABLE `t1` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `age1` int(11) DEFAULT NULL,

    ->   `age2` int(11) DEFAULT NULL,

    ->   PRIMARY KEY (`id`),

    ->   KEY `age1` (`age1`)

    -> ) ENGINE=InnoDB;

mysql> select * from t1;

+----+------+------+

| id | age1 | age2 |

+----+------+------+

|  1 |    1 |    2 |

|  2 |    1 |    2 |

|  3 |    1 |    2 |

|  4 |    1 |    4 |

|  5 |    1 |    2 |

|  6 |    2 |    3 |

+----+------+------+

建立了一個表t1,插入了部分資料，建立了age1的輔助索引，現在開兩個session來做測試：

session1	session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=55 where age1=1 and id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql>  update t1 set age2=55 where age1=1 and id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
rollback	rollback

看到這各位看官肯定覺得沒問題，就應該是這樣的啊，行鎖嘛就是利用索引找到對應行進行加鎖就好了，不急不急繼續測試

session1	session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=55 where age1=1 and age2=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=55 where age1=1 and id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
rollback	rollback

咦.............這是咋回事呢？這更新的明顯不是同一行為什麼會被阻塞呢？我這暴脾氣再用insert做下測試

session1	session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=111 where age1=2 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(age1,age2) values(1,33); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
rollback	rollback

..............出入資料和更新資料不是同一行業被阻塞了！好吧，來看下鎖等待資訊

mysql> select * from innodb_lock_waits \G;

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

                wait_started: 2016-04-07 15:35:00

                    wait_age: 00:00:08

               wait_age_secs: 8

                locked_table: `sbtest`.`t1`

                locked_index: PRIMARY

                 locked_type: RECORD

              waiting_trx_id: 33333

         waiting_trx_started: 2016-04-07 15:35:00

             waiting_trx_age: 00:00:08

     waiting_trx_rows_locked: 1

   waiting_trx_rows_modified: 0

                 waiting_pid: 3

               waiting_query: update t1 set age2=55 where age1=1 and id=1

             waiting_lock_id: 33333:33:3:2

           waiting_lock_mode: X

   .....................................

為了節約版面後面截就刪掉了，這是上面兩個update不同行被阻塞時的記錄

mysql> select * from innodb_lock_waits \G;

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

                wait_started: 2016-04-07 15:40:43

                    wait_age: 00:00:26

               wait_age_secs: 26

                locked_table: `sbtest`.`t1`

                locked_index: age1

                 locked_type: RECORD

              waiting_trx_id: 33343

         waiting_trx_started: 2016-04-07 15:40:43

             waiting_trx_age: 00:00:26

     waiting_trx_rows_locked: 1

   waiting_trx_rows_modified: 1

                 waiting_pid: 3

               waiting_query: insert into t1(age1,age2) values(1,33)

             waiting_lock_id: 33343:33:4:7

           waiting_lock_mode: X,GAP

      .......................................

這個是insert時候的記錄，第一個可以看出來是在等primary的X鎖，但是根據記錄我們可以查詢出兩個update的主索引值應該是不同，session1查出來的id應該為4，session2直接指定的id為1，這明顯不會一行啊，再來看看結合insert的鎖等待情況，這個鎖是在age1索引上，但是lock_mode多了一個GAP，這玩意是啥？好吧，只有撓著頭皮去官方文檔裡找了：

    Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

    原來msyql有間隙鎖機制，就是把兩個記錄直接的縫縫給鎖住，又或者鎖住前後記錄，在repeatable-read隔離等級下的mysql會有一個間隙鎖演算法，假如進行update掃描時，輔助索引有多個記錄滿足條件是會把這幾條記錄及記錄中間的縫縫都會鎖住，而insert的時候也會去判斷下一個記錄是否有加鎖，所以才會發生上面的阻塞。這是mysql為了保證該隔離等級下的可重複讀的機制，但也影響到業務並發的效率，可以修改系統參數innodb_locks_unsafe_for_binlog=on就可以關閉改間隙鎖機制。該值預設為off，修改交易隔離等級為read-committed也可以避免間隙鎖。修改為read-committed來試試看。

mysql> set global tx_isolation=‘read-committed‘; 

Query OK, 0 rows affected (0.00 sec)

全部session串連重新串連一下才會生效，現在來按上面的步驟再測試一下：

session1	session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=55 where age1=1 and age2=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=55 where age1=1 and id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
rollback	rollback

這個沒問題..................

session1	session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set age2=111 where age1=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(age1,age2) values(1,33); Query OK, 1 row affected (0.00 sec)
rollback	rollback

也沒問題..................

mysql在預設隔離等級repeatable-read下，會有gap lock和next-key lock兩個演算法鎖來確保資料可重複讀

Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record

next-key lock就是範圍鎖，當我們利用索引範圍掃描時，在這一個範圍內的資料都會被加鎖，以防止幻讀。

本文出自 “肖忠” 部落格，請務必保留此出處http://xiaozhong991.blog.51cto.com/2354914/1761360

mysql 行鎖的實現