session must close connection if begin fails #5034
Comments
shinberg
changed the title
zzzeek
added
the
question
|
Update: probably this issue occurs only when using the transaction level customization with event listeners: @event.listens_for(engine, "begin")
def do_begin(conn):
conn.execute("BEGIN EXCLUSIVE")Which I used in the software. I guess without usage of this hook there would be no such issues because this hook implicitly disables default exception handling of That might be the reason of dangling _ConnectionFairy finally. |
|
hi there - I'm not really following what you are experiencing, there is no "disabling of exception handling" implied by the code you illustrated. There is also no "dangling connection fairy" I'm aware of either as the ConnectionFairy is explicitly closed out when the connection is returned to the connection pool. The SQLite connection is detached from the object itself so even if it were floating around somehow, there's nothing attached to it. At the very least i would need to see a stack trace to illustrate what you are seeing and very likely a full MCVE for a complete understanding. Since what you're doing is in one of our recipes mentioned below I'll do a simple test right now. My suspicion is that you are following the recipe at https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl and perhaps didn't also disable SQLite's isolation level, so perhaps it is trying to BEGIN twice, or something, or perhaps that our recipe simply has an issue with threads such that we may need to add event handlers for commit/rollback as well. |
|
here's the test script, does not produce any error. Please see if you can modify this to illustrate the failure you are getting. I tried in Python 2.7 and 3.7, no issue. |
|
also if I remove the isolation level part, it still succeeds. the pool echo illustrates connections being returned properly. Thanks for your continued help! |
|
Sorry, Actually I use |
|
I've tried both: no failure, python 2 or 3. Please share Python version, SQlite version , stacktrace, thanks. |
|
I've tried with and without the isolation level setting and with and without emitting any SQL (as SQlite emits BEGIN under certain circumstances only), can't reproduce yet. |
|
That can help to reproduce. sqlite version I don't know since currently running on windows and pip installed some version on its own |
|
windows is obviously a huge factor for things like this |
|
are you running with any special Python GC flags ? |
|
Everything is possible, but actually I don't see the code where exception from BEGIN statement will be handled in this case. |
|
The traceback of exception that causes dangling connection on my env: |
|
Double checked, isolation level setting is currently looks like the following (seems to be correct): @sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, "connect")
def do_connect(dbapi_connection, _):
# disable pysqlite's emitting of the BEGIN statement entirely.
# also stops it from emitting COMMIT before any DDL.
dbapi_connection.isolation_level = None |
|
Tried your sample on my env - I got no issues too. try:
conn.execute("BEGIN IMMEDIATE")
except Exception:
raiseAnd set BP on raise, but - no database lock exception raised - that's the reason, need to get DB lock error somehow to reproduce it simply (or probably any other exception could work, but I'm not sure). |
|
Seeing as how this involves Windows and SQLite, there may be some commonality with #4946 |
|
Got it, just place sleep in another place: def do_a_thing():
if sys.version_info < (3,):
import thread
t_ident = thread.get_ident()
else:
t_ident = threading.get_ident()
counter = 0
while True:
counter += 1
s = Session(engine)
print(s.scalar("SELECT '%s %s'" % (t_ident, counter)))
time.sleep(0.5)
s.close()placing |
no |
|
I hope it helped to reproduce. @sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, "begin")
def do_begin(conn):
try:
conn.execute("BEGIN IMMEDIATE")
except Exception:
conn.close()
raiseBut I just interested whether there are some more elegant way to prevent from this. |
|
oh. OK, I see that your initial description talked about "database is locked" but you didn't illustrate how you were getting that or where it was happening or why. In the future, I only need a stack trace, no need to try to diagnose the problem, just the stack traces here (edit: and the error message that follows it also), thanks. fix will be in 1.3.12. |
zzzeek
changed the title
|
Mike Bayer has proposed a fix for this issue in the master branch: Close connection if begin fails https://gerrit.sqlalchemy.org/1611 |
|
Mike Bayer has proposed a fix for this issue in the rel_1_3 branch: Close connection if begin fails https://gerrit.sqlalchemy.org/1612 |
Fixed issue where by if the "begin" of a transaction failed at the Core engine/connection level, such as due to network error or database is locked for some transactional recipes, within the context of the :class:`.Session` procuring that connection from the connection pool and then immediately returning it, the ORM :class:`.Session` would not close the connection despite this connection not being stored within the state of that :class:`.Session`. This would lead to the connection being cleaned out by the connection pool weakref handler within garbage collection which is an unpreferred codepath that in some special configurations can emit errors in standard error. Fixes: #5034 Change-Id: I6502a55791d86845f34bc10889c218f00765dfdc (cherry picked from commit ff47115)
ok, I didn't want to create an issue without diagnosis because I was not sure that I use SA properly otherwise. |
Hi,
I was constantly experiencing the following exception while using sqlalchemy + sqlite:
SQLite objects created in a thread can only be used in that same thread.Actually I don't think that I used the SA constructions in some wrong way, I always insured all transactions are either committed/rolled back and closed as well.
And long story short I found the reason (I was frequent reason for me at least ): If the
database is lockedexception occurs at the following line then the underlyingConnectionand_ConnectionFairyobject is dangled.This in turn means that when
Session.close()is called - the dangling_ConnectionFairyis not released. And finally the noticed_ConnectionFairyobject is often released in another thread and causesSQLite objects created in a thread can only be used in that same thread.exception.What should I do to handle this? I didn't find anything that could help in the SA docs.
Please don't tell me that sqlite is not for concurrency and so on, it's not precisely like that, it is probably not the best DB for concurrency but knowing all proc and cons of this simple DB I should be able to use this.
And also, why didn't you store the connection (which is coded here) before beginning the transaction? It would solve the issue forever.
Thank you.
The text was updated successfully, but these errors were encountered: