MySQL的在RC和RR模式下的鎖

InnoDB的鎖機(jī)制：

10年積累的成都網(wǎng)站制作、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)經(jīng)驗(yàn)，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先做網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有邵陽免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

數(shù)據(jù)庫使用所是為了支持更好的并發(fā)，提供數(shù)據(jù)的完整性和一致性。InnoDB是一個(gè)支持鎖的存儲(chǔ)引擎，鎖的類型有：共享鎖（S）、排它鎖（X）、意向共享鎖（IS）、意向排它鎖（IX）。為了支持更好的并發(fā)，InnoDB提供了非鎖定讀：不需要等待訪問行上的鎖釋放，讀取行的一個(gè)快照。該方法是通過InnoDB的一個(gè)特寫：MVCC實(shí)現(xiàn)的。

InnoDB的鎖分類：

Record Lock：行鎖：單個(gè)行記錄上的行鎖
Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身
Next-Key Lock：Gap+Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身

無索引+RC/RR

當(dāng)對無索引的字段進(jìn)行更新時(shí)（RR級(jí)別），通過鎖主鍵的方式，來鎖住所有記錄，RC級(jí)別不會(huì)鎖所有記錄。

構(gòu)建表及初始化數(shù)據(jù)：

MySQL?-uroot?-p
USE?test;
DROP?TABLE?IF?EXISTS?t_none;
CREATE?TABLE?`t_none`?(
??`id`?int(11)?NOT?NULL,
??`mem_id`?int(11)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB;
INSERT?INTO?t_none?VALUES(1,1),(3,3),(5,5),(9,9),(11,11);

REPEATABLE-READ（RR）默認(rèn)級(jí)別

Session?A

Session?B

root@localhost[zjkj]:10:53:18>prompt A>>

PROMPT set to 'A>>'

A>>select @@session.tx_isolation;

root@localhost[(none)]:11:02:58>prompt B>>

PROMPT set to 'B>>'

B>>select @@session.tx_isolation;

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_none;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

B>>select * from t_none;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

A>>?select * from t_none where mem_id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.01 sec)

B>>insert into t_none values(2,2);

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

B>>delete from t_none where id=9;

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

show engin inondb status部分輸出：

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

TRANSACTIONS

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

Trx id counter 10661

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

History list length 351

Total number of lock structs in row lock hash table 2

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 10588, not started

MySQL thread id 4, OS thread handle 0x7f6f5085c700, query id 339 localhost root init

show engine innodb status

---TRANSACTION 10660, ACTIVE 17 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)

MySQL thread id 11, OS thread handle 0x7f6f508de700, query id 338 localhost root update

insert into t_none values(2,2)

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

RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `test`.`t_none` trx id 10660 lock_mode X locksgap?before rec insert intention waiting

結(jié)論：通過上面很容易的看到，沒有通過索引for?update時(shí)，當(dāng)進(jìn)行增刪改都會(huì)鎖住，MySQL內(nèi)部會(huì)通過基于鎖默認(rèn)主鍵方式，對所有記錄加X鎖。

下面是RC級(jí)別的實(shí)驗(yàn)

Read?Committed級(jí)別（RC）

Session?A

Session?B

A>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.00 sec)

B>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.00 sec)

A>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.00 sec)

B>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.01 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_none where mem_id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.01 sec)

B>>insert into t_none values(2,2);

Query OK, 1 row affected (0.01 sec)

B>>select * from t_none;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?2 | ?????2 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

6 rows in set (0.00 sec

A>>rollback;

Query OK, 0 rows affected (0.00 sec)

B>>rollback;

Query OK, 0 rows affected (0.00 sec)

結(jié)論：在RC級(jí)別下，事務(wù)B是可以進(jìn)行增刪改（除被鎖定的記錄本身）

非唯一索引+RR/RC

? 在RR級(jí)別下，InnoDB對于非唯一索引會(huì)加Gap Lock（也即鎖定一個(gè)區(qū)間），而在RC級(jí)別下無。

構(gòu)造初始化表及數(shù)據(jù)：

mysql?-uroot?-p
USE?test;
DROP?TABLE?IF?EXISTS?t_idx;
CREATE?TABLE?`t_idx`?(
??`id`?int(11)?NOT?NULL,
??`mem_id`?int(11)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
???KEY?`idx_mem_id`?(`mem_id`)
)?ENGINE=InnoDB;
INSERT?INTO?t_idx?VALUES(1,1),(3,3),(5,5),(9,9),(11,11);

REPEATABLE-READ（RR）默認(rèn)級(jí)別（RR模式）

Session?A

Session?B

root@localhost[(none)]:06:01:59>use test;

root@localhost[zjkj]:10:53:18>prompt A>>

PROMPT set to 'A>>'

root@localhost[(none)]:06:01:59>use test;

root@localhost[(none)]:11:02:58>prompt B>>

PROMPT set to 'B>>'

A>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| REPEATABLE-READ ???????|

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

1 row in set (0.00 sec)

B>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| REPEATABLE-READ ???????|

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

1 row in set (0.02 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * fromt_idx;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.04 sec)

B>>select * fromt_idx;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

A>>select * from t_idx where mem_id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.05 sec)

B>>insert into t_idx values(2,2);

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

#問題？這里為什么會(huì)出現(xiàn)阻塞呢？

B>>insert into t_idx values(4,4);

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

#問題？這里為什么會(huì)出現(xiàn)阻塞呢？

B>>insert into t_idx values(3,3);

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

B>>insert into t_idx values(5,5);

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

B>>insert into t_idx values(1,1);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

#######下面插入全部可以######

B>>insert into t_idx values(6,6);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_idx values(7,7);

B>>insert into t_idx values(8,8);

Query OK, 1 row affected (0.01 sec)

B>>insert into t_idx values(12,12);

Query OK, 1 row affected (0.00 sec)

B>>select * from t_idx;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?6 | ?????6 |

| ?7 | ?????7 |

| ?8 | ?????8 |

| ?9 | ?????9 |

| 11 | ????11 |

| 12 | ????12 |

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

9 rows in set (0.00 sec)

show engine?inondb status部分輸出：

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

TRANSACTIONS

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

Trx id counter 11044

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

History list length 372

Total number of lock structs in row lock hash table 5

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 3, OS thread handle 0x7fd0430df700, query id 47 localhost root init

show engine innodb status

---TRANSACTION 11039, ACTIVE 228 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4

MySQL thread id 1, OS thread handle 0x7fd064099700, query id 45 localhost root update

insert into t_idx values(4,4)

Trx read view will not see trx with id >= 11040, sees < 11038

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

RECORD LOCKS space id 70 page no 4 n bits 80 index `idx_mem_id` of table `test`.`t_idx` trx id 11039 lock_mode X locksgap?before rec insert intention waitin

結(jié)論：通過上面可以看到，通過非唯一索引字段進(jìn)行更新時(shí)，在進(jìn)行增刪改時(shí)，有的記錄會(huì)出現(xiàn)阻塞，為什么會(huì)出現(xiàn)阻塞呢？其實(shí)就是用到了MySQL的間隙鎖。那MySQL這里為什么要用間隙鎖呢？目的主要是防止幻讀。?那為什么有的記錄可以插入有的不可以，因?yàn)镮nnoDB對于行的查詢時(shí)采用了Next-Key Lock的算法，鎖定的是一個(gè)范圍（GAP）如下：（∞,1]，(1,3]，(3,5]，(5,9]，(9,11]，(11,?∞)。InnoDB對輔助索引下一個(gè)鍵值也要加上Gap Lock，例如上面進(jìn)行插入2、4、1、3、5時(shí)，就可以看出，其實(shí)鎖住的區(qū)間是(1,5)。

Read?Committed級(jí)別（RC）

Session?A

Session?B

A>>rollback;

Query OK, 0 rows affected (0.00 sec)

B>>rollback;

Query OK, 0 rows affected (0.00 sec)

A>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.00 sec)

B>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.00 sec)

A>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.00 sec)

B>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.01 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_idx where mem_id=3 for update;

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

| id | mem_id |

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

| ?1 | ?????3 |

| ?3 | ?????3 |

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

2 rows in set (0.00 sec)

B>>insert into t_idx values(1,1);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

