postgres 如何锁住一条不存在的记录？

[crclz]

锁住一条不存在的记录 postgres

假如某条记录是事务提交的前提条件，那么一般做法就是显式加锁（其他数据库）或者加 For Share 或者 For Update (postgresql).

在某些情况下，例如用户注册时，可以通过对用户名加唯一索引，来防止用户名重复，并通过 On Conflict Do Nothing Returning user_id，并且判断返回值是否为 null 来判断是否插入成功，进而给用户相应的反馈。

但是，在某些情况下，这种方法没用。例如：假如 user1 和 user2 之间不存在好友关系，那么，user1 可以向 user2 发送好友请求。我需要先确保（锁住） user1 和 user2 的好友关系不存在，然后才能插入好友请求。

mysql.innodb 可以实现，因为根据官方文档，innodb 的锁是加在索引上的。只要我在好友关系表建一个唯一索引，那么 Select For Share 将会锁住对应的索引值，尽管记录并不存在。

但是，postgres 不具备这个特性。经过测试，也不能锁住。经过仔细阅读 postgres 官方文档"Explicit Locking"节，均未发现有实现 innodb 类似功能的做法。

丑陋的解决方案

postgres 的 advisory lock

Advisory lock 以 1 个 int64(bigint)或者 2 个 int32(integer)作为 Identifier. 两个线程，获取同 Identifer 的锁，其中一个就会阻塞并等待另一个释放锁。而这个 Identifier 是库级的，并且由用户自己设计它的值。

比如我想锁住 user1(id=6), user(id=8)的朋友关系记录，我就要加一个 Identifier 为 01 006 008 的锁（空格是为了更好看）。006、008 是两个用户的 id，很好理解。开头的 01 是业务号，因为全库公用同一个 Identifier 空间。

这个方案很丑陋。一个 bigint 我不知道如何划分给业务号、id 号，并且未来有溢出的可能。 并且，假如有多个 id，那么会更快的溢出。 并且，如果某个键是字符串类型，那么将无从设计 Identifier.

模拟对某行的一个访问锁

再开一个表 FriendshipFlag，假如我想要锁住、新增、更新 Friendship （管它存在不存在） ，我只需在 FriendshipFlag 中插入(user1_id,user2_id)的记录，再进行操作，并在事务结束后删除这条记录。这相当于 advisory lock 的扩展。但是哪个神经病会这样做呢？

用业务逻辑保证一致性

免谈。 冗余设计是很需要的。如果谁说业务逻辑能保证这个一致性，有种生产环境别加各种约束。

[zhengwhizz]

unique(user1,user2) 不就行了

[crclz]

@zhengwhizz 看清楚再答题

[lovelife1994]

是只对好友关系表这一张表做不存在(user1,user2)的关系即插入吗？那用唯一索引感觉就够了。

[crclz]

@lovelife1994 假如不存在好友关系（ Friendships 表），才能发送好友请求（ FriendRequests 表），是两个表。

[tabris17]

@zhengwhizz 老实说，我也不知道楼主想要表达什么玩意儿。为什么偏不加唯一约束，然后等 insert 返回 duplicate key 来结束事务

[chinvo]

@crclz 正确做法不是 FriendRequests 是 Friendships 的一个状态么

[crclz]

@chinvo FriendRequest 字段 senderId, receiverId, message, isHandled, isExpired... 。Friendship 字段 userId, hisId, 好友备注, isBlocked .... 合在一起不合适吧。

[lovelife1994]

@crclz 可以考虑用 redis 这类的分布式锁吗？和你第二种方案类似。或者通过索引范围锁的方式，导致插入时更新索引失败，sql server 的 serializable 隔离级别用的是这种方式（最坏的情况会回退到锁表），不知道 PG 支不支持。

[chinvo]

@crclz Handled、Expired、Blocked 都应该是状态的一部分，这样就可以利用工作流或者自己实现类似的逻辑来处理了

至于 message，则可以利用站内信系统，或者保留这个栏位（但仅用一次），或和备注之类的合用一栏（总之可以通过合理设计数据库及业务逻辑来处理这个问题）

[crclz]

