带有锁定的Postgresql的一致性，并选择进行更新 [英] Consistency in postgresql with locking and select for update

问题描述

我有一个可以支持一定数量的并发操作的应用程序。这由postgres中的插槽表表示。当节点联机时，它们会在表中插入许多行，每个插槽一个。当作业声明插槽时，它们会更新表中声明其中一个插槽的行，并在完成时再次释放它。

I have an application that can support a certain number of concurrent actions. This is represented by a table of "slots" in postgres. When nodes come online, they insert a number of rows into the table, one per slot. As jobs claim the slots, they update a row in the table claiming one of the slots and release it again as they finish.

插槽表如下所示：

CREATE TABLE slots (
    id INT8 PRIMARY KEY DEFAULT nextval('slots_seq'),
    node_name TEXT NOT NULL,
    job_name TEXT
);

在任何时候，它都有一些半固定的行，每个行可能有也可能没有

At any time it has some semi-fixed number of rows each of which may or may not have a job_name filled in.

要启动新作业时，它将运行以下查询以获取应在其上运行的节点的名称：

When a new job wants to start up, it runs these queries to get the name of the node it should run on:

BEGIN;
LOCK TABLE slots IN ACCESS EXCLUSIVE MODE;
SELECT id, node_name
    FROM slots
    WHERE job_name IS NULL
    LIMIT 1
    FOR UPDATE;

（从光标中读取出node_name和id）

(the node_name and id are read out of the cursor)

UPDATE slots
    SET job_name = %(job_name)s
    WHERE id = %(slot_id)s;
COMMIT;

这通常能够声明行而不会丢失任何更新，但是并发级别更高，只有少数几个在执行许多SELECT ... FOR UPDATE和UPDATE查询时，将声明行。最终结果是，我们所运行的作业数量远远超过了它们的可用插槽。

This is often able to claim rows without losing any updates but with higher levels of concurrency, only a few rows will be claimed while many SELECT ... FOR UPDATE and UPDATE queries have been executed. The net result is that we end up with far more jobs running than there are slots for them.

我犯了一个锁定错误吗？有没有更好的方法来解决这个问题？有没有使用表锁的东西？

Am I making a locking error? Is there a better way to go about this? Something that doesn't use table locks?

事务级别SERIALIZABLE不会削减它，只能填充少量行。

Transaction level SERIALIZABLE does not cut it, only a handful of rows are ever filled.

使用postgresql版本8.4。

I'm using postgresql version 8.4.

推荐答案

好吧，我用perl编写了一个程序来模拟正在发生的事情，因为我没有想到你所说的是可能的。确实，运行模拟后，即使关闭锁定，我也没有任何问题（因为 SELECT…FOR UPDATE 和 UPDATE 应该执行必要的锁定操作。）

Well, I wrote a program in perl to simulate what was going on since I didn't think that what you were saying was possible. Indeed after running my simulation I didn't have any problems even when I turned locking off (since SELECT … FOR UPDATE and UPDATE should do the necessary locking).

我在PG 8.3和PG 9.0上运行了该命令，并且在两个位置上都可以正常工作。

I ran this on PG 8.3 and PG 9.0 and it worked fine on both locations.

我敦促您尝试该程序和/或尝试一个python版本，使其具有一个很好的严格测试用例，您可以与该类共享该用例。如果有效，您可以调查差异之处，如果无效，则可以让其他人玩。

I urge you to try the program and/or try a python version to have a nice tight test-case which you can share with the class. If it does work, you can investigate what the differences are and if it doesn't work, you have something that other people can play with.

#!/usr/bin/perl
use DBI;
$numchild = 0;
$SIG{CHLD} = sub { if (wait) {$numchild--;} };

sub worker($)
{
  my ($i) = @_;
  my ($job);

  my $dbh = DBI->connect("dbi:Pg:host=localhost",undef,undef,{'RaiseError'=>0, 'AutoCommit'=>0});

  my ($x) = 0;
  while(++$x)
  {
#    $dbh->do("lock table slots in access exclusive mode;") || die "Cannot lock at $i\n";
    my @id = $dbh->selectrow_array("select id from slots where job_name is NULL LIMIT 1 FOR UPDATE;");

    if ($#id < 0)
    {
      $dbh->rollback;
      sleep(.5);
      next;
    }
    $job = "$$-$i-($x)";
    $dbh->do("update slots set job_name='$job' where id=$id[0];") || die "Cannot update at $i\n";
    $dbh->commit || die "Cannot commit\n";
    last;
  }
  if (!$job)
  {
    print STDERR "Could not find slots in 5 attempts for $i $$\n" if ($ENV{'verbose'});
    return;
  }
  else
  {
    print STDERR "Got $job\n" if ($ENV{'verbose'} > 1);
  }
  sleep(rand(5));

#  $dbh->do("lock table slots in access exclusive mode;") || die "Cannot lock at $i\n";
  $dbh->do("update slots set usage=usage+1, job_name = NULL where job_name='$job';") || die "Cannot unlock $job";
  print STDERR "Unlocked $job\n" if ($ENV{'verbose'} > 2);
  $dbh->commit || die "Cannot commit";
}

my $dbh = DBI->connect("dbi:Pg:host=localhost",undef,undef,{'RaiseError'=>0, 'AutoCommit'=>0});

$dbh->do("drop table slots;");
$dbh->commit;
$dbh->do("create table slots (id serial primary key, job_name text, usage int);") || die "Cannot create\n";
$dbh->do("insert into slots values (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0), (DEFAULT,NULL,0);") || die "Cannot insert";
$dbh->commit;

for(my $i=0;$i<200;$i++)
{
  if (!fork)
  {
    worker($i);
    exit(0);
  }

  if (++$numchild > 50)
  {
    sleep(1);
  }
}
while (wait > 0)
{
  $numchild--;
  print "Waiting numchild $numchild\n";
  sleep(1);
}
my $dbh = DBI->connect("dbi:Pg:host=localhost",undef,undef,{'RaiseError'=>0, 'AutoCommit'=>0});
my $slots = $dbh->selectall_arrayref("select * from slots;") || die "Cannot do final select";
my $sum=0;
foreach my $slot (@$slots)
{
  printf("%02d %3d %s\n",$slot->[0], $slot->[2], $slot->[1]);
  $sum += $slot->[2];
}
print "Successfully made $sum entries\n";

这篇关于带有锁定的Postgresql的一致性，并选择进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！