Thread: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Michael B. <mi...@zz...> - 2011-05-14 16:35:21
|
I'm assuming this is intentional behavior, as illustrated by the script below. Wondering what the rationale is for delaying the close of a connection until the object is garbage collected - in particular, this makes it difficult for applications that are attempting to manage the total number of TCP connections opened, where the actual connection object might still be referenced somewhere. Such an approach is even more problematic if and when cx_Oracle begins to be run on platforms such as Pypy where reference counting GC isn't used, as garbage collection can be delayed entirely for any amount of time.
This is cx_Oracle 5.1, script is tested on Mac OSX as well as Fedora 14. The script creates one connection, closes it, illustrates what network connections are set up to port 1521. Then del is called, network connections are illustrated again. On both platforms, the one ESTABLISHED connection goes to TIME_WAIT only after "del c" is called.
import os
import cx_Oracle
import time
print "Version:", cx_Oracle.version
c = cx_Oracle.connect(
dsn='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=xe)))',
password='tiger',
user='scott',
)
c.close()
time.sleep(.01)
print "-" * 20
os.system("netstat -nt | grep 1521")
del c
time.sleep(.01)
print "-" * 20
os.system("netstat -nt | grep 1521")
output on OSX:
Version: 5.1
--------------------
tcp4 0 0 127.0.0.1.1521 127.0.0.1.60111 ESTABLISHED
tcp4 0 0 127.0.0.1.60111 127.0.0.1.1521 ESTABLISHED
--------------------
tcp4 0 0 127.0.0.1.1521 127.0.0.1.60111 CLOSE_WAIT
tcp4 0 0 127.0.0.1.60111 127.0.0.1.1521 FIN_WAIT_2
output on Fedora 14 (this is the server where Oracle is running, so some 1521s are already present):
Version: 5.1
--------------------
tcp 0 0 66.228.40.238:48786 66.228.40.238:1521 ESTABLISHED
tcp 0 0 127.0.0.1:49302 127.0.0.1:1521 ESTABLISHED
tcp 0 0 127.0.0.1:1521 127.0.0.1:49302 ESTABLISHED
tcp 0 0 66.228.40.238:1521 66.228.40.238:48786 ESTABLISHED
--------------------
tcp 0 0 66.228.40.238:48786 66.228.40.238:1521 ESTABLISHED
tcp 0 0 127.0.0.1:49302 127.0.0.1:1521 TIME_WAIT
tcp 0 0 66.228.40.238:1521 66.228.40.238:48786 ESTABLISHED
|
|
From: Amaury F. d'A. <ama...@gm...> - 2011-05-14 16:46:34
|
Hi, 2011/5/14 Michael Bayer <mi...@zz...>: > I'm assuming this is intentional behavior, as illustrated by the script below. > Wondering what the rationale is for delaying the close of a connection until the object is garbage collected It's true that the .close() method only calls OCISessionEnd(). OCIServerDetach() is only called by the destructor. A former colleague already noticed this: after close(), the connection disappears from v$session, but is still present in v$process (or v$thread, I don't remember) Since a closed connection can't be reopened, does this makes sense to keep the connection alive? IMO close() should release all resources, especially on the database server... -- Amaury Forgeot d'Arc |
|
From: Michael B. <mi...@zz...> - 2011-05-14 16:56:41
|
On May 14, 2011, at 12:46 PM, Amaury Forgeot d'Arc wrote: > Hi, > > 2011/5/14 Michael Bayer <mi...@zz...>: >> I'm assuming this is intentional behavior, as illustrated by the script below. >> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected > > It's true that the .close() method only calls OCISessionEnd(). > OCIServerDetach() is only called by the destructor. > > A former colleague already noticed this: after close(), the connection > disappears from v$session, > but is still present in v$process (or v$thread, I don't remember) > Since a closed connection can't be reopened, does this makes sense to > keep the connection alive? > IMO close() should release all resources, especially on the database server... Specifically the issue we're having running SQLAlchemy unit tests, particularly the ones that test pools and connection configuration thereby by definition circumventing the usual practice of using a single connection pool, leave a few connections piled up which are closed, but in some cases are still in memory (fixing that now to seek and destroy absolutely every connection). What happens next is you start getting ORA-12519 errors - no more connection slots left in which to connect. What is particularly insidious about ORA-12519 is that even if the connection is fully closed, Oracle doesn't release the memory for a *full minute longer*, since apparently no message is sent to the listener to "deallocate" on close - its based on some kind of ping/timeout mechanism. |
|
From: Anthony T. <ant...@gm...> - 2011-05-14 23:12:59
|
On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc <ama...@gm...> wrote: > Hi, > > 2011/5/14 Michael Bayer <mi...@zz...>: >> I'm assuming this is intentional behavior, as illustrated by the script below. >> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected > > It's true that the .close() method only calls OCISessionEnd(). > OCIServerDetach() is only called by the destructor. I have just checked in changes to do exactly this. It calls OCIServerDetach() on close. Let me know if this resolves the problem for you. Thanks. Anthony |
|
From: Michael B. <mi...@zz...> - 2011-05-17 15:17:57
|
On May 14, 2011, at 7:12 PM, Anthony Tuininga wrote: > On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc > <ama...@gm...> wrote: >> Hi, >> >> 2011/5/14 Michael Bayer <mi...@zz...>: >>> I'm assuming this is intentional behavior, as illustrated by the script below. >>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected >> >> It's true that the .close() method only calls OCISessionEnd(). >> OCIServerDetach() is only called by the destructor. > > I have just checked in changes to do exactly this. It calls > OCIServerDetach() on close. Let me know if this resolves the problem > for you. Thanks. I had already worked around the issue on our end, but the new changes work great, thanks ! |
|
From: Anthony T. <ant...@gm...> - 2011-05-17 17:08:58
|
On Tue, May 17, 2011 at 9:18 AM, Michael Bayer <mi...@zz...> wrote: > > On May 14, 2011, at 7:12 PM, Anthony Tuininga wrote: > >> On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc >> <ama...@gm...> wrote: >>> Hi, >>> >>> 2011/5/14 Michael Bayer <mi...@zz...>: >>>> I'm assuming this is intentional behavior, as illustrated by the script below. >>>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected >>> >>> It's true that the .close() method only calls OCISessionEnd(). >>> OCIServerDetach() is only called by the destructor. >> >> I have just checked in changes to do exactly this. It calls >> OCIServerDetach() on close. Let me know if this resolves the problem >> for you. Thanks. > > > I had already worked around the issue on our end, but the new changes work great, thanks ! You're welcome. And thanks for following up. These changes will be included in the next release -- not sure when that will be yet, though. I have a few other things I'd like to take care of first. Anthony |
|
From: Tyler H. <tyl...@gm...> - 2012-10-24 15:15:10
|
Michael Bayer <mike_mp@...> writes: > >> 2011/5/14 Michael Bayer <mike_mp@...>: > >>> I'm assuming this is intentional behavior, as illustrated by the script below. > >>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected > >> > >> It's true that the .close() method only calls OCISessionEnd(). > >> OCIServerDetach() is only called by the destructor. > > > > I have just checked in changes to do exactly this. It calls > > OCIServerDetach() on close. Let me know if this resolves the problem > > for you. Thanks. > > I had already worked around the issue on our end, but the new changes work great, thanks ! What was the work around that you were using to get around this issue? |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X