溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL的在RC和RR模式下的鎖

發(fā)布時(shí)間：2020-07-16 03:48:29 來(lái)源：網(wǎng)絡(luò) 閱讀：706 作者：zjdevops 欄目：MySQL數(shù)據(jù)庫(kù)

InnoDB的鎖機(jī)制：

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

InnoDB的鎖分類(lèi)：

  • Record Lock：行鎖：?jiǎn)蝹€(gè)行記錄上的行鎖

  • Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身

  • Next-Key Lock：Gap+Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身



  • 無(wú)索引+RC/RR

當(dāng)對(duì)無(wú)索引的字段進(jìn)行更新時(shí)（RR級(jí)別），通過(guò)鎖主鍵的方式，來(lái)鎖住所有記錄，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 locks gap?before rec insert intention waiting

結(jié)論：通過(guò)上面很容易的看到，沒(méi)有通過(guò)索引for?update時(shí)，當(dāng)進(jìn)行增刪改都會(huì)鎖住，MySQL內(nèi)部會(huì)通過(guò)基于鎖默認(rèn)主鍵方式，對(duì)所有記錄加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對(duì)于非唯一索引會(huì)加Gap Lock（也即鎖定一個(gè)區(qū)間），而在RC級(jí)別下無(wú)。

構(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 * from t_idx;

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

| id | mem_id |

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

| ?1 | ?????1 |

| ?3 | ?????3 |

| ?5 | ?????5 |

| ?9 | ?????9 |

| 11 | ????11 |

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

5 rows in set (0.04 sec)

B>>select * from t_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

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

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

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

#問(wèn)題？這里為什么會(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 locks gap?before rec insert intention waitin

結(jié)論：通過(guò)上面可以看到，通過(guò)非唯一索引字段進(jìn)行更新時(shí)，在進(jìn)行增刪改時(shí)，有的記錄會(huì)出現(xiàn)阻塞，為什么會(huì)出現(xiàn)阻塞呢？其實(shí)就是用到了MySQL的間隙鎖。那MySQL這里為什么要用間隙鎖呢？目的主要是防止幻讀。?那為什么有的記錄可以插入有的不可以，因?yàn)?/span>InnoDB對(duì)于行的查詢時(shí)采用了Next-Key Lock的算法，鎖定的是一個(gè)范圍（GAP）如下：（∞,1]，(1,3]，(3,5]，(5,9]，(9,11]，(11,?∞)。InnoDB對(duì)輔助索引下一個(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)行增刪改（除被鎖定的記錄本身），沒(méi)有出現(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é)論：從這里可以看到，對(duì)于基于唯一索引的更新，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é)論：說(shuō)明上面的推導(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é)論:說(shuō)明RC級(jí)別下，沒(méi)有間隙鎖存在。

  • 主鍵+RR/RC

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






向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手