鍍金池/ 問答/數(shù)據(jù)庫/ 關(guān)于next-key鎖，gap鎖區(qū)間的疑問

關(guān)于next-key鎖，gap鎖區(qū)間的疑問

表user結(jié)構(gòu)如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `score` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_score` (`score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

當(dāng)前的user表數(shù)據(jù)如下：

id name score
1 test1 5
2 test2 10
3 test3 15
4 test4 22

我測試gap鎖區(qū)間的sql如下：

事務(wù)隔離級別為RR
session 1 session 2
begin; begin;
update user set name = 'test' where score = 15; -
- insert into user values(5, 'test5', 10); # 阻塞
- insert into user values(5, 'test5', 21); # 阻塞
commit; -
- commit;
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

14.6.4.2 InnoDB Record, Gap, and Next-Key Locks

按照上面的說明，gap鎖的區(qū)間應(yīng)該是(10, 15],(15, 22]，也就是(10, 22]，但是我實(shí)際測試發(fā)現(xiàn)gap鎖區(qū)間為[10, 22)？

環(huán)境說明：

System: ubuntu 16.04
Mysql Version: 5.5.56
回答
編輯回答
青黛色

=.=.

事物分開，你是21的時(shí)候阻塞了。應(yīng)該是鎖（10,22）

begin;
-- 插入score value為10~21的時(shí)候會阻塞
insert into user values(5, 'test5', 10);

2018年1月14日 20:25