Postgres 9.3中SELECT FOR UPDATE的配置问题

Question

我在这个问题上挣扎了两天。我们有一个解决方案，其中多个工作线程将尝试从单个数据库/表中选择作业请求，方法是在所选请求上设置标志，从而有效地阻止其他工作线程选择相同的请求。

我创建了一个java测试应用程序来测试我的查询，但在正常情况下，测试执行没有问题，在高争用的情况下（例如。1个表条目有50个线程；没有延迟或处理），我仍然有线程获得相同的请求/条目，有趣的是在测试刚刚开始时发生。我不明白为什么。我已经阅读了所有相关的Postgres锁定和隔离相关留档...虽然问题可能与测试应用程序本身有关，但我怀疑我忽略了SELECT FOR UPDATE在READ COMMITTED隔离上下文中的工作方式。

所以问题是，SELECT FOR UPDATE（使用读取提交隔离）能否保证像我描述的一般并发问题能够安全解决？

获取查询：

UPDATE mytable SET status = 'LOCK'
  WHERE ctid IN (SELECT ctid FROM mytable
    WHERE status = 'FREE'
    ORDER BY id
    LIMIT %d
    FOR UPDATE)
  RETURNING id, status;

发布查询：

UPDATE mytable SET status = 'FREE'
  WHERE id = %d AND status = 'LOCK'
  RETURNING id, status;

那么，你会认为这两个查询应该是安全的，还是有可能允许两个线程获取同一行的一些奇怪的情况？我想提到的是，我也尝试了序列化隔离，但没有起到任何作用。

Answer 1

我想分享的另一个方面是关于限制为2的Acquire查询的速度。见测试结果：

Starting test...
DB setup done
All threads created & connections made
All threads started
Thread[36] 186/190/624=1000
Thread[19] 184/201/615=1000
Thread[12] 230/211/559=1000
Thread[46] 175/200/625=1000
Thread[ 9] 205/211/584=1000
...
Thread[ 4] 189/232/579=1000
Thread[ 3] 185/198/617=1000
Thread[49] 218/204/578=1000
Thread[ 1] 204/203/593=1000
...
Thread[37] 177/163/660=1000
Thread[31] 168/199/633=1000
Thread[18] 174/187/639=1000
Thread[42] 178/229/593=1000
Thread[29] 201/229/570=1000
...
Thread[10] 203/198/599=1000
Thread[25] 215/210/575=1000
Thread[27] 248/191/561=1000
...
Thread[17] 311/192/497=1000
Thread[ 8] 365/198/437=1000
Thread[15] 389/176/435=1000
All threads finished
Execution time: 31408
Test done; exiting

将上述内容与此查询进行比较：

UPDATE mytable SET status = 'LOCK'
  WHERE id IN (SELECT t1.id FROM (SELECT id FROM mytable 
      WHERE status = 'FREE' ORDER BY id LIMIT 2) AS t1
    FOR UPDATE)
  RETURNING id, status;

结果是：

Starting test...
DB setup done
All threads created & connections made
All threads started
Thread[29] 32/121/847=1000
Thread[22] 61/151/788=1000
Thread[46] 36/114/850=1000
Thread[41] 57/132/811=1000
Thread[24] 49/146/805=1000
Thread[13] 47/135/818=1000
...
Thread[20] 48/118/834=1000
Thread[47] 65/152/783=1000
Thread[18] 51/146/803=1000
Thread[ 8] 69/158/773=1000
Thread[14] 56/158/786=1000
Thread[ 0] 66/161/773=1000
Thread[38] 60/148/792=1000
Thread[27] 69/158/773=1000
...
Thread[45] 78/177/745=1000
Thread[30] 96/162/742=1000
...
Thread[32] 162/167/671=1000
Thread[17] 329/156/515=1000
Thread[33] 337/178/485=1000
Thread[37] 381/172/447=1000
All threads finished
Execution time: 15490
Test done; exiting

测试为每个线程打印Acquire查询返回2、1或0资源的次数，总计测试循环数（1000）。

从上面的结果可以得出结论，我们可以以增加线程争用为代价来加快查询速度（将时间减半！）。这意味着我们将从Acquire查询接收到更多倍于0的资源。从技术上讲，这不是一个问题，因为我们无论如何都需要处理这种情况。

当然，如果在没有返回资源的情况下添加等待时间（Hibernate），情况会发生变化，但选择正确的等待时间值取决于应用程序性能要求。。。

Answer 2

结果证明（怎么会不一样？）我在考试中犯了一个错误。我没有遵守资源获取/发布命令。测试是在发布查询之后注册发布（减少计数器），这会导致另一个线程获取资源并同时注册它。类别中的错误，您知道如何解决，但即使查看多次也无法看到，因为您编写了代码。。。同行评议最终起到了作用。

我想现在我有一个测试来证明：

1. 以上两个查询都是安全的
2. 您不需要SERIALIZABLE隔离来解决DB获取/释放的问题，只要您使用行锁定，如SELECT...为更新
3. 使用行锁定时，您必须ORDER（BY）结果（即使您使用LIMIT 1！），否则您最终会出现死锁
4. 通过一个查询（LIMIT 2及以上）安全地获取多个资源
5. 在查询中使用ctid是安全的；它实际上有点快，但这在现实世界中并不重要

我不确定这对其他人是否有帮助，但我已经绝望了。因此，在Postgres 9.3中一切都很好：）