MySQL交易隔離等級和鎖

標籤：

MySQL交易隔離等級和鎖

表結構

create table record(id int auto_increment primary key,title varchar(255) not null,shortName varchar(255) not null,authorId int not null,createTime datetime not null,state int  not null,totalView int default null);insert into record (title,shortName,authorId,createTime,state,totalView) values (‘hello world 000‘,‘hello-world-0‘,1,‘2015-10-11 08:08:08‘,1,10),(‘hello world 111‘,‘hello-world-1‘,1,‘2015-10-11 08:08:08‘,2,10),(‘hello world 222‘,‘hello-world-2‘,2,‘2015-10-11 08:08:08‘,3,10),(‘hello world 333‘,‘hello-world-3‘,3,‘2015-10-11 08:08:08‘,4,10),(‘hello world 444‘,‘hello-world-4‘,3,‘2015-10-11 08:08:08‘,5,10);

首先關於事務的隔離等級

http://my.oschina.net/xinxingegeya/blog/215419

http://my.oschina.net/xinxingegeya/blog/296513

還有鎖的分類，粒度和策略

http://my.oschina.net/xinxingegeya/blog/215417

MySQL的多版本控制MVCC

http://my.oschina.net/xinxingegeya/blog/208821

RC隔離等級下的鎖

在READ-COMMITTED隔離等級下，行鎖的表現如下，

SessionA

開啟事務

mysql>mysql> SELECT @@global.tx_isolation;+-----------------------+| @@global.tx_isolation |+-----------------------+| READ-COMMITTED        |+-----------------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>

SessionB

開啟事務

mysql> SELECT @@global.tx_isolation;+-----------------------+| @@global.tx_isolation |+-----------------------+| READ-COMMITTED        |+-----------------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>

SessionA

在Session A中更新id ＝ 1 的紀錄，如下，

mysql> update record set title = ‘session a update‘ where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0

更新成功了，接下來在Session B中更新同一個id ＝ 1的紀錄，

Session B

mysql> update record set title = ‘session b update‘ where id = 1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到由於Session A還沒有提交，SessionA持有id ＝ 1 的紀錄的行鎖，所以當Session B更新時沒有相應的行鎖，所以鎖等待逾時更新失敗。同時也可以看到在當前的事務下可以更新其他的紀錄。

mysql> update record set title = ‘session b update‘ where id = 1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update record set title = ‘session b update‘ where id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

更新id ＝ 2 的紀錄成功了。

但要注意的是，我們通過id欄位進行更新的，通過id欄位選擇要更新的資料行，同時id欄位是一個主鍵列，如果在沒有索引的欄位上尋找更新會有怎麼樣的效果呢？我們來看一下。

Session AA

開啟事務，

mysql> begin;Query OK, 0 rows affected (0.00 sec)

Session BB

開啟事務，

mysql> begin;Query OK, 0 rows affected (0.00 sec)

Sessoin AA

mysql> update record set title = ‘session a update‘ where authorId = 1;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0

更新成功。接下來在Session BB中更新authorId ＝ 1 的資料行。按照上面說的情況，authorId列上沒有索引，這樣會導致鎖表，但實際的效果是怎麼樣的呢？

Session BB

mysql> update record set title = ‘session c update‘ where authorId = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update record set title = ‘session c update‘ where authorId = 1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到更新authorId ＝ 2 的紀錄沒有等待鎖，也就是其他資料行沒有被鎖住，而更新authorId ＝ 1 的資料行時卻發現鎖等待逾時（Session AA事務還沒有提交）。

我們前面也說了，當更新非索引列時會把整個表鎖住，這是怎麼回事？

這時因為當通過authorId更新時，mysql儲存引擎不知道要鎖定哪些資料行，因為authorId上沒有索引，所以返回整個表的資料行，同時鎖住。然後mysql伺服器層進行過濾，同時解鎖不合格資料行（調用儲存引擎的unlock操作）。

最後提交 Session AA的事務，

mysql> commit;Query OK, 0 rows affected (0.00 sec)

然後在Session BB中執行更新，提交，

mysql> update record set title = ‘session c update‘ where authorId = 1;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)

RR隔離等級下的鎖

我們來看一下RR隔離等級下的鎖，首先我們把authorId列上加上索引。

Session A

開啟事務

mysql> begin;Query OK, 0 rows affected (0.00 sec)

