当关闭连接池需要太多时间时，如何强制关闭连接池中的连接？ [英] How do I forcefully close a connection from a connection pool when it's taking too much time to close?

问题描述

有时关闭连接需要花费大量时间，例如超过10分钟到1小时，或者更糟，甚至是无限期，具体取决于查询的重量或速度。

There are times closing connections takes a lot of time, like more than 10 minutes upto 1 hour, or worse, even for indefinite time, depending on how heavy or slow the query was.

在客户取消查询的情况下，因为它花费了太多时间，我想尽快释放使用的基础连接。

In a situation where the client cancels the query because it has been taking too much time, I would want to free up the underlying connection used as soon as possible.

我尝试取消PreparedStatement，关闭它，然后关闭结果集，然后最后关闭连接。几乎立即取消。关闭PreparedStatement和ResultSet需要花费太多时间，我必须将它包装在Callable with timeout中，以便在适当的时候跳过该进程并继续关闭连接本身。我还没有什么好运可以尝试。

I tried cancelling the PreparedStatement, closing it, then closing the resultset, and then finally closing the connection. Cancelling took almost instantly. Closing the PreparedStatement and ResultSet took too much time that I had to wrap it in a Callable with timeout to skip that process in due time and proceed with closing the connection itself. I haven't got any much luck on what else to try out.

我该如何处理？我不能简单地让连接不公开，我不能让用户等待10分钟才能进行另一次类似的查询。

How do I deal with this? I can't simply let the connections unclosed and I can't let the users wait for 10 minutes before they can make another similar query.

此外，导致关闭的原因是什么连接需要花费太多时间？还有什么我能做的吗？您认为Oracle查询提示会有所帮助吗？

Also, what's causing the closure of connection to take too much time? Is there anything else I could do? Do you think Oracle query hints would help?

顺便说一句，我通过瘦型驱动程序使用Oracle JDBC。

I'm using Oracle JDBC via thin type of driver by the way.

更新：

显然，可以通过在connectionCacheProperties中配置TimeToLive属性来强制关闭连接，该属性会在特定时间内关闭连接。但是，我需要的是根据需要。这值得一提，因为这证明可以像连接池那样强制关闭它。事实上，我甚至在我的日志上收到以下消息。

Apparently, it's possible to close the connection forcefully by configuring TimeToLive property in the connectionCacheProperties which closes the connection for a specific amount of time. However, what I need is on as-needed basis. This is worth mentioning because this proves that it is possible to forcefully close it as the Connection Pool just did. In fact, I even got the following message on my logs.

ORA-01013: user requested cancel..

推荐答案

这是我们在POW中使用的，而不是授予更改系统$ UID作为SYS拥有的过程（简化工作版本）：

This is what we use at my POW instead of "grant alter system to $UID" as a SYS-owned procedure (simplified working version):

CREATE OR REPLACE procedure SYS.kill_session(in_sid varchar2)
as
  l_serial number;
  l_runsql varchar2(1000) := 'alter system kill session ''$1,$2'' immediate';
begin
  begin
  select serial# into l_serial from v$session where username =
  (
    SELECT USER FROM DUAL
  ) and sid=in_sid and rownum<=1;
  exception when no_data_found then
    raise_application_error( -20001, 'Kill candidate not found');
  end;
  l_runsql := replace( l_runsql, '$1', in_sid);
  l_runsql := replace( l_runsql, '$2', l_serial);
  execute immediate l_runsql;
end;
/

这样你只能杀死你自己的会话。

This way you can only kill your own sessions.

这篇关于当关闭连接池需要太多时间时，如何强制关闭连接池中的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！