Resource closed error when trying to insert the record in table randomly

[Abhishek Sharma]

One of my project is built around Django rest framework and Sqlalchemy as ORM.

This application is thread based so randomly we are seeing one of thread transaction showing connection closed error.

Randomly when my application is trying to insert the record in table we are seeing exception the resource is closed, Below is stack trace:

"./modules/managers/contracts/contracts_manager.py", line 745, in submit_fix_contract#012 deal_amn_contract_activity_id = self.save_contract_activity_data(deal_contract_id,fix_request,response.text, 'Submitted', 'Contract Submitted')#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/dtplatform/common/transaction.py", line 123, in inner#012 self.session.commit()#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 813, in commit#012 self.transaction.commit()#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 392, in commit#012 self._prepare_impl()#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl#012 self.session.flush()#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2027, in flush#012 self._flush(objects)#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2145, in _flush#012 transaction.rollback(_capture_exception=True)#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 63, in __exit__#012 compat.reraise(type_, value, traceback)#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2145, in _flush#012 transaction.rollback(_capture_exception=True)#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 408, in rollback#012 self._assert_active(prepared_ok=True, rollback_ok=True)#012 File "/opt/www/dt_deal_products_api/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 223, in _assert_active#012 raise sa_exc.ResourceClosedError(closed_msg)#012ResourceClosedError: This transaction is closed

So my application is closing the connection after every commit or rollback so as per my understanding after connection close that connection will be return to pool for further use then why sqlalchemy is saying connection close in next transaction.

Is this error due to alchemy connection pool returning expired connection when requesting due to application is ideal for certain amount of time or connection is expiring at transaction time.

[Mike Bayer]

in a threaded application, where the behavior is non-deterministic, this error is typically due to sharing a Connection, Session or ORM mapped object across threads in some way; the transaction was ended in some thread while another tries to continue to work with it.    It also might be possible to get this error due to exception handling within a rollback that tries to work with the connection after the transaction has already ended.      I can't provide much more information than that without a reproducing example, unfortunately, however please also share the database driver you're using as that is often related to this kind of thing.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/51595dbe-5574-41ff-8fb7-05b17c9abbee%40googlegroups.com.

[Abhishek Sharma]

Thanks Mike for your reply.

We are using cx Oracle as driver

[Mike Bayer]

On Thu, Aug 15, 2019, at 12:25 PM, Abhishek Sharma wrote:

Thanks Mike for your reply.

We are using cx Oracle as driver

OK actually this error is very specific to the ORM session, it's from trying to a work witha transaction that's already done.   It can be reproduced in many ways, such as:

from sqlalchemy.orm import Session

s = Session()

with s.transaction:

    s.commit()

or with autocommit:

from sqlalchemy.orm import Session

s = Session(autocommit=True)

with s.begin():

    s.commit()

or without a context manager:

s = Session()

trans = s.transaction

s.commit()

trans.commit()

or other permutations of that pattern.  you're getting the exception on a flush, but the Session is in an invalid state with an operation like those above.

this is why it's easy for this issue to be due to multithreading.  but it could just be a weird commit/rollback pattern.    need to see the code.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8bf92caa-aa5c-405c-9c3b-9e51ea7a9935%40googlegroups.com.

[Abhishek Sharma]

Thanks Mike for your response.

It's more likely due to multi threading because this error is coming very randomly, If this was due to permutations you mentioned then it should happen every time which is not the case here.

Any recommendations for handling sessions in multithreading?

[Mike Bayer]

mostly it depends on the kind of application you are working with and what kinds of containment are present.  For example, a web application has a certain set of practices, then an application that runs database logic in a Celery task queue has a set of practices, an application that runs as a cron job has a set of practices, etc.

The general idea is of course "share nothing" between threads but specifics matter, so, still need to know lots of specifics like all those things in order to have a useful answer.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f01cad62-c4cf-4674-b677-71f9702ddca6%40googlegroups.com.

[Jonathan Vanasco]

some situations that may be screwing up the connection pool like this include (and are not limited to):

* your application may not be properly closing, resetting or returning a connection when there is an exception; 

* multiple threads/processes are using the same connection because of how it was obtained/shared/returned

when dealing with this stuff in pyramid and twisted, i did a lot of debug logging in Python and the Database where I was just concerned with looking at the ids of a thread/process and the db connection at certain points in the code /and/ when the exception was raised.  doing that you can usually look backwards in the logs from the exception and see where that database connection was obtained and what it's history was.  with django, i'd pay attention to the request start, request end, and whenever there is an exception.