B>>insert into t_idx values(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_idx values(3,3);

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

B>>insert into t_idx values(4,4);

Query OK, 1 row affected (0.01 sec)

結(jié)論：在RC級(jí)別下，事務(wù)B是可以進(jìn)行增刪改（除被鎖定的記錄本身），沒有出現(xiàn)間隙鎖的現(xiàn)象。

唯一索引+RR/RC

構(gòu)造初始化表及數(shù)據(jù)：

mysql?-uroot?–p
use?test;
DROP?TABLE?IF?EXISTS?t_pk;
CREATE?TABLE?`t_pk`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`mem_id`?int(11)?NOT?NULL?,
??PRIMARY?KEY?(`id`),
??UNIQUE??`uq_mem_id`?(`mem_id`)
)?ENGINE=InnoDB;
INSERT?INTO?t_pk?VALUES(1,1),(3,3),(5,5),(9,9),(11,11);

REPEATABLE READ（RR級(jí)別）

root@localhost[(none)]:10:04:34>use test;

root@localhost[test]:10:04:41>prompt A>>

PROMPT set to 'A>>'

root@localhost[(none)]:10:04:37>use test;

root@localhost[test]:10:04:52>prompt B>>

PROMPT set to 'B>>'

A>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| REPEATABLE-READ ???????|

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

1 row in set (0.01 sec)

B>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| REPEATABLE-READ ???????|

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

1 row in set (0.00 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_pk;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

B>>select * from t_pk;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

A>>select * from t_pk where mem_id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.00 sec)

B>>insert into t_pk values(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(4,4);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(3,3);

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

B>>insert into t_pk values(5,5);

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

B>>insert into t_pk values(7,7);

Query OK, 1 row affected (0.00 sec)

結(jié)論：從這里可以看到，對于基于唯一索引的更新，MySQL只是鎖定了記錄本身。

同理，我們可以推導(dǎo)出主鍵也是一樣的。實(shí)驗(yàn)的話我就略了，其實(shí)就是將上面的mem_id改成id即可。

基于主鍵的Record Lock，還是RR級(jí)別

A>>rollback;

Query OK, 0 rows affected (0.00 sec)

B>>rollback;

Query OK, 0 rows affected (0.00 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_pk where id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.00 sec)

B>>insert into t_pk values(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(4,4);

Query OK, 1 row affected (0.00 sec)

結(jié)論：說明上面的推導(dǎo)正確。

Read-Committed級(jí)別（RC）

A>>rollback;

Query OK, 0 rows affected (0.00 sec)

B>>rollback;

Query OK, 0 rows affected (0.00 sec)

A>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.01 sec)

B>>set @@session.tx_isolation="read-committed";

Query OK, 0 rows affected (0.00 sec)

A>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.00 sec)

B>>select @@session.tx_isolation;

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

| @@session.tx_isolation |

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

| READ-COMMITTED ????????|

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

1 row in set (0.00 sec)

A>>begin;

Query OK, 0 rows affected (0.00 sec)

B>>begin;

Query OK, 0 rows affected (0.00 sec)

A>>select * from t_pk;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

B>>select * from t_pk;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.00 sec)

A>>select * from t_pk where mem_id=3 for update;

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

| id | mem_id |

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

| ?3 | ?????3 |

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

1 row in set (0.00 sec)

B>>insert into t_pk values(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(4,4),(6,6),(10,10);

Query OK, 3 rows affected (0.00 sec)

Records: 3 ?Duplicates: 0 ?Warnings: 0

結(jié)論:說明RC級(jí)別下，沒有間隙鎖存在。

主鍵+RR/RC

這跟唯一索引+RR/RC是一樣的，請參看上面的唯一索引+RR/RC。

分享文章：MySQL的在RC和RR模式下的鎖
路徑分享：http://www.rwnh.cn/article16/gcgedg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站改版、外貿(mào)網(wǎng)站建設(shè)、營銷型網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)公司

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

中文字幕日韩精品一区二区免费_精品一区二区三区国产精品无卡在_国精品无码专区一区二区三区_国产αv三级中文在线

MySQL的在RC和RR模式下的鎖