如何使用 Perl DBI 在 Oracle 中超时“选择更新”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/966147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to timeout a "select for update" in Oracle using Perl DBI
提问by Olfan
is there an easy way to timeout an SQL statementso that it will fail instead of waiting (e.g. deliver an empty result set or an error message or whatever else) so I can let a job's ressource reservation fail and give another one a chance? I'm looking for some DBI option I've overlooked so far; sending SIGALRMs to myself to commit suicide is rather not what I have in mind (although I might have to resort to that if I had to).
有没有一种简单的方法可以使 SQL 语句超时，使其失败而不是等待（例如，传递空结果集或错误消息或其他任何内容），以便我可以让作业的资源预留失败并给另一个机会？我正在寻找迄今为止我忽略的一些 DBI 选项；向我自己发送 SIGALRM 以自杀并不是我的想法（尽管如果必须的话，我可能不得不求助于它）。
The code snipped is pseudo-ized and shortened to the extreme, but I hope you'll catch the drift.
剪下的代码被伪化并缩短到极致，但我希望您能理解其中的变化。
my $sql = "SELECT one, two, three FROM sometable WHERE this = ? AND that = ?";
my $sth = $self->make_handle( $sql );
eval {
foreach my $this ( sort keys %needed_ressources ) {
# vvv This is where the idle time is spent vvv
$sth->execute( $this, $that ) or die( "DB connection gone?!" );
# ^^^ This is where the idle time is spent ^^^
my ( $one, $two, $three ) = $sth->fetchrow_array();
unless( $one ) { # undefined record set == not found
$self->{DB_HANDLE}->rollback();
die( "$this not defined for $that!" );
}
}
# If we haven't died so far, we can move on
foreach... #similar loop here doing the actual update statement
$self->{DB_HANDLE}->commit();
};
return( 1 ) unless $@;
return( undef );
Here are the gory details for the interested:
以下是感兴趣的血腥细节：
In an application that does massively parallel numbercrunching there is a ressource locking mechanism implemented that uses an oracle table. Each job needs to lock a number of ressources for reading and/or a number of ressources to write to, and can only start if all the locks have been acquired successfully. Instead of patiently waiting for ressources to be freed, jobs should just fail and be re-run later by their master (this keeps the number of open transactions low while boosting performance by having more jobs around that actually crunch).
在执行大规模并行数字处理的应用程序中，实现了使用 oracle 表的资源锁定机制。每个作业需要锁定多个读取资源和/或多个写入资源，并且只有在所有锁都已成功获取后才能启动。与其耐心地等待资源被释放，作业应该只是失败并稍后由它们的主人重新运行（这可以保持打开事务的数量较低，同时通过在实际紧缩周围拥有更多作业来提高性能）。
Of course, before actually updating the table, each row is being reserved using a "SELECT ... FOR UPDATE" statement so Oracle uses row level locking and concurrent transactions can happen on the table. In order to further reduce possible race conditions and deadlocks all jobs first select their ressource rows and then row lock them using the same ordering before performing the update.
当然，在实际更新表之前，每一行都使用“SELECT ... FOR UPDATE”语句保留，因此 Oracle 使用行级锁定，并发事务可以在表上发生。为了进一步减少可能的竞争条件和死锁，所有作业首先选择它们的资源行，然后在执行更新之前使用相同的顺序行锁定它们。
As of the current implementation this works fine in mostof the cases. But, because the "Select for update" blocks until Oracle actually grants the row lock, it may still happen that a job is idle waiting for its ressources, and I'm after those to better utilize the available CPU power. It's OK to wait a second or two, but not ten or more just for the locking. For unlocking later, waiting is required of course, so setting the whole DB connection to only accept immediate results will not work.
从目前的实现来看，这在大多数情况下都可以正常工作。但是，因为在 Oracle 实际授予行锁之前“选择更新”会阻塞，所以可能仍然会发生作业空闲等待其资源的情况，我希望更好地利用可用的 CPU 能力。等待一两秒钟是可以的，但不能仅仅为了锁定而等待十秒钟或更长时间。对于取消后锁定，等待是必需的，当然，这样设置整个数据库连接只接受直接的结果是行不通的。
I'm always grateful for RTFManswers as long as they point to the location in the Mthat I should TFhave R;-))
我总是很感激RTFM 的答案，只要它们指向M中我应该TF有R 的位置；-))
Thanks a lot in advance,
Olfan
非常感谢，
奥尔凡
回答by DCookie
I think you want the NOWAIT parameter on the FOR UPDATE clause. If the record cannot be locked, the select will fail ("ORA-00054: resource busy and acquire with NOWAIT specified") and you can handle the exception however you need to. Check out the SQL Reference manual. It's for 11g, but the syntax hasn't changed for several versions now.
我认为您需要 FOR UPDATE 子句上的 NOWAIT 参数。如果无法锁定记录，则选择将失败（“ORA-00054：资源繁忙并使用指定的 NOWAIT 获取”），您可以根据需要处理异常。查看SQL 参考手册。它适用于 11g，但现在几个版本的语法没有改变。
Another option is to give a time to wait: "FOR UPDATE WAIT 3" to wait 3 seconds for the lock to be acquired rather than failing immediately.
另一种选择是等待时间：“FOR UPDATE WAIT 3”等待 3 秒以获取锁定而不是立即失败。
