postgresql 锁兼容矩阵 mysql锁的兼容性

文章目录

• InnoDB锁

• 行锁算法
• Next-Key Lock

• 锁实验

• 准备数据
• 等值查询间隙锁
• 非唯一索引等值锁
• 非唯一索引等值锁for Update
• 主键索引范围锁
• 非唯一索引范围锁

InnoDB锁

1. InnoDB实现了两种标准的行级锁

• 共享锁(S Lock),允许事务读一行数据
• 排他锁(X Lock),允许事务删除或更新一行数据

2. 如果一个事务T1已经获取了行R的共享锁，那么另外一个事务T2可以立即获得行R的共享锁，因为读取并没有改变行R的数据，称这种情况为锁兼容，如果有其他的事务T3想获得行R的排他锁，则必须等待事务T1、T2释放行R上的共享锁（称锁不兼容）。从下图可以看出只有S与S锁兼容，X和S都是行锁，兼容是指对同一个记录锁的兼容情况。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

1. 意向锁：意向锁是一种不与行级锁冲突表级锁

• 意向锁共享锁（IS Lock），事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

• 意向排他锁（IX Lock），事务有意向对表中的某些行加排他锁。

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

行锁算法

1. MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。InnoDB有3种行锁的算法，分别是

• Record Lock:单个行记录上的锁
• Gap Lock:间隙锁，锁定一个范围，但不包含记录（是索引）本身
• Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录（是索引）本身

2. Record Lock总是会锁定索引记录，如果在建表时没有建立任何索引，此时InnoDB引擎会使用隐式的主键来进行锁定
3. Next-Key Lock是结合了 Gap Lock和Record Lock的一种锁定算法（行锁和间隙锁的组合），在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。利用这种锁定技术锁定的不是单个值，而是一个范围（前开后闭区间）。当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）
4. **在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放,这个就是两阶段锁协议。**如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
5. 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁
6. 如果你要删除一个表里面的前10000行数据

• 执行delete from T limit 10000;
• 在一个连接中循环执行20次 delete from T limit 500
• 在20个连接中同时执行delete from T limit 500
• 第一种方式单个事务的执行时间太长，锁的时间长，大事务会导致主从延迟。第三种方式会造成锁冲突，如果能先获取到所有的id，然后分段，使用第三种方式也可以。

Next-Key Lock

1. 如果事务T1已经通过next-key locking锁定了如下范围

(10,11]、(11,13]

当插入新的记录12时，则锁定的范围会变成

(10,11]、(11,12]、(12,13]

1. 当查询的索引为唯一索引时，存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围，从而提高应用的并发性。

DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

BEGIN;
INSERT INTO `t_test` VALUES (1);
INSERT INTO `t_test` VALUES (2);
INSERT INTO `t_test` VALUES (4);
COMMIT;

时间	Session1	Session2
	BEGIN;
	select * from t_test where id=4 for update;
		BEGIN;
		INSERT INTO t_test VALUES (3); 成功执行，没有阻塞
		COMMIT;
	COMMIT;

表t_test的a列建立了唯一索引，有1、2、4三个值。在Session1种对id=1进行了X锁定。因为id是唯一索引，所以锁定的不是(2,4)这个范围，而是2,这样在Session2种插入3不会阻塞，可以立即插入并返回，即降级为Record Lock

锁实验

1. MySql只有在RR的隔离级别下才有gap lock和next-key lock
2. 加锁规则

• 规则1:加锁的基本单位是next-key lock(前开后闭区间)
• 规则2:查找过程中访问到的对象才会加锁
• 优化1:索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为Record Lock（行锁）
• 优化2:索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为Gap Lock（间隙锁）

3. 默认情况下，InnoDB工作在RR级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。以下所有的实验都是在RR级别下。

准备数据

1. 建表

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);

1. 数据

+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+

等值查询间隙锁

1. 等值查询间隙锁
2. 分析