Session B

開啟事務

mysql> begin;Query OK, 0 rows affected (0.00 sec)

Session A

執行以下更新語句

mysql> select * from record;+----+------------------+---------------+----------+---------------------+-------+-----------+| id | title            | shortName     | authorId | createTime          | state | totalView |+----+------------------+---------------+----------+---------------------+-------+-----------+|  1 | hello world 000  | hello-world-0 |        1 | 2015-10-11 08:08:08 |     1 |        10 ||  2 | hello world 111  | hello-world-1 |        1 | 2015-10-11 08:08:08 |     2 |        10 ||  3 | hello world 222  | hello-world-2 |        2 | 2015-10-11 08:08:08 |     3 |        10 ||  4 | hello world 333  | hello-world-3 |        3 | 2015-10-11 08:08:08 |     4 |        10 ||  5 | hello world 444  | hello-world-4 |        3 | 2015-10-11 08:08:08 |     5 |        10 ||  6 | session a update | hello-world-0 |        4 | 2015-10-11 08:08:08 |     1 |        10 ||  7 | hello world 666  | hello-world-0 |        5 | 2015-10-11 08:08:08 |     1 |        10 ||  8 | hello world 666  | hello-world-0 |        6 | 2015-10-11 08:08:08 |     1 |        10 |+----+------------------+---------------+----------+---------------------+-------+-----------+8 rows in set (0.00 sec)mysql> update record set title = ‘session a update‘ where authorId = 4;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0

Session B

執行以下插入語句，

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values (‘hello world 666‘,‘hello-world-0‘,4,‘2015-10-11 08:08:08‘,6,10);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到鎖等待逾時，看一下是等待什麼鎖，

> select * from INNODB_LOCKS******************** 1. row *********************    lock_id: 11604:64:4:11lock_trx_id: 11604  lock_mode: X,GAP  lock_type: RECORD lock_table: `test`.`record` lock_index: idx_author_id lock_space: 64  lock_page: 4   lock_rec: 11  lock_data: 5, 7******************** 2. row *********************    lock_id: 11603:64:4:11lock_trx_id: 11603  lock_mode: X,GAP  lock_type: RECORD lock_table: `test`.`record` lock_index: idx_author_id lock_space: 64  lock_page: 4   lock_rec: 11  lock_data: 5, 72 rows in set

可以看到lock_mode項是 X,GAP。X表示獨佔鎖定，GAP間隙鎖。

再比如，

mysql> insert into record (title,shortName,authorId,createTime,state,totalView)  values (‘hello world 666‘,‘hello-world-0‘,3,‘2015-10-11 08:08:08‘,6,10);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

等待的鎖，

> select * from INNODB_LOCKS******************** 1. row *********************    lock_id: 11604:64:4:10lock_trx_id: 11604  lock_mode: X,GAP  lock_type: RECORD lock_table: `test`.`record` lock_index: idx_author_id lock_space: 64  lock_page: 4   lock_rec: 10  lock_data: 4, 6******************** 2. row *********************    lock_id: 11603:64:4:10lock_trx_id: 11603  lock_mode: X  lock_type: RECORD lock_table: `test`.`record` lock_index: idx_author_id lock_space: 64  lock_page: 4   lock_rec: 10  lock_data: 4, 62 rows in set

這兩個還是稍微有些差別的。

注意：

記錄鎖：是加在索引記錄上的。

間隙鎖：對索引記錄間的範圍加鎖，加在最後一個索引記錄的前面或者後面

Next-key鎖：記錄鎖和間隙鎖的組合，間隙鎖鎖定記錄鎖之前的範圍

間隙鎖主要是防止幻象讀，用在Repeated-Read（簡稱RR）隔離等級下。在Read-Commited（簡稱RC）下，一般沒有間隙鎖（有外鍵情況下例外，此處不考慮）。間隙鎖還用於statement based replication

間隙鎖有些副作用，如果要關閉，一是將會話隔離等級改到RC下，或者開啟 innodb_locks_unsafe_for_binlog（預設是OFF）。

間隙鎖只會出現在輔助索引上，唯一索引和主鍵索引是沒有間隙鎖。間隙鎖（無論是S還是X）只會阻塞insert操作。

＝＝＝＝＝＝＝＝＝END＝＝＝＝＝＝＝＝＝

MySQL交易隔離等級和鎖