Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
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. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a6d1e056-964a-4ac1-a224-c05a9dab05b8%40googlegroups.com.
Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
On Thu, Aug 15, 2019, at 1:09 PM, Abhishek Sharma wrote: > 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? 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f01cad62-c4cf-4674-b677-71f9702ddca6%40googlegroups.com. > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/6592c7b3-864c-4bc7-a6cf-13a139810e33%40www.fastmail.com.
Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
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? -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f01cad62-c4cf-4674-b677-71f9702ddca6%40googlegroups.com.
Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/8bf92caa-aa5c-405c-9c3b-9e51ea7a9935%40googlegroups.com. > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/03fe1b0c-d9f0-488f-a0db-0df7060e7237%40www.fastmail.com.
Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
Thanks Mike for your reply. We are using cx Oracle as driver -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8bf92caa-aa5c-405c-9c3b-9e51ea7a9935%40googlegroups.com.
Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly
On Thu, Aug 15, 2019, at 10:28 AM, Abhishek Sharma wrote: > 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. 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/51595dbe-5574-41ff-8fb7-05b17c9abbee%40googlegroups.com. > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/651c6fc0-5755-4a8d-9a16-ec20d04f4562%40www.fastmail.com.