• 由于表t种没有id=7的记录，所以根据规则1，加锁单位是next-key lock，Session1加锁范围就是(5,10]，因为id=7是一个等值查询，根据优化2，id=10不满足条件，next-key lock退化成Gap Lock，因此最终加锁的范围是(5,10)
• Session2要向这个间隙里插入id=8的记录是必须等到Session1的事务提交以后才可以
• Session3修改id=10，这个不在加锁范围，所以不会阻塞

非唯一索引等值锁

1. 非唯一索引等值锁
2. 分析

• Session1给索引c上的c=5加上读锁，根据规则1，加锁单位为next-key lock，因为c是普通索引，所以访问c=5后，还需要向右遍历，一直到c=10停止，根据原则2，访问到的都要加锁，所以加锁范围是(5,10]。根据优化2，等值查询，退化为Gap Lock，因此最终加锁范围(5,10)
• Session2要向这个间隙里插入id=7的记录是必须等到Session1的事务提交以后才可以，因为session 1的间隙锁范围是(5,10)
• 根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。所以Session3的语句可以执行成功，不会阻塞

3. 在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update。执行 for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
4. 如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将session A的查询语句改成select d from t where c=5 lock in share mode。

非唯一索引等值锁for Update

1. 新的数据准备

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `t` VALUES (2, 1);
INSERT INTO `t` VALUES (3, 3);
INSERT INTO `t` VALUES (4, 5);
INSERT INTO `t` VALUES (5, 8);
INSERT INTO `t` VALUES (6, 11);
COMMIT;

1. 因为表t在a列有索引，所以索引可能被锁住的范围为

(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)

1. 非唯一索引等值锁for update
2. Session1执行后会锁住索引行的范围为

(5, 8], (8, 11]

即锁住了8所在的范围，还锁住了下一个范围，即Next-Key。所以插入12和4都不会阻塞，这个很好理解，但是为什么11不会阻塞，5却阻塞了？似乎与预期并不符合（预期是**(5,11]**之间所有的都会阻塞才对，而5应该不会阻塞才对）

1. 先来看看插入5为什么会阻塞，如下图所示，在索引a上的等值查询（a=8）,给索引a加上了next-key(5, 8], (8, 11],Session1语句的for update会给聚集索引(id=8)加上行锁（黄色显示部分）.

Session2插入5的图，因为索引是有序的，并且非聚集索引的叶子节点中的数据是顺序存放的，所以对于聚集索引来说，行锁只锁住（id=5，a=8）行索引，所以插入是没问题的，但是对于非聚集索引来说（a=5，id=4）之后是无法插入数据（a=5，id=7）的，因为锁的范围是**( (a=5,id=4), (a=8,id=5) ], ( (a=8,id=5), (a=11,id=6) ]**，（a=5，id=7）在锁的范围里，即无法插入，也就是说只要(a=5,id>4)都是无法插入的.

Session2插入11的图，因为索引是有序的，所以对于聚集索引来说，行锁只锁住（id=5，a=8）行索引，所以插入是没问题的，对于非聚集索引来说（a=11，id=6）之后是插入数据（a=11，id=7）也是没问题的，因为（a=11，id=7）不在锁的范围**( (a=5,id=4), (a=8,id=5) ], ( (a=8,id=5), (a=11,id=6) ]****里,即(a=11,id>6)都是可以插入的

6. 这里有一个扩展，如果执行以下SQL会不会阻塞呢？

insert into t(id,a) values(1,5);

其实是不会阻塞的，根据上面的分析，索引是有序的，(a=5,id=1)不在(a=5,id>4)范围，所以不会阻塞

主键索引范围锁

1. 对于以下两条sql，加锁范围不一样，第一条是id=10的行锁，第二条是id=10的行锁和(10,15]的next-key lock

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

1. 主键索引范围锁
2. 分析

• Session1根据规则1，加锁单位为next-key lock，因为是id=10等值查询，所以退化为行锁，id<11范围查找继续找，一直到id=15停止，因此next-key lock(10,15]。最终Session1在主键索引的加锁范围行锁id=10和next-key lock(10,15]
• Session2和Session3的分析与上面的一样

非唯一索引范围锁

1. 非唯一索引范围锁
2. 分析：Session1索引c上的(5,10] 和(10,15] 这两个next-key lock