Save not committing when using pool

[boscharun]

Tried out connetion pool and it worked great for read queries. However, when saving, the queries doesn't seem to commit and hence not able to write anything to db. Not 100% sure its the commit not working, assuming so because there were no errors in the output.

Config -

'default': { 'ENGINE': 'dj_db_conn_pool.backends.oracle', 'NAME': 'xxx', 'USER': 'xxxx', 'PASSWORD': 'xxx', 'CONN_MAX_AGE': 250, # For ATP, the timeout period at db end seen to be 5 mins. 'POOL_OPTIONS': { 'POOL_SIZE': 5, 'MAX_OVERFLOW': 5, 'autocommit': True, }, }

[altairbow]

在获取数据库连接时，django会调用django.db.utils.ConnectionHandler(settings.DATABASES).ensure_defaults(alias)来确保一些未明确指定的参数 ，其中就有 conn.setdefault('AUTOCOMMIT', True)，在django.db.utils模块的第172行，如果未显性指定AUTOCOMMIT参数，那么dj_db_conn_pool.backends.oracle.base.DatabaseWrapper 实例的autocommit就会被赋值为 True，另外如果需要明确指定 autocommit 参数，请设置与ENGINE同级，而不是在POOL_OPTIONS内，如果你不太明白我的意思，你在django.db.utils模块的第196行设置断点，跑一遍debug就行了

[boscharun]

Thanks @altairbow ,

Tried it out the proper syntax bu still the same result. Enabled debugging to print out sql queries and I can see the insert queries running but data is not getting stored. I doubt now if autocommit is the culprit or something else.. My setup- Python3.6 + django 2.2.1 + Oracle 12C

'default': {

'ENGINE': 'django.db.backends.oracle',

    'ENGINE': 'dj_db_conn_pool.backends.oracle',
    'NAME': '***',
    'USER': '***',
    'PASSWORD': '***',
    'AUTOCOMMIT': True,
    'POOL_OPTIONS': {
        'POOL_SIZE': 10,
        'MAX_OVERFLOW': 10
    }
}

[altairbow]

很抱歉，眼下我不能重现你的问题，我可以给你提供一个思路： 在 django.db.transaction（django 2.1.5的第177行）模块中找到 Atomic.exit 方法的第一行 connection = get_connection(self.using)，在此处设置断点，debug你的服务，然后做一次事务操作（create、delete、update），看看是否能够执行到（django 2.1.5的第212行）connection.commit()

如果能够执行到这一步，说明django的autocommit有效，下一步进入connection.commit()看看问题出现在哪里...

[altairbow]

No feedback, Close.

[kevin-kessler]

Dear @boscharun, @altairbow, did you eventually find a solution for this issue?

I'm facing the same problem with this setup: Python 3.9, Django 3.2.7, Oracle 19

[kevin-kessler]

So it looks like the Oracle connections created with SQLAlchemy (which django-db-connection-pool is based on) have their autocommit flag set to 0 by default.

A quick test of setting the flag to 1 after the connection has been returned by the pool confirmed the assumption and Django started persisting my changes all of a sudden again.

I fixed it by implementing _set_autocommit in oracle/base.py and submitted a pullrequest here: https://github.com/altairbow/django-db-connection-pool/pull/35