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 的扩展。但是哪个神经病会这样做呢？

用业务逻辑保证一致性

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

文章内容乍一看不像是提问，像是一篇文章。其实我是想问一下有没有人知道 postgres 中锁住不存在记录的范式（一般方法）？

今天又搜索了一波资料。

## a
https:/🐎/wiki.postgresql.🐎org/wiki/SQL_MERGE
PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature. （注：2010 的内容，到现在为止无证据显示内容无效）

## b
https:/🐎/wiki.postgresql.🐎org/wiki/Value_locking
这篇文章讲述了 postgres 实现 value locking 从而实现 upsert （ on conflict ...）操作。
但仅此而已，只提到了 upsert。

## c
https:/🐎/rosscoded.🐎com/blog/2018/05/02/locking-phantom-postgresql/
Locking on rows or values that don't exist in PostgreSQL
这篇文章的作者也在纠结类似的问题，在文中他说经过搜索资料，发现 postgres 不提供锁住不存在的记录的方法。他找到的方法：on conflict、advisory lock 也在我的前文中提到了。他还说即使是 postgres 的 serializable 级别也无法防止。这和我昨天的实验得出的结论一样。
所以顺便提醒一下各位，postgres 的 Serializable 隔离级别有坑。postgres 文档中的 To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first.也是具有迷惑性的。另外经过实验，发现 postgres 的 serializable 级别的“假阳性反应”的概率不低，当然场景不同这个是非常难说的。

