Closed atsu0127 closed 1 year ago
"SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL"
for connections acquired from the pool. Update: Instead of calling dbms_session.sleep(), just do a sleep in Python between closing and acquiring a connection.Thank you for confirming.
I set max=1
and confirmed the change of SID through the same procedure. However, even after a longer period than max_lifetime_session
, the SID did not change.
I was planning to use max_lifetime_session
in order to reduce the workload during planned maintenance by regularly renewing DB sessions. In order to resolve this issue with retries, I am considering the following two options:
I will close this issue. Thank you for your support.
I'm going to reopen this since I was also seeing something I wanted to check. Having it open will remind me to look at it.
This is what I was testing:
import os
import time
import oracledb
MLT = 20
SLEEP = 120
un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')
oracledb.init_oracle_client()
sql = "select sid || ' ' || serial# from v$session where sid = sys_context('USERENV','SID')"
#------------------------------------------------------------------------------
def start_pool():
pool_min = 0
pool_max = 1
pool_inc = 0
pool = oracledb.create_pool(user=un, password=pw, dsn=cs,
min=pool_min,
max=pool_max,
increment=pool_inc,
max_lifetime_session=MLT,
session_callback=init_session)
return pool
def init_session(connection, requestedTag_ignored):
with connection.cursor() as cursor:
s, = connection.cursor().execute(sql).fetchone()
print(f"In init_session: SID and SERIAL#: {s}")
#------------------------------------------------------------------------------
pool = start_pool()
with pool.acquire() as connection:
s, = connection.cursor().execute(sql).fetchone()
print(f"In block 1: SID and SERIAL# {s}")
print(f"Sleeping {SLEEP} seconds with max_lifetime_session of {MLT}")
connection.cursor().callproc("dbms_session.sleep", (MLT,))
time.sleep(2)
with pool.acquire() as connection:
s, = connection.cursor().execute(sql).fetchone()
print(f"In block 2: SID and SERIAL# {s}")
with pool.acquire() as connection:
s, = connection.cursor().execute(sql).fetchone()
print(f"In block 3: SID and SERIAL# {s}")
The output is like:
cjones@mdt:~/p$ python3 cp.py
In init_session: SID and SERIAL#: 73 14816
In block 1: SID and SERIAL# 73 14816
Sleeping 120 seconds with max_lifetime_session of 20
In block 2: SID and SERIAL# 73 14816
In init_session: SID and SERIAL#: 73 64501
In block 3: SID and SERIAL# 73 64501
cjones@mdt:~/p$
Showing the session was recreated. However when I played with some times, or set the pool min size to 1, I got different behavior. I suspect there are some heuristics in the implementation.
But fundamentally, trying to reduce the number of sessions to help failover is optimizing for arguably the wrong case. It would generally be better to keep sessions open so normal app access was efficient. If you are worried about the number of sessions, then try DRCP.
This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.
This issue has been automatically closed because it has not been updated for a month.
cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (21, 4, 0, 0, 0)
Since the first
time.sleep
waits for more thanmax_lifetime_session
, I thought that a new session would be created in the subsequentdbms_session.sleep
, but the session that existed when the pool was created was used as is.Also, we do not plan to use DRCP and have not set it up(conn_str is as follows).
db_config = ('', '', '')
pool = cx_Oracle.SessionPool(user=db_config[0], password=db_config[1],
dsn=db_config[2], min=2,
max=5, increment=1, max_lifetime_session=30)
def fetch(sql: str, bind: list[str|int] =[]): with pool.acquire() as connection: with connection.cursor() as cursor: cursor.execute(sql, bind) rows = cursor.fetchall() return rows
def exec(sql: str, bind: list[str|int] =[]): with cx_Oracle.connect(db_config[0], db_config[1], db_config[2]) as connection: with connection.cursor() as cursor: try: cursor.execute(sql, bind) result = connection.commit() return result except Exception as e: print("exec失敗") return e
def dbms_sleep(seconds): with pool.acquire() as conn: cursor = conn.cursor() print("dbms sleep start:", seconds) cursor.callproc("dbms_session.sleep", (seconds,)) print("dbms sleep end:", seconds)
sql_insert_all = """INSERT ALL INTO test_tbl VALUES (1,'u1', 'oracle', 'sake') INTO test_tbl VALUES (2,'u2', 'postgresql', 'beer') INTO test_tbl VALUES (3,'u3', 'oracle', 'wine') INTO test_tbl VALUES (4,'u4', 'postgresql', 'beer') INTO test_tbl VALUES (5,'u5', 'mysql', 'wine') select from dual""" sql_select_all = 'select from test_tbl' sql_delete='DELETE FROM test_tbl'
result = exec(sql_insert_all) if result is not None: print("insert failed: ", result) exit(1)
print("initial data") rows = fetch(sql_select_all) pprint.pprint(rows)
print("start") print("sleeping...")
sleep longer than max_lifetime_session
time.sleep(70) print("sleeping...end")
this session will use a new session
dbms_sleep(10) print("sleeping...") time.sleep(70) print("sleeping...end") dbms_sleep(10)
print("Delete Data") result = exec(sql_delete) if result is not None: print("delete failed: ", result) exit(1)
print("Done")