@tabris17 就是我想要往 B 表插数据，前提是 A 表中不存在符合条件 p 的数据。现在开始事务，然后检测到 A 表里面不存在符合条件 p 的数据，然后在事务即将向 B 表插入数据的时候，有人往 A 表插入了符合条件 p 的数据。求如何防止这人往 A 表里面插符合条件 p(一般是唯一索引)数据。(postgres)。

[crclz]

@chinvo 但你不觉得这样不太优雅么？明明用 mysql 的特性（刚刚去查了 sql server 的微软文档，发现 sql server 也支持同样的特性）可以优雅的设计逻辑清晰的表结构，而轮到 postgres 就不行了。

[chinvo]

@crclz 个人感官问题，我反而认为这种顺序的状态应该使用状态位和工作流来处理，只有并行状态（同时存在两个或以上有效状态）才使用多个列（或者使用 bit state ）

[crclz]

@lovelife1994 这个也考虑过，但是多增加一个 redis 会大幅度增加开发复杂度，并且你有没有想过 redis 和 postgres 已经构成了一个分布式系统了，保证一致性必须付出额外的开发量。假如在提交 postgres 事务前向 redis 发出的（释放锁的）请求失败了，如何保证这个锁被释放？那又得增加开发量（比如 redis 锁一段时间自动过期），并且退化到了最终一致性。
postgres 的 serializable 我今天读了无数遍官方的文档、wiki(ssi, serializable)，也做了实验，发现不能解决我提到的应用场景。去网上（外网，内网）搜发现基本没有 serializable 的资料，更何况 postgres 的 serializable。我最后想，postgres 的 serializable 有点坑，还是不入好了。

[tabris17]

@crclz 无论是 INSERT 表 A 或是表 B，都用 LOCK TABLE 同时锁住表 A 和表 B。

老实说，我宁愿在业务上允许一些“额外”数据也不会选择去加锁

[lovelife1994]

@crclz redis 做分布式锁确实不是很完美的方案，我们现在的业务一般不倾向在 DB 上加锁，仅仅把它当做一个可靠的存储，而是把隔离做在了应用层，通过 redis 和 zk 之类的。至于 serializable，不同家的 DB 应该差距挺大的，我们用的 sql server 好像是可以的。还是看业务和系统的规模吧，规模不大的话你的第二种方案就可以了，我们之前没有用 redis 的时候也是这样做的。

[tabris17]

@crclz 如果你的逻辑相当于

if row not exists in table A then
insert row into table B

那么可以写成这样：

INSERT INTO B
(left_user_id, right_user_id)
SELECT 6,8 from A WHERE NOT EXISTS
(SELECT 1 FROM A WHERE left_user_id=6 AND right_user_id=8);

[crclz]

@tabris17 对，我也想过这种方法。这种方法本质上和网上的“mysql 乐观锁”很像。网上的“mysql 乐观锁”和这段代码都有一个特性，就是：在只有 if..then 这个逻辑的时候很好用，但是语句再多一个，就不行了：比如 if p then insert xxx and update yyy. 这时候就不行了。

[zhengwhizz]

抛开其它场景不谈，对于好友关系这种场景，你和我就两个人，从检测关系不存在到发请求之间产生了好友关系的概率有多大？即使产生了，多一个请求也无妨吧？对方操作时提示下就好了，有必要这样钻么。就像
@tabris17 说的，业务上'额外'总比锁住表不给其它人并发操作关系好吧。

[lenmore]

pg 这个设计感觉像 bug
insert 后不提交，在别的事务里 update 或 select for update 不阻塞，但是 insert 却可以阻塞，这是什么逻辑啊。

[reus]

不用加锁，就默认的 read committed 下，在事务最后看有没有好友关系，如果有，就 rollback。

其实单表实现是最好的，用两个字段分别表示互相的态度（想要建立关系，确认建立关系，拉黑），因为各种态度是互斥的，所以可以这样做。这样可以很容易表示出“a 想加 b 为好友，但 b 已经拉黑了 a”这种状态。像你那样设计，遇到这种情况，你除了要锁好友表，还要锁拉黑表吧？一张表，就完全没有这些问题。至于时间和文本，完全可以合并使用一些字段。例如申请加好友的信息，和对好友的备注，可以用同一个字段。既然叫做 relationship，那就用这个表表示关系的变化，完全可行。