[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，那就用这个表表示关系的变化，完全可行。

[reus]

状态其实两种就够了，好友和拉黑
没有关系时，没有记录
如果一方发起申请，那插入一条 (friend, null)，如果另一方同意，就变成 (friend, friend)
这样如果一方发起申请，另一方也同时发起申请，那结果也是 (friend, friend)
只有 (friend, friend) 这种状态，才是好友状态
任何一方都可以解除好友状态，变成 (friend, null) 或者 (null, friend) 或者 (null, null)，这些都不是好友状态了
拉黑就是将 friend 改成 block，变成 (friend, block) 或者 (block, friend) 或者 (block, block)
这样就完全成了单表单行的操作，不存在锁的问题了
这样还能增加其他状态，例如好友之上的特别关注等等

[xuanbg]

实在不理解为何要对不存在的数据加锁。怕同时写入不应该在方法上加锁吗？

[zunceng]

建立好友关系 这种操作 做成幂等的就可以了


可以用 insert ... on duplicate
也可以用 transaction 先查一下是否存在好友关系 如果是直接返回成功 如果否 建立好友关系

[crclz]

@xuanbg 假如我有多个方法都会存在好友关系的操作，假如这些方法是不同语言写的，最终还是得回到数据库的锁上面。

[PopRain]

第一次听说数据库锁可以加在不存在的记录上，而且，你记录不存在，索引肯定也没有这个项，怎么会加锁？ 难道自动升级为表锁，或者页面锁，不是行级锁？ 其它锁数据大了肯定有性能问题

楼主能不能给个具体的链接，谢谢！

[xuanbg]

@crclz 分布式锁可以帮到你呢，亲！

[crclz]

@PopRain

innodb
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
Gap Locks
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a **single** index value, multiple index values, or even be empty. （注意 single ）

Microsoft SQL Server
https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017#singleton-fetch-of-nonexistent-data
对不存在的数据的单独提取
如果事务中的查询试图选择不存在的行，则以后在相同的事务中发出这一查询时，必须返回相同的结果。 不允许其他事务插入不存在的行。 例如，对于下面的查询：
SELECT name
FROM mytable
WHERE name = 'Bill';
键范围锁放置在与从 Ben 到 Bing 的名称范围对应的索引项上，因为名称 Bill 将插入到这两个相邻的索引项之间。RangeS-S 模式键范围锁放置在索引项 Bing 上。 这样可阻止其他任何事务在索引项 Bill 与 Ben 之间插入值（例如 Bing ）。

[PopRain]

@crclz MySQL GAP 锁 和 sql server 范围锁我知道，但是这两个锁都是用 BETWEEN 去激活使用的吧

如果用 select * from Friendships where field_1='user1' and field2='user2' for share 并不会激活范围锁。

[crclz]

@PopRain 微软文档的 name = 'Bill' 是相等条件吧。
还有我看了一下 innodb 的，确实不是 gap lock，而是 Record lock.
innodb-locking 文档 （链接在上几个回复中，v2ex 不让发太多外链）
Record Locks
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
注意“inserting”这个单词，基本可以确定可以锁住不存在的行。

[PopRain]

@crclz

你说的例子就是记录存在才会防止 insert 呀，这个就是标准的行级锁，如果不存在 c1=10 ,那你可以随便插入 insert c1=10。

SQL SERVER: （就是你给的链接）
必须满足下列条件才能发生键范围锁定：
事务隔离级别必须设置为 SERIALIZABLE。
查询处理器必须使用索引来实现范围筛选谓词。 例如，SELECT 语句中的 WHERE 子句可以用以下谓词建立范围条件：ColumnX BETWEEN N ' AAA ' AND N ' CZZ ' 。 仅当 ColumnX 被索引键覆盖时，才能获取键范围锁 。

[crclz]

@PopRain SQL SERVER 的我看了一下确实要求 serializable 级别，之前没看到。但是 mysql 的那部分我觉得是语义上你没理解清楚。或者你可以去做一下实验。

[crclz]

@PopRain 刚刚测试了一下 mysql，是有效的。
c#运行 Select * From Users Where Username='bzzb' For Share 然后遇到断点，挂起
然后 workbench 里面 Insert Into karroo.Users Values(1,'bzzb','a','a',1,2)
然后发现 Insert 的事务阻塞了

[PopRain]

@crclz Users 表里面有没有 Username='bzzb'这个记录？ 然后，看看锁类型,是否是 record 锁， 谢谢
（今天有点事情，顾不上测试）

[passerbytiny]

我没用过 postgresql，我不是 DBA。

从业务逻辑上看，增加好友处理是一个幂等处理（或者 PUT 请求），你不管触发多少次处理，结果都是有且只有一条好友请求。所以只需要在好友请求表（不是好友关系表）一个 unique(user1,user2) 就行了，1 楼就是正确答案。

@crclz “假如 user1 和 user2 之间不存在好友关系，user1 可以向 user2 发送好友请求”这虽然是错误的业务逻辑，但仍然是业务逻辑，而不是数据逻辑。因此，这首先要选择业务逻辑来保证一致性，数据库手段是次要并且可选的。而如果从业务逻辑先入手，很容易的就会发现这是错误的逻辑，而不会像你现在这样去钻牛角尖。

[EmdeBoas]

这个问题跟隔离等级并没有什么关系，各个数据库对不同隔离级别到底能避免哪些 phenomenon 表现并不一致，合乎 ANSI 的标准即可。
对于”不存在的数据“上锁，只是一些 lock-based 的数据库为了避免 Phantom 这样实现的罢了，ANSI 标准本身并没有规定到底怎么样避免 phantom..
另外 gap 锁这个东西相当不好控制，很容易掉坑里造成大面积的事务死锁...
这个场景本身就带很强的业务属性，为什么不可以依赖业务逻辑来保障呢？

[crclz]

@PopRain 测试过程没问题，但是我错了，应该不是 record-lock. 而是 next-key lock. Innodb ReadCommited 下 For Share 不能防止插入，说明不是 record-lock （ mysql 没怎么用过所以也没有去监视测试过程中是啥锁。） RepeatableRead 下，可以防止插入，刚刚去看一下文档，发现是 next-key lock.
”等于“的查询条件可以触发 next-key lock(index record lock + gap lock)，但是粒度较大，绝对不止一个值的粒度，(如 @EmdeBoas 所说）会不好控制。。由于 mysql 的 gap lock 不好控制，所以 postgres 的 gap lock 功能缺失看起来还非常能接受...所以还是决定多点考虑业务逻辑上的控制吧。
也谢谢你 @PopRain 让我做了进一步的测试，不然很多错误的认知又会伴随着我以后的开发，那带来的错误是无法想象的。

[sujin190]

这样数据一致性和业务一致性都用数据库锁来保证这难道不是又是一个大坑？
如果好友申请时好友关系的一个状态，那么用数据库事务和唯一索引来保证不重复这没啥问题，但是既然你想分开好友申请和好友关系，那么这个业务一致性的要求，更应该用外部锁来保证业务流程准确是不是更好？数据库就应该简单高效准确的完成数据管理的问题，其他的都应该交给外部业务系统才是

关于用 redis 锁的问题，如果你业务量大，这不算啥吧，如果本来就没几个人用，这还想个啥子，这种同时发两个请求的极低概率问题，遇到本来就微乎其微，去他丫的了

[encro]

研究精神可嘉，
但是时间上如果只能给你 2 小时，你会怎么实现？
别再浪费公司资源了（几千万会员情况除外）。

如果是我，肯定为 request，ship 加唯一，然后程序上发请求和同意的时候都判断下即可，几分钟就 OK 的事情（甚至不会考虑判断，因为唯一索引已经为我判断了，这个概率太小，即使出现也不是什么问题）