Transaction rollback best practices when using connection pooling

[urosdigital]

Hello,

What would be the best practice when executing insert/update/delete statements with connection pooling?
Lets say i have a table with only one column created as VARCHAR2(20).
When executing the following statement in sql developer

insert into mytable (column_name) values ('1231111111111111111111111111111');

i get the following message:

insert into mytable (column_name) values ('1231111111111111111111111111111')
Error report -
ORA-12899: value too large for column "USER"."MYTABLE"."COLUMN_NAME" (actual: 31, maximum: 20)

When i run the following code

try:
    oracledb.init_oracle_client()
    pool = oracledb.create_pool(user=test_db_config.user, password=d, port=test_db_config.port,
                                      host=test_db_config.host,service_name=test_db_config.service_name,
                                      min=pool_min, max=pool_max, increment=pool_inc)
    
        with pool.acquire() as connection:
            with connection.cursor() as cursor:
                cursor.execute("insert into mytable (column_name) values ('123')")
        print('First connection closed')
        with pool.acquire() as connection:
            with connection.cursor() as cursor:
                cursor.execute("insert into mytable (column_name) values ('1231111111111111111111111111111')")
       print('Second connection closed')

    pool.close()

except oracledb.Error as e:
#except oracledb.DatabaseError as e:
        connection.rollback()
        print('Transaction rollback')
        error, = e.args
        print(f'Error code: {error.code}\nError full code: {error.full_code}\nError message: {error.message}')

except Exception as e:
        print(f'ERROR!:\n{e}')

i get the message displayed below(says connection has already been closed):

Traceback (most recent call last):
  File "test_db_connection_pool.py", line 198, in <module>
    connection.rollback()
  File "my_venv_path/lib64/python3.6/site-packages/oracledb/connection.py", line 646, in rollback
    self._verify_connected()
  File "my_venv_path/lib64/python3.6/site-packages/oracledb/connection.py", line 196, in _verify_connected
    errors._raise_err(errors.ERR_NOT_CONNECTED)
  File "my_venv_path/lib64/python3.6/site-packages/oracledb/errors.py", line 111, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.InterfaceError: DPY-1001: not connected to database

If i remove

connection.rollback()
print('Transaction rollback')

i can catch the exception:

Error code: 12899
Error full code: ORA-12899
Error message: ORA-12899: value too large for column "RATOR"."BELEGIC_TEST"."PHONE_NUMBER" (actual: 31, maximum: 20)

I would like to make sure, in case any of these insert statements fails, the changes wont be committed and the rollback is performed. Is this possible?

BR,
Uros

[cjbj]

• This isn't specific to pooling; the behavior would be the same with standalone connections
• the DPY-1001 is expected because the context manager (with) has closed the connection by the time the exception handler is invoked
• looking at the various python examples out there most get the resource (i.e. the connection in this case) before calling try.

[urosdigital]

Hi @cjbj

Understood. I see it now, thanks. One more additional question, i tried to use context manger when creating a pool and got error. Maybe i did something wrong but i would like to know is it allowed to use context manager in this case:

insted using:

pool = oracledb.create_pool(user=test_db_config.user, password=d, port=test_db_config.port,
                                  host=test_db_config.host,service_name=test_db_config.service_name,
                                  min=pool_min, max=pool_max, increment=pool_inc)

is this possible to use:

with oracledb.create_pool(user=test_db_config.user, password=d, port=test_db_config.port,
                                  host=test_db_config.host,service_name=test_db_config.service_name,
                                  min=pool_min, max=pool_max, increment=pool_inc) as pool:
try:
    with pool.acquire() as connection:
        with connection.cursor() as cursor:
            cursor.execute("insert into mytable (column_name) values ('123')")

except oracledb.Error as e:
#except oracledb.DatabaseError as e:
        connection.rollback()
        print('Transaction rollback')
        error, = e.args
        print(f'Error code: {error.code}\nError full code: {error.full_code}\nError message: {error.message}')

except Exception as e:
        print(f'ERROR!:\n{e}')

The idea is to avoid explicit close of pool -> pool.close()

[cjbj]

The Pool class doesn't support this, though you might be able to subclass Pool (along the lines of this) and add your own __enter__ and __exit__ methods.

However, since a pool is generally created at application startup, and it may need to be accessible from multiple modules, I wouldn't use a with context manager block. I also wouldn't worry particularly about explicitly shutting the pool at the end of a long running app since python-oracledb will do that for you, and presumably you are not closing apps frequently.

[urosdigital]

Thanks @cjbj
All clear now.

BR,
Uros