How to reduce the timeout for lost network connections ? #545
-
|
Maybe this is a bug report... but maybe I don't understand how the connection general TCP timeout and ping functionality is designed to work. I have a python oracledb client version 3.4.0 that connects to Oracle DB 23.9.0.0. If the VPN connection is lost, it takes 22 minutes for the I tried various pool settings... but it alwasy takes 22 minutes for an operation It looks like the pool tries it's best to reconnect ... as if the connection is re-established before those 22 minutes, the operation is automatically recovered. If the connection is not available at the start of the operation, The code is here # VPN connection availabe when pool is created.
pool = oracledb.create_pool(
user=username,
password=password,
host=address,
port=port,
service_name=service_name,
min=2,
max=5,
increment=1,
homogeneous=True, # All connections use the same credentials
tcp_connect_timeout=2,
retry_count=2,
retry_delay=3,
timeout=10,
wait_timeout=6 * 1000, # milliseconds
max_lifetime_session=0, # seconds, unlimited
# How often to check for dead connections.
ping_interval=16, # seconds
ping_timeout=5 * 1000, # milliseconds
)
# VPN connection is still available and first query works.
query = "SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'"
result = []
with pool.acquire() as connection:
with connection.cursor() as cursor:
for row in cursor.execute(query):
result.append(row)
# VPN connection is lost here.
query = "SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'"
result = []
with pool.acquire() as connection:
with connection.cursor() as cursor:
for row in cursor.execute(query):
result.append(row)
the traceback is here... it blocks at Looking at the code, I think that the issue is here.... ping will set the timeout...but then will disable it. python-oracledb/src/oracledb/impl/thin/pool.pyx Lines 585 to 589 in 4472770 Thanks for your help! |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 3 replies
-
|
For now, I have implemented this helper... but with multi-thread there is still the posibility of the ping operation to be called after def _getConnection(self):
"""
Get a database connection from the pool.
"""
connection = self._pool.acquire()
connection.call_timeout = _TCP_TIMEOUT * 1000
return connection |
Beta Was this translation helpful? Give feedback.
-
|
@adiroiban, your traceback shows that the error took place while calling execute() -- and specifically during the handling of the socket.timeout exception. This is well after the connection has been acquired from the pool and any pings have taken place, if thus configured. So I'm not sure why you are thinking that the ping() performed during the acquire() from the pool is at fault? Unless you are complaining that the call timeout value is reset back to 0? Your "fix" of setting the value after the acquire() returns is fine -- no pings are going to take place after that unless you call them yourself! The internal ping only takes place before the connection is returned by the pool. Once the connection is returned by the pool the only things done by that connectiion are the things that you do with it! |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the feedback. I was complaining that the timeout is set back to 0 :) So basically, if you want care about TCP timeouts, and you use the I was expecting that the But I am happy to reset the timeout after each Thanks again! It's was very helfpul. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @adiroiban. The |
Beta Was this translation helpful? Give feedback.
@adiroiban, your traceback shows that the error took place while calling execute() -- and specifically during the handling of the socket.timeout exception. This is well after the connection has been acquired from the pool and any pings have taken place, if thus configured. So I'm not sure why you are thinking that the ping() performed during the acquire() from the pool is at fault? Unless you are complaining that the call timeout value is reset back to 0? Your "fix" of setting the value after the acquire() returns is fine -- no pings are going to take place after that unless you call them yourself! The internal ping only takes place before the connection is returned by the pool. Once the …